Parameter · Resource Usage / Memory
hash_mem_multiplier
Multiple of "work_mem" to use for hash tables.
- Default:
2 - Type: real
- Range: 1 … 1000
- Applies: Session (any user)
In plain words
Hash-based steps (hash joins, hash aggregation) suffer more than sorts when they run out of memory. So PostgreSQL gives them a bigger allowance: work_mem × hash_mem_multiplier. With the default 2.0 and work_mem 4 MB, a hash table may use 8 MB.
For DBAs and operators
Introduced in PostgreSQL 13 (default 1.0), default raised to 2.0 in PostgreSQL 15. Applies to hash joins, hash aggregation (which since 13 spills to disk instead of exceeding memory), memoize nodes and hashed SubPlans. Each parallel worker gets its own allowance, and a Parallel Hash join shares one table sized by the combined allowance of the participants.
Why it exists: a sort that spills degrades gracefully (external merge), while a hash join that does not fit has to split into batches, re-reading and re-writing both inputs. Raising only the hash budget is a way to fix hash spills without giving every sort more memory.
Observe
EXPLAIN (ANALYZE) ...
-- Hash Buckets: 131072 Batches: 16 Memory Usage: 8193kB
-- HashAggregate Batches: 5 Memory Usage: 8241kB Disk Usage: 30720kBBatches: 1 means it fit. The docs suggest 2.0–8.0 or more when work_mem is already ≥ 40 MB and spills are routine. Because the allowance is per hash node, a query with several hash joins multiplies it again; watch total memory, not just one node.
How to change it
Any user can change it for their session with SET.