The Slow Query Log

work_mem doesn't mean what the name suggests, and raising it globally can take your server down

work_mem sounds like a single memory budget: "how much working memory can Postgres use." It isn't. It's a per-operation ceiling, and a single query can hit that ceiling several times over, concurrently, once per sort or hash node in its plan — multiplied again by however many connections are running similar queries at once. Bumping it in postgresql.conf because one report query was slow is how a server that was fine yesterday gets OOM-killed today.

What actually happens when a sort or hash exceeds work_mem

A Sort node that fits inside work_mem uses an in-memory quicksort. One that doesn't falls back to an external merge sort: Postgres writes sorted runs to temp files under base/pgsql_tmp/, then merges them, trading memory for disk I/O. EXPLAIN (ANALYZE) tells you which happened:

Sort Method: quicksort  Memory: 2542kB
-- vs.
Sort Method: external merge  Disk: 184320kB

A Hash Join or Hash Aggregate that exceeds its memory budget doesn't do an external merge — it increases its number of batches. The hash table gets partitioned, each partition written to its own temp file, and probing happens batch by batch instead of all at once:

Hash Cond: (o.customer_id = c.id)
Buckets: 65536 (originally 65536)  Batches: 8 (originally 1)
Memory Usage: 4129kB

Batches: 8 on a node whose plan expected Batches: 1 is the hash-join equivalent of a sort spilling to disk: more random I/O, more CPU re-reading partitions, and a number you can't see without EXPLAIN (ANALYZE, BUFFERS) or checking log_temp_files.

The multiplier problem

A single query plan can contain more than one sort or hash node — a hash join feeding a sort feeding a window function's own sort, for instance — and each node gets its own work_mem allowance, not a shared pool. A query with three sort nodes and work_mem = 256MB can legitimately try to use 768MB for that one connection, before you count anything else that connection is doing. Multiply that by max_connections running the same report concurrently, and a work_mem value that looked reasonable for "one query, one sort" turns into a multi-gigabyte spike under real concurrency.

PG13 made this sharper for hash operations specifically: hash_mem_multiplier (default 1.0 in PG13-14, raised to 2.0 in PG15) scales the effective memory limit for Hash Join and Hash Aggregate nodes to work_mem * hash_mem_multiplier, separately from sorts. That default exists because hash-based plans were found to need more headroom to avoid pathological batch counts — but it also means a work_mem bump affects hash nodes by double what you might expect from the sort behavior alone.

Why the global knob is the wrong lever

-- postgresql.conf — affects every connection, every sort, every hash node,
-- all the time, whether the query needs it or not
work_mem = 256MB

This is the version that takes a server down. It's not scoped to the slow report query you were trying to fix — it's scoped to every ORDER BY, every GROUP BY, every merge join's implicit sort, on every connection, for every query that runs until you change it back. On a box with max_connections = 200 and a workload where a meaningful fraction of connections are doing something sortable at once, that's not a tuning change, it's a memory commitment you can't bound.

The targeted alternative is to raise it only for the session or role that actually needs it:

-- just this session, just for the query you're about to run
SET work_mem = '256MB';
SELECT ...;
RESET work_mem;

-- or persistently, but only for the role that runs reports
ALTER ROLE reporting_user SET work_mem = '256MB';

Both of these bound the blast radius to the thing that was actually slow, instead of every connection on the instance. If the global default genuinely is too low for your workload's typical query, raise it in small increments and watch log_temp_files and server memory under real concurrent load before and after — not just the one query's EXPLAIN output in isolation.

Finding out you have a spill problem before someone pages you about it

-- postgresql.conf: log any temp file 20MB or larger
log_temp_files = 20480

This logs a line every time any operation spills past the threshold, with the size and the query. It's the cheapest way to find out how often and how badly your real workload is exceeding work_mem, instead of guessing from one slow query someone complained about. A workload that spills constantly at 21MB over a 20MB work_mem is a very different problem from one that occasionally spills 2GB from a single runaway report — the first is a candidate for a modest global increase, the second is a candidate for fixing or isolating that one query.

Rule of thumb: work_mem is "per sort or hash node, per connection," not "total memory Postgres may use." Before raising it globally, turn on log_temp_files, find out which queries are actually spilling and by how much, and scope the fix to those queries or roles with SET/ALTER ROLE first. Reach for the global default only once you've confirmed the increase is safe multiplied by your real peak concurrency, not just safe for one query running alone.