work_mem

The per-operation memory budget that determines when sorts and other query workspaces begin spilling to temporary files.

Note

Fact — official short description: “Sets the maximum memory to be used for query workspaces.”

Identity

Type , Valueinteger: Upstream pg_settings type
Context , Valueuser: Settable by an ordinary user
Unit , ValuekB: Raw unit
Range , Value64 – 2147483647: Raw limits in the last observed version
Enum values , Value—: — for non-enum types
Category , ValueResource Usage / Memory: Upstream classification
Latest boot value , Value4096: 4 MiB

Lifecycle

Fact	Value
First observed	PG9.0 (research boundary)
Present in	PG9.0–19 Beta 3
Removed in	No
Introduction commit	Not asserted: predates the PG9.0 research boundary
Commit date	—
Discussion	—

Default history

Measured PG9.0–19 Beta 3 boot defaults
Versions	Raw `boot_val`	Unit	Human value
PG9.0–9.3	`1024`	`kB`	1 MiB
PG9.4–19 Beta 3	`4096`	`kB`	4 MiB

How it works

work_mem is a base limit for each execution operation, not a reservation for an entire query or session. A complex plan can run several sorts or hash operations concurrently, and many sessions can do the same, so aggregate memory can be many times the configured value.

Sorts used by ORDER BY, DISTINCT, and merge joins generally use work_mem before spilling. Hash joins, hash aggregation, memoize nodes, and hash-based IN processing derive their limit from work_mem multiplied by hash_mem_multiplier.

Parallel query further multiplies exposure because resource limits such as work_mem apply to individual worker processes. The setting is therefore best understood together with plan shape, parallelism, and active-query concurrency.

Tuning advice

Tip

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

Workload	Guidance
OLTP	Keep the cluster-wide value conservative and size it against peak active backends, not the connection limit alone. Use transaction-, role-, or database-scoped overrides for known reporting jobs after checking actual spill behavior.
OLAP	Larger values can remove expensive sort and hash spills, but raise them with an explicit concurrency budget. Compare EXPLAIN (ANALYZE, BUFFERS) results and temporary-file statistics before and after each change.
Small nodes	Prefer the default or a low tens-of-megabytes setting and leave headroom for shared buffers, autovacuum, the operating system, and other processes. A single globally generous value is risky on a small host.

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	`64MB`	different	`{{ pg_workmem }}MB`
OLAP	`64MB`	different	`{{ pg_workmem }}MB`
CRIT	`64MB`	different	`{{ pg_workmem }}MB`
TINY	`32MB`	different	`{{ pg_workmem }}MB`

Caution

Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 = 64MB (dcs); OLAP: PG9.0–19 Beta 3 = 64MB (dcs); CRIT: PG9.0–19 Beta 3 = 64MB (dcs); TINY: PG9.0–19 Beta 3 = 32MB (dcs). Advice, pending human review — Editorial inference: the formula is intended to trade spill frequency against worst-case concurrent memory.

Common pitfalls

Treating work_mem as a per-connection or per-query cap; it is normally available to each eligible plan operation.
Ignoring parallel workers, which can each receive their own work_mem budget.
Raising work_mem to fix hash spills without accounting for hash_mem_multiplier.
Assuming temporary-table buffers are controlled here; those are governed by temp_buffers.

hash_mem_multiplier · temp_file_limit · log_temp_files · max_connections · max_parallel_workers_per_gather