
Every customer-facing dashboard I have seen starts the same way: a route handler, a GROUP BY, and the primary database. It ships in a week and it works. That is the trap — not that it is obviously wrong, but that it is obviously right for about eighteen months.
This post is about what breaks, why the standard fixes only buy time, and how to migrate incrementally instead of committing to a rewrite.
What Actually Breaks
The failure is not gradual. Three things go wrong roughly at once.
Buffer cache eviction. Your working set is the hot transactional data — recent orders, active sessions. A dashboard query scanning two years of history pulls cold pages into shared buffers and evicts the hot ones. The dashboard query is slow, which is expected. What is not expected is that unrelated transactional queries get slower for the next several minutes while the cache refills.
Connection and worker saturation. Long-running analytical queries hold connections. Under a pool, a handful of customers opening dashboards simultaneously can exhaust it, and now your API is timing out on requests that have nothing to do with reporting.
Vacuum interference. Long transactions hold back the xmin horizon, which stops autovacuum from cleaning up dead tuples. Bloat accumulates, and it accumulates on the tables you least want bloated.
The result is that a reporting feature degrades the core product, and the connection is not obvious from a latency graph unless you were already segmenting by query type.
Why the Usual Fixes Only Buy Time
Adding indexes. Helps a specific query. Every index costs write throughput on tables that are write-heavy. You end up trading transactional performance for reporting performance — and the next dashboard filter needs another one.
Read replicas. The standard answer, and genuinely useful. It removes the interference with transactional traffic. It does not make the queries fast, and you inherit replication lag: a customer completes an action, opens the dashboard and does not see it. Expect support tickets.
Materialised views. Closer to right, but REFRESH MATERIALIZED VIEW locks by default, and CONCURRENTLY needs a unique index and does a full rebuild. At scale you are recomputing two years of history to add one day of data, on a schedule, forever.
Each of these is a reasonable step. The problem is that they are steps along a path, and most teams stop at the one that made the pain go away rather than the one that solves it.
The Shape of the Answer
Dashboard traffic is overwhelmingly repetitive. The same metrics, the same time buckets, the same handful of filters, over and over. That repetition is the thing to exploit: compute the aggregates once as data arrives, and serve reads from a table that is already the answer.
CREATE TABLE metric_daily (
tenant_id uuid NOT NULL,
metric_key text NOT NULL,
day date NOT NULL,
dimension text NOT NULL DEFAULT '',
value numeric NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (tenant_id, metric_key, day, dimension)
);
A dashboard read is now an index scan over a few hundred rows instead of an aggregate over millions. The 500ms budget stops being a negotiation.
Two population strategies, and you will likely use both:
Incremental job. A scheduled task processes only rows changed since the last watermark and upserts into the rollup. Cheap, simple, and the lag is whatever your interval is.
Write-path increment. On the transaction that creates the event, increment the relevant rollup rows in the same database transaction. Real-time and exact, at the cost of coupling your write path to your metric definitions — which means a new metric requires a backfill and a code change.
Start with the incremental job. Move specific metrics to the write path only where freshness genuinely matters, such as anything a customer acts on immediately.

Migrating Without a Rewrite
The migration is per-metric, not big-bang.
-
Add telemetry first. Log every dashboard query with tenant, duration, rows scanned. You need to know which metrics are actually expensive rather than which ones you assume are.
-
Pick the worst one. Highest total cost, not highest per-query cost — a cheap query run constantly often outweighs an expensive rare one.
-
Build the rollup and backfill it. Backfill in batches with a sleep between them, off-peak. Backfills that run unthrottled cause the exact incident you are trying to prevent.
-
Dual-read and compare. Serve from the live query, compute from the rollup in parallel, log mismatches. Run for a week. You will find edge cases — timezone boundaries, late-arriving records, soft deletes.
-
Cut over, keep the old path behind a flag. Remove it a release later.
-
Repeat for the next metric.
Nothing here requires a new database, a new team or a platform decision. Each step is a day or two and independently valuable.
The Boundary Where This Stops Being Enough
Rollups work brilliantly for known metrics with bounded dimensionality. They stop working when customers can slice arbitrarily, because you cannot pre-compute a combinatorial explosion.
If ad-hoc exploration is genuinely a requirement, that is the point to introduce a columnar store — ClickHouse, DuckDB, or a warehouse — where wide scans are cheap enough that computing on read is viable again. That is a real architectural commitment with operational cost, and it is worth being honest with yourself about whether customers will actually explore or whether they will check five numbers.
Most of the time it is five numbers. Build for that first, and let usage data tell you whether exploration is real demand or a feature request nobody exercises.
The full picture — build versus embed versus buy, tenant isolation patterns, permissions design and what this costs over three years — is in our guide to product analytics dashboards.
Frequently Asked Questions
Is a read replica enough for customer-facing dashboards?
It removes interference with transactional traffic, which is the most urgent problem, but it does not make the queries fast and it introduces replication lag your customers will notice after taking an action. Treat it as a stopgap that buys you time to build rollups, not as the destination.
How do I handle late-arriving data in rollup tables?
Keep a watermark per metric and reprocess a trailing window — typically the last few days — on every run, upserting rather than inserting. This absorbs corrections and late events at modest cost. For data that can arrive arbitrarily late, you also need a periodic full recomputation of older periods, run infrequently and off-peak.
Should rollups live in the same database or a separate one?
Start in the same database, in their own schema. The read pattern is entirely different from your transactional tables but the operational simplicity of one database is worth a lot early on. Split it out when rollup storage or query volume starts affecting your primary workload, which is a measurable threshold rather than a guess.
How do I verify a rollup is correct before cutting over?
Dual-read: serve the live query to users while computing the rollup answer in parallel, and log every mismatch with its inputs. Run it for at least a week so you catch weekly patterns and month boundaries. The mismatches you find will mostly be timezone handling, soft deletes and late-arriving records, and each one is a real bug you would otherwise have shipped.
What if customers need to filter by arbitrary combinations?
Pre-aggregation cannot cover a combinatorial explosion, so at that point you need a columnar store where scanning wide is cheap. Before committing, check whether arbitrary filtering is real demand — instrument which filter combinations are actually used. In most B2B products the answer is a small, stable set, and the ad-hoc requirement turns out to be aspirational.