The Postgres migration that locks your table at 3am, and how to catch it in review

-- looks fine in review, locks your busiest table in prod:
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- same result, no outage:
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

The top one scans every row under an ACCESS EXCLUSIVE lock, so every read and write to the table blocks until it finishes. On a big table that is a four-minute outage. The bottom one gets to the same place without ever holding that lock during a scan. A generalist reviewing the diff cannot tell them apart, because both just say "make email NOT NULL".

That is the whole problem. Whether a Postgres migration is safe has almost nothing to do with intent and everything to do with locks. A migration is dangerous when it holds a strong lock while it scans or rewrites a large table:

• ACCESS EXCLUSIVE blocks reads and writes.
• SHARE blocks writes.

On a ten-row table you never notice. On a hot production table it is an outage. And there is a second trap: Flyway and Liquibase wrap each migration in a transaction by default, which changes what is safe and what errors out.

Here are the changes most likely to hurt, and the safe rewrite for each.

1. CREATE INDEX without CONCURRENTLY

A plain CREATE INDEX takes a SHARE lock that blocks every write for the entire build. On a big table that is minutes of blocked writes.

-- danger
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- safe (note: cannot run inside a transaction)
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

The catch: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which is exactly how Flyway and Liquibase wrap migrations by default. A bare concurrent-index migration will error out. You have to disable the wrapping transaction for that one migration (Flyway: a script config flag; Liquibase: runInTransaction="false").

2. SET NOT NULL, directly

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL scans the whole table under ACCESS EXCLUSIVE to prove no nulls exist.

-- danger
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- safe (PG12+ skips the scan because a validated CHECK already proves it)
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;  -- scans, but only takes a SHARE UPDATE EXCLUSIVE lock (writes continue)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;      -- fast, no scan

3. ADD CHECK without NOT VALID

Same shape as above. A plain ADD CONSTRAINT ... CHECK scans the whole table under ACCESS EXCLUSIVE. Add it NOT VALID first (instant, only checks new rows), then VALIDATE CONSTRAINT in a separate step (scans under a weaker lock that lets writes continue).

4. ADD FOREIGN KEY without NOT VALID

Adding a foreign key validates every existing row while holding a write-blocking lock on both the referencing and referenced tables. Same fix: add it NOT VALID, then VALIDATE CONSTRAINT separately.

5. ADD COLUMN with a volatile DEFAULT or SERIAL

Since PG11, adding a column with a constant default is cheap (metadata only). But a volatile default has to be evaluated per row, which rewrites the whole table under ACCESS EXCLUSIVE.

-- danger: gen_random_uuid(), random(), and SERIAL are volatile -> full rewrite
ALTER TABLE events ADD COLUMN token uuid DEFAULT gen_random_uuid();

-- safe
ALTER TABLE events ADD COLUMN token uuid;            -- nullable, cheap
-- backfill in batches (see 8), then:
ALTER TABLE events ALTER COLUMN token SET DEFAULT gen_random_uuid();

A constant default like now() evaluated once, or a literal, is fine.

6. ALTER COLUMN TYPE

Changing a column type rewrites the table, with a few binary-coercible exceptions (for example varchar to text, or widening a varchar(n)). For anything else: add a new column, backfill it, swap in application code, drop the old column in a later deploy.

7. ADD PRIMARY KEY or UNIQUE, directly

Adding these builds the underlying index under ACCESS EXCLUSIVE. Build the index first, concurrently, then attach it:

CREATE UNIQUE INDEX CONCURRENTLY orders_pkey_idx ON orders (id);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_idx;

8. Backfills in the wrong order, or one giant statement

UPDATE big_table SET ... with no WHERE bound takes one long transaction, bloats the table, and can deadlock. And backfilling after you have already added the gating constraint fails. The order that works: add the column nullable, batch-backfill (by primary key ranges, committing each batch), validate, then tighten the constraint, across separate deploys.

9. Lock-taking DDL with no lock_timeout

Even a fast ALTER queues behind long-running queries, and everything queues behind it. One slow ALTER can stall all traffic to a table. Bound the wait:

SET lock_timeout = '2s';
ALTER TABLE ...;   -- if it cannot get the lock in 2s, it fails instead of stalling the app; retry

10. Destructive operations

DROP TABLE, TRUNCATE, DROP COLUMN, DROP SCHEMA CASCADE are irreversible and break rolling deploys where old app instances still reference the object. Stop referencing it in the app, deploy, then drop it in a later migration.

The pattern

Almost every safe rewrite above is the same move: never hold a strong lock while you scan or rewrite. Split the dangerous one-step change into a fast metadata change plus a separate, weakly-locked scan or a batched backfill. Do it across deploys.

I turned this into a tool

I do this review by hand often enough that I encoded it. The Postgres Migration Safety Auditor reads a Flyway or Liquibase migration (SQL, XML, YAML, JSON), parses it with libpg_query (the same parser Postgres uses, so a column named type or a dollar-quoted body is understood, not misread by a regex), and flags each hazard with the reason and, where it can justify one from a cited catalog, the safe rewrite. 33 rules, every one pointing at an official Postgres, Flyway, or Liquibase source.

It runs three ways: as a CLI, as a CI gate (machine-readable JSON, exit codes keyed to severity so it fails the build on a blocker), or as a Claude Code skill. It never connects to your database and never runs the migration, so it needs no credentials and nothing leaves your machine. Python 3, nothing to pip install (the parser is bundled per platform). One-time EUR 14.99.

The full ten-hazard cheatsheet is free on the landing page, so grab that even if the tool is not for you: https://technical-turtle.com/postgres-migration-auditor#cheatsheet

There is also a free, open-source version, pg-migration-guard, that runs these ten rules as a CLI and a GitHub Action, if you want the check in CI without the full catalog.

Most migration outages are one of these ten. Knowing them is most of the fight; automating the check across all 33 rules is the rest.