Catch queries spilling to temp files
When a sort or hash exceeds work_mem it spills to disk as an 'external merge'. EXPLAIN shows the Sort Method: a 64kB work_mem spills 3600kB to disk; 256MB keeps it an in-memory quicksort.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A sort or aggregate that does not fit in work_mem writes temporary files to disk, which is far slower and shows up as temp_files/temp_bytes in pg_stat_database. EXPLAIN's 'Sort Method' line tells you exactly when this happens.
Meridian sorts 300,000 shipment weights. With a tiny work_mem the sort spills to disk (external merge); with enough work_mem it stays an in-memory quicksort, the plan labels each.
Simple terms
A sort or a hash gets a memory budget called work_mem. When the data is bigger than that budget, PostgreSQL writes the overflow out to temporary files on disk and merges it back, much slower, and easy to miss. EXPLAIN spells it out on the "Sort Method" line: an "external merge" means it spilled to disk, while a "quicksort" means it all stayed in memory. That one line tells you whether more work_mem would help.
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 Observability 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.