hash_mem_multiplier

Definition PG 18 manual

Used to compute the maximum amount of memory that hash-based operations can use. The final limit is determined by multiplying work_mem by hash_mem_multiplier. The default value is 2.0, which makes hash-based operations use twice the usual work_mem base amount.

Consider increasing hash_mem_multiplier in environments where spilling by query operations is a regular occurrence, especially when simply increasing work_mem results in memory pressure (memory pressure typically takes the form of intermittent out of memory errors). The default setting of 2.0 is often effective with mixed workloads. Higher settings in the range of 2.0 - 8.0 or more may be effective in environments where work_mem has already been increased to 40MB or more.

How it works

The memory ceiling for a hash table is work_mem multiplied by hash_mem_multiplier. It applies to hash joins, hash aggregation, memoize nodes, and other hash-based executor work, but does not enlarge the limit for sort operations.

The parameter first appears in the PG9.0–19 Beta 3 inventory in PostgreSQL 13. Its boot default is 1.0 in PostgreSQL 13–14 and 2.0 from PostgreSQL 15 onward.

A query can contain several hash operations, and parallel workers can execute their own operations, so the product is still an operation-level limit rather than a whole-query memory cap.

Operational considerations

Reading the value as an absolute memory size rather than a multiplier of work_mem.

Forgetting that it has no effect before PostgreSQL 13 and that the upstream default changed in PostgreSQL 15.

Expecting it to help sorts, which remain governed by work_mem.

Multiplying only once per query despite multiple hash nodes or parallel workers.

Workload guidance

OLAP: Higher values can help large hash joins and aggregations when memory is genuinely available. Increase under a controlled concurrency ceiling and compare batches, spill volume, and end-to-end runtime.

OLTP: Keep the upstream default or make modest increases only after proving recurring hash spills. Evaluate the product with work_mem and peak active plans; do not tune the multiplier in isolation.

SMALL: Stay near the default. A high multiplier can turn a seemingly modest work_mem into hundreds of megabytes per hash operation.