Missing Index on a Hot Endpoint
A production incident, replayed by hand: the orders endpoint got an order of magnitude slower with no deploy and no traffic spike. Bring up the same broken database, find the offender in pg_stat_statements, read the plan, and fix it with one index.
The checkout endpoint suddenly slowed down by an order of magnitude. CPU looks fine, traffic is normal, nothing was deployed — and yet every request crawls. This book replays the incident on a database you bring up yourself: the schema, the data and the broken state are all in plain SQL files, so nothing is hidden. You reproduce the pain with a stopwatch, catch the offending query with pg_stat_statements, convict it with EXPLAIN (ANALYZE, BUFFERS), and fix it with a single composite index — then prove the fix by watching the plan flip. The diagnosis path is the real one you'd use on call.
What you'll build
- Reproduce a latency incident on a local PostgreSQL stand
- Find the most expensive query with pg_stat_statements
- Read an EXPLAIN (ANALYZE, BUFFERS) plan: Seq Scan, rows removed, buffers
- Design a composite index that matches filter + order
- Verify a fix by comparing plans and timings, not by faith
Contents
- The stand
- Bring it up
- The schema
- Apply the schema
- The data
- Load it
- A clean slate for statistics
- Reproduce the pain
- Let the traffic flow
- Ask the database what hurts
- Convict it with a plan
- Design the fix
- Apply it
- Watch the plan flip
- Close the ticket