Why Did My Postgres Query Suddenly Spill to Disk? (work_mem and Hash/Sort Spills)

A report that used to return in 400 milliseconds now takes eleven seconds and writes two gigabytes of temporary files to disk. Nothing in the query changed. The answer is almost always the same: a sort or a hash outgrew work_mem and started using the disk instead of memory.

This is one of the most common “it was fine yesterday” incidents in PostgreSQL, and it is nearly always a threshold effect rather than a regression. The data grew past a line you could not see, an operation that had been sitting comfortably in memory tipped over, and Postgres did exactly what it is designed to do: it spilled to temporary files rather than fail. The query still returns the right answer. It just does it slowly, and it hammers the disk while it does. Here is how to read the evidence and fix it without creating a worse problem.

What work_mem actually governs

work_mem is the amount of memory a single sort or hash operation may use before it spills to disk. The default in PostgreSQL 17 is a deliberately conservative 4MB. That is not per query and not per connection — it is per operation. A single query with three sorts and a hash join can allocate work_mem several times over, and every concurrent session does the same. That multiplication is the reason the default is small, and it is the trap waiting for anyone who decides to “just raise it”.

One wrinkle catches people out. Hash-based operations — hash joins and hash aggregates — do not use work_mem directly. They use work_mem multiplied by hash_mem_multiplier, which defaults to 2.0 in PostgreSQL 17. So with a 4MB work_mem, a hash join may use up to 8MB before it partitions and spills. Sorts get the base figure; hashes get double. If you are diagnosing a hash node, do the multiplication.

Reading the spill in EXPLAIN ANALYZE

The plan tells you precisely which operation spilled and how much it wrote. Run EXPLAIN (ANALYZE, BUFFERS) — the BUFFERS clause adds the temp-block counters that make the spill unambiguous.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.customer_id, count(*)
FROM orders o
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.customer_id
ORDER BY count(*) DESC;

-- Sort node, spilled:
Sort  (cost=... rows=..) (actual time=..)
  Sort Key: (count(*)) DESC
  Sort Method: external merge  Disk: 214880kB
  Buffers: shared hit=..., temp read=26860 written=26860

-- Hash join node, spilled:
Hash Join  (cost=... rows=..) (actual time=..)
  Hash Cond: (l.order_id = o.id)
  ->  Hash  (actual time=..)
        Buckets: 65536  Batches: 8  Memory Usage: 3585kB

Three signals matter. Sort Method: external merge Disk: 214880kB means the sort could not fit in work_mem and wrote 214MB of temp files; the in-memory equivalent would read Sort Method: quicksort Memory: 25kB. On the hash side, Batches: 8 is the tell — any value above 1 means the hash table was partitioned across the disk. A clean in-memory hash shows Batches: 1. And temp read=... written=... counts the actual temp blocks (8KB each), so 26860 written is roughly 210MB of I/O this query generated on its own.

Watch for a bad row estimate as the root cause. If a node planned for 5,000 rows and processed 5,000,000, the planner sized the hash for the wrong world. In that case the fix is ANALYZE on stale statistics or a better index — not more memory to accommodate a mistake.

Correlating with log_temp_files

EXPLAIN tells you about the query you already suspect. log_temp_files tells you about the ones you do not — every statement across the estate that spills, captured to the server log. It defaults to -1 (disabled). Set it to a byte threshold and Postgres logs any temp file at or above that size.

-- postgresql.conf: log any temp file of 10MB or more.
-- The value is in kilobytes; 0 logs every temp file, -1 disables.
log_temp_files = 10240

-- Reload without a restart:
SELECT pg_reload_conf();

-- Log lines then look like:
-- LOG:  temporary file: path "base/pgsql_tmp/...", size 214884352
-- STATEMENT:  SELECT o.customer_id, count(*) ...

Set the threshold above trivial spills so the log surfaces the offenders rather than noise, and leave it on. This is the same instinct behind instrumenting the metrics that actually predict an incident: you want the signal captured continuously, so the next spill shows up in the log before it shows up as a support ticket.

Fixing it without OOM-ing the box

The tempting fix is to raise work_mem globally in postgresql.conf. Resist it. Because the setting is per operation and per session, a global bump is multiplied by every concurrent sort and hash across every connection. Set work_mem to 256MB globally with 200 active connections each running a two-sort query, and you have theoretically authorised roughly 100GB of working memory. Under load, the kernel OOM killer arrives and takes the whole instance down. You have traded a slow report for an outage.

Raise it locally, scoped to the one transaction that needs it, using SET LOCAL.

BEGIN;
-- Applies only for this transaction; resets automatically on COMMIT.
SET LOCAL work_mem = '256MB';

SELECT o.customer_id, count(*)
FROM orders o
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.customer_id
ORDER BY count(*) DESC;
COMMIT;

Now only the heavy analytical query gets the extra memory, and it is released the moment the transaction ends. The same discipline applies to a hash aggregate that is spilling: if the multiplication tells you the hash needs headroom, raise hash_mem_multiplier for that session rather than pushing work_mem up for everything. And keep the target realistic — a 214MB spill does not need 256MB to clear it, but nudging work_mem just above the spill size in EXPLAIN turns the external merge back into an in-memory quicksort.

Before you reach for memory at all, ask whether the sort should exist. An ORDER BY on an indexed column can often be served by an index scan with no sort node; a hash join that spills on a huge build side may be better as a nested loop against a selective index. Memory is the fix when the work is genuinely large and unavoidable. When the work is large because the plan is wrong, the index or the fresh statistics is the fix, and it costs no RAM at all.

Make the fix permanent, and provable

A one-off SET LOCAL in a psql session fixes today’s incident and forgets it by tomorrow. Put the setting where the query lives: in the reporting function, the migration, or the application code path that issues the statement, under version control with the reason recorded in the commit. That is the same argument for keeping enforced configuration in the repository rather than in someone’s memory — a tuning decision nobody can find is a tuning decision that gets undone. If the spilling query is a write path being retried, make sure the retry is idempotent before you make it faster, or you will simply spill twice.

The spill is not a bug. It is Postgres refusing to fall over and telling you, in the plan and in the log, exactly which operation outgrew the memory you gave it. Read the external merge line, count the batches, size the fix to the spill, and scope it to the transaction — and the box stays up while the report gets fast again.