I85PREP
⌘ K
EDB PostgreSQL Associate study materials
EDB PostgreSQL Associate study resources

Nightly OOM kills on a 64 GB primary

EDB PostgreSQL Associate · Put your knowledge to work in context.

All case studies

Nightly OOM kills on a 64 GB primary

A 64 GB / 16-core primary runs PostgreSQL with max_connections = 500, shared_buffers = 16 GB and work_mem = 64 MB; hash_mem_multiplier is left at its default of 2.0. Application servers connect directly with no pooler. During the nightly reporting window roughly 150 sessions run concurrently, most of them executing two or three sorts or hash joins each. Twice this month the kernel OOM killer terminated the postmaster, and dmesg shows the postgres backends were the largest consumers. pg_stat_database shows temp_bytes growing by about 40 GB every night. Management asks you to stop the crashes without making the reports slower, and a developer proposes raising work_mem to 256 MB so that nothing spills to disk.

1. Estimate the worst-case work_mem footprint of the reporting window and evaluate the developer's proposal.

Read the worked answer

work_mem is a per-sort-or-hash-node limit, not a per-connection limit, and hash nodes are allowed hash_mem_multiplier times that amount.

  • Per node worst case: 64 MB x 2.0 = 128 MB
  • Per reporting session with 3 nodes: ~384 MB
  • 150 concurrent sessions: ~57 GB, on top of 16 GB shared_buffers and the OS page cache

That already exceeds comfortable headroom on a 64 GB box. Raising work_mem to 256 MB makes the ceiling 150 x 3 x 512 MB, roughly 230 GB. The proposal makes the crash more likely, not less.

Two further points matter:

  1. work_mem is an allocation ceiling, not a reservation. Backends allocate incrementally, so the observed memory is usually well below the worst case. A crash therefore means a handful of very large concurrent queries, not 150 medium ones. Confirm with pg_stat_activity sampling and log_temp_files = 0 before sizing.
  2. Spilling is not automatically a problem. 40 GB of temp files per night is cheap compared with an OOM kill; the goal is a bounded, predictable footprint.

2. Which changes do you make, in what order, and how do you confirm they worked?

Read the worked answer

Order of work - biggest, cheapest lever first.

  1. Reduce concurrency. Put a pooler (pgbouncer in transaction mode) in front, or lower max_connections to ~120 and size the pool per app. This reduces the multiplier on every memory setting at once and is the only change that helps with all future per-node settings.
  2. Set a conservative global default: work_mem = 8-16 MB for normal OLTP.
  3. Grant memory where it is needed, not globally: ALTER ROLE reporting SET work_mem = '128MB'; and for individual monster queries use SET LOCAL work_mem inside the transaction so it reverts automatically.
  4. Cap autovacuum's own footprint separately: maintenance_work_mem times autovacuum_max_workers, plus each worker may use up to autovacuum_work_mem.
  5. Protect the box: confirm vm.overcommit_memory = 2 with sane overcommit ratios, or at minimum a cgroup/systemd MemoryMax on the postgres service so a runaway query is killed instead of the postmaster.

Verification

  • Set log_temp_files = 256MB (logs only large spills); watch count and size trend.
  • Track pg_stat_database.temp_bytes and pg_stat_database.blks_read daily.
  • Sample ps RSS during the reporting window; the sum of backend RSS must stay well under RAM minus shared_buffers.
  • Run the reporting window for a week with no OOM before declaring done. A single quiet night proves nothing.