The migration ran in 40ms in staging and took the checkout table down for six minutes in production. Both statements in it were correct. Staging just had nothing to say about either of them.
Two failures that look like one
The migration was:
ALTER TABLE orders ADD COLUMN fulfilled_at timestamptz;
UPDATE orders SET fulfilled_at = shipped_at WHERE shipped_at IS NOT NULL;
ALTER TABLE orders ALTER COLUMN fulfilled_at SET NOT NULL;
On a table with eleven rows, that is a round trip. On 40 million rows:
- The
UPDATErewrote every row with a non-nullshipped_at. That is a lot of WAL, a lot of dead tuples, and a lock on all of them. -
SET NOT NULLhad to scan the table to validate, holdingACCESS EXCLUSIVEfor the duration. No reads, no writes, no queries at all on that table while it ran.
Staging was created from a migration chain, not from data. Every table was empty or nearly so. Both operations completed before anyone could notice they were the wrong shape.
What a data-shaped database would have said
The point of staging is not "the SQL is syntactically valid" — a linter does that. The point is to answer three questions that only data can answer:
- How many rows does this touch? If the answer is "all of them", the migration needs to be batched.
- How long does the lock last? Measure it, do not guess. Add a statement timeout and see it fire.
- What does the new constraint do to existing rows? An empty table can satisfy any constraint.
A production-shaped staging database answers all three in the same 40 seconds the empty one took. A masked dump of a few million rows is enough; it does not need to be recent or complete, it needs to have the same shape of distribution.
The migration we should have written
Three separate deploys, each boring.
Add the column, nullable. No rewrite on a modern Postgres, no lock beyond the catalog update.
Backfill in batches with a bound.
UPDATE orders
SET fulfilled_at = shipped_at
WHERE id IN (
SELECT id FROM orders
WHERE fulfilled_at IS NULL AND shipped_at IS NOT NULL
LIMIT 5000
);
Run it until it returns zero rows. Each batch is a short transaction, so autovacuum can keep up and a failure resumes where it stopped. Log the row count per batch; a batch that suddenly takes ten times longer is the warning that the table changed under you.
Add the constraint as a validated-none index first.
CREATE UNIQUE INDEX CONCURRENTLY orders_fulfilled_at_idx ON orders (fulfilled_at);
For NOT NULL there is no CONCURRENTLY, but there is a two-step: add a CHECK (fulfilled_at IS NOT NULL) NOT VALID constraint, which is instant and only applies to new rows, then VALIDATE CONSTRAINT in its own transaction. Validation takes a SHARE UPDATE EXCLUSIVE lock — reads and writes continue.
The cheap version
If you cannot get a production-shaped staging database, at least make the migration fail loudly on the wrong size:
SET lock_timeout = '3s';
SET statement_timeout = '30s';
A migration that times out in staging and gets rewritten is infinitely cheaper than one that holds a table for six minutes. And add one assertion to the deploy: count the rows the migration expects to touch, and compare it with the count it actually touched. Eleven is a number that should have looked wrong.
Staging that cannot disagree with you is not a test. It is a syntax check with a database attached.
I write about production failures in Postgres, queues, and distributed systems.
