Pool Size and the Total work_mem Budget #

work_mem looks like a per-query setting and behaves like a per-node, per-backend, per-worker one. That difference is why a value chosen to fix one report can exhaust a server’s memory once a connection pool multiplies it across a hundred backends.

This page gives the arithmetic that turns a proposed work_mem into a worst-case figure you can compare against actual RAM. The pooling context is in connection pooling and plan cache.

The Multiplication Chain #

Four factors compound, in this order:

worst case ≈ work_mem
           × memory-hungry nodes per plan
           × hash_mem_multiplier   (for hash-based nodes: hash join, hash aggregate)
           × (parallel workers + 1)
           × concurrent backends running such plans

A worked example makes the scale obvious. With work_mem = 64MB, a reporting plan containing one sort and two hash nodes, hash_mem_multiplier = 2.0, four parallel workers, and twenty concurrent report sessions:

sort:          64 MB × 1 node                    =   64 MB
hash nodes:    64 MB × 2.0 × 2 nodes             =  256 MB
per backend:                                        320 MB
× 5 participants (4 workers + leader)            = 1600 MB
× 20 concurrent sessions                         = 32000 MB  ≈ 31 GB

Thirty-one gigabytes from a setting that reads as “64 MB”. And this sits on top of shared_buffers, the OS page cache, and every other backend’s baseline footprint.

How 64 MB becomes 31 GB Four multiplication stages shown as widening bars: the base allowance, multiplied by memory nodes and the hash multiplier, then by parallel participants, then by concurrent sessions. work_mem 64 MB × nodes (1 sort + 2 hash × 2.0) 320 MB per backend × 5 parallel participants 1.6 GB per query × 20 concurrent sessions ≈ 31 GB worst case — before shared_buffers

Annotated Evidence #

Count the memory-hungry nodes directly in the plan — every one of them can hold an allowance at the same time:

Finalize GroupAggregate  (actual rows=118422 loops=1)
  ->  Gather Merge  (actual rows=473688 loops=1)
        Workers Planned: 4  Workers Launched: 4          -- ×5 participants
        ->  Sort  (actual rows=94738 loops=5)
              Sort Method: quicksort  Memory: 61204kB    -- allowance #1
              ->  Partial HashAggregate  (actual rows=94738 loops=5)
                    Peak Memory Usage: 128402 kB         -- allowance #2 (×2.0 multiplier)
                    ->  Hash Join  (actual rows=824066 loops=5)
                          ->  Hash  (actual rows=200000 loops=5)
                                Batches: 1  Memory Usage: 121804kB   -- allowance #3
Execution Time: 4102.1 ms

Three memory nodes, five participants: this single query held roughly 1.5 GB while it ran. loops=5 on each of those nodes is the multiplier made visible — every participant executed its own copy.

Then check how many such queries can run at once:

SHOW max_connections;                       -- server ceiling
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
-- and, at the pooler:
--   PgBouncer:  SHOW POOLS;   → the server-side connection count is what matters

Behind a transaction-mode pooler the number that matters is the server-side pool size, not the client count. Ten thousand clients sharing forty server connections can hold at most forty simultaneous allowances of each node.

Step-by-Step Budgeting #

  1. Take three representative plans — the heaviest report, the typical page query, and the heaviest write path.

  2. Count nodes that claim an allowance: Sort, Hash, HashAggregate, Bitmap Heap Scan (its bitmap), and materialised CTEs.

  3. Apply the hash multiplier to the hash-based ones:

    SHOW hash_mem_multiplier;    -- default 2.0

  4. Multiply by parallel participants for any plan containing a Gather, using Workers Planned + 1.

  5. Multiply by the realistic concurrency, which behind a pooler is the server-side pool size:

    ; pgbouncer.ini
default_pool_size = 40
max_client_conn = 10000

  6. Compare against RAM minus shared_buffers and OS overhead. If the worst case exceeds it, lower the allowance or the concurrency rather than hoping the peak never arrives.

  7. Scope generous values to the workload that needs them:

    ALTER ROLE analytics SET work_mem = '256MB';   -- reporting only
-- or, per statement:
BEGIN;
SET LOCAL work_mem = '512MB';
-- heavy query here
COMMIT;

    SET LOCAL is essential behind a pooler — a plain SET leaks to the next client that borrows the connection, which is exactly the session parameter drift problem.

Global versus scoped allowance With a global increase, all forty pooled backends can claim the large allowance. With a role-scoped increase, only the four reporting backends can, while the OLTP backends keep the small default. global work_mem = 256 MB 40 pooled backends × 256 MB × nodes → tens of GB worst case ALTER ROLE analytics SET work_mem = '256MB' 4 rpt 36 OLTP backends stay at the 4 MB default same query performance for the reports · a fraction of the worst-case footprint large allowance default

Before and After #

-- BEFORE: global work_mem raised to 256MB to fix one report
worst case ≈ 256 MB × 3 nodes × 5 participants × 40 pooled backends ≈ 153 GB
observed   : OOM killer terminating backends under peak load

-- AFTER: default restored to 16MB, ALTER ROLE analytics SET work_mem = '256MB'
worst case ≈ 256 MB × 3 × 5 × 4 reporting backends  ≈ 15 GB
             + 16 MB × 3 × 36 OLTP backends          ≈  1.7 GB
report performance: unchanged

The report kept its memory. The other thirty-six backends stopped being entitled to it.

Common Pitfalls #

Treating work_mem as per query. Each memory node claims it independently. Diagnostic signal: a plan with several Sort and Hash nodes each reporting large Memory Usage. Fix: count nodes before choosing a value.

Forgetting parallel workers. Each participant gets its own allowance. Diagnostic signal: memory use tracking Workers Launched. Fix: multiply by participants, and cap max_parallel_workers_per_gather on memory-constrained servers.

Sizing against client connections rather than server connections. Behind a transaction-mode pooler only the server-side pool can hold allowances. Diagnostic signal: a budget built from max_client_conn. Fix: budget from default_pool_size.

Using plain SET behind a pooler. The value leaks to whoever borrows the connection next. Diagnostic signal: OLTP backends with an unexpected work_mem in pg_settings sourced from session. Fix: SET LOCAL, or role-level defaults.

Budgeting only for the steady state. Peak concurrency is what exhausts memory, and it arrives with a retry storm or a scheduled report overlapping the daily traffic peak. Diagnostic signal: an incident timeline showing memory exhaustion at a predictable hour. Fix: size the worst case against peak concurrency, not average, and cap the reporting pool separately from the application pool.

Ignoring the per-backend baseline. Beyond work_mem, each backend holds catalog caches, plan caches, and its own temporary buffers, which on a long-lived pooled connection grows to tens of megabytes. Diagnostic signal: resident memory per backend far above the allowance arithmetic. Fix: include a per-backend baseline in the budget, and recycle pooled server connections periodically.

Assuming temp_file_limit protects memory. It caps temporary file usage, which is what a spill writes to disk — it does not limit the in-memory allowance at all. Diagnostic signal: a limit set in the belief it bounds RAM. Fix: bound memory with work_mem and concurrency, and use temp_file_limit for what it does, which is stopping one runaway query from filling the disk.

Budget from server connections, not clients Ten thousand client connections funnel through the pooler into forty server connections, and only those forty can hold a memory allowance at any moment. clients 10,000 pooler transaction mode server connections 40 — the real multiplier worst-case memory scales with the right-hand box, never the left-hand one

Frequently Asked Questions #

Is work_mem per query or per operation? Per operation. Every sort, hash join, hash aggregate, and bitmap build in a plan may hold its own allowance simultaneously, and hash-based nodes get work_mem × hash_mem_multiplier on top.

How do parallel workers change it? Each participating worker runs its own copy of the memory-hungry nodes with its own allowance, so a plan with two hash nodes and four workers plus the leader can hold ten allowances at once.

Global or per role? Per role or per transaction. A global increase entitles every pooled backend to the same allowance, multiplying worst-case memory by the pool size, while a role-scoped value confines the cost to the workload that benefits.