Postgres Migration Lock check — check migration locks free

22 findings in a 20-statement migration. That is what a Rails engineer on a live orders table gets back from the file an assistant wrote in four seconds.

The migration is unremarkable. Add a tenant_id column, tighten amount_cents to numeric(12,2), add a foreign key to tenants, add a unique constraint on external_ref, build an index on tenant_id, rename legacy_total, drop legacy_notes, backfill the new column, clear an events table. Twenty statements between BEGIN and COMMIT. Nothing in it is wrong SQL. Every statement runs. That is the problem: the lock behaviour is not in the text.

Sixteen of the 22 findings are at error severity, meaning writes stop or the statement aborts outright. ALTER COLUMN amount_cents TYPE numeric(12,2) rewrites the whole table and every index on it while holding ACCESS EXCLUSIVE. CREATE INDEX without CONCURRENTLY holds SHARE, so reads continue and every INSERT, UPDATE and DELETE waits for the build. ADD CONSTRAINT ... FOREIGN KEY without NOT VALID takes ACCESS EXCLUSIVE on the child table and SHARE ROW EXCLUSIVE on the parent while it scans existing rows. ADD COLUMN tenant_id bigint NOT NULL with no default does not block at all - it fails, SQLSTATE 23502, because the table already has rows. And CREATE INDEX CONCURRENTLY, the correct form, aborts with 25001 when it is sitting inside the BEGIN/COMMIT the generator wrapped everything in.

The finding that matters most is the one that is not a statement. There is no lock_timeout anywhere in the file. A DDL statement that cannot get its lock does not fail; it waits. And while it waits at the head of the lock queue, every query that arrives after it waits too, including the plain SELECTs that were perfectly compatible with whatever is already running. One analytics query holding a read lock plus one ALTER TABLE behind it is enough to make a healthy table unreadable. SET lock_timeout = '3s' turns that outage into a retry.

Version matters and the text does not tell you the version. ADD COLUMN x int DEFAULT 0 rewrote the table on PostgreSQL 10 and has been instant since 11, so flagging it now is noise - the rule set leaves it alone. SET NOT NULL skips its table scan on 12 and later, but only when a validated CHECK (x IS NOT NULL) already exists, which generated migrations almost never add first. REINDEX CONCURRENTLY exists from 12. Advice written before those releases is still what most of this code is imitating. PostgreSQL 14 receives its final minor release in November 2026, so a lot of teams are writing their 14-to-17 upgrade migrations right now.

The free tools each see one half of the problem. strong_migrations reads the Rails DSL and nothing else. squawk parses raw SQL and nothing else. Neither reads a Django operations list, where AddIndex, AlterField and a RunPython backfill inside an atomic migration carry exactly the same locks. Both run in CI, after the migration is already written, already committed, already in a pull request with three approvals.

Postgres Migration Lock Lint reads the file in the editor. 36 rules, one message each, naming the lock mode, the SQLSTATE where there is one, and the rewrite. Raw .sql, Rails ActiveRecord::Migration, Django migrations.Migration. The same schema change, split so nothing blocks - constant default, CHECK NOT VALID then VALIDATE, unique index built CONCURRENTLY then attached USING INDEX, a batched backfill with a WHERE - returns zero findings.

Free tier: the file you have open, all 36 rules. The same rules run in the browser on pasted text, with nothing uploaded, at getreadystack.com/tools/pg-migration-lock-lint.