Diagnosing HashAggregate Disk Spills #

A HashAggregate that spills turns a single in-memory pass into several rounds of writing and re-reading temporary files, and it does so silently — the plan shape is unchanged, the results are correct, and only the timing and two extra fields betray it. This page is about turning those fields into the correct fix, which is not always “raise work_mem”.

The spill mechanism itself and the strategy choice behind it are covered in aggregate and grouping strategies; here we work from the symptom backwards.

The Condition That Triggers a Spill #

A hash aggregate holds one transition state per distinct group. It spills the moment that table would exceed work_mem × hash_mem_multiplier. Two independent things can push it over:

• The group count is larger than the planner believed. The planner sized its expectations from n_distinct in pg_stats, multiplied across the grouping columns. If the real number of groups is ten times higher, the node was doomed before it started.
• The group count was right but the allowance is too small. Common on reporting queries running under an OLTP-sized work_mem, and on aggregates with wide transition states such as array_agg or jsonb_agg.

The distinction matters because the repairs are opposites. The first is a statistics problem — fix it and the planner may abandon the hash strategy entirely. The second is a resource problem, where the planner made the right call and simply needs headroom.

Annotated EXPLAIN Evidence #

Only three fields matter, and all three appear on the aggregate node itself:

HashAggregate  (cost=214880.00..216400.00 rows=152000 width=40)
                (actual time=1980.4..2610.7 rows=1489223 loops=1)
  Group Key: o.customer_id, o.status
  Planned Partitions: 8                    -- how many the executor prepared for
  Batches: 17                              -- how many it actually needed (>1 = spill)
  Peak Memory Usage: 4145 kB               -- resident portion only, NOT the full table
  Disk Usage: 214560 kB                    -- temp bytes written during the spill
  ->  Seq Scan on orders o  (actual rows=8000000 loops=1)
      Buffers: shared hit=1024 read=61440, temp read=26820 written=26820

Batches: 17 against Planned Partitions: 8 says the executor had to re-partition mid-flight — the first split was not enough, so it recursed. That is a strong indicator of an estimate problem rather than a marginal shortfall.

The decisive number is on the same line as the node name: rows=152000 estimated against rows=1489223 actual. That is a 9.8× underestimate of the group count, which is why 8 planned partitions turned into 17 batches.

Compare that with a resource-only spill, where estimates are honest:

HashAggregate  (cost=…, rows=1462000 …) (actual rows=1489223 loops=1)
  Group Key: o.customer_id, o.status
  Batches: 2  Peak Memory Usage: 131072 kB  Disk Usage: 41208 kB

Estimate and actual are within 2%. The planner knew what it was doing and just ran out of room by a small margin — a candidate for a higher allowance, not for statistics work.

Step-by-Step Resolution #

1. Capture the evidence with buffers included, so you can see the temporary I/O as well as the node fields:

   EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
   SELECT customer_id, status, count(*), sum(total_cents)
     FROM orders GROUP BY customer_id, status;

2. Compute the estimate ratio on the aggregate node. Above roughly 5×, treat it as a statistics problem and go to step 3. Near 1×, skip to step 5.

3. Check whether per-column statistics are simply stale, then refresh:

   SELECT attname, n_distinct, last_analyze
     FROM pg_stats s JOIN pg_stat_user_tables t ON t.relname = s.tablename
    WHERE s.tablename = 'orders' AND attname IN ('customer_id','status');
   
   ANALYZE orders;

4. If the grouping columns are correlated, teach the planner the combination. Multiplying independent n_distinct values is exactly what produces the wrong number here:

   CREATE STATISTICS orders_cust_status (ndistinct) ON customer_id, status FROM orders;
   ANALYZE orders;

5. Size the allowance for this statement only. Never raise the global value to fix one report:

   BEGIN;
   SET LOCAL work_mem = '256MB';        -- hash nodes get 2× this by default
   EXPLAIN (ANALYZE) SELECT customer_id, status, count(*) FROM orders GROUP BY 1,2;
   COMMIT;

6. If the honest group count is in the millions, stop feeding the hash table. A sorted path costs a predictable amount of temporary space instead of an unpredictable amount of re-partitioning:

   CREATE INDEX CONCURRENTLY orders_customer_status_idx ON orders (customer_id, status);

7. Confirm the repair. Batches: 1, no Disk Usage line, and no temp written in the buffers output.

Before and After #

-- BEFORE: group estimate 9.8× low, executor re-partitions
HashAggregate (rows=152000) (actual rows=1489223)  Batches: 17  Disk Usage: 214560kB
Execution Time: 2681.4 ms

-- AFTER: CREATE STATISTICS + ANALYZE, planner sizes correctly and stays resident
HashAggregate (rows=1462311) (actual rows=1489223)  Batches: 1  Peak Memory Usage: 178432 kB
Execution Time: 1104.2 ms

The only field that changed in the SQL is none — the query text is identical. Batches fell from 17 to 1 and the Disk Usage line disappeared entirely, which is the signature of a fixed spill rather than a merely faster machine.

Common Pitfalls #

Raising work_mem when the estimate is the problem. With a 10× group underestimate you may need ten times the memory you predicted, and each concurrent backend claims it. Diagnostic signal: a large estimate-to-actual ratio on the aggregate node. Fix: correct the statistics first and re-plan before spending memory.

Reading Peak Memory Usage as the requirement. It reports the resident portion after partitioning, not the size the full table needed. Diagnostic signal: a small peak beside a huge Disk Usage. Fix: size from the actual group count and the state width instead.

Forgetting hash_mem_multiplier. Hash nodes get work_mem × hash_mem_multiplier, so sorts and aggregates spill at different thresholds. Diagnostic signal: sorts clean, aggregate still batching. Fix: raise the multiplier rather than doubling work_mem for everything.

Ignoring wide transition states. array_agg and jsonb_agg accumulate every input row per group, so a few thousand groups can exceed any sane allowance. Diagnostic signal: a spill with a small rows count on the aggregate. Fix: aggregate to a scalar, or reduce the input with a pushed-down filter as described in filter pushdown mechanics.

Frequently Asked Questions #

Does Batches: > 1 always mean I should raise work_mem? No. When the estimated and actual group counts agree, the allowance genuinely is too small and raising it for that transaction is the right fix. When the estimate is far below reality, the planner would have chosen differently with honest numbers, so repairing the statistics is cheaper and lasts longer.

Why is Peak Memory Usage small on a node that clearly spilled? Because it measures only the hash table that stayed resident. Once partitioning starts, the executor keeps the resident portion inside the allowance by design, so a heavily spilling node can report a modest peak next to hundreds of megabytes of Disk Usage.

How much I/O does the spill really cost? About twice the reported Disk Usage, since every spilled partition is written once and read back once. That traffic shares the temporary file area and the temp_file_limit with every other backend, which is why spills degrade sharply under concurrency.

Related #

• Aggregate and Grouping Strategies — parent guide: how the planner picks between hash and sorted aggregation
• GroupAggregate vs HashAggregate Selection — sibling: the cost comparison behind the choice
• Execution Plan Fundamentals — section overview: operator trees, access paths, and costing
• Tuning work_mem for Hash Aggregates — sizing the allowance across a whole workload