CREATE INDEX without CONCURRENTLY

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

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a release migration built an index on the hot `orders` table with a
plain `CREATE INDEX` (no CONCURRENTLY). A non-concurrent build holds a SHARE lock
on the table for its entire duration, and SHARE conflicts with the ROW EXCLUSIVE
lock that writes need — so every INSERT/UPDATE blocked until the build finished
(here, the migration kept the transaction open, so it blocked indefinitely).

How it was found: pg_stat_activity showed a `migration_runner` session whose query
was `CREATE INDEX ...`, holding a lock that pg_blocking_pids() tied to the stalled
`app_writer`.

The fix: terminate the blocking build, then rebuild without blocking writes:
  CREATE INDEX CONCURRENTLY idx_orders_user_id_safe ON orders (user_id);
CONCURRENTLY builds the index without taking a write-blocking lock (and can't run
inside a transaction block).

Lesson: on a live table, always build indexes with CONCURRENTLY. A plain
CREATE INDEX in a migration is a release-time write outage. Pair it with a
`lock_timeout` so a migration fails fast instead of stalling production.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Writes to orders started blocking during a release. pg_stat_activity shows a migration session whose query is a CREATE INDEX (no CONCURRENTLY), running inside a transaction and holding a lock.
2. A plain CREATE INDEX takes a SHARE lock for the whole build, which conflicts with the ROW EXCLUSIVE that writes need — on a live table that's a write outage. The safe form for a hot table is CREATE INDEX CONCURRENTLY.
3. End the migration's blocking build (application_name LIKE 'migration_runner%'), then rebuild the index the safe way: CREATE INDEX CONCURRENTLY ... (it can't run inside a transaction block).