work_mem

Per-operation memory for sorts and hash joins: too low spills to disk; too high times active connections crashes the whole server.

RISK: HIGH LAB VALIDATED

Category: Resource / Memory
Default: 4MB (4096 kB)
Type: integer (kB)
Context: user
Blast Radius: Cluster
Versions: PG 14 – 18
Severity: ⚡ 5 / 5
Reviewed: 2026-06-19

Safe Starting Point

Keep cluster default 4MB. Escalate per-role for analytics only.

Answers the Question

Why is my query spilling to disk? Why did my database crash?

Failure Pattern

Too high × concurrency = OOM kills cluster. Too low = disk spill.

slow queries sort spill disk temp files database crash OOM cluster restart temp_bytes rising

What It Actually Does

work_mem sets the maximum memory for each individual sort or hash operation — not per query, not per connection. It is a ceiling, not a reservation: only the memory actually needed (up to the limit) is allocated.

A single complex query with 3 sorts and 2 hash joins can open 5 separate work_mem workspaces simultaneously. Multiply by concurrent connections to get the real memory exposure.

What it controls

check_circle Memory per sort node (ORDER BY, DISTINCT, merge join)

check_circle Memory per hash node (hash join, hash aggregate)

check_circle The spill-to-disk threshold: below the sort workspace, writes temp files

check_circle Hash nodes additionally use work_mem × hash_mem_multiplier

What it does NOT control

cancel Per-query memory (one query can open multiple workspaces)

cancel Per-connection memory budget

cancel Index scans, sequential scans, or OLTP lookups (no sort = no effect)

cancel shared_buffers (a completely separate allocation)

Common Misconception

“I raised work_mem to 256MB so each connection gets 256MB.” — False. Each operation node gets up to 256MB. A query with 4 nodes × 50 connections = 200 potential workspaces × 256MB = 50GB exposure on a 32GB server. This is the exact shape that triggers OOM kills.

Memory Structure — One Query, Multiple Workspaces

Diagram showing a query spawning multiple work_mem workspaces

Lab Validated Evidence

Environment

pgc17 · PostgreSQL 17.10

Dataset

2M rows · 209MB

Container Limit

512MB · no swap

Runs per Point

3 reps · median

Benchmark Date

2026-06-19

OOM Reproduced

Yes — kernel dmesg

Key Findings

Sort needs ~48MB (49153kB). Below that threshold: disk spill, 730ms median. Above it: in-memory sort, 657ms median — 12% improvement for narrow integer keys.

Flat curve past the flip point. 64MB = 128MB = 256MB all measured 49153kB and the same latency. Extra memory beyond the sort requirement is never allocated.

In-memory is not always faster. Wide payload + parallel workers: disk sort 1815ms beat serial in-memory sort 5831ms. work_mem improvements are workload-specific.

OOM boundary measured: N=8 concurrent (512MB predicted) survives. N=10 (608MB predicted) OOMKills on a 512MB capped container. One kill drops all connections.

EXPLAIN’s “Sort Memory: 49153kB” understates reality. Backend anon-rss was 103516kB (~2×) for wide rows. Calculate budgets from measured RSS, not from EXPLAIN output.

Performance Curve

Threshold Curve Flip Point: 64MB (49,153kB workspace)

Key Finding

Plan flips from external merge Disk to quicksort Memory at 64MB. The sort workspace requires 49,153kB (~48MB) of resident memory. Below that: disk spill. At 64MB: zero temp writes. Curve is flat past the flip — 64MB = 128MB = 256MB all measure 49153kB and ~655ms. work_mem is a ceiling, not a reservation.

work_mem	Sort Method	Median (ms)	Reps
64kB	Disk	1003.4	3
1MB	Disk	867.2	3
4MB	Disk	730.8	3
16MB	Disk	741.2	3
32MB	Disk	759.3	3
64MB	Memory ← flip point	657.8	3
128MB	Memory	659.2	3
256MB	Memory	655.1	3

Honesty Note

The speedup above is specific to narrow integer keys, non-parallel. A second benchmark (wide text payload + 2 parallel workers) showed the opposite: disk sort 1815ms beat in-memory sort 5831ms. Parallel external merge on NVMe can be faster than serial in-memory quicksort for wide rows. Measure your actual workload.

Failure Story

Incident Trigger — Lab Reproduced

Container: postgres:17, –memory=512m, no swap, shared_buffers=128MB
Trigger: 6 concurrent sessions — SET work_mem='1GB'; SELECT payload FROM wm ORDER BY payload OFFSET 2000000;

1

Configuration Change

DBA raises work_mem from 4MB to 1GB cluster-wide via ALTER SYSTEM + pg_reload_conf() to fix a slow analytics report. No restart required — change is live immediately for new sessions.
2

Memory Pressure Builds

Six existing and new sessions immediately run sort queries. Each sort node allocates up to 1GB. The sort workspace per query reaches the required ~48MB — but each of 6 backends also accumulates anon-rss (~103MB each, ~2× EXPLAIN output). Combined with 128MB shared_buffers, RAM is exhausted.
3

OOM Kill

Linux kernel’s OOM killer targets the largest PostgreSQL backend process.

chevron_right Kernel OOM evidence — dmesg

Memory cgroup out of memory: Killed process 25608 (postgres) total-vm:471048kB, anon-rss:103516kB, file-rss:62672kB, oom_score_adj:0
4

PostgreSQL Restarts Entire Cluster

The postmaster detects a backend process killed by signal 9. It terminates ALL server processes and reinitialises — not just the one that was OOM-killed.

chevron_right PostgreSQL log evidence

2026-06-19 00:12:18.861 UTC [1] LOG: server process (PID 133) was terminated by signal 9: Killed 2026-06-19 00:12:18.862 UTC [1] LOG: terminating any other active server processes 2026-06-19 00:12:18.874 UTC [1] LOG: all server processes terminated; reinitializing
5

All Connections Drop

Every client — OLTP transactions, read replicas, application pools — loses its database connection simultaneously. This is a full cluster availability event, not a slow query incident. Recovery time measured at 784ms (pg_reload_conf after RESET, pgc18).

Safe Usage Guide

Avoid

cancel Do not raise the global default just to fix one slow report. Every connection inherits the new ceiling immediately.

cancel Do not treat work_mem as a per-connection or per-query budget. One query can open multiple workspaces.

cancel Do not trust EXPLAIN’s “Sort Memory: NkB” as the RSS cost. Measured anon-rss was ~2× EXPLAIN output for wide rows.

cancel Do not assume in-memory is always faster. Parallel disk sort can outperform serial in-memory sort for wide payload + multiple workers.

cancel Do not forget hash_mem_multiplier. Hash joins get work_mem × hash_mem_multiplier. Default = 2.0 since PG15.

warning On memory-constrained servers (<16GB), treat any value above 64MB globally as dangerous without exact peak calculations.

Before / After Comparison

4MB (default)

external merge Disk

730ms

Too Low

Sorts spill to disk — temp_writes active, latency high

curve_int_17.txt

64MB

quicksort Memory

644ms

Sweet Spot

Sort fits in RAM — zero temp writes, 12% latency improvement

curve_int_17.txt

1GB × 6 sessions

quicksort Memory (until OOM)

—

Danger

OOMKilled=true — cluster reinitialised, all connections dropped

oom_inspect.txt + oom_pglog.txt

Version Matrix

Version	Default	Context	hash_mem_multiplier default	Notable Change
PG 14	4096 kB	user	1	hash_mem_multiplier=1
PG 15	4096 kB	user	2	hash_mem_multiplier=2 (changed)
PG 16	4096 kB	user	2	—
PG 17	4096 kB	user	2	—
PG 18	4096 kB	user	2	—

Source: pg_settings captured from pgc14–pgc18 (2026-06-24). work_mem default unchanged across all versions. hash_mem_multiplier changed from 1 → 2 in PG15. This doubles the memory budget for all hash operations.

Parameter Interactions

Parameter interaction graph showing work_mem connected to related PostgreSQL parameters

max_connections

multiplicative peak_mem = work_mem × 2 × concurrent_queries

N=8 (512MB) survives; N=10 (608MB) OOMKill on 512MB cap

LAB TESTED

hash_mem_multiplier

multiplicative hash_peak = work_mem × hash_mem_multiplier

Default changed PG14→1, PG15+→2. Hash joins silently get 2× your work_mem budget.

NOT BENCHMARKED

max_parallel_workers_per_gather

additive sort_mem = work_mem × (1 + workers)

plan_low_17.txt: 2 workers, each worker sort used 52-55MB disk separately.

LAB TESTED

shared_buffers

competing available = total_RAM − shared_buffers

formula_concurrency.txt: 512MB − 128MB shared_buffers = 384MB for sorts.

LAB TESTED

Operational Metadata

Blast Radius

explore Cluster

Time to Failure

timer Seconds

Reversibility

undo Instant

Upgrade Risk

upgrade None

Confidence

verified LAB VALIDATED

Observability

visibility Easy

Automation

smart_toy Requires Validation

Human Error Risk

person_alert Common Footgun

Workload Sensitivity

workspaces Workload Dependent

Environment

memory Memory Bound

Common Pitfalls

warning

Per-operation, not per-query

work_mem applies per sort/hash node. One query with 3 sorts + 2 hash joins opens 5 work_mem workspaces simultaneously.

warning

Global raise is dangerous

Raising the cluster default hands the ceiling to every connection at once. The reproduced OOM came from this exact shape: work_mem=1GB × 6 sessions on a 512MB server.

warning

EXPLAIN memory ≠ real RSS

EXPLAIN reported Sort Memory: 49153kB, but measured anon-rss was 103516kB (~2×) for wide rows. Size with headroom, not from EXPLAIN alone.

warning

In-memory isn’t always faster

plan_low_17.txt: parallel disk sort 1815ms beat serial in-memory sort 5831ms for wide payload + 2 workers. Context matters.

warning

Flat curve past flip point

Past the sort workspace requirement, extra memory is never allocated. 64MB = 128MB = 256MB all report 49153kB and the same latency.

warning

hash_mem_multiplier silent multiplier

Hash joins get work_mem × hash_mem_multiplier. Default = 2.0 since PG15. Your actual hash budget is double the work_mem you set.

warning

One OOM drops all connections

A single OOM-killed backend terminated ALL server processes and reinitialised (oom_pglog.txt). Every connection dropped cluster-wide.

Monitoring & Early Warning workspace_premium PRO

Early Warning Signal — Lab Confirmed (captures/early_warning_baseline.txt)

Baseline: temp_files=0, temp_bytes=0 after pg_stat_reset.
After 3 narrow-key sorts at work_mem=4MB: temp_files=3, temp_bytes=69MB.
After 3 sorts at work_mem=64MB (in-memory): 0 new temp_bytes.
Rising temp_bytes = sorts spilling to disk right now.

Monitoring SQL Queries

All 4 queries were executed against pgc17 (PostgreSQL 17.10) and outputs captured. Pro members get the exact SQL + captured output for spill detection, active query watch, current setting check, and per-role override audit.

Spill detection SQL Active query watch Current setting check Per-role override audit Captured real output

workspace_premium Unlock Pro Access

Per-Workload Guide workspace_premium PRO

The right work_mem depends entirely on your workload archetype. OLTP gets zero benefit from raising it. Analytics and ETL get measurable gains. Setting it globally is almost always wrong. Pro members get the full measured per-archetype breakdown with real EXPLAIN ANALYZE output.

Per-Workload Benchmark Results

4 workload archetypes run against pgc17. Real EXPLAIN ANALYZE output, measured latencies, and per-archetype recommendations.

OLTP point lookup Analytics full sort Write-heavy ETL Mixed OLTP+Analytics

workspace_premium Unlock Pro Access

Raw Evidence workspace_premium PRO

Full Lab Evidence

EXPLAIN plans, OOM kernel logs, benchmark raw output, and negative-control captures are available to Pro members.

EXPLAIN ANALYZE plans OOM kernel dmesg PostgreSQL pg_log Negative control capture Benchmark raw output

workspace_premium Unlock Pro Access

Key Risks

Cluster-wide availability event. A single OOM-killed backend terminates ALL server processes. This is not a slow query — it is a full database restart affecting every client.

Silent memory multiplication. work_mem × hash_mem_multiplier (default 2.0 since PG15) means hash joins use 2× your configured value without any explicit setting.

Concurrency multiplier. peak_mem ≈ work_mem × 2 × concurrent_queries. A modest per-session value becomes catastrophic at high concurrency.

Key Benchmark Results

Flip point: 64MB (workspace requires 49153kB ~48MB). Below: 730ms disk spill. Above: 657ms in-memory. Improvement: ~12% for narrow integer sort.

Flat curve: 64MB = 128MB = 256MB all measured 49153kB and ~655ms. Extra memory beyond the sort requirement is never allocated.

OOM boundary: N=8 concurrent at work_mem=128MB (512MB predicted) survives. N=10 (608MB predicted) kills the cluster on a 512MB capped server.

Write workload (100k rows INSERT from sorted SELECT): work_mem=4MB→1025ms, 64MB→563ms. 45% latency reduction for sort-heavy ETL.

work_mem