The work_mem trap · PostgreSQL

work_mem is the setting most often raised on the strength of advice rather than measurement, and the advice contains two claims that deserve testing: that spilling to disk is very expensive, and that the risk of raising it is manageable by arithmetic.

Testing the first claim

A sort of 2,000,000 rows from a 215 MB table. Three consecutive runs at each setting, warm cache, on local NVMe.

Read-only / Safework_mem = 4MB, the default— Captured on 18.6. Three runs, showing the Sort Method line and the total.

$ psql -U postgres -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON) SELECT count(*) FROM (SELECT * FROM ordersx ORDER BY amount, created_at) s"

 Sort Method: external merge  Disk: 50896kB   Execution Time: 1142.022 ms
Sort Method: external merge  Disk: 50896kB   Execution Time: 1142.370 ms
Sort Method: external merge  Disk: 50896kB   Execution Time: 1147.128 ms

Read-only / Safework_mem = 1GB, a 250-fold increase— Captured on 18.6 immediately afterwards. Same query, same data, same cache state.

$ psql -U postgres -c "SET work_mem='1GB'; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON) SELECT count(*) FROM (SELECT * FROM ordersx ORDER BY amount, created_at) s"

 Sort Method: quicksort  Memory: 111653kB     Execution Time: 1166.916 ms
Sort Method: quicksort  Memory: 111653kB     Execution Time: 1168.633 ms
Sort Method: quicksort  Memory: 111653kB     Execution Time: 1182.044 ms

The in-memory sort was slower. 1142–1147 ms spilling, 1167–1182 ms in memory, consistently across three runs each.

Two further readings from the same capture.

The in-memory sort needed 111,653 kB to sort data that occupied 50,896 kB on disk. More than twice as much, because the in-memory tuple representation carries overhead the on-disk sort format does not. Sizing work_mem from the Disk: figure of a spill will under-provision by roughly that factor.

The spill was visible in the plan and in the log:

Buffers: shared hit=570 read=16103, temp read=12723 written=12751
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp2684.0", size 52117504

Testing the second claim

The arithmetic usually offered is max_connections × work_mem. Lesson XI-01 showed why it understates: work_mem is granted per memory-consuming node per participant, and one plan can contain several such nodes running across a leader and its workers.

There is no formula that gives a safe cluster-wide value, because the multiplier depends on plans you cannot enumerate.

Scope it per role, not cluster-wide

-- conservative default for everyone
ALTER SYSTEM SET work_mem = '4MB';

-- generous for the role that needs it
ALTER ROLE reporting SET work_mem = '256MB';

-- or per session, for one known query
SET work_mem = '512MB';
SELECT … ;
RESET work_mem;

work_mem is user context, so all three work. The per-role form is almost always the right shape: an analytics role running four concurrent queries at 256 MB is a bounded, understood commitment, while the same value applied to two hundred application connections is not.

For an application that occasionally needs more, setting it per transaction around the specific statement is better still, because it cannot leak into every other query the connection runs afterwards.

Reading what spilled

Plan line	Means
`Sort Method: quicksort Memory: N`	Fitted in memory
`Sort Method: top-N heapsort Memory: N`	A `LIMIT` allowed only the top N to be kept — cheap, no spill
`Sort Method: external merge Disk: N`	Spilled
`Batches: 1 Memory Usage: N`	A hash node that fitted
`Batches: 5 … Disk Usage: N`	A hash node that spilled into five passes
`Buffers: … temp read=N written=N`	Temporary file I/O, in 8 kB blocks

Turn on the logging so spills are recorded without anyone having to be watching:

ALTER SYSTEM SET log_temp_files = 0;   -- log every temporary file
SELECT pg_reload_conf();

The default is -1, meaning never. Zero logs all of them, and on a healthy system that is a small number of lines. Setting it to a size threshold such as '10MB' is a reasonable compromise on a busy cluster.

`hash_mem_multiplier`

Hash nodes — hash joins and hash aggregates — get work_mem × hash_mem_multiplier rather than plain work_mem. The default is 2.0 in PostgreSQL 18.

Read-only / Safethe multiplier deciding whether a hash aggregate spills— Captured on 18.6. work_mem fixed at 16MB, 200,000 groups, sorting disabled to force a hash.

$ psql -U postgres -At -c "SET work_mem='16MB'; SET hash_mem_multiplier=N; SET enable_sort=off; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT customer_id, count(*) FROM ordersx GROUP BY customer_id"

  hash_mem_multiplier=1.0 :  Batches: 5  Memory Usage: 16441kB  Disk Usage: 1568kB
hash_mem_multiplier=2.0 :  Batches: 1  Memory Usage: 16409kB
hash_mem_multiplier=8.0 :  Batches: 1  Memory Usage: 16409kB

At 1.0 the aggregate spilled into five batches. At the 18 default of 2.0 it did not. Raising it further changed nothing, because it already fit.

Why the separate multiplier exists: hash nodes benefit disproportionately from extra memory, because a hash table that fits avoids re-reading its input entirely, whereas a sort that spills still does the same comparisons and merely writes and re-reads runs. Giving hashes more without giving sorts more is a better trade than raising both.

If a workload’s spills are all hash nodes, raise hash_mem_multiplier rather than work_mem — it is the more targeted change.

Why hash aggregates could once exceed work_mem without spilling, and what changed

Before PostgreSQL 13, HashAggregate had no spill mechanism. If the planner estimated the hash table would fit in work_mem it chose the plan, and if the estimate was wrong the node simply grew past work_mem until the query finished or the server ran out of memory.

This is the mechanism behind a specific and once-common outage: a query that had worked for years killed the server after a data change, because n_distinct drifted, the planner underestimated the group count, and the hash table grew without bound. The OOM killer, from lesson XI-06, usually ended it.

Since 13, hash aggregates spill to disk like hash joins, which is what the Batches and Disk Usage figures in the capture above report. The failure mode is now a slow query rather than a dead server.

Two things this changes for an operator.

An upgrade from 12 or earlier can make queries slower, in exchange for making them survivable. A query that used to blow through work_mem silently now spills and takes longer. That is the correct trade and it is worth expecting rather than being surprised by, because it looks like a regression.

n_distinct accuracy matters more for hash aggregates than for most things, since it is what the group count is estimated from. Lesson VIII-07 covers overriding it, and this is the case where doing so occasionally pays.

The same estimate drives the choice between HashAggregate and GroupAggregate in the first place. A badly underestimated group count picks a hash and then spills; a badly overestimated one picks a sort that was not needed.

What to take from this

Measured: a 250-fold work_mem increase made a large sort slightly slower on warm NVMe. Test the claim on your own storage.
An in-memory sort needed 2.2× the memory of the corresponding disk spill. Do not size from the Disk: figure.
No formula gives a safe cluster-wide value. Scope per role, or per transaction.
log_temp_files = 0 records spills without anyone watching. The default is never.
hash_mem_multiplier is the targeted setting when the spills are hash nodes; the 18 default of 2.0 prevented a spill that 1.0 caused.
Hash aggregates spill since 13. Before that they grew until the server died.

Cross-course references

Linux for Production Sysadmins — Part XL (Memory Performance) covers what actually happens as a host approaches its memory limit, which is the failure this setting can cause.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting temporary-file bytes, which is the measurement that should drive this setting rather than a guess.