A dashboard that loads in 4.2 seconds from a read replica doesn’t need a warehouse. I’d say the same at 8 seconds. Somewhere past 15 I’d start measuring, and I still wouldn’t buy anything yet.
The move off the operational database tends to get decided on a feeling. The database seems big, or the last company had a warehouse. I’d rather decide it the way I’d decide a disk upgrade: readings against thresholds that were written down before anyone got annoyed. Five readings cover it, and the split starts when three of them trip.
How far a replica and a few rollups go
The top line of the diagram is cheap to build. A streaming read replica takes every analytics connection, so a clumsy query competes with other reports and leaves the app alone. A reporting schema of views gives the BI tool names that hold still when the app’s tables change. Heavy aggregates become materialized views, refreshed at night. Partition the biggest table by month, and a query for last quarter reads three partitions and skips the rest.
Two mechanics decide how long that lasts.
A replica is read-only. REFRESH MATERIALIZED VIEW runs on the primary, and so does every load of outside data. The results reach the replica through the WAL like any other write. Whatever analytics wants stored, the primary stores first.
The second one is slower to show up. A long query on the replica holds a snapshot open while WAL keeps arriving. When replay has to remove rows that query can still see, Postgres holds replay back, which is lag. Once max_standby_streaming_delay is used up, 30 seconds by default, it cancels the query. Raise the delay and reports finish while the replica falls behind. Turn on hot_standby_feedback and the primary keeps those dead rows around, so the cost moves there as bloat. Every option sends the bill somewhere.
Five signals, each with a line to cross
| Signal | Trips when | While it’s the only one, try |
|---|---|---|
| Replica lag under long queries | Peak replay delay passes 5 minutes | A second replica for long reports, with a generous standby delay |
| Scan time on the largest table | The slowest regular query on it passes 15 seconds, with partitions and rollups in place | A rollup at the grain that query groups by |
| Outside sources to join | There are five or more | Loading one or two as plain tables, and stopping there |
| History the live tables can’t keep | A recurring report needs values the app overwrote or deleted | A nightly snapshot table, while it stays small next to the live data |
| An analytics query pages someone | It happens once | statement_timeout on the BI role, and no analytics on the primary |
One or two tripped signals get the fix in the third column, and I’d take that fix every time. At three I stop tuning. By then the fixes are stacking up on the primary: more refreshes and more loads, all of it WAL that every replica has to replay.
The thresholds are my defaults, and I’d expect you to move them. Five minutes is as stale as I’d let a schedule screen get if it reads the same replica. Fifteen seconds is about where I’d expect people to stop waiting and ask for an export. Five sources means five load jobs writing to a database whose day job is the app. I count the page as one signal like the other four. One query that woke someone up is a missing timeout, and that’s an afternoon’s work.
What the signal monitor runs
The lower line of the diagram starts at a signal monitor. It’s a scheduled job that runs these and keeps the results.
-- 1. Replica lag. On the replica, every minute; keep the peak.
SELECT CASE
WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN interval '0 seconds'
ELSE now() - pg_last_xact_replay_timestamp()
END AS replay_delay;
-- 2. Slowest statements from the BI role. On the replica, which keeps
-- its own pg_stat_statements. Column names are PostgreSQL 13 and later.
SELECT left(s.query, 80) AS statement,
s.calls,
round(s.mean_exec_time::numeric / 1000, 1) AS mean_s,
round(s.max_exec_time::numeric / 1000, 1) AS max_s,
s.shared_blks_read
FROM pg_stat_statements s
JOIN pg_roles r ON r.oid = s.userid
WHERE r.rolname = 'bi_reader'
ORDER BY s.mean_exec_time DESC
LIMIT 10;
-- 3. Scan time on the largest table. Take the worst statement from (2)
-- that reads it, run it on the replica, and note "Execution Time".
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.clinic_id,
count(*) AS visits,
count(c.claim_id) AS claimed
FROM appointments a
LEFT JOIN reporting.claims c ON c.appointment_id = a.appointment_id
WHERE a.starts_at >= now() - interval '3 years'
GROUP BY a.clinic_id;
-- 4. Rollup refresh duration. On the primary, where the refresh runs.
-- The job has to send REFRESH as its own top-level statement.
SELECT s.query,
s.calls,
round(s.max_exec_time::numeric / 1000) AS max_s
FROM pg_stat_statements s
WHERE s.query ILIKE 'refresh materialized view%'
ORDER BY s.max_exec_time DESC;
Refresh duration isn’t on the scorecard. I’d still track it because it’s primary time, and when it stops fitting in the night the rollup fix is used up. The other three signals don’t come from SQL. Sources and history come out of the request queue, and the page comes out of the incident log.
Fernhill Clinics, twelve months apart
Fernhill is invented. I wanted a filled-in scorecard and wasn’t going to pass one off as a real company’s numbers.
At the first reading, Fernhill runs a 60 GB scheduling database. Its appointments table holds 210 million rows, partitioned by month. The slowest dashboard takes 4.2 seconds on the replica and the nightly rollups refresh in 6 minutes. Lag peaks at 40 seconds. Two small outside sources load as tables. Nothing trips. A warehouse at this point would mean a pipeline to run and a second copy of scheduling data to secure, all to speed up a dashboard nobody is waiting on.
A year later, finance wants appointments reconciled against nine outside sources, insurance claims and payroll among them. The claims join goes row by row across three years, so partition pruning barely helps and no rollup can stand in for it. It takes 31 seconds. To let month-end reports finish, someone raised the standby delay, and lag now peaks at 14 minutes. For those 14 minutes the front desks, which read the same replica, are looking at an old schedule.
That’s three. History hasn’t tripped, because the app already logs every status change, and nobody has been paged.
What moves at three, and what stays
The nine sources move to a columnar warehouse, along with a copy of appointments. It reads only the columns a query names, which suits a wide scan far better than a row store does. Short incremental extracts from the replica feed it, so the primary takes on no new load. They do tie the warehouse to the app’s column names, and last month I wrote up how to drop a column without breaking that reader.
The replica keeps what it’s good at: today’s schedule and anything else that has to be seconds fresh. With the long reports gone, the standby delay goes back to its default.
Then I’d read the scorecard again after the first month-end on the new setup. Lag should be back under a minute. If it isn’t, the long queries never moved, and somebody is still pointing the old reports at the replica.