REINDEX needed

REINDEX needed
Type: incident simulation · Topic: Vacuum & Bloat · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-07/09-reindex-needed

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: tenant_b.orders' index on external_id was oversized/inefficient after
churn, and the correct operational response was to rebuild that specific index. The
common wrong moves are to add another index, drop it, or VACUUM FULL the table.

How it was found: pg_stat_user_indexes showed the external_id index far larger than
its usage justified; the decision was which index to rebuild and how, safely.

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

Lesson: when an index needs rebuilding, rebuild *that* index — don't pile on a
duplicate, don't drop it, and don't reach for VACUUM FULL on the whole table.
Prefer REINDEX CONCURRENTLY (or pg_repack) so production writes aren't blocked. Act
on the right database.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Queries on tenant_b.orders using external_id slowed and the index is oversized. Identify the exact index: \connect tenant_b then SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan FROM pg_stat_user_indexes WHERE relname='orders' ORDER BY pg_relation_size(indexrelid) DESC; idx_orders_external_id is the problem.
2. The operational decision is to REBUILD that one index — not to add another index (more to maintain), not to drop it (queries lose it), not VACUUM FULL the table (heavy lock).
3. Rebuild it in tenant_b: REINDEX INDEX idx_orders_external_id; (in production prefer REINDEX INDEX CONCURRENTLY). Don't create a duplicate index and don't reindex the wrong database.