DEV Community

Cover image for Catch the dangerous Postgres migration at the CI step
Technical Turtle
Technical Turtle

Posted on

Catch the dangerous Postgres migration at the CI step

#postgres #sql #database #devops 
-- 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;
Enter fullscreen mode Exit fullscreen mode

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

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

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

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 33 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.

Top comments (1)

Collapse
 
mads_hansen_27b33ebfee4c9 profile image
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.