Postgres Migration Safety Auditor.

Most migration reviews are a generalist reading a diff. That misses the specific ways a PostgreSQL migration takes your site down. The auditor checks 33 rules, parses your actual migration with the real PostgreSQL parser (not regex), and flags each hazard with an official citation:

• Lock-taking ALTERs (ACCESS EXCLUSIVE and friends)
• Non-concurrent index builds (CREATE / REINDEX / DROP INDEX without CONCURRENTLY)
• SET NOT NULL full-table scans, and when a validated CHECK lets you skip them on PG12+
• Foreign keys or CHECKs added without NOT VALID then VALIDATE
• Volatile column defaults and type changes that force a full-table rewrite
• Backfill-before-constraint ordering mistakes
• Destructive operations (DROP TABLE / TRUNCATE / DROP COLUMN / DROP SCHEMA CASCADE)
• Partition attach/detach, explicit LOCK TABLE, unbatched bulk UPDATE / DELETE

The ten schema changes most likely to take a live PostgreSQL database down, and the safe rewrite for each. A migration is dangerous when it holds a strong lock (ACCESS EXCLUSIVE blocks reads and writes; SHARE blocks writes) while it scans or rewrites a big table. On a small table you never notice; on a hot production table it is an outage. Flyway and Liquibase wrap each migration in a transaction by default, which matters for several of these.

1. CREATE INDEX (without CONCURRENTLY) takes a SHARE lock that blocks every write for the whole build. Safe: CREATE INDEX CONCURRENTLY ... outside a transaction.
2. SET NOT NULL (directly) scans the whole table under ACCESS EXCLUSIVE. Safe: ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT c, then SET NOT NULL (scan skipped on PG12+).
3. ADD CHECK (without NOT VALID) scans the whole table under ACCESS EXCLUSIVE. Safe: add it NOT VALID, then VALIDATE.
4. ADD FOREIGN KEY (without NOT VALID) validates every row while holding a write-blocking lock on both tables. Safe: add it NOT VALID, then VALIDATE.
5. ADD COLUMN with a volatile DEFAULT or SERIAL rewrites the entire table (gen_random_uuid(), random(), SERIAL). now() / CURRENT_TIMESTAMP is safe. Safe: add nullable, backfill in batches, then SET DEFAULT.
6. ALTER COLUMN ... TYPE rewrites the table, except binary-coercible changes like varchar to text. Safe: new column, backfill, swap, drop the old one.
7. ADD PRIMARY KEY / UNIQUE (directly) builds the index under ACCESS EXCLUSIVE. Safe: CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX.
8. Backfills in the wrong order or one giant statement fail or bloat. Safe: add nullable, batch-backfill, validate, then tighten, across separate deploys.
9. Lock-taking DDL with no lock_timeout can stall traffic queued behind it. Safe: SET lock_timeout = '2s'; before risky DDL, and retry on timeout.
10. Destructive operations (DROP / TRUNCATE / DROP COLUMN) are irreversible and break rolling deploys. Safe: expand/contract, stop referencing the object first, drop it in a later deploy.

Gotcha: CREATE INDEX CONCURRENTLY cannot run inside a transaction, which is how Flyway and Liquibase wrap migrations by default, so a bare concurrent-index migration errors out. Disable the wrapping transaction for that one migration. The auditor catches this too, and 22 more rules beyond these ten.

By design, pure static analysis: it reads the migration text and never connects to your database, so it needs no credentials and runs identically in CI or on your laptop, with nothing leaving your machine. It reasons from the Postgres version and row counts you give it (or auto-filled via the optional local read-only introspection), and catches the common, high-impact hazards a diff review misses.