hash_mem_multiplier and the Hash Memory Budget #

A reporting query’s Hash node shows Batches: 8 and its HashAggregate spills, but its two Sort nodes are comfortably in memory. Raising work_mem from 64 MB to 256 MB fixes the hash nodes — and quadruples the memory every sort in every session is allowed to use. There is a narrower lever for exactly this situation.

Hash-based nodes have their own memory budget, derived from work_mem but scaled separately. How that budget decides between a single-batch and a spilling hash join is described in hash join mechanics; this page covers sizing it.

The Algorithmic Condition #

Since PostgreSQL 13, every memory-hungry node that builds a hash table uses a limit of:

hash memory = work_mem × hash_mem_multiplier

The multiplier defaulted to 1.0 in PostgreSQL 13 and 14, and to 2.0 from PostgreSQL 15. It applies to:

It does not apply to sorts, which use plain work_mem.

The reasoning is asymmetry of failure. A sort that exceeds work_mem degrades gracefully into an external merge sort, whose cost grows slowly. A hash table that exceeds its budget must batch or spill, and batches can multiply, re-read input, and — with skewed keys — overshoot anyway. Giving hash nodes a larger share is usually a better use of memory than raising every allowance equally.

The planner uses the same limit: a hash join is planned with the number of batches that fits the budget, and a HashAggregate plans its Planned Partitions from it. Changing the multiplier therefore changes both execution behaviour and, sometimes, plan choice.

Two budgets from one setting A table of node types against their memory limit. Sort and Incremental Sort use work_mem. Hash under a hash join, HashAggregate, Memoize, hashed SubPlan and HashSetOp use work_mem multiplied by hash_mem_multiplier. Parallel Hash additionally multiplies by the number of participants. memory limit spill behaviour Sort work_mem external merge, gradual Hash (hash join) work_mem × multiplier more batches HashAggregate work_mem × multiplier partitions spilled Memoize work_mem × multiplier LRU evictions Parallel Hash × multiplier × procs shared batches default multiplier: 2.0 from PostgreSQL 15, 1.0 in 13 and 14

Annotated EXPLAIN Evidence #

SHOW work_mem;              -- 64MB
SHOW hash_mem_multiplier;   -- 1   (carried over from an upgraded PostgreSQL 13 config)

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.segment, count(*)
FROM events e JOIN customers c ON c.id = e.customer_id
GROUP BY c.segment;
HashAggregate  (actual time=21840.6..21840.7 rows=14 loops=1)
  Group Key: c.segment
  Batches: 1  Memory Usage: 24kB
  ->  Hash Join  (actual time=4210.2..19204.8 rows=41802110 loops=1)
        Hash Cond: (e.customer_id = c.id)
        ->  Seq Scan on events e  (actual rows=41802110 loops=1)
        ->  Hash  (actual time=4190.4..4190.4 rows=6120400 loops=1)
              Buckets: 1048576  Batches: 8  Memory Usage: 64998kB
              --  Memory Usage ≈ 64 MB, i.e. exactly work_mem × 1.0
              --  Batches: 8 → the customer hash table needed ~420 MB
              Buffers: shared hit=81220, temp read=51840 written=51840
Execution Time: 21902.4 ms

With the multiplier raised for this statement:

BEGIN;
SET LOCAL hash_mem_multiplier = 8.0;   -- 512 MB for hash nodes, sorts stay at 64 MB
EXPLAIN (ANALYZE, BUFFERS) SELECT c.segment, count(*) FROM events e JOIN customers c ON c.id = e.customer_id GROUP BY c.segment;
ROLLBACK;
        ->  Hash  (actual time=3410.8..3410.8 rows=6120400 loops=1)
              Buckets: 8388608  Batches: 1  Memory Usage: 428104kB
              Buffers: shared hit=81220
Execution Time: 14208.9 ms
-- single batch, no temp I/O; sorts elsewhere keep their 64 MB limit
Reading the budget off the Hash node Three fields are annotated. Memory Usage sitting exactly at work_mem times the multiplier means the budget was hit. Batches above one means the table spilled. Temp read and written counts on the Hash node show the I/O cost of batching. Batches: 8 Memory Usage: 64998kB usage pinned at the budget temp read=51840 written=51840 batch files on disk Batches: 1 Memory Usage: 428104kB fits once budget ≥ 420 MB usage equal to work_mem × multiplier means the cap, not the need

Step-by-Step Resolution #

  1. Read both settings and where they come from.

    SELECT name, setting, source FROM pg_settings
WHERE name IN ('work_mem', 'hash_mem_multiplier');

    Upgraded clusters often carry an explicit hash_mem_multiplier = 1 from an old configuration file, hiding the newer default of 2.0.

  2. Find spilling hash nodes and their real needs. Batches > 1 on Hash, Batches > 1 or Disk Usage on HashAggregate, and many Evictions on Memoize all point to the hash budget.

  3. Estimate the needed budget. For a single-batch hash, Memory Usage × Batches from the spilling run is a reasonable upper bound on the in-memory size.

  4. Test with SET LOCAL hash_mem_multiplier rather than work_mem, so sorts are not affected.

  5. Budget the total. Every hash node in every concurrent query can use the full hash budget, and Parallel Hash multiplies it by participants. Apply the arithmetic in pool size and the total work_mem budget with the multiplied figure.

  6. Scope the change. Raise it for the reporting role or transaction that needs it:

    ALTER ROLE reporting SET hash_mem_multiplier = 4.0;
The budget one hash node may use With work_mem at 64 megabytes, a multiplier of 1 allows 64 megabytes, 2 allows 128, 4 allows 256, and 8 allows 512. The customer hash table in the example needs about 420 megabytes, so only the multiplier of 8 keeps it in a single batch. multiplier 1.0 64 MB → 8 batches multiplier 2.0 128 MB → 4 batches multiplier 4.0 256 MB → 2 batches multiplier 8.0 512 MB → 1 batch sorts in the same query keep 64 MB at every multiplier

Before and After #

-- BEFORE: work_mem 64MB, hash_mem_multiplier 1.0
Hash  Batches: 8  Memory Usage: 64998kB  temp written=51840          Execution Time: 21902.4 ms

-- AFTER: SET LOCAL hash_mem_multiplier = 8.0
Hash  Batches: 1  Memory Usage: 428104kB                             Execution Time: 14208.9 ms

Why not just raise work_mem? #

Raising work_mem to 512 MB would also have produced a single batch — and allowed every sort, incremental sort and materialisation in every session the same 512 MB. On a query mix with many sorts that is a large, mostly wasted increase in worst-case memory. The multiplier concentrates the increase on the nodes whose failure mode is steep. It also keeps the planning arithmetic for sorts unchanged, so plans that currently choose an in-memory sort over an index do not suddenly shift.

The multiplier also changes how the planner compares strategies. A HashAggregate that the planner expects to fit in the hash budget is costed without spill overhead, so a larger multiplier can turn a sort-based GroupAggregate plan into a hash-based one, and a larger single-batch hash join can displace a merge join that needed two sorts. Those shifts are usually improvements, but they are plan changes, and they deserve the same before-and-after check as any other planner setting. Capture the affected queries with EXPLAIN (ANALYZE, SETTINGS) before and after, so the Settings: line records which multiplier produced which plan, and keep the comparison with the migration that changes it.

There is one case where work_mem is still the right knob: when the spilling nodes are sorts. External merge sorts show Sort Method: external merge Disk: …, and the multiplier does nothing for them. The diagnostics for that path are in debugging unexpected sort operations.

Common Pitfalls #

Assuming the default is 2.0 everywhere. Clusters upgraded from 13 or 14 often have 1.0 pinned in configuration. Diagnostic signal: source = configuration file for hash_mem_multiplier. Fix: remove the override or set the intended value explicitly.

Forgetting Parallel Hash multiplies again. The shared table may use the hash budget times participants. Diagnostic signal: memory spikes during parallel reporting. Fix: budget work_mem × multiplier × (workers + 1) per parallel hash.

Raising the multiplier for a skewed join. A hot key overshoots any budget. Diagnostic signal: Batches grew beyond originally and memory far above the limit. Fix: see hash join skew and hot-key batches.

Changing it server-wide for one report. Every hash node everywhere gains the headroom. Diagnostic signal: total memory growth after the change. Fix: role or transaction scope.

Frequently Asked Questions #

What does hash_mem_multiplier do? #

It sets the memory limit for hash-based nodes — hash joins, hash aggregates, Memoize and hashed subplans — to work_mem multiplied by this value. Sorts are unaffected and keep plain work_mem.

What is the default hash_mem_multiplier? #

2.0 from PostgreSQL 15 onward. It was introduced in PostgreSQL 13 with a default of 1.0, and clusters upgraded with their old configuration often still carry that value.

Does hash_mem_multiplier affect query plans or only execution? #

Both. The planner uses the same hash memory limit to plan batch counts and to decide whether a hash aggregate or hash join fits in memory, so a larger budget can change the chosen plan as well as how it executes.

Up: Hash Join Mechanics