Runbooks

What to run when it's on fire

Each runbook is an ordered procedure for a real PostgreSQL incident: the exact SQL to diagnose it, the steps to resolve it, and a query to confirm you're actually done.

ConcurrencyintermediatePro deep-dive

Preventing deadlocks with consistent lock ordering

Design every worker to acquire rows and tables in the same order so lock cycles cannot form.

Inventory, billing, and ledger workflows often touch the same entities in different sequences across services. That inconsistency creates deadlocks under concurrency.

Prerequisites

  • • You know which tables and rows the workflow touches.
  • • You can identify the transaction boundaries in the application code.
  • • You can run a load test or replay representative concurrent requests.

Verify you're done

SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();
Related40P01
  1. 01

    Inventory the lock footprint

    List the rows and tables touched by each workflow path so you can see where ordering diverges.

  2. 02

    Pick one canonical order

    Choose a stable order, such as account id ascending or parent table before child table, and document it.

    -- canonical ordering example
SELECT *
FROM transfer_items
WHERE transfer_id = $1
ORDER BY account_id ASC
FOR UPDATE;
  3. 03

    Refactor every code path to follow it

    The rule only works if every caller follows the same order, including backfills and admin scripts.

  4. 04

    Load test and verify

    Run concurrent workers and confirm deadlocks disappear while throughput remains acceptable.

OperationsadvancedPro deep-dive

Building idempotent data migrations

Make migration steps safe to rerun during rollbacks, retries, and partial deploys.

Deploy pipelines and manual recovery often rerun the same migration steps. Non-idempotent SQL creates outages instead of resilience.

Prerequisites

  • • The migration can be split into schema change, backfill, and verification phases.
  • • You can test on a copy of production-shaped data.

Verify you're done

SELECT count(*) FILTER (WHERE normalized_status IS NULL) AS remaining_nulls FROM invoices;
Related40001
  1. 01

    Guard every schema mutation

    Use IF EXISTS / IF NOT EXISTS or explicit metadata checks so reruns do not explode.

    ALTER TABLE invoices ADD COLUMN IF NOT EXISTS normalized_status text;
  2. 02

    Backfill in bounded batches

    Avoid one giant transaction; chunking reduces lock time and makes retries predictable.

    UPDATE invoices
SET normalized_status = lower(status)
WHERE normalized_status IS NULL
AND id > $1
AND id <= $2;
  3. 03

    Ship verification queries with the migration

    A migration is not complete until you can prove its post-conditions with SQL.

Concurrencyintermediate

Diagnosing a live deadlock from pg_stat_activity

Read the wait-for graph straight from the catalogs so you can name the two transactions in a deadlock and the exact rows they fought over.

You see SQLSTATE 40P01 in the logs but need to know which sessions collided, what SQL they were running, and which locks were held versus waited on.

Prerequisites

  • • You can connect as a role that can read pg_stat_activity for other backends.
  • • log_lock_waits is on and deadlock_timeout is at its default so the server log carries the deadlock report.

Verify you're done

SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();
Related40P01
  1. 01

    List who is blocked by whom

    pg_blocking_pids() returns the backends blocking a given PID. Joining it back to pg_stat_activity turns raw PIDs into running queries.

    SELECT blocked.pid   AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid  AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
  2. 02

    Inspect the raw lock rows for those backends

    pg_locks shows every lock each backend holds (granted = true) and waits on (granted = false). A two-node cycle of waiters is the fingerprint of a deadlock.

    SELECT pid, locktype, mode, granted, relation::regclass AS object
FROM pg_locks
WHERE pid IN (SELECT pid FROM pg_stat_activity WHERE state = 'active')
ORDER BY pid, granted DESC;
  3. 03

    Read the deadlock report the server already wrote

    When PostgreSQL cancels a transaction it logs the full 'deadlock detected' report, including the process/lock detail, in the server log. Confirm which victim it chose there.

Operationsbeginner

Cancelling a runaway query without restarting Postgres

Stop a single expensive or stuck statement using cancel/terminate functions, and prevent the next one with a timeout — no full restart required.

One session is pinning CPU or holding locks. You need to stop just that statement (or that connection) without taking an outage for everyone else.

Prerequisites

  • • You can identify the offending backend PID from pg_stat_activity.
  • • You have privileges to signal that backend (same role, or a superuser / pg_signal_backend member).

Verify you're done

SELECT pid, state FROM pg_stat_activity WHERE pid = $1;
Related57014
  1. 01

    Find the offending backend

    Sort active sessions by how long the current statement has been running to spot the outlier.

    SELECT pid, now() - query_start AS running_for, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY running_for DESC;
  2. 02

    Cancel the statement first

    pg_cancel_backend() asks the backend to abort just the current query and keep the connection. Prefer it over terminating the whole connection.

    SELECT pg_cancel_backend($1);  -- $1 = the target pid
  3. 03

    Terminate only if cancel does not take

    If the backend ignores the cancel (for example stuck in a non-interruptible call), terminate the connection outright.

    SELECT pg_terminate_backend($1);
  4. 04

    Prevent the next one with a bound

    Set a statement_timeout for the workload or role so a single query can never run unbounded again.

    ALTER ROLE report_worker SET statement_timeout = '30s';
Reliabilityintermediate

Resolving "too many connections" under load

Triage a connection saturation event, reclaim idle slots, and put a pooler in front so bursts stop translating into SQLSTATE 53300.

New connections are refused with 'sorry, too many clients already'. The app is opening more backends than max_connections allows, often idle-in-transaction ones.

Prerequisites

  • • You can read pg_stat_activity and change role/parameter settings.
  • • You know whether a connection pooler (PgBouncer or similar) sits in front of the database.

Verify you're done

SELECT count(*) AS backends, current_setting('max_connections') AS max_connections FROM pg_stat_activity;
Related53300
  1. 01

    See where the connections are going

    Group current backends by state. A large 'idle in transaction' count points at application transactions left open.

    SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;
  2. 02

    Reclaim the worst offenders

    Terminate connections stuck 'idle in transaction' beyond a threshold to free slots immediately.

    SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';
  3. 03

    Stop it recurring with idle timeouts

    idle_in_transaction_session_timeout auto-aborts abandoned transactions so a leaked connection cannot camp on a slot forever.

    ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();
  4. 04

    Put a pooler in front

    The durable fix for spiky client concurrency is a transaction-mode pooler so hundreds of clients share a small pool of backends instead of each opening one.

OperationsadvancedPro deep-dive

Adding a NOT NULL column to a large table without locking prod

Introduce a required column on a big, busy table in phases so you never hold a long ACCESS EXCLUSIVE lock or scan the whole table under one transaction.

Adding NOT NULL naively forces a full-table validation while holding a strong lock, which can freeze writes on a large table long enough to cause an incident.

Prerequisites

  • • You can deploy migrations in multiple steps rather than one transaction.
  • • You can run a bounded backfill during off-peak windows.

Verify you're done

SELECT count(*) FILTER (WHERE source IS NULL) AS remaining_nulls FROM events;
Related23502
  1. 01

    Add the column as nullable

    On modern PostgreSQL adding a nullable column (even with a constant default) is a fast catalog-only change.

    ALTER TABLE events ADD COLUMN IF NOT EXISTS source text;
  2. 02

    Backfill in bounded batches

    Populate existing rows in chunks so each transaction is short and lock time stays small.

    UPDATE events
SET source = 'legacy'
WHERE source IS NULL
  AND id BETWEEN $1 AND $2;
  3. 03

    Add the constraint as NOT VALID, then validate

    A CHECK ... NOT VALID is recorded without scanning the table; VALIDATE CONSTRAINT then checks existing rows with only a SHARE UPDATE EXCLUSIVE lock that does not block writes.

    ALTER TABLE events
  ADD CONSTRAINT events_source_not_null CHECK (source IS NOT NULL) NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT events_source_not_null;