The worst PostgreSQL incident I ever caused with a configuration change was one line: work_mem raised from 64MB to 512MB, globally, to make one slow report stop spilling to disk. The report got faster. Then, three days later, a busy Tuesday arrived, two hundred concurrent connections each ran queries with several sort and hash nodes and parallel workers, the sum of all those per-node allocations blew past physical RAM, and the Linux OOM killer shot a backend. When the OOM killer shoots any PostgreSQL backend, the postmaster takes down the whole cluster and restarts it, so every connection on the server dropped at once. The postmortem sentence I remember writing: we budgeted memory as if work_mem were per query, and it is not.
This is the most commonly misunderstood memory setting in PostgreSQL, and the misunderstanding is expensive in both directions. Set too low, sorts and hashes spill to disk and queries crawl. Set too high, and the multiplication you did not do in your head happens on the server, all at once, under peak load. Here is the arithmetic that keeps you safe.
The per-node multiplier, stated plainly
work_mem is the memory budget for one internal operation before it spills to disk. One sort node gets work_mem. One hash table gets work_mem, times hash_mem_multiplier since PostgreSQL 13, which defaults to 2, so hash-based nodes are allowed double. A single query plan can contain several such nodes: a hash join, a hash aggregate on top of it, a sort for the ORDER BY, maybe a merge join that sorts both inputs. Every one of those nodes has its own budget, and they can hold it simultaneously. Then multiply by parallel execution: each parallel worker is a separate process running its own copy of the plan fragment, and each worker gets the full per-node budget again. Two hash nodes plus a final sort, with four parallel workers and a leader, at work_mem = 512MB, is a single query that can legitimately reach for several gigabytes. Then multiply by however many such queries run concurrently.
The settings themselves are easy to inspect:
SHOW work_mem;
SHOW hash_mem_multiplier;The defaults are work_mem = 4MB and hash_mem_multiplier = 2.0. The 4MB default is deliberately tiny, a safe value for a database that might serve anything, and it is almost always wrong for a serious analytical workload. But the direction of safety matters: 4MB wastes time on spills, while a careless large value wastes the one resource that takes the whole cluster down when it runs out.
A useful mental formula for the ceiling: worst-case memory is roughly max_connections, times the number of simultaneous sort or hash nodes in your heaviest plans, times work_mem, times a factor for hash nodes and parallel workers. You will never hit the exact ceiling, because not every connection runs a heavy plan at once, but if that arithmetic exceeds RAM, you are one unlucky Tuesday from finding out what happens. Add shared_buffers and the operating system's own needs on top, and the picture is complete.
Finding spills instead of guessing
The good news is that PostgreSQL tells you, precisely, when and where memory ran short. At the level of a single query, EXPLAIN with ANALYZE reports the sort method and disk usage directly in the plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT d.region, sum(f.amount)
FROM fact_sales f
JOIN dim_store d USING (store_id)
GROUP BY d.region
ORDER BY 2 DESC;In the output, a sort that fit in memory reports quicksort with a memory figure; a sort that spilled reports external merge with a Disk figure in kilobytes. Hash nodes report their batch count, and any batch count above one means the hash table overflowed work_mem times hash_mem_multiplier and got partitioned to disk. Reading those two lines off the real plan is the ground truth for whether a given query needs more memory or a better shape.
At the server level, two instruments cover everything. log_temp_files writes a log line for every temporary file a query creates, past a threshold you choose, and pg_stat_database accumulates temp file volume per database:
SELECT datname,
temp_files,
pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;temp_bytes is cumulative since the stats reset, so a single reading means little; the delta over a day means a lot. A database writing tens of gigabytes of temp files daily is telling you that some workload is chronically under-budgeted, and the log lines from log_temp_files tell you which statements. I set the threshold to catch spills that matter, something like 10MB, because the tiny ones are normal and the big ones are the problem:
ALTER SYSTEM SET log_temp_files = '10MB';
SELECT pg_reload_conf();Right-sizing without the OOM
My sizing discipline, learned the hard way: keep the global work_mem modest and raise it surgically where the workload justifies it. On a mixed OLTP database, a global value in the tens of megabytes, say 16MB to 64MB, handles ordinary sorts on a well-provisioned server, but treat that range as an input to the ceiling arithmetic above, not as a default to copy: the same 16MB that is invisible on a 128GB host with fifty connections can sink a small container running a few hundred. What "survivable" means is always your worst-case multiplication against your actual RAM. The heavy lifting belongs in session- and role-level overrides:
BEGIN;
SET LOCAL work_mem = '256MB';
-- the heavy report or ETL step runs here
COMMIT;
ALTER ROLE etl SET work_mem = '512MB';SET LOCAL scopes the generous budget to the one transaction that needs it, and everything else on that connection stays at the safe global value. ALTER ROLE gives a dedicated service account, the ETL user, the reporting user, a permanently higher budget, while the hundreds of application connections stay small. This is the same pattern as every other dangerous-setting story in PostgreSQL: the global default protects the many, and the override serves the few.
When you do the headroom arithmetic, be honest about concurrency and about the environment. The number that matters is not max_connections but the realistic count of simultaneous heavy queries, which on most systems is small and knowable. On a container or cgroup-limited deployment, the ceiling is the container's limit, not the host's RAM, and the OOM killer there is just as fatal. And note that work_mem is not the only consumer in this family: maintenance_work_mem governs VACUUM and CREATE INDEX separately, and autovacuum workers draw from that budget, so keep the two conversations separate. The PostgreSQL memory tuning post covers the full family of memory settings together.
hash_mem_multiplier and when to nudge it
hash_mem_multiplier deserves its own paragraph because it changed the tuning story in PostgreSQL 13. Before it existed, hash operations and sorts shared the same work_mem budget, and raising work_mem to fix a spilling hash join also inflated every sort. The multiplier decouples them: hash nodes may use work_mem times the multiplier, so with the default of 2, a 64MB work_mem already allows a 128MB hash table. If your spill evidence shows hash batches overflowing while sorts are comfortable, raising the multiplier to 4 or 8 on the affected workload is a more targeted fix than raising work_mem itself, because sorts stay on the tighter budget. As with everything here, prefer the per-session override over a global change, and verify with a fresh EXPLAIN ANALYZE that the batch count actually dropped to one.
Keeping memory honest with MonPG
work_mem problems are trends before they are incidents: temp_bytes climbing week over week as data grows, a report that spilled twice a day now spilling constantly, a new deployment introducing a plan with one more hash node than before. Those are all visible if you are watching the right signals. Watching those signals is what MonPG does for PostgreSQL databases today: query history, wait events, and database-level statistics collected over time, so a spilling query family stands out against its own baseline weeks before it ruins a peak-traffic afternoon. Our PostgreSQL sizing guide puts work_mem in the context of the whole memory budget, shared_buffers, connections, and the OS, which is the calculation that actually keeps the OOM killer away from your postmaster.