The Nightly Report Is Slow. Just Run It in One Big Transaction?
Scenario
Every night an analytics job runs against the Postgres primary, and it wraps hours of reads and aggregations in a single long transaction. The consequences show up in daylight: tables and indexes keep growing, autovacuum runs but can't reclaim space, OLTP queries during business hours slow down, and occasionally something blocks on a lock the report is holding. The job frequently overruns into the morning, so "nightly" is increasingly a courtesy title.
The proposal on the table works with the design instead of against it: give the database bigger hardware, and raise the statement and idle-transaction timeouts so the report can always run to completion without being killed. The transaction stays; the environment adapts around it.
The Quick Fix on the Table
Scale the instance up and lift the timeouts so nothing interrupts the big transaction. No changes to the report code, and the overnight failures from timeout kills stop immediately.
The quick fix is on the table and the room is waiting for your call. Would you sign off on it? Take a position and justify it — out loud or on paper — before revealing the analysis.
Why the Quick Fix Fails
- A long transaction freezes vacuum for the whole database. Postgres's MVCC keeps old row versions until no transaction can still see them. One transaction holding an old snapshot pins the
xminhorizon — autovacuum dutifully runs and reclaims almost nothing, on every table, not just the ones the report touches. No hardware size changes this; it is how visibility works. - Bloat compounds and outlives the night. Dead tuples that can't be reclaimed mean tables and indexes grow, cache hit rates fall, and every daytime OLTP query scans through more garbage. You pay for the nightly transaction all day, and next night starts from a fatter baseline.
- Raising timeouts removes the last circuit breaker. Today a runaway report eventually gets killed. With generous timeouts, a bad plan or an unexpected data volume holds its snapshot (and its locks) indefinitely — the protective rail is exactly what the quick fix deletes.
- Bigger hardware feeds the problem, then meets it again. More memory and IOPS make the symptoms tolerable for a quarter or two while data volume grows. The mechanism — snapshot pinning and lock duration scaling with runtime — is untouched, so the same wall returns at a higher instance price.
- It entrenches analytics on the OLTP primary. Accommodating the report on the primary deepens the coupling that caused the pain, making the eventual separation of workloads harder and more political.
The interviewer nods: “Fine, the quick fix is off the table. So what exactly would you do — step by step?” Sketch your plan before revealing the approach.
The Right Approach
- Measure the damage to make the case. Check the oldest transaction age (
SELECT max(now() - xact_start) FROM pg_stat_activity), dead-tuple counts (pg_stat_user_tables.n_dead_tup), and table bloat estimates. Correlate the nightly job window with daytime latency graphs — the before/after picture usually ends the debate. - Break the job into short, committed batches. Process in chunks (by ID range, date range, or
LIMIT ... OFFSET-free keyset pagination) and commit each batch. Each commit releases the snapshot and locks, letting vacuum reclaim as you go. If the report needs a consistent point-in-time view, take it once — export a snapshot ID or materialize the needed source rows to a working table quickly, then compute from that at leisure. - Precompute instead of re-deriving. Turn the heavy aggregations into incremental work: a materialized view refreshed off-peak (
REFRESH MATERIALIZED VIEW CONCURRENTLY), or rollup tables updated by small periodic jobs. A report that reads precomputed rollups takes minutes, not hours. - Move analytics off the primary. Point the report at a read replica (with
hot_standby_feedbackweighed carefully, since it exports the same vacuum-pinning problem to the primary) — or better, ship data to a proper analytical store (warehouse, columnar engine) via logical replication or CDC, and let OLTP be OLTP. - Reinstate guardrails, tighter not looser. Set
idle_in_transaction_session_timeoutand a sanestatement_timeoutfor the reporting role specifically, and alert on any transaction older than a threshold (e.g. 15 minutes) on the primary. Long transactions should page someone, not be accommodated. - Verify with the same metrics. After the redesign, dead tuples should be reclaimed nightly, table growth should flatten, and the daytime p95 should lose its morning hangover. Those graphs are the acceptance test.
Final pushback: “Your plan costs more time and money than the quick fix. Convince me.” How do you defend your position under pressure?
How to Defend It
- "The report isn't just slow — it's holding a snapshot that stops vacuum from cleaning up the entire database for hours. Every daytime query pays for that. Bigger hardware makes the bloat roomier, not smaller."
- "Raising the timeouts deletes our only circuit breaker. The night the report goes wrong, it will hold its locks and snapshot until someone wakes up and kills it by hand."
- "Batching with regular commits gets the same numbers out — the difference is that vacuum reclaims space as we go and locks live for seconds instead of hours. This is a report-structure change, not a rewrite."
- "If we precompute the aggregates into rollups or a materialized view, the 'nightly report' becomes a five-minute read. That's cheaper than any instance upgrade on the table."
- "Long-term, analytics doesn't belong on the OLTP primary at all. A replica or a warehouse fed by replication ends this class of incident instead of renegotiating with it every quarter."