work_mem

Definition PG 18 manual

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (4MB). Note that a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, memoize nodes and hash-based processing of IN subqueries.

Hash-based operations are generally more sensitive to memory availability than equivalent sort-based operations. The memory limit for a hash table is computed by multiplying work_mem by hash_mem_multiplier. This makes it possible for hash-based operations to use an amount of memory that exceeds the usual work_mem base amount.

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.

Operational considerations

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.

Workload guidance

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.

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.

SMALL: 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.