Runbooks
What to run when it's on fire
105 runbooks across 16 categories. Find the symptom that matches yours and open it, every runbook is a page of its own, with the exact SQL, the live output, and the fix. 10 are free end to end.
Showing 105 of 105
Slow query from a sequential scan on a large table
Same query, no code change, quietly slower every month as the table grows.
Replace deep OFFSET paging with keyset pagination
Page 1 is instant, page 10,000 crawls, and infinite scroll is your slowest endpoint.
Tame a lossy bitmap heap scan
Row counts look sane, CPU does not, the plan quietly went lossy on you.
Why your query ignores the index, and how to fix it
You built the index. EXPLAIN still says Seq Scan and nobody can say why.
Speed up COUNT(*) on a large table
A dashboard polls count(*) and every refresh reads the entire table again.
Fix bad row estimates with extended statistics
Estimate says ~1,250 rows, reality is 10,000, and the join strategy goes with the guess.
Why parallel query did not kick in
A big aggregate runs on one core while the rest of the box sits idle.
Choose the right join: fix a nested loop on a misestimate
A join that returns a single row hashes two whole tables to find it.
Control CTE materialization fences
Your WHERE clause runs after the WITH block has already spilled to disk.
Refresh stale statistics that break query plans
Plans go wrong straight after a bulk load: estimated 1 row, actual ~100.
Replace an N+1 pattern with a LATERAL join
Latest-row-per-parent reads every child row, then throws nearly all of them away.
Stop disk-spilled sorts by tuning work_mem
Sort Method says external merge Disk, and no row count explains the extra time.
Cure HOT update degradation with fillfactor
Update-heavy table and its indexes keep growing while the row count stays flat.
Diagnose detoasting overhead on wide rows
Identical plan, 100x slower the moment you select the big text column.
Custom plan versus generic plan in prepared statements
The sixth run of an unchanged prepared statement is twenty times slower than the fifth.
Reduce high planning time on complex queries
Execution is milliseconds, the request is not, it is all planning time.
Index the foreign key you forgot
Deleting one parent row scans the whole child table to check the constraint.
Make index-only scans actually skip the heap
Plan says Index Only Scan, Heap Fetches is huge, and it is not fast.
Covering indexes with INCLUDE
The index finds rows quickly, then the heap trip for the selected columns costs everything.
Partial indexes for hot subsets of data
A multi-megabyte index when queries only ever touch last week's rows.
Get the multicolumn index column order right
The composite index exists and the planner still sorts, the columns are the wrong way round.
GIN indexes for jsonb containment queries
A jsonb @> filter ignores every B-tree you own and reads every document.
BRIN indexes for huge append-only tables
A huge B-tree on a timestamp column whose rows already arrive in order.
Eliminate redundant and duplicate indexes
Writes are slow and the same leading column is indexed three different ways.
Find and drop unused indexes
An index nobody has scanned in years that every INSERT still pays for.
Reclaim table bloat: plain VACUUM vs VACUUM FULL
The table file is far bigger than the live data and VACUUM does not shrink it.
Rebuild a bloated index with REINDEX CONCURRENTLY
The index is four times the size of its entries and scans keep getting slower.
Tame autovacuum on a high-churn table
A hot table reaches six figures of dead rows before autovacuum even considers it.
Unblock vacuum held back by a long transaction
VACUUM runs, reports rows not yet removable, and the bloat never goes down.
Preempt transaction ID wraparound
Transaction age climbs toward two billion and nothing is freezing the old rows.
Find tables autovacuum keeps skipping
One table bloats forever, autovacuum never touches it, and nothing warns you.
Shrink TOAST bloat from wide rows
Heap size looks healthy; the disk filled because the TOAST table grew five-fold.
Assess multixact wraparound risk
A second wraparound counter nobody monitors until the cluster stops accepting writes.
Detect and resolve lock contention
Queries hang with no error and no progress, and you need the wait graph now.
Stop idle-in-transaction sessions from holding locks
A session sits idle in transaction, holding its locks and pinning vacuum.
Build a safe job queue with SKIP LOCKED
Several queue workers all fight over the same top rows and serialize.
Set sane statement, lock, and idle timeouts
Every timeout ships as 0, so one runaway statement turns into an outage.
Coordinate work with advisory locks
Two app instances run the same cron job at the same time, twice.
Keep ALTER TABLE from blocking your app
One migration waits on a lock and every reader behind it queues too.
Resolve row-level lock contention from SELECT FOR UPDATE
A session waits on wait_event transactionid while every table-lock dashboard stays green.
Find your slowest queries with pg_stat_statements
Everything feels slow and you are guessing which query deserves the blame.
Read EXPLAIN (ANALYZE, BUFFERS) like a pro
You cannot tell whether the query is slow from disk reads or from CPU.
Track table and index bloat over time
You want a bloat early-warning number instead of finding out when the disk fills.
Watch wait events in pg_stat_activity
A session is stuck and you need the reason, not another copy of the query text.
Measure and raise your buffer cache hit ratio
Someone wants to buy RAM and nobody has measured whether reads miss the cache.
Catch queries spilling to temp files
temp_bytes keeps climbing and no one can name the query writing it.
Monitor VACUUM progress on a large table
A VACUUM has run for an hour and you cannot tell slow from stalled.
Watch CREATE INDEX CONCURRENTLY progress
CREATE INDEX CONCURRENTLY reports zero blocks done and someone is about to cancel it.
Track ANALYZE progress on large and partitioned tables
ANALYZE on a partitioned table looks hung, there is no output until it commits.
Monitor a CLUSTER or VACUUM FULL rewrite
The table is locked for a full rewrite and the only question left is the ETA.
Monitor COPY progress during a bulk load
A multi-gigabyte COPY prints nothing until it commits, so nobody can give an ETA.
Diagnose I/O behavior with pg_stat_io
One cache-hit number for the whole cluster while a nightly report evicts your OLTP pages.
Spot checkpoint write pressure before it hurts
Requested checkpoints are outrunning timed ones and I/O spikes follow.
Catch a high transaction rollback rate
A retry loop quietly rolls back four transactions in ten and everything looks fine.
Read pg_locks: relation waits versus tuple waits
pg_locks shows you the granted row, not the waiting one, and you read it wrong.
Add a NOT NULL column to a huge table safely
SET NOT NULL grabs a strong lock, scans a huge table, and the deploy stalls behind it.
Change a column type without a full table rewrite
ALTER COLUMN TYPE is instant on one column and a locked full rewrite on the next.
Partition a growing table so the planner can prune
Every query has to consider the whole table because there is nothing to prune.
Recover from a failed CREATE INDEX CONCURRENTLY
An invalid index left behind by a failed build: reads ignore it, writes still pay for it.
Validate a CHECK constraint without a long lock
Adding a CHECK scans the entire table under lock and the migration times out.
Detach and attach partitions with minimal locking
DETACH PARTITION queues behind one open reader and takes the parent table down with it.
Fix random_page_cost for SSD storage
The storage is SSD and the planner still avoids index scans it should be choosing.
Help the planner with effective_cache_size
The planner costs repeated index access as if nothing is ever cached.
Right-size work_mem for hash aggregates
A GROUP BY falls back to an on-disk merge instead of a hash aggregate.
Reclaim WAL held by an inactive replication slot
pg_wal keeps growing and the replica the slot was created for is long gone.
Measure your WAL generation rate
Archiving and disk sizing need a WAL bytes-per-hour number nobody has measured.
Flatten checkpoint I/O spikes by tuning checkpoints
Latency spikes on a regular cadence while the totals graph stays perfectly flat.
Size WAL with max_wal_size and checkpoint_timeout
max_wal_size copied from a blog post, never checked against your own WAL rate.
Reduce WAL volume with wal_compression
Full-page images dominate the WAL stream on an update-heavy workload.
Right-size synchronous_commit for latency
Tiny transactions spend most of their life waiting for WAL to reach durable storage.
Survive a connection storm when there is no pooler
Connections pile up, the app times out, and CPU is high while throughput sits flat.
Rotate md5 passwords to SCRAM without locking out clients
Every service fails authentication the second pg_hba.conf is reloaded.
Stop work_mem from OOM-killing the server
The server restarted into crash recovery while the memory dashboard looked healthy.
Find unlogged tables before a crash empties them
A table came back empty after an unclean restart, and no backup or replica has the rows.
Capture Sev-1 evidence before recovery erases it
Every postmortem ends at 'high load' because the evidence was gone by morning.
What happens when your synchronous standby dies
Writes hang with no error after a replica goes down, and Ctrl-C makes it worse.
Measure what a failover costs your application
Nobody can say how many seconds of writes the last failover actually cost.
Rejoin a demoted primary with pg_rewind
The failed-over node cannot rejoin, and rebuilding it means copying everything again.
Route writes through a failover without a proxy
After promotion the app keeps writing to the old node, or to a replica that refuses.
Prove your WAL archive can actually restore
Archiving reports success for months and the restore has nothing to read.
Restore to a point in time before the damage
Someone dropped it, and the newest backup is from before half the data existed.
Stop an abandoned slot from filling the disk
One dead consumer can fill the disk, and dropping slots by hand is not a control.
Read replication lag as three numbers
The replica is behind, and one lag number cannot say which part is behind.
hot_standby_feedback and the bloat it buys
Replica queries stopped dying and the primary quietly stopped reclaiming space.
Find out whether pg_stat_statements is lying to you
The slow-query dashboard is empty, or the query you are hunting is not in it.
Find what is actually burning CPU
CPU is pinned at 100% and every session looks busy, but you cannot name the query.
Cap parallel workers that multiply CPU and memory
One dashboard query quietly claims a dozen cores because every worker is a whole process.
Know which lock mode your statement takes
A one-line migration froze every query on the table and the CPU graph got the blame.
ANALYZE the partitioned parent autovacuum never will
Plans across a partitioned table get worse for months while every partition looks healthy.
Keep temp tables from eating session memory
Long-lived pooled sessions grow steadily and nobody can point at a query to blame.
Read the loops multiplier in EXPLAIN ANALYZE
The expensive step in the plan reports a rounding error and the total still makes no sense.
Diagnose PgBouncer queueing and session-state leaks
Clients wait in PgBouncer even though PostgreSQL has capacity, or session settings behave inconsistently.
Measure and fence a Patroni failover
You have never timed failover, proved the old primary is fenced, or verified client routing.
Prove point-in-time recovery from archived WAL
Backups exist, but nobody has proved a timestamp restore excludes later commits.
Know whether you could detect page corruption
A query fails on one block, and nobody can say which table it is or how long it has been wrong.
Stop a logical slot on a quiet database pinning WAL
A slot nobody writes to is quietly holding the whole cluster's WAL.
Your RLS policy does not apply to the table owner
RLS is enabled, the policy looks right, and the app still sees every tenant's rows.
Read logical decoding lag from the spill counters
Logical replication is behind and the slot looks healthy, so nobody knows what to tune.
Run pg_upgrade and survive the statistics cliff
The upgrade finished in minutes and the database has been slow ever since.
Switch to archive_library without stopping archiving
Archiving stopped completely and the failure counter is still sitting at zero.
Guarantee exactly-once settlements with idempotency keys
A retried or double-clicked payout writes two settlements and finance has to claw one back.
Keep an immutable settlement audit trail
Anyone with table rights can edit or delete audit rows, so the trail proves nothing to an auditor.
Prove a monthly restore drill for auditors
Backups run green every night but nobody has restored one, so recovery is unproven.
Measure freeze debt before autovacuum forces your hand
age(relfrozenxid) keeps climbing and the vacuums that run never bring it down.
Map RPO and RTO targets to a PostgreSQL HA topology
You have RPO/RTO numbers on a slide but no mapping to a Postgres mechanism that proves them.