Partial Index Opportunity

A production incident, replayed by hand: the failed-orders dashboard crawls because a query that only ever needs a two-percent slice of the table scans all of it. Bring up the same database, weigh a full index against a partial one, and fix it with the index that stores only the hot slice.

An ops dashboard that lists recent failed orders has been getting slower every week. Nothing was deployed; the table just grew. This book replays the incident on a database you bring up yourself: the schema, the data and the broken state are plain SQL files, so nothing is hidden. You reproduce the pain with a stopwatch, catch the query with pg_stat_statements, convict it with EXPLAIN (ANALYZE, BUFFERS) — and then face the real decision. The reflex answer is a btree on the status column; you build it and weigh it. The right answer is a partial index that stores only the failed rows: a fraction of the size, no maintenance tax on the other ninety-eight percent of writes, and exactly as fast for the only query that matters. You compare both with pg_relation_size, drop the wasteful one, and watch the plan flip.

What you'll build

• Reproduce a slow-dashboard incident on a local PostgreSQL stand
• Find the offending query with pg_stat_statements
• Measure how rare the hot predicate really is before designing an index
• Compare a full-column index against a partial index by size and by plan
• Build a partial index whose WHERE clause matches the query predicate

Contents

1. The stand
2. Bring it up
3. The schema
4. Apply the schema
5. The data
6. Load it
7. A clean slate for statistics
8. Reproduce the pain
9. Ask the database what hurts
10. How rare is failed?
11. Convict it with a plan
12. The reflex index
13. Design the real fix
14. Build it
15. Weigh the two answers
16. The plan flips — close the ticket