When Your Dashboard Loads Like It's on Dial-Up

The Issue

There is a special kind of dread that comes with watching a dashboard spinner rotate for twelve seconds. The sales team clicks "filter by total call time," goes to make coffee, and comes back to find the page still thinking.

The root cause was textbook. CRM pipeline dashboards were running analytics queries against tables with millions of rows -- admin_user_call_log, admin_user_task, admin_activity -- and every filter click triggered expensive JOINs with full-table aggregations. Want "total call time per opportunity"? That is a SUM(duration) across millions of call log rows, joined to the opportunity table, computed on every single request. Want "latest task due date"? A DISTINCT ON (opportunity_id) subquery sorting the entire task table by due date. Every. Single. Time.

It worked great with 100 rows in development. Production had other plans.

The Goal

Sub-second analytics queries. Non-negotiable. But the constraint was equally firm: we could not sacrifice write performance, and the data needed to be near-real-time. Running REFRESH MATERIALIZED VIEW CONCURRENTLY on a cron every five minutes was out -- the tables were too large for a full recompute, and five-minute staleness is unacceptable when a sales rep just logged a call and wants to see the number update.

The Solution

The pattern: regular PostgreSQL tables acting as materialized views, kept in sync by PL/pgSQL trigger functions that incrementally update only the affected rows.

Step 1: Create a pre-computed summary table. Not a native MATERIALIZED VIEW -- a plain table called user_total_talk_time with columns user_id, total_talk_time, and updated_at. This is what the dashboard queries instead of aggregating millions of call log rows.

Step 2: Write a PL/pgSQL trigger function. This fires AFTER INSERT on admin_user_call_log. When a new call log is inserted, the trigger recomputes SUM(duration) for only that user and upserts the result into the summary table:

CREATE OR REPLACE FUNCTION trigger_refresh_user_total_talk_time()
RETURNS TRIGGER AS $trig$
DECLARE
  v_total_talk_time INT;
BEGIN
  IF NEW.user_id IS NOT NULL THEN
    SELECT COALESCE(SUM(duration) FILTER (WHERE duration > 0), 0)::int
    INTO v_total_talk_time
    FROM admin_user_call_log WHERE user_id = NEW.user_id;
 
    INSERT INTO user_total_talk_time (user_id, total_talk_time, updated_at)
    VALUES (NEW.user_id, v_total_talk_time, CURRENT_TIMESTAMP)
    ON CONFLICT (user_id) DO UPDATE SET
      total_talk_time = EXCLUDED.total_talk_time,
      updated_at = EXCLUDED.updated_at;
  END IF;
  RETURN NEW;
END;
$trig$ LANGUAGE plpgsql;

One row touched. One user recomputed. The dashboard query goes from a full-table scan to an indexed lookup.

Step 3: State tracking for batch backfill. You cannot just flip a switch on millions of existing rows. We created a mv_user_total_talk_time_state table with a single row tracking last_processed_call_log_id. A backfill loop processes 50,000 rows at a time, computing aggregates only for affected users in each batch, then advancing the cursor. No table locks, no timeouts.

Step 4: Replace the original query. The dashboard model swaps a gnarly inline subquery for a simple LEFT JOIN user_total_talk_time ON .... Done.

We applied this same pattern to three different analytics dimensions: total call time per user, latest due task per opportunity, and not-interested counts using ARRAY_AGG and FILTER clauses over JSONB activity data.

Architecture

Complexities Faced

DELETE handling is where things get interesting. For call time (append-only log), we only needed an AFTER INSERT trigger. But for tasks, a user can delete or update a task. The trigger must check both OLD.opportunity_id and NEW.opportunity_id, recompute for both, and -- critically -- if the recomputation returns zero rows, it must DELETE from the summary table rather than upsert a stale value. Getting the IF NOT FOUND THEN DELETE logic right required careful thought.

Batch backfill without locking. A single UPDATE across millions of rows would lock the table and tank production writes. The cursor-based batch approach -- process 50k, update cursor, repeat -- kept lock durations minimal and let the migration run alongside live traffic.

JSON aggregation in triggers. The latest_admin_activity table aggregates multiple activity types into arrays using ARRAY_AGG with FILTER and ORDER BY clauses, plus extracts JSONB fields like data->>'newStage' to compute not_interested_count. Writing that as a CTE inside a trigger function is not for the faint of heart.

Testing. You cannot unit test PL/pgSQL functions with Jest. Validation was integration-heavy: insert rows, check the summary table, delete rows, check again. The feedback loop is slow and the debugging experience is "read the Postgres logs and think hard."

What I Learned

Sometimes the best optimization is not a better query plan -- it is not running the query at all. Pre-computation via triggers is a sweet spot between stale cron-refreshed views and expensive real-time aggregations. PostgreSQL triggers are remarkably powerful, but they demand a different mental model: you are writing code that runs inside the database engine, with no debugger, no console.log, and transaction semantics that will humble you.

The pattern is not novel. But knowing when to reach for it -- when your tables cross the million-row threshold and your dashboards start feeling like they are powered by a hamster wheel -- that is the engineering judgment that matters.