work_mem, sorts and spills

ConceptAdvanced3 min read15 min hands-on#storage#query-planning

The idea in 30 seconds

Sorts and hash tables get work_mem of memory each, 4 MB by default; when they need more, they write to temporary files instead. EXPLAIN ANALYZE shows it as "external merge" or extra batches. A spill is often cheaper than its reputation, so measure before raising the limit, and know that the limit applies per operation, not per query.

Helps to know first: How a table is stored on disk

Where a sort keeps its rows

A sort needs somewhere to hold rows while it orders them. PostgreSQL gives each sort, and each hash table for a hash join or hash aggregation, up to work_mem of memory, 4 MB by default. If that is not enough, it writes sorted runs to temporary files and merges them: a spill.

EXPLAIN ANALYZE shows which happened:

   Sort Method: external merge  Disk: 14160kB      -- spilled to temporary files
   Sort Method: quicksort  Memory: 43327kB         -- fitted in memory

For a hash aggregation, a spill shows up as more than one batch:

   Batches: 17  Memory Usage: 8345kB  Disk Usage: 47416kB

What a spill costs

PredictSorting 800,000 rows

800,000 integers are sorted with work_mem at 4 MB, so the sort spills 14 MB to disk. Raising work_mem to 64 MB lets it run in memory. How much faster does the query get?

About 10%. Two runs of each, PostgreSQL 18.3 on a laptop with a fast SSD:

`work_mem`	Sort method	Execution time
4 MB	external merge, 14 MB on disk	217 ms, 217 ms
64 MB	quicksort, 43 MB in memory	202 ms, 195 ms

The temporary files were small and went to a fast SSD; they may well never have left the operating system’s cache. So the spill was cheap. On a slow or busy disk the gap can be much larger, which is why you measure on your own hardware rather than assume. A hash aggregation over the same rows behaved similarly: 17 batches and 47 MB on disk took 411 ms at 4 MB; at 64 MB it ran as a single batch using 90 MB of memory and took 349 ms.

Memory per operation, not per query

work_mem limits each sort and hash operation, and one query can run several at once. With work_mem set to 64 MB, one three-table query on our test data produced this:

 Sort                               -- external merge, 68 MB on disk
   ->  Hash Join
         ->  Hash Join
               ->  Hash             -- 64 MB in memory
         ->  Hash                   -- 36 MB in memory

Two hash tables held 100 MB between them, and the sort still needed more and spilled. A single hash table can even exceed work_mem: hash operations get work_mem × hash_mem_multiplier, which is 2.0 by default, and the hash aggregation in the previous example used 90 MB under the same 64 MB setting.

Multiply that by every connection that might run a query like this at the same moment. The documentation says it plainly: the total memory used could be many times work_mem.

Check yourself

What does Sort Method: external merge Disk: 14160kB tell you?
Show answer
The sort needed more memory than work_mem allowed, so it wrote about 14 MB of sorted runs to temporary files and merged them.
I could explain this without looking
Why can one query use far more memory than work_mem?
Show answer
work_mem applies to each sort and hash operation separately, a query can run several at once, and hash operations may use work_mem × hash_mem_multiplier. The same query running in many connections multiplies it again.
I could explain this without looking
Raising work_mem from 4 MB to 64 MB made the sort only 10% faster. Why so little?
Show answer
The spilled data was small and went to a fast SSD, and probably never left the operating system’s cache, so the temporary files cost little. Spills cost more on slower or busier storage.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 19.4 Resource Consumption

Primary· §19.4.1 Memory: work_mem, hash_mem_multiplier

work_mem (default 4 MB) is the memory a sort or hash operation may use before writing temporary files; a query may run several such operations at once, and sessions run concurrently; hash operations may use work_mem times hash_mem_multiplier (default 2.0).

PostgreSQL documentation: 14.1 Using EXPLAIN

Primary· §14.1.2 EXPLAIN ANALYZE

EXPLAIN ANALYZE reports the sort method and memory or disk used by sort and hash nodes.

PostgreSQL documentation: 23.1 Locale Support

Primary· §23.1 Locale Support

Locales other than C or POSIX have a performance impact and slow character handling.