Block unsafe Postgres migrations before merge.

Local, deterministic analysis using PostgreSQL's parser. Runs in your terminal and CI. No account required.

npx migrationpilot analyze migration.sql

Try it in your browserMIT licensed. All 112 rules, free.

The panel beside this one is the engine, not a screenshot of it. It runs entirely in your browser: open DevTools and watch the network tab while you type. Nothing you write is sent anywhere.

migrations/20260812_orders_constraints.sqlPostgreSQL 17

ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0);

CREATE INDEX idx_orders_customer_id
  ON orders (customer_id);

ALTER TABLE users
  ALTER COLUMN email TYPE varchar(255);

RED 100/1006 critical4 warningsfails --fail-on criticalanalysed locally

DDL statement acquires ACCESS EXCLUSIVE lock without a preceding SET lock_timeout. Without a timeout, this statement could block the lock queue indefinitely if it can't acquire the lock, causing cascading query failures.
```
-- Set a timeout so DDL fails fast instead of blocking the queue
SET lock_timeout = '5s';
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0)
RESET lock_timeout;
```
Why this rule exists

Static analysis: it reads the SQL, never the database. 15 of the 112 rules need --database-url and stay silent here.

Measured against Squawk and pgfence on 56 labelled migrations

bench/RESULTS.md

Tool	Hazards named (33 dangerous files)	False positives (17 safe files)
MigrationPilot	30/33 (90.9%)	1/17 (5.9%)
Squawk	20/33 (60.6%)	1/17 (5.9%)
pgfence	25/33 (75.8%)	3/17 (17.6%)

56 labelled files. Author-built corpus. Tools pinned. Detection is strict: the tool has to name the specific hazard the file was written to contain. Every file MigrationPilot missed is listed by name in the results.

Methodology Corpus What we missed Reproduce it

Where the rules come from

Public write-ups of migrations that went wrong. MigrationPilot flags the SQL pattern described in each one. Whether a rule would have changed the outcome is not something a linter gets to claim.

GitLab.com incident #6642
18 March 2022
A post-deploy migration could not acquire its lock and blocked auto-deploy. The write-up walks through the lock queue and the missing timeout.
MP004require-lock-timeout MP020require-statement-timeout
GitLab.com incident #21712
6 April 2026
A deadlock during a post-deploy migration. Adding a foreign key locks the referenced table as well as the referencing one, which is where the cycle came from.
MP069warn-fk-lock-both-tables MP005require-not-valid-fk

How one waiting DDL statement forms a lock queue

ACCESS EXCLUSIVE conflicts with every other lock mode, and a request that cannot be granted takes the head of the queue. Everything arriving after it waits, including plain SELECTs that would not have conflicted with anything. Below is one measured run of SET NOT NULL on a 50 million row table, done two ways against the same workload.

The one-liner

1 critical

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

2,190.9 msAESET NOT NULL (scans 50M rows)

ALTER TABLE completes -- ACCESS EXCLUSIVE released

Lock held on usersACCESS EXCLUSIVE, 2,190.9 ms

Connections queuednow 0 of 20

Slowest query, log scalenow 0.46 ms

Peak queue depth: 20 of 20
Worst single query: 2.19 s

The choreography MP002 asks for

0 critical

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;

ALTER TABLE users DROP CONSTRAINT users_email_nn;

2.04 msAEADD CONSTRAINT ... NOT VALID
1,748.9 msSUEVALIDATE CONSTRAINT
0.50 msAESET NOT NULL
0.66 msAEDROP CONSTRAINT

DROP CONSTRAINT completes -- scaffolding gone

Lock held on usersACCESS EXCLUSIVE, 3.2 ms total

Connections queuednow 0 of 20

Slowest query, log scalenow 0.60 ms

Peak queue depth: 0 of 20
Worst single query: 1.48 ms

Same schema change, same workload, same machine. p99 client latency went 0.57 ms to 2,028 ms, and peak queue depth went 0 to 20 of 20 connections. Fourteen of the queries stuck in that queue were plain SELECTs holding only AccessShareLock, which conflicts with nothing except the ACCESS EXCLUSIVE request sitting in front of them. That is the lock queue: they were not blocked by the migration, they were blocked by waiting for it.

The choreography makes the scan free, not the schema change. A third run with one slow reader present found the safe path's brief metadata locks stuck behind that reader for seconds, queueing everything behind them in turn. Brief locks still have to be acquired, which is why MP004 wants SET lock_timeout on every one of them, and why this lab deliberately sets none. That run is written up in full.

One measured run. PostgreSQL 18.4, 50M rows, 20 clients, 80% point SELECT / 20% point UPDATE by primary key, rate-limited to 2,000 tx/s (pgbench, seed 42). Storage was tmpfs, so these are a floor: real disks are worse. The unsafe scan varies about 13% between runs. 8785fdc9b. Raw traces and the reproduce script.

One rule, all the way down

Every rule is a claim about PostgreSQL, so every rule carries what backs it: the manual, a handbook entry, a public write-up, and its result in the benchmark. Here is one of them in full.

MP001require-concurrent-index-creationcriticalauto-fixable

Triggers on

CREATE INDEX idx_users_email ON users (email);

Lock it takes

ACCESS EXCLUSIVE on the table, held for the entire index build. Blocks reads and writes.

Affected versions

All supported versions, 14 through 18.

Safe form

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Known boundary

CONCURRENTLY cannot run inside a transaction, and a failed build leaves an invalid index behind that has to be dropped before retrying. Those are separate rules: MP025 and MP070.

Evidence

Handbook entry MPH-001, which cites the CREATE INDEX manual and a carwow engineering write-up.

Benchmark: the non-concurrent-index hazard appears in 7 corpus files. MigrationPilot names it in 7 of 7, as do Squawk and pgfence.

The full MP001 page

One engine, three entry points

The terminal, CI and your coding agent are three callers of the same analysis. Same rules, same verdict, same exit code. Nothing to sign up for and nothing to send anywhere.

Terminal

--fix rewrites what can be rewritten mechanically: 20 of the 112 rules. Here it added the timeouts, NOT VALID and CONCURRENTLY. It left the column type change alone, because that one is a five-step plan rather than a rewrite, and MP007 says so.

$ migrationpilot analyze migrations/ --fix

SET lock_timeout = '5s';
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

SET statement_timeout = '30s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
  ON orders (customer_id);

ALTER TABLE users
  ALTER COLUMN email TYPE varchar(255);

CI

The Action analyses every changed migration and exits non-zero when something crosses fail-on, so the required check fails. It also emits SARIF for GitHub code scanning. This is what it leaves on the pull request.

.github/workflows/migration-safety.yml

on:
  pull_request:
    paths: ['migrations/**']

jobs:
  migration-safety:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      pull-requests: write
    steps:
      - uses: actions/checkout@v4
      - uses: mickelsamuel/migrationpilot@v1
        with:
          migration-path: migrations/
          fail-on: critical

github-actionsbotcommented on migrations/

MigrationPilot: Migration Safety Report

Risk Level: RED (score: 80/100)

DDL Operations

#	Statement	Lock Type	Blocks R/W
1	ALTER TABLE orders ADD CONSTRAINT orders_amount_posi...	ACCESS EXCLUSIVE	R+W

Safety Violations

CRITICAL [MP004]: DDL statement acquires ACCESS EXCLUSIVE lock without a preceding SET lock_timeout. Without a timeout, this statement could block the lock queue indefinitely if it can’t acquire the lock, causing cascading query failures.
CRITICAL [MP030]: CHECK constraint "orders_amount_positive" on "orders" without NOT VALID scans the entire table under ACCESS EXCLUSIVE lock, blocking all reads and writes.

Suggested safe alternative for MP004

-- Set a timeout so DDL fails fast instead of blocking the queue
SET lock_timeout = '5s';
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0)
RESET lock_timeout;

Generated by MigrationPilot. Pass database-url to add production context: table sizes, affected queries, replication state.

Agents

Coding agents write migrations now. The MCP server gives them a gate to call first: check_before_apply resolves your project config exactly like the CLI and answers pass or fail, naming the rules that block. Abridged below; the real response carries the messages and the safe alternative too.

check_before_apply

{
  "verdict": "fail",
  "failOn": "critical",
  "counts": { "critical": 2, "warning": 0, "blocking": 2 },
  "violations": [
    { "ruleId": "MP004", "blocking": true },
    { "ruleId": "MP030", "blocking": true }
  ]
}

112 rules. Every lock explained.

Every rule names the lock the statement takes, what that lock blocks, and the pattern to use instead. They come from the PostgreSQL manual and from twenty handbook entries built on public incident write-ups, not from a list of things that sounded risky.

34: critical
78: warning
97: run from the file alone
15: need --database-url to say anything

The 15 catalogue-aware rules read table sizes, write traffic, replication state and index definitions. Without a connection they stay silent rather than guess, and the CLI says so on every run.

Browse all 112 rules Read the handbook

The linter is free. The proof costs money.

Everything that finds a problem is free forever, with no account and no quota. The paid plan exists for the separate question an auditor asks: prove this was enforced.

Free

All 112 rules, every auto-fix, the CLI, the GitHub Action, the MCP server, the VS Code extension, and production context when you point it at a database. Unlimited runs. MIT licensed, so you can read every rule and fork it.

Quick start

Org

$499 / year

The $499/year Org plan turns the free linter into an enforceable control: one policy across repositories that developers cannot quietly disable, a JSONL audit trail of every check, and direct support from the maintainer.

Talk to us What the artifacts look like

Point it at your migrations.

npx migrationpilot analyze migration.sql

Or paste one in the playground Source on GitHub