When Month-End Took 22 Hours
Batch jobs that outgrow their data volume fail slowly and then all at once. A practical order of operations for reclaiming hours from a nightly close.
- PostgreSQL
- Query Optimization
- Batch Processing
Batch processing time is a lagging indicator of data growth. A job written when a table held fifty thousand rows behaves very differently at fifty million, and the degradation is gradual enough that nobody notices until the close no longer fits in the night.
Measure before you tune
The instinct is to add indexes. The discipline is to first find out which statements consume the time. In practice a small number of queries account for the overwhelming majority of a long batch, and they are rarely the ones people expect.
Query plans on production-scale data tell you the truth. Plans on a development database with a thousand rows tell you almost nothing — the planner will happily choose a sequential scan that is optimal there and catastrophic in production.
The order that works
- Profile first — find the handful of statements that dominate
- Fix access patterns before adding indexes; an N+1 in a loop out-costs any index
- Index deliberately, and measure write cost as well as read gain
- Batch in bounded chunks so memory and lock duration stay predictable
- Only then consider partitioning or archival for the largest tables
Archival is the cheapest optimization
Most batch jobs recompute over data that has not changed in years. Separating hot operational data from cold historical data often delivers a larger improvement than any query rewrite, because the fastest way to process a row is not to read it.
A nightly close that finishes in ten minutes instead of two hours is not just faster — it is re-runnable. That changes how safely you can respond to an incident.
Thoughts on this?
Always happy to talk through the engineering trade-offs.