Blameless review: the 'quick' migration that blocked every write to our orders table

Short incident review, because the lesson is more useful than the embarrassment.

What happened

A normal Tuesday deploy at 08:12 ran two migrations generated by our migration tool:

ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0;
CREATE INDEX orders_priority_idx ON orders (priority);

Both look harmless. Since Postgres 11, adding a column with a constant default does not rewrite the table, so the ALTER is instant. And it was, once it got its lock.

Timeline (UTC+2)

• 08:12 The ALTER TABLE needs an ACCESS EXCLUSIVE lock. An analytics query had been reading orders for 11 minutes. The ALTER waits behind it.
• 08:12 Every new query on orders, even plain SELECTs, queues behind the waiting ALTER. API requests pile up. 504s.
• 08:14 The analytics query finishes. The ALTER runs in milliseconds. Queue drains.
• 08:14 CREATE INDEX without CONCURRENTLY takes a lock that blocks writes while it builds the index over 90 million rows.
• 08:18 Index done. Writes resume. Total: about 6 minutes of failed or very slow order creation.

What we changed

-- every migration now starts with this
SET lock_timeout = '3s';
SET statement_timeout = '15min';

ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0;

If the ALTER cannot get its lock in 3 seconds, it fails instead of building a queue behind it, and our deploy retries it with backoff a few times. A failed migration is annoying. A queue in front of the busiest table is an outage.

-- separate migration, run outside a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_priority_idx ON orders (priority);

CONCURRENTLY builds the index without blocking writes. It is slower and it cannot run inside a transaction block. If it fails halfway, it leaves an invalid index behind, so we check after every deploy:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

We also added squawk, a linter for Postgres migrations, to CI. It flags CREATE INDEX without CONCURRENTLY, missing lock timeouts, and a few other classics.

Blameless part

The engineer did what the tool generated and what our review checklist allowed. The tool defaults and the checklist were wrong. Both are fixed. Nobody needs to feel bad, everybody needs to know about lock queues.

I wrote this on the train because it rained too hard even for me. Do not tell anyone.