Tuning work_mem for Hash Aggregates in PostgreSQL #

A HashAggregate builds an in-memory hash table keyed by your GROUP BY columns, accumulating one aggregate state per distinct group. As long as that table fits within its memory budget, grouping is fast and linear. When it does not fit, modern PostgreSQL spills partitions to temporary files — and older PostgreSQL crashed. Reading the node’s memory fields tells you which situation you are in and whether the cure is more memory, fewer groups, or a different aggregation strategy entirely.

This page is a companion to Sort and Hash Node Analysis and focuses purely on the aggregate case. For the sort-side equivalent, see debugging unexpected sort operations.

Reading the HashAggregate Node #

Start with a grouping query over an events table and capture the node fields:

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, COUNT(*) AS events, MAX(created_at) AS last_seen
FROM events
GROUP BY user_id;
HashAggregate  (cost=41200.00..44950.00 rows=250000 width=24)
               (actual time=612.3..701.8 rows=250000 loops=1)
  Group Key: user_id
  Planned Partitions: 8   Batches: 17   Disk Usage: 20480kB   -- ← spilled to disk
  Buffers: shared hit=8200 read=6300, temp read=2560 written=2560
  ->  Seq Scan on events  (cost=0.00..18200.00 rows=1000000 width=16)
                          (actual time=0.02..118.4 rows=1000000 loops=1)

Three fields on the HashAggregate line carry the diagnosis:

The temp read=2560 written=2560 in the Buffers line corroborates the spill: those are the temporary blocks the batches read and wrote.

Why the Budget Is Not Just work_mem #

Since PostgreSQL 13, hash nodes get a larger budget than sorts. The effective hash memory limit is:

effective_hash_mem = work_mem × hash_mem_multiplier   -- hash_mem_multiplier defaults to 2.0

So with work_mem = 4MB and the default multiplier, a HashAggregate may use 8 MB before spilling. This separation exists because hash tables carry more per-entry overhead than a sort’s array, and because you often want to give hashing headroom without also inflating every sort in the query. The same multiplier governs the build side of a hash join, so raising it affects both.

HashAggregate vs GroupAggregate: How the Planner Chooses #

The planner weighs two ways to compute a GROUP BY:

HashAggregate versus GroupAggregate selection A comparison showing that HashAggregate builds an in-memory hash table sized by the estimated group count and spills when it exceeds work_mem times hash_mem_multiplier, while GroupAggregate consumes sorted input and streams groups with constant memory but needs an ordering index or an explicit Sort. GROUP BY to plan estimate group count HashAggregate in-memory hash table no input order needed fits if table size < work_mem × hash_mem spills to Batches > 1 if it overflows GroupAggregate consumes sorted input streams one group at a time constant memory, never spills, but needs an ordering index/Sort planner picks the cheaper estimated total cost Underestimated group count → HashAggregate chosen → spill at runtime

If the estimated number of groups is small enough that the hash table fits the budget, HashAggregate wins because it needs no sorted input. If the estimate is large — or an index already delivers rows in GROUP BY order — GroupAggregate (which streams sorted rows with constant memory) can be cheaper. The whole decision runs on estimated group count, so a bad n_distinct estimate is what pushes the planner into a HashAggregate that then spills. The mechanics of that cost comparison live in cost estimation models.

Step-by-Step: Stopping the Spill #

Step 1 — Read the memory fields #

Capture Batches, Disk Usage, and Planned Partitions from EXPLAIN (ANALYZE, BUFFERS). Batches: 1 and no Disk Usage line means you are already in memory — stop.

Step 2 — Check whether the group count was underestimated #

-- What the planner believes about distinct users
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'events' AND attname = 'user_id';

Compare actual rows on the HashAggregate (the real group count) against the planner’s rows= estimate. If actual is much larger, the spill came from a cardinality miss, and ANALYZE or a raised statistics target may help the planner budget correctly.

Step 3 — Raise the hash memory budget #

Test at session scope first. You can lift the base budget or just the hash portion:

SET LOCAL hash_mem_multiplier = 4.0;   -- gives hash nodes 4× work_mem, sorts unaffected
-- or
SET LOCAL work_mem = '32MB';           -- raises the budget for every operation in the query

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, COUNT(*), MAX(created_at)
FROM events
GROUP BY user_id;

Prefer hash_mem_multiplier when only the aggregate is spilling — it leaves sort memory alone.

Step 4 — Reduce cardinality when memory cannot win #

If there are genuinely millions of groups, no reasonable budget will hold them. Filter earlier or pre-aggregate at a coarser grain:

-- Aggregate only the rows that matter instead of the whole table
SELECT user_id, COUNT(*)
FROM events
WHERE created_at >= now() - interval '7 days'
GROUP BY user_id;

Step 5 — Fall back to GroupAggregate with an ordering index #

When memory is capped and the group count is large, an index that delivers rows in GROUP BY order lets the planner stream groups with constant memory:

CREATE INDEX CONCURRENTLY idx_events_user ON events (user_id);

The planner may now choose GroupAggregate over a spilling HashAggregate, trading a scan-order requirement for zero temporary I/O.

Step 6 — Validate #

Re-run EXPLAIN (ANALYZE, BUFFERS). Success is Batches: 1, no Disk Usage line, no temp buffers, and a lower actual time.

Before/After Plan Comparison #

-- BEFORE: budget too small, aggregate spills across 17 batches
HashAggregate  (actual time=612.3..701.8 rows=250000 loops=1)
  Planned Partitions: 8  Batches: 17  Disk Usage: 20480kB
  Buffers: temp read=2560 written=2560

-- AFTER: SET LOCAL hash_mem_multiplier = 4.0, whole table fits in memory
HashAggregate  (actual time=248.1..291.6 rows=250000 loops=1)
  Batches: 1                                   -- ← no spill, no Disk Usage line
  Buffers: shared hit=8200 read=6300           -- ← temp buffers gone

The disappearance of the Disk Usage line and the temp buffers, with Batches back to 1, is the proof the aggregation now runs entirely in memory.

Common Pitfalls #

Running the pre-13 OOM risk unknowingly. On PostgreSQL 12 and earlier, HashAggregate never spilled — an underestimated group count grew the hash table until the backend was killed by the OOM killer. If you are still on such a version, an underestimate is a stability risk, not just a performance one; upgrade or force GroupAggregate for unbounded-cardinality groupings.

Chasing a spill caused by a bad n_distinct. If pg_stats.n_distinct is wildly wrong, raising work_mem treats the symptom. Fix the estimate first with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS and ANALYZE, then re-check whether the spill even remains.

Raising work_mem globally. Every hash and sort in every concurrent session can claim the budget, so a large global work_mem multiplied by hash_mem_multiplier and by connection count can exhaust server RAM. Scope changes with SET LOCAL or ALTER ROLE.

Forgetting enable_hashagg as a diagnostic. Setting SET LOCAL enable_hashagg = off forces the planner to a GroupAggregate so you can compare timings directly. It is a diagnostic lever for A/B comparison, not a production setting — leave it off only long enough to read the alternative plan.

Frequently Asked Questions #

What do Batches and Disk Usage mean on a HashAggregate node? Batches greater than 1 means the hash table did not fit the memory budget, so PostgreSQL 13+ partitioned the groups into multiple passes and spilled some to temporary files. Disk Usage reports how many kilobytes were written. Batches: 1 with no Disk Usage line means the whole aggregation stayed in memory, which is the goal.

How does hash_mem_multiplier differ from work_mem? work_mem is the base per-operation budget. Since PostgreSQL 13, hash-based nodes such as HashAggregate and Hash Join may use work_mem multiplied by hash_mem_multiplier, which defaults to 2.0. Raising the multiplier gives hash tables more room without enlarging the sort budget, so you can stop an aggregate spilling without touching sort behavior.

Why did HashAggregate cause an out-of-memory crash before PostgreSQL 13? Before PostgreSQL 13, HashAggregate had no spill path. If the planner underestimated the group count, the hash table grew past work_mem and kept growing until the backend exhausted memory and was killed. PostgreSQL 13 added disk spilling, so an underestimate now degrades to slower disk batches instead of an OOM crash.

Related