skip to content

A 64 GB PostgreSQL server for a logistics workload lost every session when the Linux OOM killer struck during month-end reports; which memory settings do you examine, and what do you change?

level: seniorimportance: should knowfreq 48%

answer

  1. shared_buffers is not the one that grows
  2. nodes x workers x sessions
  3. per-role budgets for the reporting role
  4. temp_file_limit guards disk, not RAM

basics

~20 s

Add up the worst case: shared_buffers plus work_mem times hash_mem_multiplier per sort or hash node, per parallel process, per active session, plus autovacuum and maintenance memory. Cut the global work_mem, grant more only to reporting, and bound concurrency.

solid answer

~40 s

The fixed part is `shared_buffers`; what grows under month-end load is private memory: `work_mem` per sort or hash node (times `hash_mem_multiplier`, 2.0, for hash nodes), times the leader and each parallel worker, times every active report session, plus `maintenance_work_mem` or `autovacuum_work_mem` per autovacuum worker. I would compute that worst case against the 64 GB, lower the server-wide `work_mem` to something safe for peak concurrency, give the reporting role a larger value with `ALTER ROLE ... SET work_mem`, cap the number of concurrent report sessions and parallel workers, and set `autovacuum_work_mem` explicitly. `temp_file_limit` then stops the extra spills from filling the disk. On the host, the manual recommends strict overcommit, so an oversized allocation usually fails with an `out of memory` error in one query instead of ending in a SIGKILL.

code

console · 5 lines
console
LOG:  client backend (PID 48213) was terminated by signal 9: Killed
DETAIL:  Failed process was running: SELECT carrier_id, sum(cost) FROM route_legs ...
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was not properly shut down; automatic recovery in progress

go deeper

for a junior

Recall that an OOM kill of one PostgreSQL backend resets every session, and that work_mem is the usual memory that multiplies.

for a middle

Explain the multiplication of work_mem by nodes, parallel processes and sessions, and why shared_buffers is fixed rather than growing.

for a senior

Walk through the worst-case arithmetic, cut the global budget, grant per-role memory, bound concurrency and add temp_file_limit and strict overcommit.

for a principal

Treat reporting and transactional traffic as separate capacity classes, with their own pools and memory budgets, rather than tuning one global value.

## What the logs show When the Linux out-of-memory killer picks a PostgreSQL backend, the kernel sends it SIGKILL. The postmaster sees a child die abnormally, cannot trust shared memory any more, terminates every other session and reinitialises, which runs crash recovery from WAL (the default `restart_after_crash = on`). In PostgreSQL 18 the server log shows `client backend (PID 48213) was terminated by signal 9: Killed`, then `terminating any other active server processes` and `all server processes terminated; reinitializing`.**Every** connection is dropped, not only the one running the report, which is why one oversized report can look like a whole-database outage. How the kernel chose its victim, and how to read the kernel log, belong to the operating system's OOM killer and memory analysis. ## Where the memory goes Only part of PostgreSQL's memory is fixed at start-up: | consumer | setting | multiplied by | |---|---|---| | shared buffer pool | `shared_buffers` | nothing: one fixed-size area | | sorts | `work_mem` | sort nodes x processes per query x active sessions | | hash tables | `work_mem x hash_mem_multiplier` (2.0) | hash nodes x processes x sessions | | autovacuum | `autovacuum_work_mem`, else `maintenance_work_mem` | `autovacuum_max_workers` (3) | | manual maintenance | `maintenance_work_mem` | concurrent index builds, vacuums, restores | A typical failure on a 64 GB logistics box: `shared_buffers = 16GB`, someone raised the global `work_mem` to `256MB` to speed up the reports, and at month-end 20 report sessions start together, each with two parallel workers and four sort or hash nodes. The worst case for work memory alone is 20 x 3 x 4 x 256MB = 60 GB before the hash multiplier; added to 16 GB of shared memory it exceeds the host. Normal days never reach it, so the setting looked safe for weeks. ## Confirming the diagnosis Before changing anything, tie the kill to PostgreSQL's own numbers: - The `DETAIL: Failed process was running:` line names the statement the killed backend was executing; it is usually one of the reports. - `SHOW work_mem`, `SHOW hash_mem_multiplier` and the role and database settings tell you which budget those sessions really had, which may differ from `postgresql.conf`. - `EXPLAIN (ANALYZE)` of the report on a quiet copy shows the sort and hash nodes, their memory usage and how many parallel workers were launched, which gives the per-query factor. - `pg_log_backend_memory_contexts(pid)` writes a running backend's memory contexts to the server log, and `pg_backend_memory_contexts` shows your own session's, for a query that grows while you watch. - The number of report sessions active at month-end gives the last factor of the multiplication. ## What to change 1. **Lower the server-wide `work_mem`** to a value that survives peak concurrency, for example 16MB to 32MB here, and recheck the arithmetic with the real `max_connections` and pooler size. 2. **Give memory to the workload that needs it**: `ALTER ROLE reporting SET work_mem = '256MB'`, or `SET LOCAL` inside each report's transaction. The mechanics of role settings belong with parameter settings. 3. **Bound how many heavy queries run at once**: a smaller pool for the reporting service (in PgBouncer) and a lower `max_parallel_workers_per_gather` for that role (a planner setting). Fewer concurrent processes shrink the multiplier directly. 4. **Keep `hash_mem_multiplier` in view**: it doubles every hash node's budget at its default, so do not raise it as well without redoing the sums. 5. **Set `autovacuum_work_mem`** so background vacuum cannot add `autovacuum_max_workers x maintenance_work_mem` during the same window. 6. **Leave `shared_buffers` alone** unless the arithmetic says it is genuinely too large; its size does not grow with the number of sessions. ## Guarding the disk side Lowering `work_mem` turns some memory use into temporary files. `temp_file_limit` (default `-1`, no limit; settable only by superusers or roles granted the right to set it) caps the temporary-file space **per process**; a query that goes past it is cancelled with SQLSTATE `53400` and the message *temporary file size exceeds "temp_file_limit"*. That protects the data volume from a runaway report, but it limits disk only, never RAM. ## Host-side settings The manual's own advice for Linux (the kernel mechanics behind it are covered in the OS leaves): - **Strict overcommit** (`vm.overcommit_memory=2`) makes most allocations that cannot be backed fail inside PostgreSQL, so one query ends with `ERROR: out of memory` (SQLSTATE `53200`) instead of a SIGKILL that restarts the whole instance; the manual says it lowers the chance of an OOM kill significantly rather than ruling it out. - Setting the postmaster's `oom_score_adj` to `-1000` keeps the supervisor itself out of the killer's choice. - The manual also lists lowering `shared_buffers`, `work_mem` and `hash_mem_multiplier`, and reducing `max_connections` in favour of an external pooler, as the PostgreSQL-side remedies.

  • Why not just set temp_file_limit to stop the OOM kills?
    Because `temp_file_limit` caps temporary-file disk space per process, not RAM. Memory is consumed before anything spills, inside the `work_mem` budgets. The limit becomes useful after you lower `work_mem`: more operations spill, and the limit cancels a runaway report with SQLSTATE `53400` before it fills the data volume.
  • What does strict overcommit change about how PostgreSQL fails?
    With `vm.overcommit_memory=2` the kernel refuses most allocations it cannot back, so the backend usually sees the failure and reports `ERROR: out of memory` (SQLSTATE `53200`); that one query aborts and the other sessions keep running. Under the default overcommit the allocation succeeds, the kernel may later SIGKILL a backend, and the postmaster resets every session. The manual presents it as lowering that risk, not removing it.

  • Lower shared_buffers first, since it grows as more reports run.
  • work_mem is a hard per-query cap, so connections times work_mem is the worst case.
  • temp_file_limit stops a query from using too much RAM.
  • Only the killed backend's session is lost; other sessions keep running.
  • Raise max_connections so month-end reports queue less and finish sooner.