In PostgreSQL, why is work_mem not a per-query or per-connection limit, and how does that change the way you choose its value?
answer
- count the nodes, not the queries
- sorts and hash tables
- a multiplier for hash-based nodes
- each parallel worker applies it again
basics
~20 swork_mem (default 4MB) budgets each sort or hash operation in a plan; hash nodes get work_mem times hash_mem_multiplier (2.0). A query holds several budgets, per parallel worker, across many sessions, so keep it modest globally and raise it locally.
solid answer
~40 s`work_mem` (default `4MB`, a unitless value is kB) is the memory each sort or hash *operation* may use before it writes temporary files. A plan with three sorts and two hash joins can hold five budgets at once, and hash-based nodes (hash joins, hash aggregation, Memoize, hashed `IN` subqueries) get `work_mem x hash_mem_multiplier`, which defaults to 2.0. With parallel query each worker applies the limit separately, and every session can do the same at the same moment. So the server-wide value is sized against the worst concurrent case, roughly sessions x nodes x processes, and kept modest; queries that genuinely need more get it locally, with `SET LOCAL work_mem = '256MB'` inside a report's transaction or a setting attached to the reporting role.
code
sql · 11 linesBEGIN;
SET LOCAL work_mem = '256MB';
SELECT carrier_id,
date_trunc('day', delivered_at) AS day,
sum(cost) AS total_cost
FROM route_legs
WHERE delivered_at >= date '2026-09-01'
AND delivered_at < date '2026-10-01'
GROUP BY carrier_id, day
ORDER BY total_cost DESC;
COMMIT;go deeper
Remember that work_mem defaults to 4MB, is measured in kB, and limits each sort or hash step before it spills to disk.
Explain the per-node rule, the 2.0 hash_mem_multiplier and the per-worker rule, and show the multiplication that makes the total exceed work_mem many times over.
Show how you keep the global value safe for peak concurrency and grant larger budgets per transaction or per role to the few queries that spill.
Treat memory as a capacity plan across workloads: which sessions may run heavy plans concurrently, and how pooling and parallelism limits keep that worst case bounded.
## What work_mem actually bounds `work_mem` is PostgreSQL's **base memory budget for one query operation**: a sort or a hash table that the executor builds while running a plan. When a sort or hash operation's data outgrows the budget it generally starts writing **temporary files** under `pgsql_tmp` instead of using more RAM. The default is `4MB`, and a value without a unit is read as **kilobytes**, so `work_mem = 64` means 64 kB, not 64 MB. The manual lists who uses it: - **Sorts**: `ORDER BY`, `DISTINCT`, and the inputs of merge joins. - **Hash tables**: hash joins, hash-based aggregation, Memoize nodes (which evict cached entries rather than spill), and hash-based processing of `IN` subqueries. Each of those nodes in a plan gets its **own** budget. The setting is not a ceiling for the query, the transaction or the connection. ## hash_mem_multiplier Hash-based operations are more sensitive to running short of memory than the equivalent sort, so their limit is `work_mem x hash_mem_multiplier`. The multiplier defaults to **2.0**, which means that with all defaults a hash join's table may grow to 8MB before it spills to batches on disk while a sort spills at 4MB. The manual suggests raising the multiplier, rather than `work_mem`, where hash spills are routine but a higher `work_mem` causes memory pressure, and mentions values of 2.0 to 8.0 where `work_mem` is already 40MB or more. ## Why the total is many times work_mem The manual's own warning: a complex query might run several sort and hash operations at the same time, and several sessions could be doing so concurrently, so the total can be many times `work_mem`. Three factors multiply: | factor | where it comes from | example | |---|---|---| | operations per plan | each Sort, Hash, HashAggregate, Memoize node | 4 nodes | | processes per query | the leader plus each parallel worker, which applies `work_mem` individually | 1 + 2 workers | | concurrent queries | every active session | 20 report sessions | With `work_mem = 256MB` and those numbers, the worst case is 4 x 3 x 20 x 256MB = 60 GB before counting the 2.0 multiplier on hash nodes, which is more than a 64 GB logistics server can give once `shared_buffers` and the kernel are paid for. Most queries use far less than their budgets, which is why a large value can look fine for weeks and then fail on the one day when many reports run together. ## What happens when a node needs more A node that exceeds its budget **does not fail**; it changes strategy: - a **sort** switches to an external merge using temporary files; - a **hash join** splits its input into batches and processes them one at a time; - **hash aggregation** writes groups that do not fit to disk and processes them later. `EXPLAIN (ANALYZE)` then shows lines such as `Sort Method: external merge Disk: 812344kB` or a hash node with more than one batch (reading those lines is part of reading EXPLAIN). The query gets slower, and the temporary files consume disk, which `temp_file_limit` can cap per process. The budget is an executor target rather than a hard operating-system limit, so a node can occasionally overshoot it. The opposite mistake is just as real: a `work_mem` far larger than any sort needs costs nothing on a quiet day, which is exactly why it survives review and then bites under concurrency. ## Choosing a value 1. Keep the **server-wide** value modest, sized against the worst plausible concurrency: the number of sessions that can be active at once, times a few nodes each, times the parallel processes per query. 2. Find the queries that spill, from `EXPLAIN (ANALYZE)` output or the temporary-file counters (part of the statistics views). 3. Grant more memory **only where it is needed**: `SET LOCAL work_mem` inside the reporting transaction, or a per-role or per-database setting for the reporting role (the mechanics belong with parameter settings). 4. Where only hash nodes spill, consider `hash_mem_multiplier` before `work_mem`. 5. Re-check the arithmetic whenever `max_connections`, a pooler's size or `max_parallel_workers_per_gather` changes. The engine-neutral argument for why per-operation memory is riskier than shared memory is general database theory; what is specific to PostgreSQL is the unit, the 2.0 multiplier, the per-worker rule and the fact that the limit can be changed per session, per role and per transaction.
- What does PostgreSQL do when a sort needs more than work_mem?It keeps going on disk: the sort becomes an external merge using temporary files under `pgsql_tmp`, and `EXPLAIN (ANALYZE)` reports `Sort Method: external merge` with the disk used. The query succeeds but runs slower and consumes temporary disk space, which `temp_file_limit` can cap per process; there is no out-of-memory error from merely exceeding `work_mem`.
- When would you raise hash_mem_multiplier instead of work_mem?When the spills are in hash joins or hash aggregation but raising `work_mem` would also enlarge every sort and push the server into memory pressure. The multiplier (default 2.0) only scales hash-based nodes; the manual suggests values of 2.0 to 8.0 where `work_mem` is already 40MB or more.
- work_mem is the total memory one query is allowed to use.
- A query that needs more than work_mem fails with an out-of-memory error.
- Parallel workers share a single work_mem budget for the whole query.
- hash_mem_multiplier defaults to 1.0, so hash tables get exactly work_mem.
- work_mem = 64 in postgresql.conf means 64 megabytes.