Right-size work_mem for hash aggregates
On PG16.14, 1MB work_mem made one GROUP BY spill through 161 hash batches plus a 6MB external sort. At 32MB it still used 5 hash batches; four concurrent clients wrote 269MB versus 46MB of temp data.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
work_mem is a budget per sort/hash operation, not per connection. One plan can allocate it several times, parallel workers get their own budgets, and many sessions can do that concurrently. Raising it reduces spill but can exchange disk pressure for RAM pressure.
Four clients aggregate one million ledger rows into 200,000 accounts at the same time. The lab compares 1MB and 32MB, captures EXPLAIN spill details, pg_stat_database temp-byte deltas, live temp files, and PostgreSQL process RSS.
Simple terms
A GROUP BY and its final ORDER BY are two separate memory consumers. At 1MB both overflow badly; at 32MB the sort fits but the hash still needs five batches. Multiply those operations by four clients (and by parallel workers when enabled) before deciding a global setting is safe.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Planner tuning runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.