ObservabilityintermediateFree in full

Capture Sev-1 evidence before recovery erases it

pg_stat_activity, pg_locks and the blocking chain describe only this second — restoring service deletes them. Spend sixty seconds copying them into a schema when you are paged, and the postmortem stops being a memory test.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

Fixing the incident destroys the evidence that explains it. Sessions end, lock queues drain, wait events clear, and the views go back to looking healthy. An RCA written the next morning is reconstructed from dashboards and recollection, which is why so many of them conclude with a symptom — high load, lock contention — instead of a cause.

Meridian stabilises a Sev-1 in eleven minutes by cancelling a migration and restarting the workers. The next morning nobody can say which session held the lock, what the blocked queries were waiting on, or how far behind vacuum had fallen. Every view that held those answers has moved on, and the postmortem lands on 'high load'.

In plain English

Most of what explains an outage lives in views that only describe right now. pg_stat_activity lists the sessions that exist this second; pg_locks lists the waits happening this second. The moment you fix the incident all of that is gone — the sessions end, the queue drains, and the views go back to looking perfectly healthy. So the postmortem gets written from graphs and memory, and lands on something like 'high load', which is a description of the symptom rather than a cause. The fix is small and cheap: before you start changing things, copy those views into ordinary tables. They stop being a live picture and become evidence you can still read tomorrow.

Before you start

  • • A role that can create a schema and read the statistics views; pg_monitor grants the visibility needed to see other users' query text.
  • • The discipline to spend sixty seconds capturing before you start changing things — the evidence does not survive the fix.

How to identify it

  • ›A Sev-1 has been declared and you are about to change something — that is the one moment the evidence still exists.
  • ›Your postmortems keep concluding with a symptom rather than a trigger, an amplifier and a control that failed.
  • ›Monitoring shows the shape of the incident but cannot name the blocking session or the statement behind it.
  • ›The views that hold the answer — pg_stat_activity and pg_locks — keep no history at all, so nothing recovers them after the fact.

Pitfalls to avoid

  • ✕Do not capture into logged tables on a server already struggling to write — an UNLOGGED capture table adds no WAL, which is exactly what you want mid-incident.
  • ✕Do not take one snapshot and stop; two or three a minute apart show direction, and direction is what distinguishes an amplifier from a coincidence.
  • ✕Do not rely on pg_stat_statements alone — it is cumulative and does survive, but it cannot tell you who was blocked by whom, which is usually the question.
  • ✕Do not truncate query text in the evidence copy; left(query, 80) is fine for reading during triage and useless when you need the predicate three days later.

Trace it

  1. 01

    Open a capture schema and freeze the session list

    Capture before you remediate; this is the whole discipline. UNLOGGED capture tables are deliberate: they write no WAL, so the snapshot costs a struggling server almost nothing and cannot make a WAL or replication problem worse. Stamping every row with captured_at is what lets you take repeat snapshots into the same table later and still tell them apart.

    CREATE SCHEMA IF NOT EXISTS incident;

CREATE UNLOGGED TABLE IF NOT EXISTS incident.activity_snap AS
SELECT now() AS captured_at, * FROM pg_stat_activity;

SELECT count(*) AS backends_captured FROM incident.activity_snap;
  2. 02

    Freeze the lock waits and the blocking chain

    pg_locks alone tells you what was waiting; it does not tell you who to blame. Resolving pg_blocking_pids at capture time joins both sides into one row — the blocked statement and the statement holding it up — which is the single most valuable artifact in a lock-driven postmortem and the one that is impossible to reconstruct afterwards. Keep the full query text here rather than truncating it.

    CREATE UNLOGGED TABLE IF NOT EXISTS incident.locks_snap AS
SELECT now() AS captured_at, * FROM pg_locks;

CREATE UNLOGGED TABLE IF NOT EXISTS incident.blocking_snap AS
SELECT now()             AS captured_at,
       a.pid             AS blocked_pid,
       a.usename         AS blocked_user,
       a.wait_event_type,
       a.wait_event,
       a.query           AS blocked_query,
       b.pid             AS blocking_pid,
       b.state           AS blocking_state,
       b.query           AS blocking_query
FROM   pg_stat_activity a
JOIN   LATERAL unnest(pg_blocking_pids(a.pid)) AS bp(pid) ON true
JOIN   pg_stat_activity b ON b.pid = bp.pid;

SELECT count(*) AS blocking_pairs FROM incident.blocking_snap;
  3. 03

    Freeze the maintenance debt the incident inherited

    Vacuum debt and transaction age are the slow-moving context that explains why today was the day. These counters are cumulative rather than instantaneous, so they survive the incident — but they keep moving, and a value read a day later no longer describes the moment. Capturing them alongside the live views is what lets an RCA distinguish a trigger from a pre-existing condition.

    CREATE UNLOGGED TABLE IF NOT EXISTS incident.tables_snap AS
SELECT now() AS captured_at, * FROM pg_stat_user_tables;

CREATE UNLOGGED TABLE IF NOT EXISTS incident.xid_snap AS
SELECT now() AS captured_at, datname, age(datfrozenxid) AS xid_age
FROM   pg_database;

SELECT count(*) AS tables_captured FROM incident.tables_snap;

Resolution approach

  1. 1.Capture first, remediate second — the whole point is that these views cease to exist the moment service is restored.
  2. 2.Take the session list, the lock waits and the resolved blocking chain together, so the three can be joined afterwards.
  3. 3.Repeat the capture once or twice a minute apart, into the same tables, so the postmortem can describe direction rather than a single frame.
  4. 4.Keep the capture schema after the incident and read the RCA from it, rather than from the dashboards that only show shape.

Stop it recurring

  1. 01

    Take the second and third snapshots

    The tables already exist and every row carries captured_at, so a repeat capture is an INSERT rather than a new table. Two frames a minute apart answer the question a single frame cannot: was the blocking chain lengthening or draining, and was the backend count still climbing when you intervened.

    INSERT INTO incident.activity_snap SELECT now(), * FROM pg_stat_activity;
INSERT INTO incident.locks_snap    SELECT now(), * FROM pg_locks;

SELECT captured_at,
       count(*)                                  AS backends,
       count(*) FILTER (WHERE state = 'active')  AS active,
       count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_on_lock
FROM   incident.activity_snap
GROUP  BY captured_at
ORDER  BY captured_at;
  2. 02

    Read the RCA out of the evidence, not out of memory

    This is what the capture buys. The blocking snapshot names the statement that held the lock and the statements that queued behind it, so the postmortem can state a trigger, an amplifier and the control that was missing — rather than restating that the database was busy.

    SELECT captured_at,
       blocking_pid,
       count(*)                    AS sessions_blocked,
       min(left(blocking_query, 120)) AS blocking_statement
FROM   incident.blocking_snap
GROUP  BY captured_at, blocking_pid
ORDER  BY sessions_blocked DESC;

Verify you're done

SELECT captured_at, count(*) AS backends FROM incident.activity_snap GROUP BY captured_at ORDER BY captured_at;  -- at least two distinct captured_at values means you caught direction, not just a frame
Related40P0155P0357014

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

More in this category

Other Observability runbooks

Neighbouring incidents that share the same diagnostic surface.

All Observability runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references — what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →