hash_mem_multiplier

The multiplier that lets hash-based executor operations use a larger memory budget than the work_mem base.

Note

Fact — official short description: “Multiple of “work_mem” to use for hash tables.”

Identity

Type , Valuereal: Upstream pg_settings type
Context , Valueuser: Settable by an ordinary user
Unit , Value—: Raw unit
Range , Value1 – 1000: Raw limits in the last observed version
Enum values , Value—: — for non-enum types
Category , ValueResource Usage / Memory: Upstream classification
Latest boot value , Value2: 2

Lifecycle

Fact	Value
First observed	PG13
Present in	PG13–19 Beta 3
Removed in	No
Introduction commit	`78530c8e7a5a` — Add hash_mem_multiplier GUC.
Commit date	2020-07-29
Discussion	thread 1 · thread 2

Default history

Measured PG9.0–19 Beta 3 boot defaults
Versions	Raw `boot_val`	Unit	Human value
PG13–14	`1`	—	1
PG15–19 Beta 3	`2`	—	2

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.

Tuning advice

Tip

Advice. These are workload-specific starting points and must be validated with measurements.

Workload	Guidance
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.
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.
Small nodes	Stay near the default. A high multiplier can turn a seemingly modest work_mem into hundreds of megabytes per hash operation.

Pigsty

Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.

Template	Effective value	Versus upstream boot	Source expression
OLTP	`8.0`	different	`8.0`
OLAP	`8.0`	different	`8.0`
CRIT	`8.0`	different	`8.0`
TINY	`8.0`	different	`8.0`

Caution

Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG13–19 Beta 3 = 8.0 (dcs); OLAP: PG13–19 Beta 3 = 8.0 (dcs); CRIT: PG13–19 Beta 3 = 8.0 (dcs); TINY: PG13–19 Beta 3 = 8.0 (dcs). Advice, pending human review — Editorial inference: Pigsty bounds the base work_mem and then gives memory-sensitive hash operations a larger allowance; the resulting per-operation products and parallel concurrency require explicit human review.

Common pitfalls

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.

work_mem · temp_file_limit · enable_hashjoin · enable_hashagg · max_parallel_workers_per_gather