External Merge Sort Spill Diagnosis #

A report’s Sort node reports Sort Method: external merge Disk: 4102208kB. Raising work_mem from 64 MB to 4 GB should fix it — except that the sort then reports quicksort Memory: 6104220kB, half again as much as the disk figure suggested, and the server runs out of memory when three such reports run at once.

Sort nodes are introduced in sort and hash node analysis. This page is about sizing them from the numbers the plan gives.

The Condition #

A Sort node reports one of three methods:

The disk figure is smaller than the memory the same sort would need. On disk, tuples are written in a compact form; in memory, the sort holds tuples plus per-tuple pointers and alignment overhead. A rule of thumb is that in-memory sorting needs roughly 1.5 to 2.5 times the reported disk volume, which is why sizing work_mem from the Disk figure alone under-provisions.

Two other factors inflate memory:

An external merge is not a failure. Sorting more data than fits in memory is legitimate, and the merge is efficient as long as the number of runs stays small. The question is whether the spill is avoidable — by sorting fewer or narrower rows, or by removing the sort entirely.

Disk figure versus memory needed The external merge reports 4,102 megabytes on disk. Sorting the same input in memory takes 6,104 megabytes, about 1.5 times as much. Sorting only the needed columns takes 1,204 megabytes. With an index providing the order, no sort memory is needed at all. reported Disk 4,102 MB on disk same sort in memory 6,104 MB narrow columns only 1,204 MB index-provided order no sort sizing work_mem from the Disk figure under-provisions by half

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE placed_at >= '2026-01-01' ORDER BY customer_id, placed_at;
Sort  (actual time=41204.6..44102.1 rows=18402110 loops=1)
  Sort Key: customer_id, placed_at
  Sort Method: external merge  Disk: 4102208kB
  Buffers: shared hit=412008 read=180422, temp read=512776 written=512776
  ->  Index Scan using orders_placed_at_idx on orders  (actual rows=18402110 loops=1)
Execution Time: 46104.8 ms
-- 18.4M rows, full width, sorted on two columns
-- temp read and written are equal: one pass of runs written and merged back

With enough memory:

SET work_mem = '8GB';

Sort  (actual time=28104.2..30102.6 rows=18402110 loops=1)
  Sort Key: customer_id, placed_at
  Sort Method: quicksort  Memory: 6104220kB
  Buffers: shared hit=412008 read=180422
Execution Time: 32104.1 ms
-- 6.1 GB in memory for a sort that wrote 4.1 GB to disk

With the sort removed by an index:

CREATE INDEX CONCURRENTLY orders_customer_placed_idx ON orders (customer_id, placed_at);

Index Scan using orders_customer_placed_idx on orders  (actual time=0.04..8104.2 rows=18402110 loops=1)
  Filter: (placed_at >= '2026-01-01'::date)
Execution Time: 9204.6 ms
-- no Sort node; rows arrive in order
Four fields on a spilling sort Four items are annotated. Sort Method external merge with a Disk figure gives the spilled volume. Temp read and written equal to each other indicate a single merge pass. The sort's startup time shows when the first row could be emitted. The quicksort memory figure after raising work_mem shows the real memory requirement. Sort Method: external merge Disk: 4102208kB the spilled volume temp read=512776 written=512776 one write-and-merge pass actual time=41204.6..44102.1 startup = the whole sort later: quicksort Memory: 6104220kB ≈1.5× the disk figure size work_mem from the memory figure, not the disk figure

Step-by-Step Resolution #

  1. Read the method and volume. external merge with a Disk figure is a spill; note the number.

  2. Ask whether the sort is needed at all. An index matching the ORDER BY — or the grouping key for a GroupAggregate — removes it entirely and is usually the best fix.

  3. Narrow the rows. Sorting SELECT * carries every column through the sort. Selecting only the needed columns, or sorting ids and joining back, can cut memory several-fold.

  4. If the sort must stay, size memory from a measurement: run it once with a large work_mem in a session, read the quicksort Memory: figure, and set the session or role allowance slightly above it.

  5. Budget the total. Multiply by concurrent sessions and, for parallel plans, by participants, using the arithmetic in pool size and the total work_mem budget.

  6. Accept the spill when the data genuinely does not fit. A single merge pass — temp read equal to temp written — is efficient; the alternative of allocating gigabytes per session is worse.

  7. Check for an unnecessary ORDER BY. Reports that sort millions of rows for a client that re-sorts them anyway are common.

Which fix fits this spill? Three cases. If an index can provide the ordering, remove the sort. If the rows are wide but the row count is modest, narrow the projection. If the data genuinely does not fit in reasonable memory, accept a single-pass external merge and size work_mem so the number of runs stays low. sort spilled — what now? index can supply the order remove the sort best outcome rows are wide narrow the projection less memory, same plan data is genuinely large accept one merge pass size work_mem raising work_mem to avoid every spill is rarely affordable

Before and After #

-- BEFORE: full-width sort of 18.4M rows
Sort Method: external merge  Disk: 4102208kB   temp written=512776       Execution Time: 46104.8 ms

-- AFTER: composite index provides (customer_id, placed_at) order
Index Scan using orders_customer_placed_idx  (no Sort node)               Execution Time: 9204.6 ms

Reading the merge pass count #

When a sort spills, it writes sorted runs and then merges them. If all runs can be merged in one pass, temp read and temp written are approximately equal and the cost is one extra write and read of the data. When work_mem is very small relative to the input, the sort needs multiple merge passes, and the temp figures grow to multiples of the data size — that is the case worth fixing, because each pass is another full write and read.

So the goal of raising work_mem for a large sort is usually not to eliminate the spill but to keep it to a single pass. That needs far less memory than holding the whole sort: enough for runs large enough that their count stays below the merge fan-in. In practice, a few hundred megabytes handles single-pass merges for inputs of tens of gigabytes, which is a much easier allocation to justify than the 6 GB the fully in-memory sort wanted.

PostgreSQL 17 and later report sort memory and disk usage for parallel workers individually with EXPLAIN (ANALYZE, VERBOSE), which makes it possible to see whether one worker’s share is disproportionate — usually a sign of skew in how rows were distributed.

Common Pitfalls #

Sizing work_mem from the Disk figure. Memory needs roughly 1.5 to 2.5 times that. Diagnostic signal: a spill that persists after raising memory to the disk figure. Fix: measure the quicksort Memory: value.

Global work_mem increases. Every node in every session gains the allowance. Diagnostic signal: memory exhaustion under concurrency. Fix: session or role scope.

Sorting wide rows. Width multiplies memory. Diagnostic signal: a large Memory: for a modest row count. Fix: narrow the projection.

Ignoring parallel multiplication. Each worker sorts with its own allowance. Diagnostic signal: memory spikes during parallel reports. Fix: budget per participant.

Frequently Asked Questions #

What does Sort Method: external merge mean? #

The sort could not fit its input in work_mem, so it wrote sorted runs to temporary files and merged them. The Disk figure is the volume written.

How much work_mem does a sort need to stay in memory? #

Roughly 1.5 to 2.5 times the reported external Disk figure, because in-memory sorting carries per-tuple overhead that the on-disk format does not. Measure it by running the sort once with a large allowance and reading the quicksort memory value.

Is an external merge sort always bad? #

No. Sorting more data than fits in memory is legitimate, and a single merge pass is efficient. The cases worth fixing are sorts that could be avoided with an index, sorts of unnecessarily wide rows, and spills that need multiple merge passes.

Up: Sort and Hash Node Analysis