skip to content

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?

level: middleimportance: must knowfreq 74%

answer

  1. count the nodes, not the queries
  2. sorts and hash tables
  3. a multiplier for hash-based nodes
  4. each parallel worker applies it again

basics

~20 s

work_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 lines
sql
BEGIN;
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

for a junior

Remember that work_mem defaults to 4MB, is measured in kB, and limits each sort or hash step before it spills to disk.

for a middle

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.

for a senior

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.

for a principal

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.