The Nightly Report Is Slow. Just Run It in One Big Transaction?
A nightly report holds one Postgres transaction open for hours and writes are bloating. A teammate wants a bigger box and higher timeouts. Defend the real fix.
Databasesthe decision you defend
A nightly analytics job holds one long-running Postgres transaction open for hours, and now writes are bloating and autovacuum can't keep up. A teammate says just give the DB more resources and raise timeouts so the long transaction can finish. What do you do, and why?
the situation
A nightly reporting job runs inside a single transaction that stays open for hours, and lately production sees growing table bloat and lock contention. During the day, previously fast OLTP queries are getting slower, indexes are ballooning, and autovacuum seems to be running constantly without ever catching up.
context
The report is a big analytics query wrapped in one BEGIN that is not committed until the whole thing finishes, hours later, on the primary database. A teammate proposes bumping the instance size and increasing statement and idle-in-transaction timeouts so the transaction is allowed to run to completion without being killed.
How this challenge works
Take a position on the decision above and defend it. A senior-engineer AI will push back over up to 4 rounds. When you are done, you are scored against a verified rubric so you can see exactly what a complete answer covers - these are learning prompts, not gotchas.