Index bloat after churn

Index bloat after churn
Type: incident simulation · Topic: Vacuum & Bloat · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-07/03-index-bloat-after-churn

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: heavy churn on the indexed column (external_id) in tenant_b.orders left
the index on external_id bloated — many dead/!near-empty pages it never gave back —
so it grew large and scans over it slowed.

How it was found: pg_stat_user_indexes showed the external_id index far larger than
its live row count justified, distinct from table-level bloat.

The mitigation: rebuild the index — REINDEX INDEX idx_orders_external_id (in
production, REINDEX INDEX CONCURRENTLY to avoid blocking writes).

Lesson: index bloat needs a rebuild, not just a VACUUM — a plain VACUUM reclaims
index entries for reuse but doesn't shrink the index file. Prefer REINDEX
CONCURRENTLY (or pg_repack). Dropping the index, adding a redundant one, or
reindexing the wrong database is wrong.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. tenant_b's queries slowed and its index is huge after churn on the indexed column. Look at index sizes: \connect tenant_b then SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)), idx_scan FROM pg_stat_user_indexes WHERE relname='orders' ORDER BY pg_relation_size(indexrelid) DESC;
2. This is index bloat: the index on external_id is bloated from heavy update/delete churn. A plain VACUUM reclaims index entries for reuse but does NOT shrink the index file — you need to rebuild it.
3. Rebuild the index in tenant_b: REINDEX INDEX idx_orders_external_id; (in production prefer REINDEX INDEX CONCURRENTLY). Don't add another redundant index, don't drop it, and don't reindex the wrong database.