Is Your Postgres Migration Safe? Catch the Table Lock First

TL;DR Is your Postgres migration safe? Not if it takes an ACCESS EXCLUSIVE lock on a table your app is actively reading or writing. A migration’s safety is decided by which lock each statement takes, not by how short or simple its SQL looks — CREATE INDEX and CREATE INDEX CONCURRENTLY build the identical index, but one blocks every write on the table for the whole build and the other doesn’t. Run CONCURRENTLY for indexes, split constraints into NOT VALID plus VALIDATE CONSTRAINT, and confirm your ADD COLUMN has a constant default before you assume it’s instant.

Most migration incidents don’t come from bad SQL — the SQL is often perfectly correct. They come from correct SQL that happens to need a lock your production traffic can’t survive holding for as long as the statement takes to run. A CREATE INDEX on a 40-million-row table can take minutes; if that statement holds a lock that blocks writes, every INSERT and UPDATE against that table queues behind it for the full duration, and a connection pool that can’t drain fast enough starts timing out requests that have nothing to do with the migration.

Is Your Postgres Migration Safe? Check the Lock Type First

The question to ask before running any ALTER TABLE, CREATE INDEX, or ADD CONSTRAINT isn’t “how long will this take” — it’s “what lock does this statement take, and what does that lock block.” Postgres documents eight lock modes for table-level operations, but only three come up in practice for migrations:

• ACCESS EXCLUSIVE — blocks every other lock, including plain SELECT. Nothing touches the table while this is held.
• SHARE — blocks writes (INSERT/UPDATE/DELETE) but not reads. This is what a plain CREATE INDEX takes.
• SHARE UPDATE EXCLUSIVE — blocks other schema changes and VACUUM, but not ordinary reads or writes. This is what CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT take.

A migration that only ever takes the third kind, for as long as it needs, is safe to run against a live table at any hour. One that takes the first kind is safe only if you can afford to freeze that table — briefly on a small table, or not at all on one your app hits every second.

How Postgres Locks Tables During ALTER TABLE

Most ALTER TABLE subcommands default to ACCESS EXCLUSIVE because they change something every concurrent reader needs to agree on — the column list, a constraint, a type. Postgres 11 carved out one common exception: adding a column with a constant, non-volatile DEFAULT no longer rewrites the table. Before Postgres 11, ALTER TABLE orders ADD COLUMN status text DEFAULT 'active' copied every row to fill in the new column, holding ACCESS EXCLUSIVE for the entire copy. Since Postgres 11, the same statement is metadata-only — Postgres stores the default once and computes it lazily on read — so the lock is held for milliseconds no matter how many rows the table has.

That exception has edges. DEFAULT now() or DEFAULT random() is volatile, so Postgres still rewrites every row to fix each one’s value at the moment of the ALTER. Adding a column as NOT NULL without a default has to verify every existing row satisfies the constraint, which is a full scan even though it’s read-only. And ALTER COLUMN ... TYPE almost always rewrites — a varchar(50) to varchar(100) is metadata-only, but integer to bigint copies the table, because the on-disk representation changes.

What Makes CREATE INDEX CONCURRENTLY Different

CREATE INDEX and CREATE INDEX CONCURRENTLY produce the same index — same columns, same type, same query plans afterward. The difference is entirely in what happens while it builds. Plain CREATE INDEX takes a SHARE lock and finishes in one pass; every write against the table queues until the build completes, which on a large table can be minutes of frozen INSERT/UPDATE/DELETE. CREATE INDEX CONCURRENTLY takes the much weaker SHARE UPDATE EXCLUSIVE lock instead, which lets writes continue — at the cost of scanning the table twice instead of once, so it takes noticeably longer in wall-clock time to buy that concurrency.

The trade-off comes with three constraints that catch people who reach for CONCURRENTLY without reading the fine print: it cannot run inside an explicit transaction block, so it can’t be wrapped with your other migration statements in one atomic unit; if a concurrent write conflicts with the build, Postgres aborts the build and leaves the index behind marked INVALID rather than serve from a half-built one; and it takes roughly twice as long as the blocking version on the same table, because of the second scan. None of those make it unsafe — they make it a different tool with a different failure mode, one that fails loud (an INVALID index sitting there, easy to spot and drop) instead of failing by freezing production traffic.

How to Check Migration Safety in 5 Steps

Work through this before any migration touches a table your application reads or writes right now:

1. Read every statement and name its lock. For each ALTER TABLE, CREATE INDEX, or ADD CONSTRAINT, look up (or recall from the table above) which lock mode it takes — don’t guess from how the SQL reads.

2. Flag anything that takes ACCESS EXCLUSIVE for longer than a catalog update. ADD COLUMN with a constant default is fine; ADD COLUMN NOT NULL without one, or a type change, is not — on any table with rows you can’t afford to freeze.

3. Swap CREATE INDEX for CREATE INDEX CONCURRENTLY on any table your app writes to, and run it outside a transaction block since it can’t join one anyway.

4. Split every new constraint into NOT VALID then VALIDATE CONSTRAINT. The first adds the constraint definition under a brief lock without checking existing rows; the second scans and validates under a lock that doesn’t block reads or writes. Two statements, same end state, none of the downtime.

5. Test against a copy with realistic row counts, not an empty schema. A metadata-only ADD COLUMN looks identical to a full rewrite on a table with ten rows — the difference only shows up at the row count you actually run in production.

Tools like safe-not-safe.dev automate step 1: it parses your migration SQL with a WASM build of libpg_query entirely in the browser — nothing is sent to a server — and flags statements against the table-size bucket you select (under 50k rows, 50k to 5M, or over 5M), which matters because a full-table lock on 500 rows is a non-event and the same lock on 50 million rows is an outage.

What Breaks If You Skip the NOT VALID Two-Step

Add a foreign key or check constraint the plain way — ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) — and Postgres has to confirm every existing row in orders satisfies it before the constraint can be trusted. That validation is a full table scan, and Postgres runs it under the same lock that blocks concurrent writes, so a orders table with a few million rows can freeze checkout for as long as the scan takes.

Split it in two and the outcome is identical but the downtime isn’t:

-- Step 1: add the constraint, skip validating existing rows (fast, brief lock)
ALTER TABLE orders
  ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id)
  REFERENCES customers(id) NOT VALID;

-- Step 2: scan and validate under a lock that doesn't block reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

Between the two statements, the constraint is already enforced for every new row — NOT VALID only means Postgres hasn’t yet confirmed the existing rows, not that the constraint is inactive. VALIDATE CONSTRAINT can run minutes or hours later, whenever there’s a quiet window, without anything downstream noticing.

Where Automated Checkers Like Safe-Not-Safe Fit

A parser can catch the pattern — “this is a bare CREATE INDEX, not the CONCURRENTLY form” — reliably and for free, because that’s a syntactic fact about the statement. It cannot tell you your orders table has 40 million rows and 200 writes per second, because that’s a fact about your production database, not your SQL file. Treat a tool like this as the first of the five steps above, not a replacement for the other four — it narrows what you have to think about, it doesn’t remove the need to think about it.

This is the same shape of problem as reviewing any automated change before it reaches production: grouping Dependabot updates works because it sorts which PRs need a careful look from which don’t, and running migrations through a CI/CD pipeline on Cloudflare Workflows only helps if the pipeline itself refuses to apply an unreviewed ACCESS EXCLUSIVE statement at 2pm on a Tuesday. The lock-safety check belongs in that same gate, not as a separate manual step someone forgets under deadline pressure.

The takeaway

A Postgres migration’s safety is a property of the lock it takes, not of how it reads or how fast it usually finishes. ADD COLUMN with a constant default, CREATE INDEX CONCURRENTLY, and the NOT VALID / VALIDATE CONSTRAINT pair all exist because Postgres gives you a slower, non-blocking path to the same schema for exactly the operations that would otherwise take ACCESS EXCLUSIVE. Check which path your migration is on before you run it against a table anything else is touching — the five-step list above takes less time than the incident review after you find out the hard way.

If you’re rearchitecting where data lives rather than its shape, the lock questions above still apply, but so does how you rebalance shards across servers once the schema itself isn’t the bottleneck. If the change touches your data layer’s transport rather than its schema, the same “verify before you flip it” discipline is what the post-quantum TLS migration checklist applies to a completely different layer of the stack. Either way, a Node.js backend serving that data only stays up if the layer underneath it does.

FAQ

Is your Postgres migration safe if it only runs for a few milliseconds? Not necessarily — duration measures how long a lock is held, not which kind. A 5ms statement can still take ACCESS EXCLUSIVE and block every read and write on a busy table for that window, which is enough to queue connections and trip an upstream timeout.

Does adding a column to a Postgres table always lock it? It always takes a brief ACCESS EXCLUSIVE lock for the catalog update, but since Postgres 11 a constant default (or no default) makes that a metadata-only change regardless of table size. A volatile default or a NOT NULL backfill still rewrites every row under that same lock.

Why does CREATE INDEX CONCURRENTLY sometimes fail and leave an invalid index? It scans the table twice without a write-blocking lock, so a conflicting concurrent write can abort the build; Postgres marks the result INVALID rather than serve from an incomplete index. Drop it and rerun.

Do I need NOT VALID and VALIDATE CONSTRAINT for every foreign key? Only on a table already live with rows you can’t afford to lock. On a new or nearly empty table, a plain ADD CONSTRAINT is simpler and the extra complexity buys nothing.

Can a tool catch an unsafe Postgres migration automatically? Yes — parser-based checkers like safe-not-safe.dev flag risky statement patterns client-side, weighted by the table-size bucket you specify. It’s a pre-flight check on the SQL, not a replacement for testing against a realistic copy of the data.

Sources

• Safe-Not-Safe — browser-based Postgres migration analyzer using a WASM build of libpg_query; row-count buckets and flagged-statement patterns referenced above.
• PostgreSQL documentation: Explicit Locking — table-level lock modes (ACCESS EXCLUSIVE, SHARE, SHARE UPDATE EXCLUSIVE) and which statements take each.
• PostgreSQL documentation: ALTER TABLE — ADD COLUMN fast-path behavior for constant defaults, and the NOT VALID / VALIDATE CONSTRAINT options for ADD CONSTRAINT.