hash_mem_multiplier
hash_mem_multiplier sets how much memory hash-based executor nodes may use, as a MULTIPLE of work_mem.
Tier 1 — Lab-Proven — Measured behavior and safety boundaries are available below.
Measured Effect
summary
Validated in controlled PostgreSQL lab environments over hmm_t (6M rows, ~100k groups), work_mem fixed at 4MB, parallelism off, GROUP BY forced to HashAggregate. The hash table needs ~12.5MB; the multiplier decides whether the work_mem*multiplier budget covers it. RAISING hash_mem_multiplier removes the spill (Batches 5 -> 1, Disk Usage 87MB -> 0) without touching work_mem.
sweep work mem 4MB
| hash mem multiplier | note | plan | batches | memory kb | disk kb | cost est | exec ms |
|---|---|---|---|---|---|---|---|
| 1 | PG14 default | HashAggregate | 5 | 4145 | 89552 | 489513.53 | 1691.6 |
| 2 | PG15+ default | HashAggregate | 5 | 8241 | 32040 | 489513.53 | 1394.1 |
| 4 | fits in memory | HashAggregate | 1 | 12561 | 0 | 135139.89 | 1500.7 |
| 8 | HashAggregate | 1 | 12561 | 0 | 135139.89 | 1513.8 | |
| 16 | HashAggregate | 1 | 12561 | 0 | 135139.89 | 1563.2 |
the threshold
With work_mem=4MB the ~12.5MB hash table fits once the budget reaches ~16MB, i.e. at hash_mem_multiplier=4 (4MB*4). At multiplier 1 and 2 the budget (4MB / 8MB) is too small, so HashAggregate spills: at multiplier=1 it writes 89552kB (~87MB) of temp files across 5 batches; at multiplier=2 it still spills but less (32040kB). At multiplier>=4 it is fully in memory (Batches=1, no Disk Usage), and the planner's estimated cost drops ~3.6x (489513 -> 135139) because it expects no spill.
timing anchor
best-of-3, work_mem=4MB: hash_mem_multiplier=1 (spilling, ~87MB temp files, Batches=5) = 1810.8 ms vs hash_mem_multiplier=4 (in-memory, Batches=1) = 1434.4 ms = 1.26x slower. HONEST note: on this box (NVMe + plenty of OS cache) the wall-time penalty of spilling is moderate (~1.26x) because temp files hit cache; the unambiguous signal is the I/O footprint (~87MB temp files written vs 0) and the planner cost (3.6x lower in-memory). On slower/cold storage, under memory pressure, or with a larger-than-RAM spill, the wall-time gap grows substantially.
reading
hash_mem_multiplier is a direct dial on hash-node spill behavior. The mechanism (budget = work_mem*multiplier; exceed it -> partition into on-disk batches) is box-independent; the exact MB and the 1.26x are this box's numbers. The headline is the Batches/Disk Usage flip from the multiplier alone, work_mem untouched.
Reproduced Danger
Sizing / Safe Range
Workload Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Reproduced Danger
- Sizing / Safe Range
- Workload Guidance
hash_mem_multiplier sets how much memory hash-based executor nodes may use, as a MULTIPLE of work_mem. A HashAggregate or a Hash Join is allowed work_mem * hash_mem_multiplier of memory for its hash table before it must SPILL to disk (partition the input into batches and process them one at a time). So the effective hash budget is hash_mem = work_mem * hash_mem_multiplier. The default is 2.0 (PG 15+), meaning hash nodes get twice the memory that sort nodes (which use plain work_mem) get — a deliberate asymmetry, because spilling a hash table is more expensive than spilling a sort, and hash nodes are common in analytic queries. PostgreSQL 13 introduced this knob with a default of 1.0 (matching the historical 'hash gets exactly work_mem' behavior); PostgreSQL 15 RAISED the default to 2.0 specifically to reduce the disk spills that the new (PG13) memory-bounded HashAggregate could cause. RAISING hash_mem_multiplier gives hash nodes more room = fewer spills, faster, but more RAM per hash node; LOWERING it (or running PG14's 1.0) shrinks that room = more spills, more temp-file I/O, slower. context=user, so it takes effect immediately and can be set per session/role/database.
- name
hash_mem_multiplier
- setting default
2 (PG 15+); 1 (PG 14)
- unit
(none — a multiplier on work_mem)
- vartype
real
- category
Resource Usage / Memory
- context
user
- min val
1
- max val
1000
- enumvals
(n/a — real)
- boot val
2 (PG 15+); 1 (PG 14)
- reset val
2 (PG 15+); 1 (PG 14)
- pending restart
false
- short desc
Multiple of "work_mem" to use for hash tables.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-21. setting/boot_val/reset_val = 1 on PG14 and 2 on PG15/16/17/18 (default_changed=TRUE — a genuine version cliff in the default). Proven live: SHOW boot_val = 1 on, = 2 on /16/17/18. context=user (session-changeable, no reload/restart). vartype=real, no unit, min_val=1, max_val=1000. category='Resource Usage / Memory', short_desc='Multiple of "work_mem" to use for hash tables.' min/max/context identical across all five versions; only the default moved (1->2 at PG15).
Version cliff in the DEFAULT: hash_mem_multiplier defaults to 1.0 on PG14 and 2.0 on PG15, 16, 17, 18 (default_changed=true; proven by live boot_val SHOW). The knob itself was introduced in PG13 (default 1.0); PG15 raised the default to 2.0 to cut hash spills. min=1, max=1000, context=user, vartype=real are identical on all five versions — only the shipped default changed. Practical consequence: a query that spills on PG14 (or on any version where someone left/forced 1.0) may run in-memory on PG15+ with the same work_mem, purely from the default change. Per-version detail in _guc_matrix_machine.
context=user: set it per session with SET hash_mem_multiplier = 4; (effective immediately), per role/database with ALTER ROLE/DATABASE . SET, or cluster-wide via postgresql.conf / ALTER SYSTEM + reload. CONTEXT PROOF (captured literal, identical on and): context=user -> SET hash_mem_multiplier = 3; SHOW hash_mem_multiplier; returns 3, no wrong-context error. BOUNDARY PROOF (captured literal, identical on and): the range is 1 . 1000 -> ALTER SYSTEM SET hash_mem_multiplier = 0.5; -> ERROR: 0.5 is outside the valid range for parameter "hash_mem_multiplier" (1 .. 1000) and = 1001 -> ERROR: 1001 is outside the valid range ... (1 .. 1000); 1 and 1000 are both accepted. The minimum of 1 means you can never give hash nodes LESS than plain work_mem (the old behavior floor); there is no 'disable' value because the product is always at least work_mem.
- amazon rds aurora
Settable via DB parameter group. On instances created before/after the PG15 upgrade, confirm whether the effective value is 1.0 or 2.0; for analytic workloads raising it (with memory headroom) reduces hash spills. RDS often scales work_mem-related memory with instance size, which interacts with the multiplier.
- azure flexible server
Exposed as a server parameter; tune for analytic queries that spill. context=user means per-session SET works for ad-hoc heavy queries.
- google cloud sql
Settable as a database flag; same guidance — product of work_mem and the multiplier is what matters, and concurrency bounds the safe ceiling.
- general
Available on every managed platform. The danger (hash spills from a low multiplier, e.g. PG14's 1.0) and the mechanism (budget = work_mem * multiplier) are identical to self-managed PostgreSQL; the OOM-under-concurrency risk of a high multiplier is also the same.
Common Pitfalls
- items
- Assuming hash and sort share one budget — they don't. Hash gets work_mem * hash_mem_multiplier; sort gets work_mem. A 'spilling sort' is fixed by work_mem, a 'spilling hash' by either work_mem or the multiplier.
- Running PG14 (default 1.0) and wondering why analytic queries spill more than on PG15+: it's the default change. Setting hash_mem_multiplier=2 on PG14 matches later versions.
- Setting it back to 1.0 'for the old behavior' or 'to save memory' and silently reinstating spills (measured ~87MB temp files on a 100k-group aggregate at work_mem=4MB).
- Cranking it high with a big work_mem and high concurrency -> OOM, because the budget is per hash node per worker per query.
- Tuning work_mem to fix a hash spill and over-allocating sorts as a side effect, when raising hash_mem_multiplier would have targeted only the hash nodes.
- Reading the multiplier in isolation: '2.0' is meaningless without work_mem — the real budget is the product.
Evidence and References
- PostgreSQL docs: Resource Consumption -> Memory (work_mem, hash_mem_multiplier).
- PostgreSQL 15 release notes: default hash_mem_multiplier raised from 1.0 to 2.0.
- PostgreSQL 13 release notes: introduction of memory-bounded HashAggregate and hash_mem_multiplier.
- pg_settings ground truth captured on PG 14-18 (-18), 2026-06-21: default 1.0 on PG14, 2.0 on PG15+, real, context=user, min 1, max 1000 (default_changed=true; proven live via boot_val).
- EFFECT/DANGER/TIMING + BOUNDARY captures: hmm_timing.txt, hmm_boundary.txt (spill sweep + best-of-3; +boundary; -18 default cliff).
Technical Reference
- Category
- Resource Usage / Memory
- Type
- real (none (a multiplier applied to work_mem))
- Blast radius
- query
- Severity
- Low
- Context
- user
- Versions
- 14 (default 1.0), 15, 16, 17, 18 (default 2.0)
- Reviewed
- 2026-06-21
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.