Work Mem External Sort Spill
A query that sorts, DISTINCTs, or hash-aggregates a large set is far slower than the row count suggests and generates heavy temporary-file write/read I/O....
In 10 seconds
A reporting query that orders, deduplicates, or aggregates a large result set has gotten slow and is pounding the disk, even though the data easily fits in RAM. The query is correct and returns the right rows -- it has just quietly stopped sorting in memory. PostgreSQL gives every sort and hash operation a fixed memory budget called work_mem, which defaults to a tiny 4MB. When a sort's working set is larger than that budget, the executor does not raise an error: it falls back to an external merge sort that writes the data out to temporary files on disk and reads it back in merge passes. The result is a silent, fully-deterministic performance cliff that you only see if you read the plan or watch temp-file I/O. This recipe reproduces the spill on a 2,000,000-row table, shows the free one-query diagnosis and the premium fleet-wide spill ranking, fixes it by sizing work_mem, and pins a right-sized, narrow-scoped durable setting.
Why This Happens
work_mem is the amount of memory a single sort or hash node may use before it must spill to disk, and it is applied per node, per connection -- a query with three sorts across 100 connections can allocate work_mem up to 300 times over. To keep that worst case bounded the default is a deliberately small 4MB. A sort of 2,000,000 four-byte keys needs roughly 48MB of working memory (the plan reports 'quicksort Memory: 49153kB' once it fits). 48MB does not fit in a 4MB budget, so the executor switches to an external merge sort: it sorts what fits, writes sorted runs to temporary files, and merges those runs back together with more disk reads.
The data and the result are identical; only the method and the I/O change.
Because the working-set size is a deterministic function of the data, the spill reproduces byte-for-byte on every run and on every supported version. Hash nodes behave the same way but with a larger budget: they may use work_mem * hash_mem_multiplier (default 2.0) before spilling, which is why a hash aggregate and a sort over the same data can spill at different thresholds. Hash nodes behave the same way but with a larger budget: they may use work_mem * hash_mem_multiplier (default 2.0) before spilling, which is why a hash aggregate and a sort over the same data can spill at different thresholds.
Diagnose
Free Tier -- Basic Approach
The free diagnosis is a single EXPLAIN (ANALYZE) read next to one SHOW. The plan already names the problem -- 'external merge' with a Disk figure -- and putting work_mem beside it makes the cause self-evident: the sort needed far more scratch space than the budget it was given.
SHOW work_mem;
work_mem ---------- 4MB (1 row)
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS OFF) SELECT k FROM big ORDER BY k;
QUERY PLAN ----------------------------------------------------- Sort (actual rows=2000000 loops=1) Sort Key: k Sort Method: external merge Disk: 23528kB -> Seq Scan on big (actual rows=2000000 loops=1) (4 rows)
SHOW work_mem reports 4MB; the EXPLAIN (ANALYZE) on the same query reports 'Sort Method: external merge Disk: 23528kB'. A 4MB budget against a ~23MB working set means the sort had to spill.
Anything a read-only role can run reproduces this: one SHOW and one EXPLAIN (ANALYZE). The decision it drives is binary -- either raise the budget so the sort fits, or shrink the sort (an ordering index or a tighter predicate) so it does not need the memory.
Fix
The fix is to hand the sort enough memory to run in RAM. Raising work_mem for the session lets the executor choose an in-memory quicksort instead of an external merge -- same query, same rows, zero temp files.
SET work_mem = '512MB';
SET
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS OFF) SELECT k FROM big ORDER BY k;
QUERY PLAN ----------------------------------------------------- Sort (actual rows=2000000 loops=1) Sort Key: k Sort Method: quicksort Memory: 49153kB -> Seq Scan on big (actual rows=2000000 loops=1) (4 rows)
SET work_mem = '512MB' (comfortably above the ~48MB the sort needs), then re-run the identical EXPLAIN (ANALYZE). The Sort Method flips from 'external merge Disk: 23528kB' to 'quicksort Memory: 49153kB': the entire sort now stays in memory and no Disk line appears.
Session scope is the right place to verify the fix; PREVENTION pins a durable, right-sized value at a narrow scope so it survives reconnects without being applied cluster-wide.
Verify
It is not enough that the plan says quicksort -- prove the spill is actually gone by measuring it. Under the raised budget, reset pg_stat_statements, re-run the identical query, and read temp_blks_written back: it is zero.
SET work_mem = '512MB'; EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS OFF) SELECT k FROM big ORDER BY k;
SET
QUERY PLAN
-----------------------------------------------------
Sort (actual rows=2000000 loops=1)
Sort Key: k
Sort Method: quicksort Memory: 49153kB
-> Seq Scan on big (actual rows=2000000 loops=1)
(4 rows)
SELECT pg_stat_statements_reset(); SELECT k FROM big ORDER BY k;
pg_stat_statements_reset -------------------------- (1 row)
SELECT substring(query for 40) AS statement,
calls,
temp_blks_written
FROM pg_stat_statements
WHERE query LIKE 'SELECT k FROM big%';
statement | calls | temp_blks_written ------------------------------+-------+------------------- SELECT k FROM big ORDER BY k | 1 | 0 (1 row)
With work_mem = '512MB' the EXPLAIN (ANALYZE) again shows 'quicksort Memory: 49153kB'. Then reset pg_stat_statements, re-run the bare query, and query temp_blks_written for it: the value is 0.
Same query, same 2,000,000 rows, but not a single temp block written -- the spill is eliminated, not merely hidden. Reading the counter back (rather than trusting the plan label) is what turns 'looks fixed' into 'measured fixed'.
⏪ Rollback
The session-scope fix (SET work_mem = '512MB') is automatically discarded when the session ends; RESET work_mem or opening a new connection reverts it immediately. The durable fix (ALTER DATABASE ... SET work_mem = '64MB') is reverted with ALTER DATABASE <db> RESET work_mem, which removes the row from pg_db_role_setting; existing sessions keep whatever value they connected with, and new sessions fall back to the cluster default. Nothing about either change is destructive -- no data is touched, no rewrite occurs, and the setting affects only how future sorts and hashes choose between memory and disk.
📦 Version Matrix (PG 14--18)
The spill and its fix are core sort-executor behaviour, not version-specific. Across PostgreSQL 14 through 18 the identical 2,000,000-row sort spills to an external merge under the default work_mem and switches to an in-memory quicksort once the budget is raised.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=external merge | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=external merge | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=external merge | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=external merge | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=external merge | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Is an external merge sort a bug or data corruption?
No. It is a correct, intentional fallback: PostgreSQL guarantees the sort completes regardless of how little memory it is given by spilling to temporary files. The rows returned are identical to an in-memory sort. The only cost is speed and disk I/O, which is why it is a silent performance issue rather than an error.
How big should I make work_mem?
Size it to the peak in-memory requirement of the sorts you actually want to keep in RAM -- the 'quicksort Memory: NNNNNkB' figure from EXPLAIN (ANALYZE) is a direct measurement of that need (here ~48MB, so 64MB is a safe durable value). Then bound the total: roughly max_connections * (typical concurrent sort/hash nodes per query) * work_mem should stay well under available RAM. When in doubt, raise it at database or role scope for the heavy workload rather than cluster-wide.
Why does the Disk figure (23528kB) differ from the in-memory figure (49153kB)?
They measure different things. 'Memory: 49153kB' is the peak RAM an in-memory quicksort of the whole set needs. 'Disk: 23528kB' is the peak temporary-file space the external merge sort uses, which is the on-disk representation of the sorted runs and is more compact than the in-memory tuple layout. Both describe the same 2,000,000 rows; one is the memory footprint, the other the spill footprint.
Can I tell which queries are spilling without EXPLAIN-ing each one?
Yes -- that is the premium step. pg_stat_statements records temp_blks_written per normalized statement, so a single query ranks every statement the instance has run by how much it spilled to disk. You find the worst offenders fleet-wide without touching the application, and you can wire the same query into monitoring as a standing guard.
Does setting log_temp_files help?
Yes, as a passive tripwire. Setting log_temp_files = 0 logs a line every time any backend creates a temporary file, including the size. It is the cheapest way to be alerted that spilling is happening at all, complementing pg_stat_statements (which tells you which statement) and EXPLAIN (ANALYZE) (which tells you the method and budget).