Failed CREATE INDEX CONCURRENTLY

Failed CREATE INDEX CONCURRENTLY
Type: incident simulation · Topic: Migrations & Releases · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-09/03-failed-create-index-concurrently

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a release migration ran `CREATE INDEX CONCURRENTLY` on orders, but the
build failed during its validation phase. Unlike a plain CREATE INDEX (which rolls
back cleanly), a *failed concurrent* build leaves an INVALID index behind
(pg_index.indisvalid = false). The planner ignores it, but it still consumes space
and blocks a straight retry under the same name.

How it was found: pg_index showed an index on orders with indisvalid = false — the
leftover from the failed concurrent build.

The fix: drop the invalid leftover, then rebuild concurrently:
  DROP INDEX IF EXISTS idx_orders_customer_id;
  CREATE INDEX CONCURRENTLY idx_orders_customer_id_valid ON orders (customer_id);
(Use DROP INDEX CONCURRENTLY on a hot table to avoid the brief exclusive lock.)

Lesson: CONCURRENTLY trades a blocking lock for the risk of a half-finished,
invalid index. Always check indisvalid after a concurrent build, clean up the
invalid leftover, and only then retry — don't pile a second index on top of it,
and don't rebuild without CONCURRENTLY.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. A migration ran CREATE INDEX CONCURRENTLY and it failed partway. CONCURRENTLY avoids blocking, but a failed build leaves an INVALID index behind. Inspect pg_index.indisvalid for indexes on orders.
2. Find the invalid leftover: SELECT c.relname, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_class t ON t.oid = i.indrelid WHERE t.relname = 'orders'; The invalid index isn't used by the planner and must be cleaned up before retrying.
3. Drop the invalid index (DROP INDEX IF EXISTS idx_orders_customer_id), then rebuild the right index concurrently: CREATE INDEX CONCURRENTLY idx_orders_customer_id_valid ON orders (customer_id). Don't leave the invalid one in place and don't rebuild without CONCURRENTLY.