The Database That Won’t Answer

Locks, Latches & Transactions · Open board

The queries waiting on each other, drawn side by side with the engine’s own split-second holds. This lesson is what you do when one of those queues has frozen a real app and nobody yet knows which one.

Before you start. The last seven lessons opened one database a layer at a time: a table is a pile of 8 KB pages (Pages & Heap Files), an index is a sorted tree that finds a row in a few page reads (B-Tree on Disk), an update leaves the old row behind for vacuum to sweep up (MVCC & Vacuum), the planner bets on a plan from a small summary of each table (The Query Planner), and a lock makes one query wait for another’s whole transaction (Locks, Latches & Transactions). This lesson uses all of it at once, to work out which of those parts has gone wrong when a database gets sick.

The idea in 30 seconds

When a database is sick, read what Postgres has already written down before you restart anything. A restart throws that record away and rarely fixes the cause.
pg_stat_activity lists every open connection, what it is running and what it waits on; pg_blocking_pids() names who each one waits behind, which turns a hang into a readable line of waiters.
A lock queue forms when a query that needs a table to itself waits behind a running read: every query that arrives after it waits too, even ones that could have shared the table.
pg_stat_statements, once switched on, keeps a running total per kind of query, so the one eating the most time is on top; EXPLAIN (ANALYZE, BUFFERS) on that one shows where it goes wrong.
pg_stat_user_tables records what happened to each table: dead rows vacuum could not clear, and rows changed since the planner’s statistics were last rebuilt.
After any fix, run the same diagnostic query that found the problem. The fix is proven when that query’s output changes, not when the app feels faster.

Read the story · Skip to the numbers