The migration that locked payments for forty minutes

The migration was on the calendar for weeks. Add two indexes on payments, backfill a nullable column, deploy the code that reads it. The maintenance window opened at 22:00. By 22:04 the RDS CPU graph was flat, replication was healthy, and CloudWatch showed green across the board.

By 22:06 every checkout in Europe was returning 500.

What the script actually did

The migration file looked innocent. It had been reviewed. It had run in staging on a Friday snapshot without complaint:

ALTER TABLE payments ADD COLUMN settlement_id UUID;

UPDATE payments SET settlement_id = gen_random_uuid() WHERE settlement_id IS NULL;

CREATE INDEX idx_payments_settlement ON payments(settlement_id);
CREATE INDEX idx_payments_customer_created ON payments(customer_id, created_at DESC);

Staging had four million rows and finished in eleven minutes. Production had ninety-two million. That difference mattered, but not for the reason we expected.

The UPDATE was slow and boring: batchable, visible in pg_stat_activity, easy to abort. The indexes were the problem. Neither used CONCURRENTLY. On PostgreSQL, a plain CREATE INDEX takes an ACCESS EXCLUSIVE lock. Reads and writes on payments queue behind it. The table is not corrupted. It is simply unavailable, for as long as the index build takes.

Our connection pool did not see “database locked.” It saw timeouts. The API retried. Retries piled up. RDS looked fine because the database was working exactly as designed: one session held a lock everyone else was waiting on.

How the dashboard stayed green

Three signals lied by omission, not by error.

RDS CPU and free storage were comfortable. Index builds are I/O-heavy but not always CPU-heavy. A flat CPU graph does not mean your app can write rows.

Replica lag stayed under a second. The replica was not the one taking writes. Users were not reading stale data. They were not reading at all.

The migration runner reported step three of four as “in progress.” That was accurate and useless. Nobody had wired an alert on wait_event = 'Lock' for the payments relation, because we had never needed one before.

The forty minutes ended when someone ran SELECT * FROM pg_locks from the bastion, found the index build, and cancelled it. We re-ran both indexes with CONCURRENTLY over the next two hours. No further customer impact. The column and the backfill had already completed before the lock. Only the indexes had to be redone.

What we changed

That night:

• Split the migration into phases: schema change, backfill in batches, indexes last. Each phase in its own deployable revision.
• Added SET lock_timeout = '5s' at the top of every migration file. A migration that cannot get a lock should fail fast, not hold the table hostage.

That week:

• Staging refresh policy: restore a prod snapshot monthly and run migrations against row counts that resemble reality, not a trimmed fixture.
• Pre-migration checklist in the ticket template: index strategy (CONCURRENTLY or justify why not), estimated lock duration, rollback steps, who is watching pg_stat_activity.

The habit that stuck:

-- migration runner preamble, every file
SET lock_timeout = '5s';
SET statement_timeout = '30min';

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_settlement
  ON payments(settlement_id);

and a CI lint that fails if CREATE INDEX appears without CONCURRENTLY outside an explicit /* migration:offline-window */ block that requires a second reviewer.

The part I keep thinking about

The runbook was followed. The window was approved. The script had worked somewhere else. Every box was ticked except the one that asked whether ninety-two million rows would wait quietly while we built an index the slow way.

RDS did not break. We asked it to hold a lock during peak-adjacent hours and it complied. The failure was boring SQL in a file that staging had already forgiven.

If your proof that a migration is safe is that the infrastructure metrics look normal, you are monitoring the server, not the experience of the rows inside it.