Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-oom~40 min

One analyst query was killed by the kernel and every other session on the cluster died with it

Reported symptoms

  • ●At 14:22 every connection to the primary was dropped simultaneously and new connections were refused for eleven seconds
  • ●The application logged connection reset by peer across every service at the same instant
  • ●Uncommitted work in unrelated transactions was rolled back
  • ●The cluster returned on its own with no operator action and no data loss
  • ●Host monitoring shows a memory spike immediately before the event and normal memory afterwards
  • ●One analyst reports that a query they had been running for several minutes returned server closed the connection unexpectedly
  • ●The same event has now happened four times in six weeks, always during business hours, always briefly

Evidence

  • · The server log records LOG: client backend (PID 119) was terminated by signal 9: Killed
  • · The next line is DETAIL: Failed process was running: followed by the analyst query text
  • · The line after that is LOG: terminating any other active server processes
  • · Then LOG: all server processes terminated; reinitializing
  • · Connections during the following seconds received FATAL: the database system is in recovery mode
  • · Redo completed in 0.80 seconds and the cluster reported database system is ready to accept connections eleven seconds after the kill
  • · The failed query was a single aggregate building one array over every row of a 579 MB table
  • · oom_score_adj is 0 for the postmaster and 0 for every backend, so the kernel treats them all alike
  • · A large sort with an absurd work_mem on the same cluster completed successfully by spilling to disk rather than exhausting memory
Diagnosis and resolutionclick to reveal

Root cause

The kernel's out-of-memory killer sent `SIGKILL` to a single backend, and the postmaster responded by restarting the whole cluster. That response is correct and it is not configurable. A backend killed with `SIGKILL` gets no opportunity to clean up. It may have been holding a lightweight lock on a shared buffer, or partway through modifying a shared data structure. The postmaster has no way to determine whether shared memory is consistent, so it must assume it is not: it terminates every other backend, discards shared memory, and re-initialises from the last checkpoint. So the blast radius of one runaway query is the entire cluster. Every session dies, every uncommitted transaction rolls back, and the cluster is unavailable for the duration of crash recovery — 11 seconds here, and proportional to the WAL written since the last checkpoint, which on a busy cluster with a long `checkpoint_timeout` can be minutes. The query itself is the second half of the story, and it is more specific than "a big query". PostgreSQL's sorts and hash aggregates **spill to disk** when they exceed `work_mem`. On this same cluster, a sort of two million rows with `work_mem` set to 2 GB inside a 768 MB container completed successfully by spilling. `work_mem` is a target, not a reservation, and a large sort is not what exhausts memory. What exhausts memory is an allocation that cannot spill. Building a single array over every row of a 579 MB table produces one contiguous in-memory value with no spill path. That is the shape to look for: `array_agg`, `string_agg`, `json_agg` and similar aggregates over unbounded input; very large `IN` lists; enormous single values. These grow without bound and no memory setting constrains them. `oom_score_adj` is 0 for the postmaster and 0 for every backend, so the kernel chooses purely on memory footprint. That usually means the guilty backend is chosen, which is fortunate — but nothing guarantees it, and if the postmaster is chosen the outcome is worse.

Remediation

Identify the query from the log. PostgreSQL records it, and this is the one piece of evidence that makes the incident actionable: ```bash grep -A3 'terminated by signal 9' /var/log/postgresql/postgresql-18-main.log ``` The `DETAIL: Failed process was running:` line carries the full statement text. Confirm the shape of the memory demand rather than assuming it was `work_mem`. Aggregates that build one large value cannot spill: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT ...; ``` A plan whose top node is an aggregate producing a single enormous value has no spill point. A `Sort` reporting `external merge` or a `HashAggregate` reporting `Batches: 4` is spilling and is not your problem. Bound what a single session may consume, per role, rather than globally: ```sql ALTER ROLE analytics SET work_mem = '32MB'; ALTER ROLE analytics SET statement_timeout = '5min'; ALTER ROLE analytics SET temp_file_limit = '10GB'; ``` `statement_timeout` is the control that actually helps against an unbounded allocation, because the allocation is unbounded in *time* as well as memory — the query here had been running for several minutes. Nothing else will stop it. Protect the postmaster from the kernel's choice. On a systemd host, adjusting the service's OOM score makes the kernel prefer a backend over the postmaster: ```ini # /etc/systemd/system/postgresql@.service.d/oom.conf [Service] OOMScoreAdjust=-900 ``` PostgreSQL propagates a compensating adjustment to child processes when built with the appropriate support; check `/proc/<pid>/oom_score_adj` for the postmaster and a backend after applying it, and confirm they differ. If they do not, the setting is not doing what you intended. Set the host's overcommit policy deliberately. With `vm.overcommit_memory = 2`, the kernel refuses allocations beyond a computed limit instead of granting them and killing later — so PostgreSQL receives an allocation failure and the query fails with an ordinary error, leaving the cluster up: ```bash sysctl -w vm.overcommit_memory=2 sysctl -w vm.overcommit_ratio=80 ``` This is a real trade. Strict overcommit can cause allocation failures in workloads that would otherwise have succeeded. It is the right default on a dedicated database host and the wrong one on a shared box, so decide rather than copy.

Verification

`terminated by signal 9` does not reappear. Grep for it as a standing check, not just after an incident: ```bash grep -c 'terminated by signal 9' /var/log/postgresql/postgresql-18-main.log ``` The identified query now fails with an ordinary error rather than killing the cluster. Run it deliberately and confirm which happens — either `statement_timeout` cancels it, or the allocation fails, and in both cases other sessions survive. Other sessions survive. This is the property being bought and it must be observed directly: open a second session holding an open transaction, run the offending query in the first, and confirm the second is still alive afterwards. The postmaster's `oom_score_adj` differs from a backend's: ```bash cat /proc/$(pgrep -f 'postgres.*-D' | head -1)/oom_score_adj ``` Host memory has headroom at peak concurrency, with `shared_buffers`, the per-role `work_mem` ceiling, and the connection count all accounted for in one written calculation. Crash recovery time is known. If this happens again you want to know whether it costs 11 seconds or four minutes, and that is a function of WAL written since the last checkpoint. Measure it once during a controlled test.

Prevention

**Alert on `terminated by signal 9` in the server log.** It is unambiguous, it names the query, and four occurrences in six weeks went uninvestigated because nobody was watching for it. **Understand that one killed backend restarts the cluster.** This is the fact that changes how much the incident matters. Anybody who thinks the OOM killer costs one query will under-prioritise it four times in six weeks. **Set `statement_timeout` per role.** It is the only control that bounds an allocation that cannot spill, because such allocations are unbounded in time as well as size. **Do not assume `work_mem` is the culprit.** Sorts and hash aggregates spill — a 2 GB `work_mem` inside a 768 MB container completed a two-million-row sort here. Look for aggregates that build one enormous value: `array_agg`, `string_agg`, `json_agg` over unbounded input. **Protect the postmaster with `OOMScoreAdjust`.** If the kernel picks the postmaster instead of a backend, the outcome is worse and the log is less informative. **Choose the overcommit policy deliberately.** `vm.overcommit_memory = 2` converts a cluster-wide kill into a failed query, at the cost of failing some allocations that would have succeeded. That is usually the right trade on a dedicated database host. **Size memory with a written calculation**: `shared_buffers` plus `max_connections x work_mem x memory_nodes_per_plan` plus the operating system's needs, against physical RAM. A cluster where nobody can produce that number is waiting for this incident. **Give analytics its own role with its own limits**, and preferably its own replica. A reporting workload and an OLTP workload should not share a memory budget or a failure domain.

Reported symptoms

At 14:22 every connection to the primary was dropped simultaneously and new connections were refused for eleven seconds. Every service logged connection reset by peer at the same instant.

Uncommitted work in unrelated transactions was rolled back. The cluster returned on its own, with no operator action and no data loss.

Host monitoring shows a memory spike immediately before the event and normal memory afterwards.

One analyst reports that a query they had been running for several minutes returned server closed the connection unexpectedly.

The same event has now happened four times in six weeks, always during business hours, always briefly.

Evidence provided

Read-only / Safeone backend killed, and what the postmaster did about it
$ grep -A3 'terminated by signal 9' /var/log/postgresql/postgresql-18-main.log
2026-08-28 08:04:23.936 UTC [1] LOG:  client backend (PID 119) was terminated by signal 9: Killed
2026-08-28 08:04:23.936 UTC [1] DETAIL:  Failed process was running: 
SELECT length(array_to_string(array_agg(pad), ',')) FROM big;
2026-08-28 08:04:23.936 UTC [1] LOG:  terminating any other active server processes
2026-08-28 08:04:23.939 UTC [1] LOG:  all server processes terminated; reinitializing
Read-only / Safethe eleven seconds
$ grep -E 'recovery mode|redo|ready to accept' /var/log/postgresql/postgresql-18-main.log
2026-08-28 08:04:23.996 UTC [134] FATAL:  the database system is in recovery mode
2026-08-28 08:04:23.997 UTC [125] LOG:  redo starts at 0/222A0DC8
2026-08-28 08:04:24.806 UTC [125] LOG:  redo done at 0/4AFFEC38 system usage: CPU: user: 0.33 s, system: 0.45 s, elapsed: 0.80 s
2026-08-28 08:04:25.126 UTC [1] LOG:  database system is ready to accept connections

oom_score_adj is 0 for the postmaster and 0 for every backend.

And a result that narrows the search considerably:

Read-only / Safea sort with work_mem = 2GB inside a 768 MB container — and it succeeded
$ psql -c "SET work_mem = '2GB'; SELECT count(*) FROM (SELECT * FROM big ORDER BY pad, id) s;"
SET
count  
---------
2000000
(1 row)

Work the evidence before reading on

  1. One backend was killed. Why did every other session die?
  2. A 2 GB work_mem in a 768 MB container completed a two-million-row sort. What does that rule out?
  3. What kind of query cannot spill?
  4. oom_score_adj is 0 everywhere. What does the kernel choose on?

Root cause

One SIGKILL costs the whole cluster

work_mem was not the culprit

Sorts and hash aggregates spill to disk when they exceed work_mem. A sort of two million rows with work_mem at 2 GB inside a 768 MB container completed successfully by spilling.

work_mem is a target, not a reservation. A large sort is not what exhausts memory.

The kernel chose on footprint alone

oom_score_adj is 0 for the postmaster and 0 for every backend, so the kernel picks purely by memory use. That usually means the guilty backend — which is fortunate, and not guaranteed. If the postmaster is chosen the outcome is worse and the log is less informative.

Resolution

Identify the query. PostgreSQL records it, and that is what makes this incident actionable:

grep -A3 'terminated by signal 9' /var/log/postgresql/postgresql-18-main.log

Confirm the shape of the demand rather than assuming:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

A plan whose top node is an aggregate producing one enormous value has no spill point. A Sort reporting external merge, or a HashAggregate reporting Batches: 4, is spilling and is not your problem.

Bound what a single session may consume, per role:

ALTER ROLE analytics SET work_mem = '32MB';
ALTER ROLE analytics SET statement_timeout = '5min';
ALTER ROLE analytics SET temp_file_limit = '10GB';

statement_timeout is the control that actually helps here, because an unbounded allocation is unbounded in time as well as size — this query had been running for several minutes. Nothing else will stop it.

Protect the postmaster from the kernel’s choice:

# /etc/systemd/system/postgresql@.service.d/oom.conf
[Service]
OOMScoreAdjust=-900

Then check /proc/<pid>/oom_score_adj for the postmaster and a backend and confirm they differ. If they do not, the setting is not doing what you intended.

Set the host’s overcommit policy deliberately:

sysctl -w vm.overcommit_memory=2
sysctl -w vm.overcommit_ratio=80

With strict overcommit the kernel refuses the allocation instead of granting it and killing later, so the query fails with an ordinary error and the cluster stays up.

Verification

terminated by signal 9 does not reappear. Grep for it as a standing check, not only after an incident.

The identified query now fails with an ordinary error rather than killing the cluster. Run it deliberately and confirm which happens.

Other sessions survive. This is the property being bought and it must be observed directly: open a second session holding an open transaction, run the offending query in the first, and confirm the second is alive afterwards.

The postmaster’s oom_score_adj differs from a backend’s:

cat /proc/$(pgrep -f 'postgres.*-D' | head -1)/oom_score_adj

Host memory has headroom at peak concurrency, with shared_buffers, the per-role work_mem ceiling and the connection count in one written calculation.

Crash recovery time is known. If this happens again you want to know whether it costs 11 seconds or four minutes. Measure it once, in a controlled test.

Prevention

Alert on terminated by signal 9. Unambiguous, names the query, and four occurrences went uninvestigated because nobody was watching.

Understand that one killed backend restarts the cluster.

Set statement_timeout per role. The only control that bounds an allocation which cannot spill.

Do not assume work_mem is the culprit. Look for array_agg, string_agg, json_agg over unbounded input.

Protect the postmaster with OOMScoreAdjust.

Choose the overcommit policy deliberately.

Size memory with a written calculation: shared_buffers plus max_connections x work_mem x memory_nodes_per_plan plus the operating system’s needs, against physical RAM.

Give analytics its own role with its own limits, and preferably its own replica. A reporting workload and an OLTP workload should not share a memory budget or a failure domain.