Our checkout page started timing out at 14:02. Not slowly — completely. Every request that touched the orders table hung, connection pool filled, and the whole app went down behind it. The migration I had just run took 40 milliseconds. That was the part that confused me for a while.
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'usd'; is fast. It also takes an ACCESS EXCLUSIVE lock on orders. That lock does not care how fast the statement runs. It cares that every read and write already in flight has to finish first, and everything arriving after it queues behind. A 40ms statement plus a 3-second queue of waiting queries is a 3-second outage on that table. Multiply by the retry storm and the pool is gone.
The lock is the outage. Statement speed is almost irrelevant.
The shape that actually works
The broken mental model is "one migration, made atomic and careful." The real constraint is different: during a rollout, old instances and new instances are both live. Both run against the same schema for some window. A migration that only works for the new code breaks the old code, and a migration that only works for the old code breaks the new code. Neither version of a single-script plan survives that window.
So the change has to be split. Expand, then contract. Both halves are boring on their own, and that is the point.
Expand: add, write both, backfill, read new
Expand is the additive half. Add the new column or table without breaking anyone, then move traffic over one step at a time.
-- Step 1: nullable, no default rewrite, no long lock.
ALTER TABLE orders ADD COLUMN currency text;
-- Step 2: a NOT VALID constraint takes only a brief lock and
-- does not scan the table. Existing rows are not checked yet.
ALTER TABLE orders
ADD CONSTRAINT orders_currency_not_null
CHECK (currency IS NOT NULL) NOT VALID;
Then the code deploys in stages. First a release that writes both the old and the new field, still reading the old one. Then a backfill, in batches, in the background. Then a release that reads the new field and keeps dual-writing. Then a release that stops writing the old field.
The backfill is where I have caused the second-worst incidents. A single UPDATE orders SET currency = 'usd' WHERE currency IS NULL on a large table rewrites every row, generates a huge amount of WAL, and holds row locks for the duration. On a primary with streaming replicas, that WAL is what your replicas have to replay. Push too much and replication lag climbs. Replicas fall behind the primary, and read-your-own-write breaks for users hitting them.
Batch it, sleep between batches, and watch lag as the loop runs, not after.
while True:
rows = db.execute("""
UPDATE orders SET currency = 'usd'
WHERE id IN (
SELECT id FROM orders
WHERE currency IS NULL
ORDER BY id LIMIT 1000
FOR UPDATE SKIP LOCKED
)
RETURNING id
""")
if not rows:
break
time.sleep(0.05)
if replica_lag_seconds() > 5:
time.sleep(2)
Measure the batch size against your own table. What matters is that you check replication lag inside the loop and slow down or stop when it grows.
Lock timeouts are the safety net
Before any DDL, set a lock timeout in the same transaction:
BEGIN;
SET lock_timeout = '3s';
SET statement_timeout = '15s';
ALTER TABLE orders ADD COLUMN currency text;
COMMIT;
If the ALTER cannot get its lock within 3 seconds, it fails instead of blocking every query behind it. A failed migration is a retry. A queued ACCESS EXCLUSIVE lock is an outage. I would rather rerun a migration than explain a 14:02 incident again.
This does not make the migration safe by itself. It makes the failure mode small.
Contract: drop last, after nothing reads it
Dropping is the easy half to get wrong, because it feels finished the moment the new code ships. It is not. Drop the old column only after you have confirmed no running code reads it and no query in your logs still references it. In a rolling deploy with long-lived instances, "the new release is out" and "old instances are gone" are different facts.
Break the work across two deploys at minimum, usually more. The clever single script is the wrong shape because it assumes one instant where the schema and all running code shift together. That instant does not exist in a rolling deploy, and every design that pretends it does is a lock waiting for traffic.
Check the lock mode of any statement you plan to run, keep the transaction short, and put a timeout on it. Then do the other half next week.
I write about production failures in Postgres, queues, and distributed systems.
Top comments (0)