DEV Community

晖莫
晖莫

Posted on

The Migration That Passed in Staging Because Staging Was Empty

#postgres #migrations #testing #database

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;
Enter fullscreen mode Exit fullscreen mode

On a table with eleven rows, that is a round trip. On 40 million rows:

  • The UPDATE rewrote every row with a non-null shipped_at. That is a lot of WAL, a lot of dead tuples, and a lock on all of them.
  • SET NOT NULL had to scan the table to validate, holding ACCESS EXCLUSIVE for 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:

  1. How many rows does this touch? If the answer is "all of them", the migration needs to be batched.
  2. How long does the lock last? Measure it, do not guess. Add a statement timeout and see it fire.
  3. 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
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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.

Subscribe by email · RSS · Bluesky

Top comments (0)