🚨 PostgreSQL Error Guide: 'out of memory' — Diagnose and Fix

Overview

PostgreSQL raises an out-of-memory error when a backend cannot allocate the memory it needs, typically during a large sort, hash, or aggregate:

ERROR:  out of memory
DETAIL:  Failed on request of size 2097152 in memory context "ExecutorState".

A worse variant comes from the operating system: the Linux OOM killer terminates a backend (or the postmaster), and the server log shows an abrupt crash and recovery rather than a clean ERROR:

LOG:  server process (PID 24417) was terminated by signal 9: Killed
LOG:  terminating any other active server connections

The DETAIL line names the memory context, which points at what was allocating — ExecutorState, a hash table, or a sort.

Symptoms

• A specific heavy query (large sort, hash join, GROUP BY, or array_agg) fails with out of memory.
• Under high concurrency, queries that normally succeed start failing as many run at once.
• The whole server restarts and enters crash recovery after a backend is killed by signal 9.
• The kernel log (dmesg) shows Out of memory: Killed process ... postgres.
• Memory failures correlate with a recent increase in work_mem or max_connections.

Common Root Causes

• work_mem too high for the concurrency — work_mem is allocated per sort/hash node, per connection, and per parallel worker, so a generous value times many concurrent queries multiplies into more RAM than exists. This is the #1 cause.
• A single huge operation — a hash join or sort over a large dataset that needs more than work_mem, spilling or, for hash tables that can’t spill well, exhausting memory.
• Too many connections — each backend has baseline overhead; thousands of connections (no pooler) consume gigabytes before any query runs.
• maintenance_work_mem during VACUUM/CREATE INDEX set very high while several maintenance operations run.
• The Linux OOM killer reclaiming memory from Postgres because total system memory (DB + other processes) was oversubscribed.
• A memory leak in an extension or a pathological query building an enormous in-memory structure.
• Overcommit / no swap on a tight container with a low memory limit (cgroup OOM).

Diagnostic Workflow

Read the DETAIL context and size first — it tells you whether the executor, a hash, or a sort was allocating.

Distinguish a Postgres ERROR: out of memory (a single backend hitting a limit) from an OS OOM kill (signal 9). Check the kernel log:

dmesg -T | grep -i 'out of memory\|killed process'
journalctl -k | grep -i 'oom'

Inspect the memory-related settings and connection count:

SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;         -- current connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

Compute a worst-case work_mem exposure — roughly work_mem x sort/hash nodes per query x concurrent active queries x parallel workers — and compare to RAM.

Find the offending query’s plan to see how much it wants to sort/hash:

EXPLAIN (ANALYZE, BUFFERS) <the failing query>;
-- look for large Sort/Hash nodes and "Sort Method: external merge  Disk: ..." (spills)

See per-backend memory-ish signals and long-running heavy queries:

SELECT pid, state, now() - query_start AS runtime, left(query, 100)
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;

Check the OS memory picture:

free -h
cat /sys/fs/cgroup/memory.max 2>/dev/null   # container memory limit (cgroup v2)

Example Root Cause Analysis

A reporting cluster started throwing out of memory errors every morning at 8am, and twice the whole server crashed into recovery. dmesg confirmed the crashes were OS OOM kills:

Out of memory: Killed process 24417 (postgres) total-vm:9.4g

Checking settings revealed the trigger:

SHOW work_mem;         -- 512MB
SHOW max_connections;  -- 500

work_mem had been raised to 512MB to speed up a few analytical queries. At 8am a scheduled batch fired dozens of concurrent reports, each with multiple sort and hash nodes. With ~40 concurrent queries each opening several 512MB operations, worst-case demand was tens of gigabytes on a 16GB server — far beyond RAM. The high work_mem, safe for one query, was catastrophic under concurrency.

The fix lowered the global work_mem to a conservative value and applied a high value only to the specific analytics role that genuinely needed it, where concurrency was controlled:

ALTER SYSTEM SET work_mem = '32MB';          -- safe global default
SELECT pg_reload_conf();
ALTER ROLE analytics_batch SET work_mem = '256MB';  -- scoped, low concurrency

A connection pooler (PgBouncer) was also placed in front to cap real concurrency, and the morning OOM crashes stopped.

Prevention Best Practices

• Size work_mem against worst-case concurrency, not a single query: model work_mem x nodes x concurrent queries x parallel workers against RAM, keep the global default conservative, and grant higher values per-role or per-session only where concurrency is controlled.
• Put a connection pooler (PgBouncer) in front and cap max_connections; thousands of direct connections waste memory before any query runs.
• Leave headroom for the OS page cache and other processes — never allocate near 100% of RAM to Postgres settings.
• Watch for disk spills (Sort Method: external merge) in plans; they indicate work_mem pressure and, if you raise work_mem to stop them, recompute the concurrency math first.
• On Linux, tune vm.overcommit_memory/vm.overcommit_ratio and protect the postmaster from the OOM killer (OOMScoreAdjust) so a backend is killed before the whole cluster.
• In containers, set the memory limit with headroom above the sum of Postgres allocations, or the cgroup OOM killer will strike.

Quick Command Reference

-- Current memory settings and connection load
SHOW work_mem; SHOW maintenance_work_mem; SHOW max_connections;
SELECT count(*) FILTER (WHERE state='active') AS active, count(*) AS total
FROM pg_stat_activity;

-- Inspect a heavy query's sort/hash footprint
EXPLAIN (ANALYZE, BUFFERS) <query>;

-- Set a safe global default and a scoped high value
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();
ALTER ROLE analytics_batch SET work_mem = '256MB';

# Was it an OS OOM kill?
dmesg -T | grep -i 'out of memory\|killed process'
free -h

Conclusion

Postgres out-of-memory failures come in two flavors: a clean ERROR: out of memory from a backend hitting a limit, and a brutal OS OOM kill (signal 9) that crashes the server into recovery. Both usually trace back to the same root cause — work_mem sized for a single query but multiplied by concurrency, per node and per parallel worker, into more RAM than the box has. The reliable fix is a conservative global work_mem, high values scoped per-role where concurrency is controlled, a connection pooler to cap backends, and OS-level headroom so the page cache and postmaster survive. Model the worst case before raising any memory knob, and the morning OOM crashes stop.