Five migrations that pass CI and take down production

Your migration test suite runs against a database created thirty seconds ago. It has no rows, or a few hundred from fixtures. Every migration you have ever written completes against it in single-digit milliseconds.

That is the structural problem. The failure mode of a dangerous migration is duration, and duration is proportional to table size. A test database has no size. So the test cannot fail.

CI is not checking whether your migration is safe. It is checking whether your migration is syntactically valid, which you already knew.

Here are five that sail through.

1. A volatile column default

ALTER TABLE users ADD COLUMN api_key uuid DEFAULT gen_random_uuid();

On PG 11+, a non-volatile default is stored once in the catalog and served to every existing row without touching them — instant. A volatile default has to be evaluated per row, so Postgres rewrites the entire table, holding ACCESS EXCLUSIVE for the duration. Reads and writes both queue.

The confusing part: now() and CURRENT_TIMESTAMP are STABLE, not volatile, and take the fast path. gen_random_uuid(), random(), clock_timestamp(), and nextval() are the ones that rewrite.

Safe path: add the column nullable, SET DEFAULT as a separate statement (which applies to new rows only and is instant), then backfill existing rows in batches. Full write-up here.

2. A non-concurrent index build

CREATE INDEX idx_users_email ON users (email);

Takes SHARE for the entire build. Reads continue; every INSERT, UPDATE, and DELETE blocks until the index is finished.

Safe path: CREATE INDEX CONCURRENTLY, which builds under SHARE UPDATE EXCLUSIVE and lets writes through. It cannot run inside a transaction, so it needs its own migration with the transaction wrapper disabled. If it fails partway it leaves an INVALID index that you must drop and rebuild:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

That failure case is real and worth handling in your runbook, but it is a much better problem than a blocked write path.

3. SET NOT NULL

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Takes ACCESS EXCLUSIVE and scans every row to verify the constraint holds. Reads and writes queue for the whole scan.

This one is widely believed to have become instant in PG 12. It did not. What PG 12 added is a scan-free path: if a validated CHECK (col IS NOT NULL) constraint already exists, SET NOT NULL trusts it and skips the scan. You still have to do the scan — you just get to do it under a lock that does not block traffic.

Safe path (PG 12+):

ALTER TABLE users ADD CONSTRAINT email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT email_nn;    -- SHARE UPDATE EXCLUSIVE
ALTER TABLE users ALTER COLUMN email SET NOT NULL; -- now instant
ALTER TABLE users DROP CONSTRAINT email_nn;

Any valid CHECK that proves no NULL can exist will do — CHECK (val > 0) works as well as an explicit IS NOT NULL. Do not drop the constraint in the same statement that sets NOT NULL; a separate later statement is fine.

On PG 11 and older there is no scan-free path — backfill the NULLs in batches first so the scan is pure validation, then run it in a low-traffic window.

On PG 18 it collapses to two steps, because NOT NULL is now a first-class constraint:

ALTER TABLE users ADD CONSTRAINT email_nn NOT NULL email NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT email_nn;

4. A foreign key without NOT VALID

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id);

Takes SHARE ROW EXCLUSIVE on both tables while validating. Writes block on orders and on users.

The referenced table is the part that catches people. You reviewed a migration touching orders and took down writes to users, which is a much busier table, and nothing in the statement made that obvious.

Safe path:

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

NOT VALID enforces the constraint for new and updated rows immediately, skipping the scan of existing ones. VALIDATE then does that scan under SHARE UPDATE EXCLUSIVE, which blocks nothing.

5. Any migration without a lock_timeout

This is the one that gets missed, because there is no bad statement to point at.

ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';

Instant. Catalog-only. Completely safe — in isolation.

Now suppose a reporting query has been running against users for four minutes. Your ALTER TABLE requests ACCESS EXCLUSIVE, conflicts, and waits. Every query that arrives afterwards and conflicts with that pending request queues behind it. The table is effectively frozen — not by your migration, which has not started, but by your migration waiting.

Your instant migration just caused a four-minute outage.

Safe path:

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';

The migration now fails fast instead of holding the queue open. A failed deploy you retry is a categorically better outcome than an incident, and this single line prevents a large share of migration-related downtime.

What CI would need to catch these

To catch any of the above, your test database would need production-scale row counts, production-representative concurrent traffic, and the same Postgres major version as production. Almost nobody has that, and building it is a much larger project than the migration under review.

The alternative is to not run the migration at all — read it, and reason about the lock it takes and how long it holds it. Both of those are determined by the statement, the Postgres version, and the table size. All three are knowable before you merge.

That is a static analysis problem, not a testing problem. Which is convenient, because static analysis is cheap and running your migration against 48 million rows in CI is not.