Every schema migration is easy the first time, when the table is empty and the only user is your test suite. The hard part is migrating a table with a hundred million rows while production traffic keeps flowing — because PostgreSQL’s ALTER TABLE takes an ACCESS EXCLUSIVE lock on most operations, and a single long-running SELECT queued behind that lock can cascade into a full application outage. Zero-downtime migrations are not a tool you install; they are a set of habits about locks, rewrites, and multi-step deployments.
This post covers the operations that are actually safe to run online, the ones that silently rewrite your table, and the multi-phase patterns for everything in between. All lock levels and behaviors described here follow PostgreSQL’s current documentation.
Why ALTER TABLE takes the strongest lock
ALTER TABLE acquires an ACCESS EXCLUSIVE lock on the table unless a subcommand specifies otherwise. This lock conflicts with everything — SELECT, INSERT, UPDATE, DELETE. It must be strong because many ALTER operations change what concurrent queries are allowed to do mid-flight, and the system cannot reconcile, say, a column disappearing under an executing query.
The danger is not the lock itself but lock queueing. PostgreSQL grants locks fairly: a new ACCESS EXCLUSIVE request waits for all existing lock holders, but every lock request that arrives after it queues behind it. A migration that waits three minutes for a long reporting query to finish means three minutes during which every query against the table — even reads — is blocked. If your connection pool fills with blocked queries, the outage spreads to tables that were never part of the migration. This is why setting lock_timeout on migrations is non-negotiable:
SET lock_timeout = '5s';
SET statement_timeout = 0;
ALTER TABLE orders ADD COLUMN status_code smallint;If the lock is not acquired within five seconds, the migration fails fast instead of stacking a queue of blocked queries. A failed migration that retries later costs nothing; a five-minute lock convoy costs an incident.
Operations that are safe (and nearly safe) online
Several common operations are fast because they touch only the catalog:
- ADD COLUMN with a constant or no default — since PostgreSQL 11 this is a catalog-only change; existing rows read the default at query time. No rewrite, near-instant even on huge tables.
- DROP COLUMN — PostgreSQL does not physically remove the data; the column is just made invisible, and new writes store NULL for it. Fast, but the disk space is reclaimed only later (VACUUM/rewrite territory).
- ADD CONSTRAINT … NOT VALID — adds a CHECK, NOT NULL, or foreign-key constraint without scanning existing rows. The constraint is enforced for all new writes immediately, but the database does not assume existing rows satisfy it.
- VALIDATE CONSTRAINT — the follow-up scan for a NOT VALID constraint. It takes only a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes.
- CREATE INDEX CONCURRENTLY — builds the index in two passes without blocking writes (though it cannot run inside a transaction and takes longer, doing two table scans).
The NOT VALID + VALIDATE pair deserves emphasis because it turns the worst kind of migration — a full-table scan under exclusive lock — into two safe steps:
-- Step 1: instant; enforces the constraint on new writes only.
ALTER TABLE orders
ADD CONSTRAINT orders_total_positive
CHECK (total >= 0) NOT VALID;
-- Step 2: scans the table, but only holds SHARE UPDATE EXCLUSIVE,
-- so reads and writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;A bonus: if the table is known to contain violations, this split lets you fix data at leisure. The moment the constraint exists, no new bad rows can be written, and VALIDATE only succeeds once every historical row is clean.
Operations that rewrite the table
Some ALTER forms are not index-and-catalog tweaks — they reconstruct every row. Adding a column with a volatile default such as clock_timestamp(), adding a stored generated column or an identity column, and most ALTER COLUMN TYPE changes all force a full table and index rewrite. During the rewrite the table is locked against everything, and the operation needs up to double the disk space temporarily.
Type changes are not always catastrophic: if the old type is binary-coercible to the new one and the USING clause does not change contents, PostgreSQL skips the rewrite (varchar(50) to varchar(100), for example, though indexes may still be rebuilt). But int to text requires the rewrite, and on a large table that is a maintenance-window operation at best.
The standard workaround is the expand-and-contract pattern — add the new column, backfill in batches, cut over, then drop the old column:
-- Phase 1 (expand): instant, no rewrite.
ALTER TABLE events ADD COLUMN user_id_new bigint;
-- Phase 2: application writes to BOTH columns.
-- Backfill in bounded batches so each transaction stays small:
UPDATE events SET user_id_new = user_id
WHERE user_id_new IS NULL AND id BETWEEN 1 AND 50000;
-- Phase 3 (contract): once the app reads user_id_new everywhere,
-- drop the old column in a later release.
ALTER TABLE events DROP COLUMN user_id;The critical rule is that the application, not the database, owns the transition. Every migration that cannot be both instant and non-blocking needs a deploy window where old and new shapes coexist, with code reading and writing both. This is also why expand/contract pairs must be reversible: if phase 3 deploys and something breaks, you need a path back to the old column while data is still being mirrored.
SET NOT NULL and the backfill trap
Making a column NOT NULL looks trivial but performs a full-table scan under an exclusive lock to verify no NULLs exist. On a large table that scan blocks all traffic for its entire duration. The modern escape hatch (PostgreSQL 12+) is to express the guarantee as a CHECK constraint first:
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_not_null
CHECK (user_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_not_null;
-- Now the NOT NULL comes with proof: the planner recognizes the
-- CHECK as sufficient and skips the table scan.
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;Backfills deserve their own warning: a single UPDATE events SET user_id_new = user_id on ten million rows is one enormous transaction that holds locks, bloats the table, and can starve replication. Batch the update (as above), commit between batches, and sleep briefly to let VACUUM and replication catch up. A keyset-paginated batching loop in your migration runner handles this well.
Indexes and constraints without downtime
CREATE INDEX CONCURRENTLY covers most index work, but converting an index into a constraint — a new primary key, for instance — needs one more trick. ADD PRIMARY KEY using an existing index avoids the blocking rebuild:
CREATE UNIQUE INDEX CONCURRENTLY orders_id_temp_idx
ON orders (id);
ALTER TABLE orders
DROP CONSTRAINT orders_pkey,
ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_temp_idx;The ALTER statement itself is brief — it swaps the constraint metadata rather than rebuilding anything. Keep the two statements in one deployment script and run the ALTER immediately after the index build completes, while nothing has changed underneath.
Watching for the failure you cannot see
Two operational details round out the picture. First, check for waiting locks before and during a migration: pg_stat_activity shows blocked queries, and pg_blocking_pids() identifies the culprit. If a migration holds the lock longer than expected, the fix is to cancel the migration — not to wait it out while the queue grows. Second, remember that prepared statements and connection poolers interact with migrations in surprising ways: a PgBouncer in transaction mode will happily route a query to the same connection where the migration just changed the plan. Most teams already run a recent PgBouncer — worth noting that the 1.26 release addressed pre-auth issues relevant to any pooler exposed to untrusted networks — but the deeper point is that schema changes invalidate cached plans, so expect a brief plan-recompilation thundering herd after a large migration.
Finally, run migrations under the same change-management discipline as code: every migration reviewed, every lock timeout set, every expand phase deployed separately from the contract phase. The database does not forgive the sloppy patterns that a staging environment hides.
Wrapping up
The zero-downtime playbook compresses to a few rules. Know which operations are catalog-only and which rewrite the table. Set lock_timeout on everything. Split scans from locks using NOT VALID and VALIDATE CONSTRAINT. Build indexes concurrently and adopt them as constraints via USING INDEX. And when a change is fundamentally a rewrite, push the transition into the application with expand-and-contract, batched backfills, and reversible phases. None of these steps is difficult individually — the discipline is refusing to ship the single giant ALTER TABLE that “worked fine in staging.”