Catch the dangerous Postgres migration at the CI step

-- looks routine in review, locks your table in production:
CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Both of those statements hold a lock that blocks traffic while they scan or build against the table. On a small table you never notice. On a busy production table it is downtime, and a diff review does not catch it because the SQL looks fine.

pg-migration-guard is a free tool that reads the migration and flags these before they reach production:

$ pg-migration-guard V42__orders.sql

  WARN  IDX01  CREATE INDEX without CONCURRENTLY   [becomes a blocker on a large or busy table]
    Why:  Plain CREATE INDEX takes a lock that blocks all writes until the build finishes.
    Safe: CREATE INDEX CONCURRENTLY idx ON tbl (col);  -- outside a transaction

  WARN  NN01  SET NOT NULL scans the whole table under ACCESS EXCLUSIVE
    Safe: ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID; VALIDATE CONSTRAINT c; then SET NOT NULL;

Summary: 0 blocker, 2 warn, 0 advisory

Why migrations bite

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. The change that reads fine in review is the one that quietly takes that lock.

Put it in CI

The most useful place for this is the pull request, so a risky migration fails the build before anyone merges it. There is a GitHub Action:

name: migration-guard
on:
  pull_request:
    paths: ["db/migration/**.sql"]
jobs:
  guard:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.x"
      - uses: technical-turtle/pg-migration-guard@v1
        with:
          path: db/migration
          fail-on: blocker

Each finding shows up as an inline annotation on the diff, so the author sees exactly which line is the problem and what the safe rewrite is.

Or run it locally

pip install pg-migration-guard
pg-migration-guard db/migration/V42__add_index.sql

Human output by default, --format json for tooling, --fail-on {blocker,warn,advisory,none} for the exit code. Python 3.8+, no other dependencies.

What it catches

Ten of the most common migration foot-guns:

• CREATE INDEX without CONCURRENTLY, and a CONCURRENTLY index op inside a transaction (which errors out)
• SET NOT NULL directly (scans the whole table under a strong lock)
• ADD CHECK and ADD FOREIGN KEY without NOT VALID
• ADD COLUMN with a volatile DEFAULT, and ALTER COLUMN TYPE (both rewrite the table)
• ADD PRIMARY KEY / UNIQUE directly
• backfill ordering mistakes and unbatched UPDATE / DELETE
• lock-taking DDL with no lock_timeout
• destructive ops (DROP TABLE, TRUNCATE, DROP COLUMN, DROP SCHEMA)

Each finding names the hazard, links to the official Postgres docs, and prints the safe rewrite.

How it works

It parses the SQL with libpg_query, the same parser PostgreSQL itself uses, so a column named like a keyword, a schema-qualified table, or a dollar-quoted body is read correctly rather than misread by a regex. The parser is bundled as a small BSD-licensed binary and bound through the Python standard library, so there are no third-party dependencies and the tool never connects to a database or the network. It is MIT-licensed: https://github.com/technical-turtle/pg-migration-guard

Full coverage

pg-migration-guard is the free version of the Postgres Migration Safety Auditor. The paid tool checks 36 rules (not 12), each linked to an official source, and adds Liquibase XML / YAML / JSON changelog support, Postgres-version and table-size aware checks, a config file with per-rule mute controls, and a Claude Code skill: https://technical-turtle.com/postgres-migration-auditor

Run it against your migrations directory once, before your next deploy. The old migrations nobody has looked at since are usually where it finds something.

Comments

[Mads Hansen]

Static analysis is an excellent first gate. The next failure mode to design for is the “safe rewrite” failing halfway. CREATE INDEX CONCURRENTLY avoids blocking writes, but cancellation or a crash can leave an INVALID index behind; a blind retry may then collide with the existing object.

I’d pair the lint rule with an execution runbook: explicit lock_timeout and statement_timeout, a preflight check for conflicting/invalid indexes, monitoring of pg_stat_progress_create_index and blockers, and idempotent cleanup/retry steps. CI can also consume a sanitized production metadata snapshot—Postgres version, relation sizes, row estimates, partitioning, replica lag expectations—so severity reflects the target environment without granting CI production access. Syntax tells you the hazard class; version, scale, and recovery behavior tell you whether the rollout is actually safe.

[Technical Turtle]

Agreed on the split. The linter's job is the hazard class; execution safety is a separate layer it cannot see. The INVALID-index-on-cancellation case is a good example: the retry-safe move is DROP INDEX CONCURRENTLY IF EXISTS before rebuilding, or checking pg_index.indisvalid first, but that belongs in the runbook, not the static check.

On severity reflecting the target environment: that is the piece the free tool leaves on the table on purpose. The paid version takes a Postgres version and rough row counts (auto-filled from an optional read-only introspection) and uses them to gate the size-dependent rules, so a warning on a 10-row table and a blocker on a 200M-row one come out differently. Partitioning I ended up building out since this: the paid tool now detects partitioned parents and adjusts the index, constraint, and lock rules for them, so a concurrent index build or a primary key that omits the partition key is flagged as an error on a partitioned table, and DDL that recurses across every partition is called out. Replica lag is the harder one, and it sits on the execution side I mentioned up top: it is a live runtime signal the static pass cannot observe, so it belongs in the deploy gate and runbook rather than in the severity a static check assigns.