PostgreSQL runbooks — 50 reproduced DBA & engineering recipes

Runbooks

What to run when it's on fire

Each runbook takes a real PostgreSQL engineering problem — the kind that shows up as a slow query, a full disk, a stuck migration, or a lock that will not clear — and walks the fix: how to spot it, the exact SQL to trace it, the output we captured live, the root cause, and how to stop it coming back. No invented numbers — every transcript was run on PostgreSQL 18.

70 runbooks across 8 categories. Filter by category or search, then open one to read it. 5 are free in full; the rest open with the scenario and unlock the full diagnosis and fix with Pro.

Showing 70 of 70

Query performanceintermediateFreeOpen runbook

Slow query from a sequential scan on a large table

A lookup that used to be instant now reads the whole table. Prove it is a sequential scan, then bring it back with the right index — measured before and after on 500,000 real rows.

A query filtering a large table by one column has quietly gotten slower as the table grew. Nothing in the SQL changed; the plan did. It is the most common performance ticket a Postgres DBA ever sees.

The scenario we reproduced

Meridian Freight's consignments table has half a million rows. A dispatcher looks up one courier's parcels by courier_id. With no index on that column the planner has to read every row, so a query returning 625 rows still touches all 500,000.

How to identify it

›EXPLAIN shows a Seq Scan on a big table with a large "Rows Removed by Filter" count.
›In EXPLAIN ANALYZE the estimated and actual row counts are close, so this is a missing-index problem, not a bad-estimate problem.
›pg_stat_user_tables shows seq_scan climbing while idx_scan stays at zero for the table.
›pg_stat_statements ranks the query high by total_exec_time (see the observability recipes).

Pitfalls to avoid

✕Do not set enable_seqscan = off in production — a seq scan is the right plan when a query returns a large fraction of the table.
✕Do not add an index without checking the table's write ratio; every index is maintained on each INSERT, UPDATE and DELETE.
✕Do not index around a type mismatch (WHERE courier_id = '473' on an integer column) — fix the predicate instead.
✕Do not trust estimated rows alone — compare estimate against actual in EXPLAIN ANALYZE before deciding the plan is wrong.

Trace it — analysis steps

Read the plan, not the clock

A Seq Scan that discards 499,375 rows to return 625 is the signature. It visited 4,762 shared buffers and took 18.5 ms to hand back a handful of rows.

SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM consignments WHERE courier_id = 473;

Captured live on PostgreSQL 18

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on consignments  (cost=0.00..11012.00 rows=621 width=44) (actual time=0.025..18.535 rows=625.00 loops=1)
   Filter: (courier_id = 473)
   Rows Removed by Filter: 499375
   Buffers: shared hit=4762
 Planning:
   Buffers: shared hit=78
 Planning Time: 0.139 ms
 Execution Time: 18.590 ms
(8 rows)

Resolution approach

1.Build the index the filter needs, using CREATE INDEX CONCURRENTLY in production so writes keep flowing.
2.If an index already exists but is ignored, look for a type mismatch in the predicate and refresh statistics with ANALYZE.
3.After fast growth, run ANALYZE first and re-check the plan before adding anything.
4.Always confirm the win with EXPLAIN ANALYZE afterwards — never assume the index helped.

Mitigation — stop it recurring

Add the B-tree index and re-measure

A plain index on courier_id turns the full-table read into a bounded lookup. The same query now plans as a Bitmap Index Scan, touches 625 buffers instead of 4,762, and returns in 1.8 ms.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM consignments WHERE courier_id = 473;

Captured live on PostgreSQL 18

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on consignments  (cost=9.24..1737.03 rows=621 width=44) (actual time=0.218..1.849 rows=625.00 loops=1)
   Recheck Cond: (courier_id = 473)
   Heap Blocks: exact=625
   Buffers: shared hit=625 read=3
   ->  Bitmap Index Scan on idx_consignments_courier  (cost=0.00..9.08 rows=621 width=0) (actual time=0.120..0.120 rows=625.00 loops=1)
         Index Cond: (courier_id = 473)
         Index Searches: 1
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=98 read=1
 Planning Time: 0.279 ms
 Execution Time: 1.921 ms
(12 rows)

Prerequisites

• Ability to run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
• No maintenance window needed — CREATE INDEX CONCURRENTLY does not block writes.

Verify you're done

EXPLAIN SELECT * FROM consignments WHERE courier_id = 473;  -- expect a Bitmap/Index Scan, not a Seq Scan

Query performanceintermediateFreeOpen runbook

Replace deep OFFSET paging with keyset pagination

Page 10,000 of an OFFSET query re-reads everything before it. Switch to keyset (seek) pagination and the query reads only the rows it returns — 200,020 rows scanned drops to 20.

OFFSET N LIMIT K makes PostgreSQL generate and throw away the first N rows on every page. Deep pages get linearly slower, and infinite-scroll APIs quietly become the slowest endpoint you own.

The scenario we reproduced

A shipment_events feed is paged 20 rows at a time. Jumping to a deep page with OFFSET 200000 forces the database to walk 200,020 index entries just to return the last 20.

How to identify it

›EXPLAIN ANALYZE shows the inner node producing rows equal to OFFSET + LIMIT while the query returns only LIMIT.
›Response time grows with the page number — the first pages are fast, deep pages crawl.
›The endpoint powers infinite scroll or an admin table where users actually reach deep pages.
›The ORDER BY is on a unique, indexed key (or can be made so), which is what keyset pagination requires.

Pitfalls to avoid

✕Do not keyset-paginate on a non-unique sort key without a tiebreaker — rows can be skipped or repeated at page boundaries.
✕Do not expose raw OFFSET to clients for deep lists; it is a denial-of-service vector on large tables.
✕Do not forget that keyset pagination cannot jump to an arbitrary page number — it is next/previous, by design.
✕Do not drop the ORDER BY when you add the WHERE seek clause; the index only helps if the sort matches it.

Trace it — analysis steps

Watch OFFSET read everything it skips

The Index Scan produces 200,020 rows and the Limit discards all but the final 20. That is 2,076 buffers and 22.7 ms of work to return one screen of data.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM shipment_events ORDER BY id OFFSET 200000 LIMIT 20;

Captured live on PostgreSQL 18

                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6726.92..6727.60 rows=20 width=32) (actual time=22.707..22.711 rows=20.00 loops=1)
   Buffers: shared hit=2076
   ->  Index Scan using shipment_events_pkey on shipment_events  (cost=0.42..13453.42 rows=400000 width=32) (actual time=0.011..17.541 rows=200020.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=2076
 Planning:
   Buffers: shared hit=75
 Planning Time: 0.196 ms
 Execution Time: 22.735 ms
(9 rows)

Resolution approach

1.Remember the last row's key instead of an offset, and fetch WHERE key > :last ORDER BY key LIMIT K.
2.Back the seek with an index on the exact ORDER BY columns so the WHERE clause is a range scan.
3.For composite sorts, use a row-value comparison — WHERE (sort_col, id) > (:last_sort, :last_id).
4.Keep OFFSET only for small, bounded lists where users never reach deep pages.

Mitigation — stop it recurring

Seek by the last key instead of counting

The keyset query starts at id > 200000 and stops after 20 rows. It reads 7 buffers instead of 2,076 and finishes in 0.03 ms — the cost no longer depends on how deep the page is.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM shipment_events WHERE id > 200000 ORDER BY id LIMIT 20;

Captured live on PostgreSQL 18

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..1.15 rows=20 width=32) (actual time=0.027..0.029 rows=20.00 loops=1)
   Buffers: shared hit=7
   ->  Index Scan using shipment_events_pkey on shipment_events  (cost=0.42..7207.89 rows=199398 width=32) (actual time=0.026..0.027 rows=20.00 loops=1)
         Index Cond: (id > 200000)
         Index Searches: 1
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=78
 Planning Time: 0.202 ms
 Execution Time: 0.043 ms
(10 rows)

Prerequisites

• A unique, indexed ordering key (a primary key works).
• An API contract that can carry a cursor (the last key) instead of a page number.

Verify you're done

EXPLAIN ANALYZE SELECT * FROM shipment_events WHERE id > 200000 ORDER BY id LIMIT 20;

Query performanceadvancedPro runbookOpen runbook

Tame a lossy bitmap heap scan

When a bitmap scan runs out of work_mem it stops tracking individual rows and rechecks whole pages. Spot the lossy switch and size work_mem so 56,903 wasted rechecks disappear.

A Bitmap Heap Scan builds a bitmap of matching rows in work_mem. If the bitmap does not fit, PostgreSQL degrades it to page granularity ("lossy") and rechecks every row on those pages — CPU you cannot see in the row counts.

The scenario we reproduced

A Meridian delivery_logs report scans a few percent of an 800,000-row table. At a small work_mem the bitmap goes lossy and the scan rechecks tens of thousands of rows it did not need to.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify tame a lossy bitmap heap scan, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceintermediatePro runbookOpen runbook

Why your query ignores the index — and how to fix it

You built the index and the planner still scans the table. The usual cause is a function on the column. Match the index to the expression and the scan drops from 29.7 ms to 3.6 ms.

An index on a column cannot be used when the query wraps that column in a function or cast — WHERE lower(status) = 'exception' cannot use a plain index on status. The planner falls back to a full scan.

The scenario we reproduced

A Meridian waybills query filters on lower(status). There is an index on status, but because the predicate calls lower() the planner ignores it and reads the whole table in parallel.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify why your query ignores the index — and how to fix it, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceintermediatePro runbookOpen runbook

Speed up COUNT(*) on a large table

SELECT count(*) on a big table scans every row, every time. When an estimate is good enough, read it from the catalog instead — 34.8 ms of scanning becomes an instant lookup.

PostgreSQL's MVCC means count(*) has to visit rows to know which are visible to you, so an exact count on a large table is always a full scan. Dashboards that poll count(*) put steady, pointless load on the server.

The scenario we reproduced

A Meridian ops dashboard shows the total parcel_events row count. An exact count scans all 700,000 rows on every refresh, even though the number only needs to be approximately right.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify speed up count(*) on a large table, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Fix bad row estimates with extended statistics

When two columns are correlated the planner multiplies their selectivities and guesses badly. CREATE STATISTICS teaches it the dependency — a 480-row guess becomes 10,024 against 10,000 actual.

By default PostgreSQL assumes columns are independent. When they are correlated (a town always maps to one postal area), it multiplies selectivities and under-estimates matching rows by orders of magnitude, which produces the wrong join and scan strategy.

The scenario we reproduced

Meridian's depot_routes table has origin_town and postal_area that move together. Filtering on both, the planner estimates 480 rows but the query actually returns 10,000 — a 20x miss that misleads every plan built on top of it.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify fix bad row estimates with extended statistics, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Why parallel query did not kick in

A big aggregate runs single-threaded while your cores sit idle. Understand the gates that block parallelism and a 297 ms serial scan becomes an 89 ms parallel one.

Parallel query has to clear several gates — table size, max_parallel_workers_per_gather, cost thresholds, and worker availability. Miss one and PostgreSQL silently runs the plan on a single worker while the box has cores to spare.

The scenario we reproduced

A Meridian aggregate over a large table runs as a single-threaded HashAggregate. With parallelism allowed, the same query fans out across workers and finishes in a third of the time.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify why parallel query did not kick in, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Choose the right join: fix a nested loop on a misestimate

Point-lookup joins want a nested loop over an index; missing that index forces a hash join that scans both tables. Adding the join-key index takes the join from 36 ms to 0.03 ms.

The planner picks a join strategy from its cost model. Without an index on the join key, a query that fetches a single matching row cannot use a cheap nested loop and falls back to hashing the whole inner table.

The scenario we reproduced

A Meridian query joins consignments to a lookup by reference and returns three rows. With no index on the join key the planner hashes a full parallel scan of the big table instead of seeking three index entries.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify choose the right join: fix a nested loop on a misestimate, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Control CTE materialization fences

A WITH block can be an optimization fence that spills to disk before your WHERE clause runs. Choosing NOT MATERIALIZED lets the filter push down — 66 ms and a 19 MB disk spill become 0.3 ms.

Before PostgreSQL 12 every CTE was materialized (computed once, then scanned). Even now a CTE referenced once can be materialized in ways that block predicate push-down, so a filter you wrote runs after the whole CTE is built and spilled to disk.

The scenario we reproduced

A Meridian ledger query wraps a scan in a CTE and filters the result by account_code. Materialized, the CTE builds all 400,000 rows, spills 19 MB to disk, then throws almost all of them away.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify control cte materialization fences, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceintermediatePro runbookOpen runbook

Refresh stale statistics that break query plans

After a bulk load the planner's stats lag reality, so it estimates 1 row where there are 312 and picks the wrong plan. A single ANALYZE restores accurate estimates.

The planner relies on column statistics gathered by ANALYZE/autovacuum. Right after a large load or a data-shape change those stats are stale, the estimates are wrong, and the planner makes decisions on numbers that no longer hold.

The scenario we reproduced

Meridian bulk-loads courier_shifts and immediately queries by courier_id. Autovacuum has not caught up, so the planner thinks the filter matches 1 row when it really matches 312 — and plans accordingly.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify refresh stale statistics that break query plans, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Replace an N+1 pattern with a LATERAL join

Fetching the latest child row per parent by scanning all children and de-duplicating is wasteful. A LATERAL subquery seeks each parent's row directly — 600,000 buffers drops to 200,309.

"Latest row per group" implemented as DISTINCT over a full child scan reads every child row to keep one per parent. It is the SQL cousin of the N+1 query: a lot of work to return a little data.

The scenario we reproduced

Meridian wants each consignment's most recent parcel scan. The naive DISTINCT ON walks all 600,000 scans; a LATERAL join instead seeks the top row per consignment.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify replace an n+1 pattern with a lateral join, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceintermediatePro runbookOpen runbook

Stop disk-spilled sorts by tuning work_mem

A sort bigger than work_mem spills to temp files and does an external merge. Size work_mem for the query and a 14 MB disk spill becomes an in-memory quicksort.

Every sort and hash gets work_mem bytes. When the data does not fit, PostgreSQL writes temp files and does an external merge — extra I/O that never shows up as a row count, only as time and temp bytes.

The scenario we reproduced

A Meridian report sorts 300,000 manifest lines by value and SKU. At the default work_mem the sort spills 14 MB to disk; with enough memory it stays a quicksort in RAM.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify stop disk-spilled sorts by tuning work_mem, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Cure HOT update degradation with fillfactor

Same table, same 1,200,000 updates. At fillfactor 100 only 319 of them were HOT and the heap grew to 59 MB. At fillfactor 70, 912,015 were HOT — 76.0% — and the heap stayed at 30 MB.

A heap-only tuple update writes a new row version on the same page and skips the indexes entirely. When there is no free space on the page, PostgreSQL has to put the new version elsewhere and add an entry to every index on the table. The table and its indexes then grow far faster than the data does, and nothing in the query text changes to warn you.

The scenario we reproduced

courier_telemetry takes a steady stream of position updates. The column being written is not indexed, but the table and its indexes keep growing anyway and nobody can explain why.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify cure hot update degradation with fillfactor, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Diagnose detoasting overhead on wide rows

The same 5,000-row index scan took 0.887 ms selecting three narrow columns and 120.265 ms once it touched the TOASTed body. The plan shape is identical; only the buffer count gives it away — 70 buffers against 20,098.

Large values are stored out of line in a TOAST table and fetched only when a query actually reads the column. That fetch does not appear as a node in the plan. It shows up as an unexplained jump in buffer counts and execution time on a scan that otherwise looks fine, which makes it easy to blame the index instead.

The scenario we reproduced

consignment_documents holds scanned paperwork. A listing endpoint that used to be quick got slow after someone added the document body to the SELECT list.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify diagnose detoasting overhead on wide rows, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Custom plan versus generic plan in prepared statements

After five executions PostgreSQL may freeze a prepared statement onto a generic plan. On a 999,000 to 1,000 skew that turned a 7.466 ms nested loop into a 153.215 ms hash join that spilled to disk — with no change to the SQL.

A prepared statement is planned with the actual parameter values for its first five executions. After that the planner compares the average custom cost against a generic plan built with no parameter knowledge, and may switch. On evenly distributed data nobody notices. On skewed data the generic plan is built for the average row count, which matches neither of the values you actually pass.

The scenario we reproduced

session_tokens_pc is 99.90% active and 0.10% revoked. A revocation sweep that runs as a prepared statement was quick in testing and slow in production, and the query text is byte-for-byte identical.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify custom plan versus generic plan in prepared statements, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Query performanceadvancedPro runbookOpen runbook

Reduce high planning time on complex queries

A fourteen-relation star join spent 378.737 ms planning. Capping join_collapse_limit at 6 brought planning down to 2.044 ms and produced exactly the same plan.

The planner searches join orders exhaustively, and the search space grows factorially with the number of relations. Past a dozen or so tables the planning itself becomes the expensive part. Nothing in EXPLAIN ANALYZE's execution numbers hints at it — you have to look at Planning Time, which most people skip straight past.

The scenario we reproduced

A Meridian reporting query joins route_legs to thirteen dimension tables. Execution is fine. The endpoint is still slow, and the slowness scales with nothing anyone can measure in the data.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify reduce high planning time on complex queries, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingintermediatePro runbookOpen runbook

Index the foreign key you forgot

PostgreSQL indexes the parent key of a foreign key but never the child column. Without it, deleting or updating a parent scans the whole child table — a delete's FK trigger drops from 22 ms to 0.2 ms once the index exists.

A FOREIGN KEY creates an index on the referenced (parent) column automatically, but not on the referencing (child) column. Every parent DELETE or key UPDATE then has to scan the child table to enforce the constraint.

The scenario we reproduced

Meridian's consignments reference couriers. Deleting one courier forces PostgreSQL to check consignments for children — and with no index on consignments.courier_id, that check is a full scan hidden inside a trigger.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify index the foreign key you forgot, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingadvancedPro runbookOpen runbook

Make index-only scans actually skip the heap

An index-only scan still visits the heap for any page the visibility map does not mark all-visible. VACUUM sets that map — after it, Heap Fetches drops to 0 and the query goes from 1.5 ms to 0.09 ms.

"Index Only Scan" is a promise, not a guarantee. For every heap page not marked all-visible in the visibility map, PostgreSQL still fetches the row to check visibility. A freshly written table has an empty map, so the "index-only" scan is anything but.

The scenario we reproduced

A Meridian count over consignments could be served entirely from an index, but right after loading the visibility map is unset, so the scan keeps diving into the heap.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify make index-only scans actually skip the heap, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingintermediatePro runbookOpen runbook

Covering indexes with INCLUDE

Add the columns a query returns as INCLUDE payload and the index can answer it without touching the table. A 833-buffer bitmap scan becomes a 1-buffer index-only scan.

An ordinary index finds the rows, then PostgreSQL visits the heap to read the columns you selected. If you fold those columns into the index as non-key INCLUDE payload, the scan never has to touch the table at all.

The scenario we reproduced

A Meridian query sums a small amount per courier from courier_ledger. A plain index on courier_id still reads 833 heap buffers to get the value column; a covering index carries it in the leaf.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify covering indexes with include, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingintermediatePro runbookOpen runbook

Partial indexes for hot subsets of data

If queries only ever touch a small slice of a table, index just that slice. A partial index on recent rows is 280 kB versus 4,152 kB for the full index — smaller, faster, cheaper to maintain.

Indexing an entire column when queries only ever hit a fraction of it wastes space and write cost. A WHERE clause on the index restricts it to the rows that matter, so it stays small and cache-friendly.

The scenario we reproduced

Meridian's delivery_attempts is huge, but the dashboard only queries the last couple of days. A full index on the timestamp indexes millions of old rows nobody reads; a partial index covers only the recent window.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify partial indexes for hot subsets of data, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingadvancedPro runbookOpen runbook

Get the multicolumn index column order right

In a composite index, column order decides whether a range query can use it. Ordering for the range predicate turns a sort-heavy scan into a clean index range scan — 829 block reads drop to a handful.

A B-tree on (a, b) is sorted by a, then b. A query with a range on one column and an order-by on another only benefits if the index columns are in the order the query can consume — otherwise PostgreSQL scans wide and sorts.

The scenario we reproduced

Meridian queries recent bookings with a time-range filter and ordering. With the composite index columns in the wrong order the planner scans broadly and sorts the result instead of walking the index in order.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify get the multicolumn index column order right, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingintermediatePro runbookOpen runbook

GIN indexes for jsonb containment queries

The jsonb containment operator @> cannot use a B-tree. A GIN index makes it a bitmap scan instead of a full scan — 42 ms drops to 11 ms on 200,000 documents.

Querying jsonb with @> ("contains this key/value") has no B-tree answer, so PostgreSQL scans every row and tests each document. A GIN index indexes the jsonb contents and turns containment into an index lookup.

The scenario we reproduced

Meridian stores consignment documents as jsonb and looks up all express-service documents with payload @> '{"service":"express"}'. Unindexed, that is a full scan of every document.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify gin indexes for jsonb containment queries, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingadvancedPro runbookOpen runbook

BRIN indexes for huge append-only tables

On an append-only table whose rows arrive in physical order, a BRIN index stores per-block ranges instead of per-row pointers. Here that is 24 kB versus 21 MB for a B-tree — for a range scan that is still fast.

A B-tree on a huge, naturally-ordered column (a timestamp on an append-only log) is enormous and mostly redundant, because the data is already clustered on disk. BRIN summarizes each block range instead, trading a little scan precision for a tiny index.

The scenario we reproduced

Meridian's scan_history is append-only and ordered by scan time. A B-tree on scanned_at is 21 MB; a BRIN index covering the same column is 24 kB and still serves the day-range report.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify brin indexes for huge append-only tables, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingintermediatePro runbookOpen runbook

Eliminate redundant and duplicate indexes

An index on (courier_id) is redundant when an index on (courier_id, booked_at) already exists — the composite serves both. Find the overlaps in the catalog and drop the dead weight off every write.

Redundant indexes are pure cost: every INSERT/UPDATE/DELETE maintains all of them, they take cache and disk, and they slow VACUUM. A single-column index is often already covered by a composite index that leads with the same column.

The scenario we reproduced

Meridian's waybill_lines has both idx_wl_courier on (courier_id) and idx_wl_courier_booked on (courier_id, booked_at). The single-column index is redundant — the composite can serve any query the narrow one can.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify eliminate redundant and duplicate indexes, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

IndexingbeginnerPro runbookOpen runbook

Find and drop unused indexes

pg_stat_user_indexes records how often each index is scanned. Indexes with idx_scan = 0 are cost with no benefit — here two indexes worth ~8.8 MB have never been used.

Indexes accumulate over years — added for a query that changed, or speculatively. Each one still costs write time, storage, and cache. The database itself tracks which are dead weight.

The scenario we reproduced

Meridian's tariff_zones has four indexes. The catalog shows two of them have never been scanned (idx_scan = 0) while carrying real size — pure overhead on every write.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify find and drop unused indexes, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatintermediateFreeOpen runbook

Reclaim table bloat without downtime

Dead tuples from updates and deletes leave a table larger than its live data. A plain VACUUM removes the dead rows but keeps the file size; VACUUM FULL rewrites it smaller — here 26 MB down to 10 MB — but takes an exclusive lock.

PostgreSQL's MVCC leaves dead row versions behind after UPDATE and DELETE. VACUUM marks that space reusable so the table stops growing, but it does not return space to the operating system. Only VACUUM FULL (or a rewrite tool) actually shrinks the file — at the cost of an exclusive lock.

The scenario we reproduced

Meridian's parcel_ledger has churned to 160,000 live rows but 240,000 dead ones, holding 26 MB. The team needs to reclaim space and decide between a non-blocking VACUUM and a shrinking VACUUM FULL.

How to identify it

›pg_stat_user_tables shows n_dead_tup large relative to n_live_tup.
›pg_table_size is much bigger than the live row count justifies.
›Sequential scans read more pages than the live data would need.
›The table has a history of heavy UPDATE/DELETE churn.

Pitfalls to avoid

✕Do not reach for VACUUM FULL to solve routine bloat — it takes an ACCESS EXCLUSIVE lock and blocks all reads and writes for the duration.
✕Do not disable autovacuum to 'save resources' — that is how bloat accumulates in the first place.
✕Do not assume plain VACUUM shrinks files; it only makes space reusable in place.
✕Do not run VACUUM FULL on a huge table without a maintenance window or an online tool (pg_repack).

Trace it — analysis steps

Measure the dead tuples and size

parcel_ledger holds 160,000 live rows but 240,000 dead ones in 26 MB — the dead versions dominate the table.

SELECT n_live_tup, n_dead_tup,
       pg_size_pretty(pg_table_size('parcel_ledger')) AS table_size
FROM pg_stat_user_tables WHERE relname = 'parcel_ledger';

Captured live on PostgreSQL 18

 n_live_tup | n_dead_tup | table_size 
------------+------------+------------
     160000 |     240000 | 26 MB
(1 row)

A plain VACUUM clears dead tuples but not size

After VACUUM the dead-tuple count is 0 — the space is now reusable by future inserts — but the table is still 26 MB on disk. VACUUM does not return space to the OS.

SELECT n_live_tup, n_dead_tup,
       pg_size_pretty(pg_table_size('parcel_ledger')) AS table_size
FROM pg_stat_user_tables WHERE relname = 'parcel_ledger';

Captured live on PostgreSQL 18

 n_live_tup | n_dead_tup | table_size 
------------+------------+------------
     160000 |          0 | 26 MB
(1 row)

Resolution approach

1.Run plain VACUUM regularly (or let autovacuum do it) so dead space is reused and the table stops growing.
2.Use VACUUM FULL only when you must return space to the OS, inside a maintenance window.
3.For online shrink without a long exclusive lock, use pg_repack or pg_squeeze instead of VACUUM FULL.
4.Tune autovacuum on high-churn tables so bloat never reaches the point of needing a rewrite.

Mitigation — stop it recurring

VACUUM FULL rewrites the table smaller

VACUUM FULL rewrites the heap and returns space to the OS — parcel_ledger drops from 26 MB to 10 MB. The trade-off is an exclusive lock while it runs.

SELECT n_live_tup, n_dead_tup,
       pg_size_pretty(pg_table_size('parcel_ledger')) AS table_size
FROM pg_stat_user_tables WHERE relname = 'parcel_ledger';

Captured live on PostgreSQL 18

 n_live_tup | n_dead_tup | table_size 
------------+------------+------------
     160000 |          0 | 10 MB
(1 row)

Prerequisites

• pg_stat_user_tables visibility for dead-tuple counts.
• A maintenance window if you choose VACUUM FULL (exclusive lock).

Verify you're done

SELECT n_live_tup, n_dead_tup, pg_size_pretty(pg_table_size('parcel_ledger')) FROM pg_stat_user_tables WHERE relname='parcel_ledger';

Vacuum & bloatintermediatePro runbookOpen runbook

Rebuild a bloated index with REINDEX CONCURRENTLY

Indexes bloat too. REINDEX INDEX CONCURRENTLY rebuilds an index without blocking reads and writes — here a bloated index shrinks from 19 MB to 4,832 kB.

B-tree indexes accumulate bloat under churn just like tables. A bloated index is slower to scan and wastes cache. REINDEX rebuilds it compactly; the CONCURRENTLY form does so without holding a lock that blocks the application.

The scenario we reproduced

Meridian's idx_routing_sort has bloated to 19 MB after heavy update churn. The index needs rebuilding, but the table is live, so a blocking REINDEX is not an option.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify rebuild a bloated index with reindex concurrently, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatadvancedPro runbookOpen runbook

Tame autovacuum on a high-churn table

The default autovacuum threshold (20% of the table) is far too lax for a hot table. Per-table settings lower it — here from 100,050 dead rows down to 11,000 — so cleanup keeps pace with churn.

Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + scale_factor * rows. At the default scale factor of 0.2, a large hot table has to accumulate a huge amount of bloat before cleanup starts. Per-table storage parameters fix that.

The scenario we reproduced

Meridian's session_tokens is updated constantly — 166,666 dead tuples already. At the default 0.2 scale factor autovacuum will not fire until ~100,050 dead rows, so bloat runs ahead of cleanup.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify tame autovacuum on a high-churn table, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatadvancedPro runbookOpen runbook

Unblock vacuum held back by a long transaction

VACUUM cannot remove dead rows newer than the oldest running transaction's snapshot. One idle-in-transaction session can pin cleanup cluster-wide — here 200,000 dead rows stay 'not yet removable' until it ends.

VACUUM only removes row versions that are invisible to every running transaction. A single long-lived transaction — even one sitting idle in a transaction block — holds back the removable cutoff (xmin), so dead tuples pile up until it commits or rolls back.

The scenario we reproduced

Meridian's courier_positions has 200,000 dead rows, but a nightly report job left a session idle in transaction. VACUUM runs but reports the dead rows as 'not yet removable'.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify unblock vacuum held back by a long transaction, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatadvancedPro runbookOpen runbook

Preempt transaction ID wraparound

PostgreSQL's 32-bit transaction IDs must be frozen before they wrap. Monitor age(relfrozenxid) per table and per database and VACUUM FREEZE the oldest — the query that finds them and the freeze that resets age to 0 are the whole technique.

Transaction IDs are 32-bit and wrap around. To stop old rows from appearing to come from the future, PostgreSQL must 'freeze' them before the age of a table's relfrozenxid approaches the two-billion limit. If freezing falls behind, the cluster eventually forces a shutdown to protect data.

The scenario we reproduced

Meridian wants to get ahead of wraparound: find the tables and databases with the oldest frozen XIDs and freeze them proactively rather than waiting for the emergency autovacuum.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify preempt transaction id wraparound, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatintermediatePro runbookOpen runbook

Find tables autovacuum keeps skipping

A table with autovacuum_enabled=false is never cleaned, no matter how much it bloats. A catalog query surfaces the ones with dead tuples and autovacuum disabled — here audit_trail, quietly turned off.

Someone disables autovacuum on a table (for a bulk load, a migration, a misguided tuning attempt) and never re-enables it. The table then bloats without limit because autovacuum simply skips it — and nothing warns you.

The scenario we reproduced

Meridian's audit_trail is bloating and never shows a last_autovacuum time. A catalog query reveals its reloptions carry autovacuum_enabled=false — cleanup was turned off and forgotten.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify find tables autovacuum keeps skipping, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatadvancedPro runbookOpen runbook

Shrink TOAST bloat from wide rows

Large values live in a separate TOAST table, and it bloats independently of the main heap. Here churn pushed TOAST from 159 MB to 639 MB while the heap stayed tiny — VACUUM FULL brings the total back down.

When a column value is too big for a page, PostgreSQL stores it out-of-line in a TOAST table. That TOAST table has its own dead tuples and its own bloat, which a glance at the main heap size completely misses. You have to size the TOAST relation explicitly.

The scenario we reproduced

Meridian stores large document bodies in document_blobs. The main heap is only ~1.3 MB, but updates to the big values have bloated the hidden TOAST table to hundreds of MB.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify shrink toast bloat from wide rows, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Vacuum & bloatadvancedPro runbookOpen runbook

Assess multixact wraparound risk

Multixact IDs have their own counter, their own freeze thresholds and their own way of shutting a cluster down — and almost nobody monitors them. Sixteen overlapping FOR SHARE sessions moved next_multixact_id from 3 to 19 here. The number is small on purpose; the query is the point.

When more than one transaction locks the same row at the same time, PostgreSQL cannot store a single xmax, so it allocates a MultiXactId that names the set of lockers. Those IDs come from a 32-bit counter that wraps, exactly like transaction IDs, and they have a separate freeze threshold that autovacuum enforces separately. A workload built on SELECT ... FOR SHARE or foreign keys over hot parent rows can consume them far faster than it consumes XIDs, and the usual age(relfrozenxid) dashboard shows nothing at all.

The scenario we reproduced

consignment_holds is read with SELECT ... FOR SHARE by several settlement workers whose transactions overlap. The rows involved are the same handful of holds, over and over.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify assess multixact wraparound risk, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencyintermediateFreeOpen runbook

Detect and resolve lock contention

When queries hang on a lock, pg_blocking_pids and pg_locks tell you exactly who is waiting on whom. One query gives you the blocker's PID; ending its transaction clears the wait.

A session waiting on a row or table lock simply hangs — no error, no progress. To resolve it you need to see the wait graph: which PID is blocked, which PID holds the conflicting lock, and what each is doing.

The scenario we reproduced

A Meridian api_request update sits waiting while a settlement_batch job holds the row it needs. The application looks frozen; the fix starts with seeing the block clearly.

How to identify it

›pg_stat_activity shows a session with wait_event_type = 'Lock' that never completes.
›pg_blocking_pids(pid) returns a non-empty array — the PIDs holding the lock it waits for.
›The blocked query is a normal UPDATE/DELETE, not a deadlock (no 40P01).
›The blocker is often idle in transaction, not actively working.

Pitfalls to avoid

✕Do not terminate a backend blindly — identify the blocker and whether it is doing real work first.
✕Do not confuse lock contention (one waits, one holds) with a deadlock (both wait; PostgreSQL raises 40P01 automatically).
✕Do not use pg_terminate_backend when pg_cancel_backend (cancel the query) is enough.
✕Do not ignore the root cause — a blocker that is idle in transaction points to a missing commit/timeout.

Trace it — analysis steps

See who is blocking whom

The wait graph shows pid 2445 (api_request) blocked on a Lock by pid 2440 (settlement_batch) — the blocker holds the row the update needs.

SELECT waiter.pid            AS waiting_pid,
       waiter.application_name          AS waiting_app,
       waiter.wait_event_type,
       blocker.pid                      AS blocking_pid,
       blocker.application_name         AS blocking_app,
       left(waiter.query, 46)           AS waiting_query
FROM pg_stat_activity waiter
JOIN LATERAL unnest(pg_blocking_pids(waiter.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid
WHERE cardinality(pg_blocking_pids(waiter.pid)) > 0
ORDER BY waiting_pid;

Captured live on PostgreSQL 18

 waiting_pid | waiting_app | wait_event_type | blocking_pid |   blocking_app   |                 waiting_query                  
-------------+-------------+-----------------+--------------+------------------+------------------------------------------------
        2445 | api_request | Lock            |         2440 | settlement_batch | UPDATE courier_accounts SET balance = balance 
(1 row)

Inspect the exact locks held

pg_locks confirms api_request is waiting on a tuple lock while settlement_batch holds the conflicting relation/row lock — the precise contention, not a guess.

SELECT a.application_name, l.locktype, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'courier_accounts'::regclass OR l.locktype = 'tuple'
ORDER BY a.application_name, l.granted DESC;

Captured live on PostgreSQL 18

 application_name | locktype |       mode       | granted 
------------------+----------+------------------+---------
 api_request      | tuple    | ExclusiveLock    | t
 api_request      | relation | RowExclusiveLock | t
 settlement_batch | relation | RowExclusiveLock | t
(3 rows)

Resolution approach

1.Identify the blocking PID with pg_blocking_pids and confirm what it is doing.
2.If it is idle in transaction or safe to interrupt, cancel or terminate it (pg_cancel_backend / pg_terminate_backend).
3.Fix the application path that left the transaction open, or add idle_in_transaction_session_timeout.
4.Confirm the previously-blocked session now has zero blockers.

Mitigation — stop it recurring

Confirm the wait cleared

After the blocker's transaction ends, the api_request session reports cardinality(pg_blocking_pids) = 0 — it is no longer waiting on anyone.

SELECT application_name, state, wait_event_type,
       cardinality(pg_blocking_pids(pid)) AS blockers
FROM pg_stat_activity
WHERE application_name IN ('settlement_batch','api_request')
ORDER BY application_name;

Captured live on PostgreSQL 18

 application_name |        state        | wait_event_type | blockers 
------------------+---------------------+-----------------+----------
 api_request      | idle in transaction | Client          |        0
(1 row)

Prerequisites

• pg_stat_activity / pg_locks visibility and pg_blocking_pids.
• Authority to cancel or terminate the blocking backend.

Verify you're done

SELECT pid, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Locking & concurrencyintermediatePro runbookOpen runbook

Stop idle-in-transaction sessions from holding locks

A session that opens a transaction, does one write, then sits idle keeps its locks the whole time. idle_in_transaction_session_timeout reaps it automatically — the short-leash session count drops from 1 to 0.

An 'idle in transaction' session has done some work but not committed. It keeps every lock and holds back vacuum, indefinitely, while doing nothing. The clean fix is a timeout that terminates such sessions automatically.

The scenario we reproduced

Meridian's reconcile_worker updated a ledger row and then stalled — idle in transaction — still holding row locks. Rather than hunt it down by hand, a timeout should reap it.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify stop idle-in-transaction sessions from holding locks, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencyintermediatePro runbookOpen runbook

Build a safe job queue with SKIP LOCKED

FOR UPDATE SKIP LOCKED lets multiple workers pull from the same queue table without stepping on each other — worker two grabs the next batch instead of stalling on the rows worker one already holds.

Multiple workers polling a queue with plain SELECT ... FOR UPDATE all try to lock the same top rows, so they serialize and stall. SKIP LOCKED tells each worker to ignore rows another worker already locked and take the next available ones.

The scenario we reproduced

Two Meridian dispatch workers pull from dispatch_jobs. Worker one holds jobs 1-5; worker two should immediately take 6-10, not wait behind worker one.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify build a safe job queue with skip locked, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencybeginnerPro runbookOpen runbook

Set sane statement, lock, and idle timeouts

By default statement_timeout, lock_timeout, and idle_in_transaction_session_timeout are all 0 — unlimited. Setting them turns a runaway query or a stuck lock wait into a fast, clean error instead of an outage.

Out of the box PostgreSQL will let a query run forever, wait on a lock forever, and sit idle in a transaction forever. Those defaults turn small problems into incidents. Three timeouts, set sensibly, bound the damage.

The scenario we reproduced

Meridian wants a query that runs too long to be cancelled, and an update that cannot get its lock quickly to fail fast rather than pile up behind a blocker.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify set sane statement, lock, and idle timeouts, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencyintermediatePro runbookOpen runbook

Coordinate work with advisory locks

Advisory locks let application code claim a named lock so only one worker runs a job at a time. pg_try_advisory_lock returns false to the second worker and true again once the first releases it.

Sometimes you need mutual exclusion that PostgreSQL's row/table locks do not express — 'only one instance of this cron job runs at once'. Advisory locks are application-defined locks on an arbitrary key, enforced by the database.

The scenario we reproduced

Meridian runs a scheduled reconciliation on several app nodes, but only one should execute at a time. Each node tries to take advisory lock 4820; only one wins.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify coordinate work with advisory locks, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencyadvancedPro runbookOpen runbook

Keep ALTER TABLE from blocking your app

An ALTER TABLE waits for an exclusive lock, and every reader that arrives behind it queues too — one migration stalls everyone. Setting lock_timeout makes the ALTER give up quickly instead of freezing the table.

ALTER TABLE needs an ACCESS EXCLUSIVE lock. If a long query already holds the table, the ALTER waits — and because its lock request is exclusive, every subsequent reader queues behind the ALTER. A single migration behind a slow query can freeze an entire table's traffic.

The scenario we reproduced

A Meridian schema_migration issues an ALTER on waybill_headers while a long_report still reads it. The ALTER waits, and an api_reader that arrives next is now stuck behind the ALTER — a two-level pile-up.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify keep alter table from blocking your app, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Locking & concurrencyintermediatePro runbookOpen runbook

Resolve row-level lock contention from SELECT FOR UPDATE

One session held a row with SELECT ... FOR UPDATE and a second sat behind it for 1.5 seconds on a transactionid wait. No table lock was involved, which is why the usual table-level lock queries show nothing.

Row locks do not appear as a lock on the table. The waiting session shows a wait_event_type of Lock and a wait_event of transactionid, and the thing it is waiting for is another transaction finishing — not a relation. Table-level lock dashboards stay green while the application queues up behind a single row.

The scenario we reproduced

settlement_run takes SELECT ... FOR UPDATE on a row in waybill_settlements and then does slow work before committing. payout_api wants the same row and blocks.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify resolve row-level lock contention from select for update, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilitybeginnerFreeOpen runbook

Find your slowest queries with pg_stat_statements

pg_stat_statements aggregates every query by normalized shape, so you can rank by total time spent — not just per-call time. Here one query burns 296.6 ms across its calls while a lookup beside it costs 0.8 ms.

You cannot fix what you cannot see. Without aggregated statistics you are guessing which queries hurt. pg_stat_statements records calls, total and mean execution time, and rows for each normalized query, so the real cost centers are obvious.

The scenario we reproduced

Meridian wants to know where the database actually spends its time. Ranking by total execution time immediately separates the expensive status scan from the cheap primary-key lookup.

How to identify it

›The extension pg_stat_statements is loaded (shared_preload_libraries) and the view is queryable.
›You need to rank queries by total time spent, not just the worst single call.
›Similar queries with different literals should be grouped as one normalized shape.
›You want calls, mean time, and rows alongside total time.

Pitfalls to avoid

✕Do not rank by mean time alone — a fast query run millions of times can outweigh a slow one run twice; total time tells the truth.
✕Do not forget the stats are cumulative; use pg_stat_statements_reset() to measure a specific window.
✕Do not ignore that literals are normalized to $1 — that is what lets you group a query shape.
✕Do not draw conclusions from a tiny sample right after a reset.

Trace it — analysis steps

Rank normalized queries by total time

The status scan (SELECT count(*) FROM parcel_events WHERE status = $1) cost 296.6 ms across 25 calls (11.866 ms mean), while the event_id lookup beside it cost 0.8 ms total — the ranking makes the real cost center obvious.

SELECT calls,
       round(total_exec_time::numeric, 1)  AS total_ms,
       round(mean_exec_time::numeric, 3)   AS mean_ms,
       rows,
       left(query, 52)                      AS query
FROM pg_stat_statements
WHERE query LIKE '%parcel_events%' AND query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;

Captured live on PostgreSQL 18

 calls | total_ms | mean_ms | rows |                        query                         
-------+----------+---------+------+------------------------------------------------------
    25 |    296.6 |  11.866 |   25 | SELECT count(*) FROM parcel_events WHERE status = $1
    25 |      0.8 |   0.033 |   25 | SELECT waybill FROM parcel_events WHERE event_id = $
(2 rows)

Resolution approach

1.Enable pg_stat_statements and query it ordered by total_exec_time DESC.
2.Investigate the top shapes with EXPLAIN (ANALYZE, BUFFERS).
3.Reset the stats to measure the effect of a change over a clean window.
4.Track the top-N over time so regressions surface early.

Mitigation — stop it recurring

01
Focus effort where the time goes
With the ranking in hand you tune the 296.6 ms query first — the 0.8 ms lookup is not worth touching. pg_stat_statements turns guessing into a priority list.
```
SELECT calls, round(total_exec_time::numeric,1) AS total_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```

Prerequisites

• pg_stat_statements in shared_preload_libraries and the extension created.
• Permission to read the pg_stat_statements view.

Verify you're done

SELECT calls, round(total_exec_time::numeric,1) AS total_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

ObservabilityintermediatePro runbookOpen runbook

Read EXPLAIN (ANALYZE, BUFFERS) like a pro

The BUFFERS option shows shared hit (found in cache) versus read (fetched from disk). A cold query reads 4 blocks; the warm run finds all 1,338 in cache with zero reads — proof of what the cache is doing.

Timings alone do not tell you whether a query is slow because of disk I/O or CPU. BUFFERS breaks each node into shared hit (served from shared_buffers) and read (fetched from the OS/disk), so you can see cache behavior directly.

The scenario we reproduced

Meridian runs the same depot_scans aggregate twice. The first run has a few reads from disk; the second is entirely cache hits — the numbers explain the difference in cost.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify read explain (analyze, buffers) like a pro, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilitybeginnerPro runbookOpen runbook

Track table and index bloat over time

pg_stat_user_tables exposes dead-tuple counts, so you can compute a bloat percentage and watch it. Here a table sits at 75% dead before a vacuum and 0% after — while the file size stays the same, proving VACUUM reclaims in place.

Bloat is invisible until it hurts. By turning n_dead_tup and n_live_tup into a percentage you get an early-warning signal, and by watching the size before and after vacuum you learn what vacuum actually does to disk usage.

The scenario we reproduced

Meridian tracks courier_positions, which has churned to 75% dead tuples. A vacuum clears the dead tuples but, as expected, does not shrink the file — exactly the distinction operators need to understand.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify track table and index bloat over time, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Watch wait events in pg_stat_activity

wait_event_type and wait_event in pg_stat_activity tell you why a session is not running — waiting on a lock, on the client, or sleeping. Three sessions here show three different causes at a glance.

A session that is not making progress could be blocked on a lock, waiting for the client to send more data, or deliberately sleeping. pg_stat_activity's wait_event columns name the exact reason, turning 'it's stuck' into a specific diagnosis.

The scenario we reproduced

Meridian sees three sessions in odd states. The wait columns immediately distinguish a real lock wait from a session that is merely idle-in-transaction waiting on the client, and from one that is just sleeping.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify watch wait events in pg_stat_activity, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Measure and raise your buffer cache hit ratio

The cache hit ratio from pg_stat_database tells you how often reads are served from shared_buffers instead of disk. Here the database sits at 99.87% and a hot lookup table at 100% — the metric to watch before adding RAM.

If most reads miss the cache and hit disk, everything is slow. blks_hit and blks_read from pg_stat_database (and per-table counters) give you the hit ratio, so you know whether more memory or better-fitting indexes would help.

The scenario we reproduced

Meridian checks whether its working set fits in memory. A database-wide ratio of 99.87% and a per-table 100% on the hot lookup confirm the cache is doing its job.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify measure and raise your buffer cache hit ratio, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityadvancedPro runbookOpen runbook

Catch queries spilling to temp files

When a sort or hash exceeds work_mem it spills to disk as an 'external merge'. EXPLAIN shows the Sort Method: a 64kB work_mem spills 3600kB to disk; 256MB keeps it an in-memory quicksort.

A sort or aggregate that does not fit in work_mem writes temporary files to disk, which is far slower and shows up as temp_files/temp_bytes in pg_stat_database. EXPLAIN's 'Sort Method' line tells you exactly when this happens.

The scenario we reproduced

Meridian sorts 300,000 shipment weights. With a tiny work_mem the sort spills to disk (external merge); with enough work_mem it stays an in-memory quicksort — the plan labels each.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify catch queries spilling to temp files, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Monitor VACUUM progress on a large table

VACUUM prints nothing until it is done. pg_stat_progress_vacuum names the phase and the block counter, so you can tell 3,445 of 19,608 blocks scanned from a job that has quietly stalled.

A long VACUUM looks identical to a hung VACUUM from the outside. pg_stat_activity gives you the query text and a start time and nothing else, so the usual reaction is to cancel it — which throws away every dead item ID collected so far and guarantees the next run is just as slow.

The scenario we reproduced

freight_manifest carries 2,000,000 live rows and 666,666 dead ones left behind by a bulk delete. The maintenance window vacuum has been running for a while and the on-call engineer needs to decide whether to wait it out.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify monitor vacuum progress on a large table, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Watch CREATE INDEX CONCURRENTLY progress

A CIC that reports 0 blocks done is usually not stuck — it is waiting. pg_stat_progress_create_index names the phase and hands you the PID it is waiting on, which is the difference between patience and a wrong cancel.

CREATE INDEX CONCURRENTLY spends its first phase doing no building at all. It waits for existing transactions to finish before it can trust the snapshot it will scan under. From outside the session it looks like nothing is happening, and the block counters genuinely are zero.

The scenario we reproduced

idx_routing_sort is being built on depot_transfers without taking the table offline. Ten minutes in, the index still does not exist and someone is asking whether to kill it.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify watch create index concurrently progress, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Track ANALYZE progress on large and partitioned tables

ANALYZE on a partitioned table is really one job per partition plus one for the parent. pg_stat_progress_analyze shows which child it is on and how many sample blocks are left — here 7,703 blocks and 4 of 4 children.

Statistics collection on a large partitioned table can take long enough that people assume it has hung, and there is no output until it commits. The parent and each child are separate units of work, so a single elapsed time tells you nothing about how far along you are.

The scenario we reproduced

consignment_history has four quarterly partitions of roughly 60 MB each and reltuples is still -1 on all of them, which means nothing has ever been analyzed. Plans on the table are bad and an ANALYZE is running to fix them.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify track analyze progress on large and partitioned tables, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityadvancedPro runbookOpen runbook

Monitor a CLUSTER or VACUUM FULL rewrite

CLUSTER and VACUUM FULL hold ACCESS EXCLUSIVE for the whole rewrite, so the only useful question is how much longer. pg_stat_progress_cluster answers it: 442,112 tuples in, then 3,000,000 written and the index rebuild underway.

Both commands rewrite the entire table into a new file while nothing else can read it. Once you have started, the only thing that matters is the remaining time, and neither command reports anything until it finishes.

The scenario we reproduced

waybill_archive is 395 MB across 3,000,000 rows and is being reorganised in a maintenance window. The window is finite and someone has to decide whether it will fit.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify monitor a cluster or vacuum full rewrite, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilitybeginnerPro runbookOpen runbook

Monitor COPY progress during a bulk load

COPY reports its row count only when it commits. pg_stat_progress_copy gives you bytes against the file size while it runs — 5,248 kB of 371 MB at the first sample, 201 MB and 54.1% a little later.

A large COPY gives you no feedback at all. On a multi-gigabyte load that means you cannot tell a slow import from a stalled one, and you cannot give anyone an honest estimate of when the table will be usable.

The scenario we reproduced

A 371 MB export is being loaded into consignment_import ahead of a cutover. The load is the critical path and someone needs a percentage, not a shrug.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify monitor copy progress during a bulk load, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityadvancedPro runbookOpen runbook

Diagnose I/O behaviour with pg_stat_io

A single cache hit ratio hides which part of the workload is doing the damage. pg_stat_io splits it by context: bulkread came in at 77.7% while normal access held 92.2%, with 29,344 evictions from one background worker.

The classic buffer-hit-ratio query gives you one number for the whole cluster, which is close to useless when a nightly report and an OLTP path share the same buffer pool. pg_stat_io breaks the same counters down by backend type, object and access context, so you can attribute the reads.

The scenario we reproduced

Meridian runs 128 MB of shared buffers. After a batch of large scans the overall hit ratio drops and the OLTP path feels slower, but nobody can point at the cause.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify diagnose i/o behaviour with pg_stat_io, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityintermediatePro runbookOpen runbook

Spot checkpoint write pressure before it hurts

num_requested climbing while num_timed stays flat means WAL volume, not the clock, is driving your checkpoints. A short write burst produced 3 requested checkpoints and 32,418 buffers written.

Checkpoints that fire because max_wal_size filled up are unplanned by definition. They arrive at whatever moment the workload happens to be busiest, and they are not spread by checkpoint_completion_target the way a timed checkpoint is. The counter that tells you this is sitting in pg_stat_checkpointer, unread.

The scenario we reproduced

A batch job on Meridian's write path produces enough WAL to fill max_wal_size repeatedly. Users report periodic stalls that do not line up with any cron entry.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify spot checkpoint write pressure before it hurts, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilitybeginnerPro runbookOpen runbook

Catch a high transaction rollback rate

xact_rollback against xact_commit is a one-line health check nobody runs. Here 200 of 506 transactions rolled back — 39.5% — and every single one was the same duplicate key.

Applications swallow errors. A retry loop that fails four times out of ten still looks fine from the outside, while the database burns transaction IDs, WAL and connection time on work that is thrown away. pg_stat_database counts it for you at no cost.

The scenario we reproduced

An importer feeding tracking_barcodes re-sends barcodes it has already loaded. Nothing alerts, because from the application's point of view the import 'completes'.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify catch a high transaction rollback rate, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

ObservabilityadvancedPro runbookOpen runbook

Read pg_locks: relation waits versus tuple waits

pg_locks mixes relation, tuple and transaction ID locks into one flat view. Decoding locktype is the difference between 'a DDL is queued behind everything' and 'two updates are fighting over one row'.

Everyone reads pg_locks eventually, and most people read it wrong. The row that looks like the wait is usually granted, and the one that actually represents the wait is a transactionid lock that names nothing recognisable. Until you decode locktype, the view is noise.

The scenario we reproduced

Three sessions on parcel_ledger_lines: settlement_owner has a row locked, settlement_waiter is queued behind it, and index_rebuild wants the whole table.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify read pg_locks: relation waits versus tuple waits, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsadvancedPro runbookOpen runbook

Add a NOT NULL column to a huge table safely

Instead of a blocking SET NOT NULL that scans the whole table under lock, add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it without a heavy lock, then flip the column. The constraint goes convalidated f then t.

Adding NOT NULL to a large existing column forces a full-table scan while holding a strong lock, which can freeze the table. The safe pattern splits the work: add the constraint as NOT VALID (instant, no scan under strong lock), validate it separately (scans without blocking writes), then set the column NOT NULL cheaply.

The scenario we reproduced

Meridian needs service_tier NOT NULL on a huge waybills_big table without a maintenance outage. The NOT VALID / VALIDATE split lets the enforcement land without a long exclusive lock.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify add a not null column to a huge table safely, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsadvancedPro runbookOpen runbook

Change a column type without a full table rewrite

Some type changes rewrite the whole table; others do not. Watch the relation's filenode: varchar to text keeps the same filenode (no rewrite), while int to bigint gets a new one (full rewrite).

ALTER COLUMN ... TYPE can be free or catastrophic. If the on-disk representation is unchanged (or binary-coercible), PostgreSQL skips the rewrite; if it changes, the whole table is rewritten under an exclusive lock. The relation's filenode tells you which happened.

The scenario we reproduced

Meridian plans two type changes on tracking_codes and wants to know which will rewrite the table. Comparing the filenode before and after each change answers it definitively.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify change a column type without a full table rewrite, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsintermediatePro runbookOpen runbook

Partition a growing table so the planner can prune

Range partitioning by month lets the planner skip every partition a query does not need. A date-equality query scans only shipments_2024_02 instead of the whole table.

A single enormous table forces every query to consider all of it. Range partitioning splits it by a key (say month), and the planner prunes to just the partitions a query's predicate can match — reading a fraction of the data.

The scenario we reproduced

Meridian's shipments grow forever. Partitioning by month means a query for one day only touches that month's partition; the rest are pruned before execution.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify partition a growing table so the planner can prune, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsadvancedPro runbookOpen runbook

Recover from a failed CREATE INDEX CONCURRENTLY

When CREATE INDEX CONCURRENTLY fails partway, it leaves an INVALID index behind that still costs writes but serves no reads. Find it (indisvalid = f), drop it, and rebuild.

CREATE INDEX CONCURRENTLY builds in two passes without a strong lock, but if it fails — say on a duplicate key for a unique index — it does not clean up. The leftover index is marked invalid: the planner ignores it for reads, yet every write still maintains it.

The scenario we reproduced

Meridian's concurrent unique-index build on courier_emails fails because of a duplicate address. An invalid index is left behind and must be cleaned up before retrying.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify recover from a failed create index concurrently, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsintermediatePro runbookOpen runbook

Validate a CHECK constraint without a long lock

Add a CHECK as NOT VALID and it enforces new writes immediately while skipping the scan of existing rows; VALIDATE later scans without blocking writes. The constraint goes convalidated f then t — and bad inserts fail from the start.

Adding a CHECK constraint normally scans the whole table under a lock to prove existing rows comply. The NOT VALID form skips that scan, starts enforcing on new rows immediately, and lets you validate the old rows in a separate, non-blocking step.

The scenario we reproduced

Meridian adds a 'weight must be positive' check to freight_charges. NOT VALID makes it enforce new writes right away, and a later VALIDATE confirms the existing rows without freezing the table.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify validate a check constraint without a long lock, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Schema migrationsadvancedPro runbookOpen runbook

Detach and attach partitions with minimal locking

A plain DETACH PARTITION died on a 2 s lock_timeout because one reader was open. The CONCURRENTLY form went through without ever taking a relation lock on the parent, and a matching CHECK constraint cut the re-attach from 53 ms to 0 ms.

Rolling a monthly partition out of a table is the routine part of running a partitioned schema, and it is also where partition maintenance most often takes an outage. ALTER TABLE ... DETACH PARTITION wants ACCESS EXCLUSIVE on the parent, which means it queues behind every open transaction and then blocks every new one. ATTACH has the mirror-image problem: it validates the partition bound by scanning the whole table under the same lock.

The scenario we reproduced

delivery_logs_p holds three monthly partitions of roughly 65 MB each. October needs to move to cold storage while the application keeps writing to November and December.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify detach and attach partitions with minimal locking, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Planner tuningadvancedPro runbookOpen runbook

Fix random_page_cost for SSD storage

random_page_cost defaults to 4.0, a spinning-disk assumption. On SSD, random reads are nearly as cheap as sequential — lowering it to ~1.1 flips a query from a Bitmap Heap Scan to a straight Index Scan.

The planner weighs random vs sequential I/O using random_page_cost (default 4.0) and seq_page_cost (1.0). That 4:1 ratio reflects spinning disks. On SSD or well-cached storage random access is far cheaper, so the default makes the planner avoid index scans it should prefer.

The scenario we reproduced

Meridian runs on SSDs but the planner still assumes random reads are 4x sequential, so it picks a Bitmap Heap Scan on parcel_scans_rpc where a plain Index Scan would fit the hardware better.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify fix random_page_cost for ssd storage, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Planner tuningadvancedPro runbookOpen runbook

Help the planner with effective_cache_size

effective_cache_size tells the planner how much memory it can assume is available for caching. It allocates nothing — but raising it lowers the estimated cost of index-friendly plans, here from 10612 to 8240 for the same query.

effective_cache_size does not reserve memory; it is a hint about how much OS+shared cache the planner can assume when costing repeated index access. Set too low, the planner overestimates how often index pages must be re-read from disk and shies away from nested-loop/index plans.

The scenario we reproduced

Meridian's effective_cache_size is left at a conservative default, so the planner costs its index-driven join pessimistically. Raising it to reflect actual RAM lowers the estimated cost of the same plan.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify help the planner with effective_cache_size, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Planner tuningadvancedPro runbookOpen runbook

Right-size work_mem for hash aggregates

When a GROUP BY does not fit in work_mem, the planner falls back to a sort that spills to disk (external merge, 5912kB here). Enough work_mem lets it use an in-memory HashAggregate with no spill (Batches: 1).

A grouping/aggregation needs memory. If work_mem is too small, PostgreSQL either sorts-then-groups with an on-disk external merge, or a hash aggregate spills into multiple batches. Sizing work_mem to the group set lets it stay a single in-memory HashAggregate.

The scenario we reproduced

Meridian aggregates 500,000 ledger_lines by account. With a small work_mem the plan sorts and spills 5912kB to disk; with enough work_mem it uses a single-batch in-memory HashAggregate.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify right-size work_mem for hash aggregates, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationadvancedPro runbookOpen runbook

Reclaim WAL held by an inactive replication slot

A replication slot guarantees WAL is kept until the consumer reads it — but an abandoned, inactive slot keeps that WAL forever and fills the disk. Here one inactive slot pins 362 MB; dropping it releases the WAL.

Replication slots stop the primary from recycling WAL a replica or logical consumer still needs. That is exactly what you want for an active consumer — and a disk-filling trap for an inactive one. A dropped replica or a stalled logical consumer leaves a slot that pins WAL indefinitely.

The scenario we reproduced

Meridian's standby went away but its physical slot remains. The slot is inactive yet still reserving WAL, and pg_wal is steadily filling toward a full disk.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify reclaim wal held by an inactive replication slot, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationintermediatePro runbookOpen runbook

Measure your WAL generation rate

pg_current_wal_lsn plus pg_wal_lsn_diff lets you measure exactly how much WAL a workload produces. Here 200,000 inserts generated 30 MB of WAL — the number that drives archiving, replication, and disk planning.

WAL volume drives replication bandwidth, archive storage, and pg_wal disk sizing, but it is invisible unless you measure it. Capturing the LSN before and after a workload and taking the difference gives you the exact bytes written.

The scenario we reproduced

Meridian wants to size WAL archiving and replication for a bulk-load pattern. Measuring the LSN delta across a 200,000-row insert gives a concrete WAL-per-operation figure to plan from.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify measure your wal generation rate, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationadvancedPro runbookOpen runbook

Flatten checkpoint I/O spikes by tuning checkpoints

At checkpoint_completion_target 0.1 the checkpointer put 20.2% of its buffers on disk in one second. At 0.9 the busiest second carried 2.3%, and the writes were spread over 50 seconds instead of 9.

A checkpoint has to write every dirty buffer in the pool. It can do that as fast as the storage allows, or it can pace itself across the checkpoint interval. The fast version is invisible on a graph of totals and extremely visible to anyone whose query happened to need the disk at that moment. checkpoint_completion_target is the knob, and its effect only shows up if you look at the write rate rather than the write count.

The scenario we reproduced

ckpt_heatmap is 75 MB against 128 MB of shared buffers, so its working set stays resident and the checkpointer — not the backend ring buffer — is what ends up writing it out. A steady stream of scattered updates keeps re-dirtying the same pages.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify flatten checkpoint i/o spikes by tuning checkpoints, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationintermediatePro runbookOpen runbook

Size WAL with max_wal_size and checkpoint_timeout

The same 217 MB of WAL forced 4 unplanned checkpoints at max_wal_size 128MB and none at 4GB. Measure the WAL rate first, then size the setting against your checkpoint interval instead of guessing.

max_wal_size is a ceiling on how much WAL accumulates before PostgreSQL gives up waiting for the timeout and checkpoints anyway. Set it too low and every write burst triggers an unplanned checkpoint, which is neither spread nor scheduled. Set it without measuring and you are picking a number from a blog post rather than from your workload.

The scenario we reproduced

A bulk load into wal_sizing_probe writes 1.44 million rows. checkpoint_timeout is pinned at an hour for the test so that nothing but WAL volume can trigger a checkpoint.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify size wal with max_wal_size and checkpoint_timeout, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationintermediatePro runbookOpen runbook

Reduce WAL volume with wal_compression

Turning wal_compression on cut the WAL for a full-table update from 82 MB to 68 MB — 17.1% with pglz, 16.1% with lz4. Useful, cheap, and nowhere near the numbers people quote.

With full_page_writes on, the first write to a page after each checkpoint copies the entire 8 kB page into the WAL. On an update-heavy workload those full-page images dominate the WAL stream, and they compress well. wal_compression compresses only those images, which is why the saving depends entirely on what fraction of your WAL they are.

The scenario we reproduced

A full-table update on wal_fpi_probe, 250,000 rows, run three times from an identical starting point with a CHECKPOINT immediately before each run so that every page write emits a full-page image.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify reduce wal volume with wal_compression, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100

WAL & replicationintermediatePro runbookOpen runbook

Right-size synchronous_commit for latency

3,000 single-row commits took 3,070 ms with synchronous_commit on and 241 ms with it off — 1.023 ms against 0.080 ms per commit. That gap is the fsync, and switching it off means accepting the loss of recently committed work after a crash.

Every COMMIT with synchronous_commit on waits for WAL to reach durable storage. For a transaction that did real work the wait is amortised into nothing. For a stream of tiny transactions it is most of the cost, and the shape of the fix — batch the work, or relax durability for that specific job — depends on knowing how big the gap actually is on your hardware.

The scenario we reproduced

An acknowledgement feed inserts one row per scan into waybill_acks, one commit per row. 3,000 of them, run twice: once with synchronous_commit on and once with it off.

Pro runbookReproduced, not just described

The full runbook for this incident

The scenario above is free. What Pro unlocks is the fix: how to identify right-size synchronous_commit for latency, the exact SQL to trace it, the output we captured live on PostgreSQL 18, the resolution path, and how to stop it recurring.

What Pro unlocks here

The full identify checklist — the exact signals that tell you it's this incident
Every diagnostic query with the output we captured live on PostgreSQL 18
The resolution path and the pitfalls that make it worse
Mitigation steps to stop it recurring, plus a verify-you're-done query

Unlock with Pro — $24.99/moor $199/yr — save ~$100