work_mem

work_mem is the memory budget for a single in-memory operation — a sort, a hash join's hash table, a hash aggregate, a bitmap. When an operation needs more than work_mem it spills to temporary files on disk, which is much slower. Crucially, the limit is per operation per connection, so one query can use several multiples of work_mem and many connections multiply it again.

Set it high enough that your typical sorts and hashes stay in memory, but conservatively enough that peak concurrency doesn't exhaust RAM. A useful tactic is a modest global value plus a per-query SET LOCAL work_mem for the few heavy reports that need more. Watch EXPLAIN (ANALYZE) for 'external merge Disk:' (sorts) and 'Batches: N>1' (hashes) — those are work_mem spills.

The multiplication trap

The single most important thing to understand about work_mem is that it is a budget per operation, per connection — not a global ceiling. One query can contain several sorts and hashes, each allowed its own work_mem. And every concurrent connection runs its own query. So the realistic worst case is roughly:

peak memory ≈ work_mem × (operations per query) × (active connections)

That's why a 1 GB global work_mem on a server with 200 connections is a recipe for out-of-memory under load, even though any single sort looks fine.

A practical strategy

1. Keep the global work_mem modest (often tens of MB) so high concurrency stays safe.

2. For the handful of heavy reports that genuinely need more, raise it just for that statement:

   SET LOCAL work_mem = '512MB';
   SELECT … ;  -- big sort / hash
   

3. Consider hash_mem_multiplier — hash-based nodes (hash joins, hash aggregates) get work_mem × hash_mem_multiplier, letting you give hashes more room than sorts without raising the base value.

Spotting a spill

In EXPLAIN (ANALYZE):

• Sort that spilled: Sort Method: external merge Disk: 34824kB. In-memory it would say quicksort Memory: …kB.
• Hash join that spilled: Batches: 32 (anything > 1).
• HashAggregate that spilled: Disk Usage: …kB and temp-file I/O.

Each of those is work_mem being exceeded on that node — raise it for the query (or add an index that removes the sort entirely).

• Sort spilled to disk
• Hash build spilled across N batches
• HashAggregate touched temp files

Paste a plan into the analyzer →

• Sort
• Hash Join
• Aggregate
• Bitmap Heap Scan

Why is work_mem 'per operation' dangerous?

A single query with several sorts/hashes can use work_mem several times over, and every concurrent connection does too. work_mem × operations × connections is the real worst-case — which is why a huge global work_mem can trigger out-of-memory under load.

How do I stop sorts from spilling to disk?

Raise work_mem for that query (SET LOCAL work_mem = '256MB'), or add an index that returns rows already ordered so the sort disappears. Raising it globally works too, but mind total memory under concurrency.

• hash_mem_multiplier
• maintenance_work_mem
• shared_buffers
• max_connections