The Schema Change Blueprint
A schema migration looks like a SQL-writing problem, but the SQL is rarely where the risk actually lives.
//
Search across all documentation pages
A schema migration looks like a SQL-writing problem, but the SQL is rarely where the risk actually lives.
The risk lives in locking: which lock a statement takes, how long it waits to acquire that lock, and what else on a busy production table gets blocked while it waits.
ALTER TABLE that runs instantly on an empty table can queue behind every other query on a busy one and then block all of them in turn.NOT VALID constraint, CONCURRENTLY, expand/contract.Most PostgreSQL DDL statements are transactional, meaning a CREATE TABLE or ALTER TABLE inside BEGIN ... COMMIT rolls back cleanly if anything later in that transaction fails.
That is a genuine advantage over databases where DDL auto-commits statement by statement, since a failed PostgreSQL migration can leave the schema exactly as it was before the attempt.
A small set of operations are the deliberate exception: CREATE INDEX CONCURRENTLY, VACUUM, and a few others cannot run inside a transaction block at all, because their whole point is to avoid holding the kind of lock a transaction-wrapped DDL statement would need.
The other foundational idea is lock strength: every DDL statement acquires a lock on the table it touches, and different statements need different strength locks, from ones that merely block other DDL to ones that block every read and write on the table.
A useful analogy is a single-lane bridge: a light lock is like a temporary lane-narrowing that traffic can still creep through, while a heavy lock is a full bridge closure, and the danger is not the construction work itself, it is how long the closure lasts while traffic backs up behind it.
ALTER TABLE ... ADD COLUMN with no default, for instance, takes a strong lock but holds it only briefly, because it just updates catalog metadata; the same statement with a volatile default historically required rewriting every row while holding that lock.
The practical danger in a migration is rarely the lock's strength alone - it is strength combined with how long the statement waits to acquire it.
A strong lock request queues behind any transaction already touching that table, and every query that arrives after the migration's lock request also queues behind it, even simple reads that would otherwise run instantly.
That queuing effect is what turns a two-second ALTER TABLE into a production incident: the statement itself is fast, but if it has to wait ten minutes for a long-running transaction to finish, every other query on that table waits with it.
-- Cheap: catalog-only change, brief strong lock
ALTER TABLE orders ADD COLUMN priority int;
-- Safer for a busy table: build the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_priority ON orders (priority);
-- Add a constraint without an initial full-table validation lock
ALTER TABLE orders ADD CONSTRAINT priority_positive
CHECK (priority > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT priority_positive;NOT VALID is the general pattern behind most zero-downtime constraint work: it adds the constraint immediately under a brief lock, then a separate VALIDATE CONSTRAINT step checks existing rows under a much lighter lock that does not block concurrent writes.
Setting lock_timeout on migration sessions turns an indefinite wait into a fast, visible failure instead of a silent traffic jam, which is exactly the difference between a migration that fails loudly in a deploy log and one that quietly stalls production.
Expand/contract is the pattern that generalizes all of this into a repeatable playbook for changes that cannot be made safely in one step: add the new shape additively, migrate reads and writes to it gradually, then remove the old shape once nothing depends on it anymore.
Renaming a column is the clearest example, because a direct RENAME COLUMN breaks every deployed application instance still expecting the old name the moment it commits, while an expand/contract version adds the new column, backfills it, cuts application traffic over, and only then drops the old column in a later, separate deploy.
At scale, "backfill" itself becomes a migration-adjacent problem, because updating every row of a large table in one statement takes the same kind of long-held lock a schema change does, which is why large backfills are typically chunked into small batches with pauses between them.
Migration tooling (Flyway, Liquibase, or a hand-rolled schema-history table) solves a genuinely different problem than lock safety: it solves ordering and idempotency, guaranteeing every environment applies the same statements in the same order exactly once, but it has no opinion about whether any individual statement is safe to run against a live table.
That distinction matters because a well-run migration tool can still faithfully apply an unsafe statement; sequencing correctness and lock safety are two separate disciplines that both have to be true at once.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Direct DDL in one transaction | Simplest to write and review | Long-held locks on busy tables; no partial rollout | Small tables, low-traffic tables, maintenance windows |
NOT VALID + separate VALIDATE | Adds constraints without a full-table blocking lock | Two-step process; constraint is unenforced between steps | Adding constraints to large, actively-written tables |
| Full expand/contract across releases | Genuinely zero-downtime; each step independently safe | More coordination, more deploys, longer total timeline | Renames, type changes, and any breaking change to a live table |
CREATE INDEX CONCURRENTLY and VACUUM, cannot run inside a transaction block at all and must be issued on their own.ADD CONSTRAINT ... NOT VALID followed by a separate VALIDATE CONSTRAINT splits that into a brief lock plus a non-blocking validation pass.Most of it is, meaning a failed transaction rolls back schema changes cleanly, but a small set of operations - CREATE INDEX CONCURRENTLY, VACUUM among them - cannot run inside a transaction block at all.
Because the statement's own execution time is rarely the problem; the time it spends queued waiting to acquire its lock, and every other query that queues behind it, is what turns milliseconds into minutes.
It adds the constraint immediately under a brief lock without scanning existing rows, deferring the full-table validation to a separate VALIDATE CONSTRAINT step that takes a much lighter lock.
It is splitting one risky schema change into an additive step (expand), a migration/cutover period, and a cleanup step (contract), so no single deploy both adds and removes something applications depend on.
Because a direct rename breaks any already-deployed application instance still using the old name the instant it commits, with no window for a gradual rollout.
No - it guarantees your migrations run in the correct order exactly once across environments, but it has no awareness of whether an individual statement's lock behavior is safe for a live table.
Without it, a statement waiting on a lock can wait indefinitely and silently queue every other query behind it; lock_timeout turns that into a fast, visible failure instead.
It is the right choice whenever the table receives live writes, since it avoids blocking them, but it is slower and cannot run inside a transaction block, so it is unnecessary overhead on tables that are not under active write load.
A backfill that updates every row in one statement takes a long-held lock much like a schema change does, which is why large backfills are typically chunked into small, pausable batches instead of one giant UPDATE.
Lock strength determines what other activity the lock blocks once acquired; wait time determines how long the statement queues before acquiring it - a strong lock held briefly can be safer than a weak lock stuck waiting behind a long transaction.
For small or low-traffic tables, or during a genuine maintenance window, where the simplicity of one straightforward change outweighs the coordination cost of a full expand/contract rollout.
Because a migration that is safe going forward is not automatically safe to reverse - forward-only teams treat a failed deploy as a new forward migration rather than assuming a symmetric rollback exists.
CONCURRENTLY, NOT VALID, and phased cutovers in detailpg_repackStack versions: This page is conceptual and describes DDL locking and transaction behavior consistent across current PostgreSQL major versions, including PostgreSQL 18.4 (stable 18, maintenance line 17).
Reviewed by Chris St. John·Last updated Jul 15, 2026