Stop disk-spilled sorts by tuning work_mem
A sort bigger than work_mem spills to temp files and does an external merge. Size work_mem for the query and a ~2 MB disk spill on 40,000 rows becomes an in-memory quicksort.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Every sort and hash gets work_mem bytes. When the data does not fit, PostgreSQL writes temp files and does an external merge, extra I/O that never shows up as a row count, only as time and temp bytes.
A Meridian report sorts 40,000 manifest lines by value and SKU. At work_mem = 64kB the sort spills about 2 MB to disk; with enough memory it stays a quicksort in RAM.
Simple terms
Every sort gets a memory budget called work_mem. If the rows being sorted fit inside it, the sort finishes quickly in RAM. If they do not, PostgreSQL writes chunks out to temporary files on disk and merges them back together, extra disk I/O that never appears as a row count, only as lost time. Giving the query enough work_mem keeps the whole sort in memory.
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 Query performance 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.