The 47-Minute Outage

What started as a routine schema migration became a production outage. The culprit? A missing CONCURRENTLY flag.

The Change

We needed to add an index on a 2M row table:

-- What we ran (WRONG):
CREATE INDEX idx_user_email ON users(email);

Should have been:

-- What we should have run:
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

The Impact

Without CONCURRENTLY: The index creation acquires an ACCESS EXCLUSIVE lock, blocking ALL reads and writes to the table.

• Duration: 47 minutes
• Affected queries: 847,392
• Failed requests: 12,847 (1.5% error rate)

The Recovery

1. Abort the blocked index (we didn’t realize for 8 minutes)
2. Clear the connection queue - kill all waiting queries
3. Re-run with CONCURRENTLY (took 3 minutes this time)

Monitoring That Failed Us

• ❌ No alert on long-running transactions
• ❌ No alert on lock waits > 5 seconds
• ❌ No canary deployment for schema changes

The Fix

1. Run migrations in staging first
2. Deploy during low-traffic window (< 5% capacity)
3. Always use CONCURRENTLY for CREATE INDEX
4. Set statement_timeout = 300s (5 minute max)
5. Alert on pg_locks.waiting_count > 10

Outage duration: 47 minutes | Root cause: Missing CONCURRENTLY flag | Fix: New migration checklist + monitoring