CREATE INDEX without CONCURRENTLY blocks writes

CREATE INDEX without CONCURRENTLY blocks writes
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/05-create-index-without-concurrently

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a migration ran a plain `CREATE INDEX` on a hot table. A
non-concurrent index build takes a SHARE lock on the table for the entire
build, which conflicts with the ROW EXCLUSIVE lock writes need — so every
INSERT/UPDATE blocked until the build finished (here, an open transaction kept
it blocking).

How it was found: pg_stat_activity showed a session whose query was
`CREATE INDEX ...`, holding a lock that pg_locks/pg_blocking_pids tied to the
stalled writes.

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

Lesson: on a live table, always build indexes with CONCURRENTLY. A plain
CREATE INDEX is a write outage. This is a release/migration discipline issue as
much as a locking one.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Writes to orders started blocking during a release. Look at pg_stat_activity — a CREATE INDEX is running (in a transaction) and holding a lock that conflicts with writes.
2. A plain CREATE INDEX takes a SHARE lock that blocks all writes to the table for the whole build. On a hot table you must use CREATE INDEX CONCURRENTLY instead.
3. Terminate the blocking index build, then rebuild safely: CREATE INDEX CONCURRENTLY ... (note: CONCURRENTLY can't run inside a transaction block).