work_mem and Spills: Where Sorts and Hashes Go to Disk

The one thing to understand first

work_mem is the memory budget for a single sort, hash, or similar operation — not per query and not per connection. A complex query can have many such operations running at once, each entitled to its own work_mem. With parallel workers, each worker also gets its own allotment. This multiplicative behaviour is why a seemingly modest work_mem can still exhaust RAM under load.

*work_mem is charged per operation, not per query — so the real ceiling is work_mem × concurrent nodes × parallel workers.* Set it too low and sorts and hashes spill to temp files; set it too high and a burst of concurrency OOMs the server. The whole skill is "modest global, generous local."

Sorts: quicksort vs external merge

The sort code (src/backend/utils/sort/tuplesort.c) keeps tuples in memory and quicksorts them if they fit in work_mem. If they do not, it switches to an external merge sort: it writes sorted runs to temporary files and merges them. EXPLAIN ANALYZE shows the difference directly:

-- In memory:
Sort Method: quicksort  Memory: 5421kB
-- Spilled to disk:
Sort Method: external merge  Disk: 102400kB

The moment you see external merge, the sort is doing disk I/O that more work_mem could eliminate.

Hash joins: build side must fit

A hash join builds a hash table on the smaller input. If that table exceeds work_mem, the executor switches to a batched (multi-pass) hash join, partitioning both inputs to temp files and processing batches. EXPLAIN ANALYZE shows Batches: 8 (more than 1 means spilling) and the peak memory used. A misestimated build side is the usual cause — the planner expected it to fit and it did not.

Hash aggregates can spill too

Modern PostgreSQL lets HashAggregate spill to disk when the grouping hash table exceeds work_mem (older versions would blow past the limit instead). You will see this on high-cardinality GROUP BY. If a hash aggregate spills heavily, either raise work_mem for that workload or reduce group cardinality.

Sizing it safely

A rough guard rail:

work_mem ≈ (RAM available for queries)
          / (max_connections * avg sort/hash nodes per query)

Because the global default applies to every operation in every session, keep the cluster-wide value conservative and raise it locally where it matters:

-- Per-session for an analytics job
SET work_mem = '256MB';

-- Per-role for a reporting user
ALTER ROLE analytics SET work_mem = '256MB';

Layer 3 — Watch it happen on your own database

-- Make spills visible in the logs and in the plan
SET log_temp_files = 0;          -- log every temp file with its size

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*) FROM orders GROUP BY customer_id ORDER BY 2 DESC;
--   Sort Method: external merge  Disk: 102400kB   <- spilling
--   HashAggregate ... Batches: 8  ...             <- spilling

SET work_mem = '256MB';          -- session-only: re-run and watch it fit
-- Cluster-wide spill volume:
SELECT datname, temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database;

Run the query, look for Sort Method: external merge or a hash join/aggregate with Batches > 1 — that is the operation doing disk I/O. Raise work_mem for the session and re-run: when the method flips to quicksort / Batches: 1, you have found the right size for that workload. pg_stat_database.temp_bytes shows the cluster-wide spill bill over time.

Layer 4 — The levers this hands you

Too low and queries grind on temp-file I/O; too high and concurrent queries OOM the server. The right approach is a modest global value plus targeted overrides:

• Conservative global default, sized roughly as RAM-for-queries / (max_connections × avg sort/hash nodes per query).
• Per-session or per-role overrides for known heavy work: SET work_mem = '256MB' or ALTER ROLE analytics SET work_mem = '256MB'.
• **hash_mem_multiplier** gives hash-based nodes more headroom than sorts without raising both.
• **log_temp_files = 0** turns spilling queries into log lines — the fastest way to find them in production.

Layer 5 — What an Oracle DBA should expect vs what they get

This is one of the biggest mental shifts for an Oracle DBA:

• **No pga_aggregate_target.* Oracle gives the instance a single global PGA budget and divides it automatically. PostgreSQL has no* global work-memory cap — work_mem is per operation, and the total is your responsibility to bound via connection limits and sane defaults.
• Spills go to temp files, like Oracle's TEMP tablespace, but you watch them through log_temp_files and pg_stat_database.temp_bytes rather than V$TEMPSEG_USAGE.
• Connection count is the multiplier. Because Oracle pools sessions (shared server) or manages PGA centrally, raw connection count matters less. In Postgres, process-per-connection plus per-node work_mem means a pooler (PgBouncer) is part of memory safety, not just throughput.
• "Automatic" memory management is manual here. There is no WORKAREA_SIZE_POLICY=AUTO; you size work_mem deliberately and override locally.

Key takeaway

work_mem caps a single sort/hash/aggregate; exceed it and tuplesort.c switches to external merge sort, hash joins go multi-batch, and hash aggregates spill — all to temp files. Diagnose with EXPLAIN ANALYZE (look for external merge and Batches > 1) and log_temp_files. Keep the global value modest because it is charged per operation across every concurrent session, and raise it locally for the few heavy queries that need it.