Zero-Downtime Schema Migrations in PostgreSQL: Expand-Contract, Lock Timeouts, and Safe ALTER TABLE

Posted on

PostgreSQL takes an ACCESS EXCLUSIVE lock for most ALTER TABLE operations. That lock conflicts with everything — reads, writes, even plain SELECTs. The lock itself usually isn’t the problem; the queue behind it is. A schema change that takes 50 milliseconds can take a table down for 30 seconds or more if it gets stuck waiting behind a long-running transaction, because every new query on that table queues behind the pending DDL.

This post walks through the expand-and-contract pattern for zero-downtime schema migrations on PostgreSQL: how the lock queue causes outages, the concrete ALTER TABLE statements that are and aren’t safe to run directly, and how to run large migrations — column type changes, NOT NULL constraints, renames — without blocking production traffic.

The lock queue: how a fast migration causes a slow outage

PostgreSQL queues lock requests in order. Session A holds a long-running query with an ACCESS SHARE lock. Session B issues an ALTER TABLE and requests ACCESS EXCLUSIVE — blocked, waiting on A. Session C issues a simple SELECT. That SELECT is fully compatible with A’s lock, but PostgreSQL processes lock requests in order: C queues behind B. Now every read and write on the table is stuck behind a DDL statement that itself is stuck behind a query that may run for another hour.

The classic production failure: connection pools fill with blocked queries, application threads exhaust their pool connections, and the failure cascades well beyond the one table you were altering. The DDL statement itself was cheap — the lock wait was the outage.

Rule 1: always set lock_timeout

The single highest-value habit for production DDL: make the migration fail fast instead of queueing behind other sessions. Set lock_timeout so PostgreSQL cancels the ALTER TABLE if it can’t get its lock quickly:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN notes text;

If the lock can’t be acquired in two seconds, PostgreSQL raises canceling statement due to lock timeout and the queue clears instantly — your application keeps running, and you simply retry the migration later. In a migration runner, apply this per migration session and retry with backoff rather than letting DDL wait indefinitely.

Two related guards: statement_timeout for backfill batches, and idle_in_transaction_session_timeout to kill sessions stuck idle inside a transaction — those are the most common holders of the locks that block migrations.

What’s actually safe to run: DDL by lock behavior

Not all DDL is equally dangerous. The practical breakdown of common operations:

  • Metadata-only, instant: adding a nullable column without a default (ADD COLUMN notes text), renaming a column or table, adding a constraint as NOT VALID. These update the catalog and return immediately.
  • Fast on modern PostgreSQL, with a catch: adding a column with a constant default is metadata-only since PG11 (the default is stored in the catalog, evaluated on read), but a volatile default like clock_timestamp() rewrites every existing row.
  • Full table rewrite, ACCESS EXCLUSIVE held throughout: changing a column type (except widening varchar), SET NOT NULL (it scans the table to verify), and adding UNIQUE or PRIMARY KEY constraints from scratch.
  • Split operations: CREATE INDEX CONCURRENTLY avoids blocking writes but takes brief SHARE UPDATE EXCLUSIVE locks at start and end, and can’t run inside a transaction. ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT turns a heavyweight operation into a quick exclusive lock plus a concurrent validation pass.

Expand and contract: the migration playbook

Expand-and-contract structures every risky migration as three phases, keeping the schema backward-compatible with both the old and new application versions at every step:

Phase 1 — Expand

Add new structures without removing or changing anything old. Old code keeps working untouched; new code doesn’t use the new structures yet:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN total_cents integer;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_total_cents
    ON orders (total_cents);

Phase 2 — Migrate data and dual-write

Deploy code that writes to both the old and new structures in the same transaction, then backfill historical rows in small batches. Backfills must be batched and throttled — a single unbounded UPDATE on a large table holds row locks, bloats the table, floods WAL, and can stall replication. A batched loop looks like this:

-- Run repeatedly until 0 rows updated; pause between batches
UPDATE orders
SET    total_cents = (total * 100)::integer
WHERE  total_cents IS NULL
AND    id IN (
    SELECT id FROM orders
    WHERE total_cents IS NULL
    ORDER BY id
    LIMIT 1000
);

Track progress by the highest ID processed, pause when replication lag exceeds a threshold, and keep the inter-batch delay long enough that autovacuum and replicas keep up. During this phase, reads still come from the old column; you cut reads over once the backfill completes and the numbers reconcile between old and new.

Phase 3 — Contract

Only after every reader and writer has moved off the old structure — verified with query logging or column-usage tracking, not hope — remove it:

SET lock_timeout = '2s';
ALTER TABLE orders DROP COLUMN total;

Contract has one sharp edge: dropping a column that a still-running old deployment reads. DROP COLUMN takes ACCESS EXCLUSIVE and is not recoverable in the moment. Confirm no deployed code path references the old column before running it.

Worked example: changing a column type without downtime

ALTER TABLE orders ALTER COLUMN total TYPE numeric(10,2) takes an ACCESS EXCLUSIVE lock and rewrites the whole table. On a large, busy table, the lock queue turns that into an outage. The expand-and-contract version:

-- 1. Expand: add the new column and an index on it
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN total_new numeric(10,2);
CREATE INDEX CONCURRENTLY idx_orders_total_new ON orders (total_new);

-- 2. Deploy code that writes to both columns in one transaction.

-- 3. Backfill in batches (pattern above).

-- 4. Cut reads over to total_new in application code.

-- 5. Contract: swap names once nothing references the old column
ALTER TABLE orders RENAME COLUMN total TO total_old;
ALTER TABLE orders RENAME COLUMN total_new TO total;
-- After a final observation window:
ALTER TABLE orders DROP COLUMN total_old;

Renaming both columns in one transaction is metadata-only and instant — but it breaks every client still referencing the old name, so it belongs in the contract phase, only after all code has deployed.

NOT NULL without the table scan

ALTER COLUMN ... SET NOT NULL performs a full table scan to verify no NULLs exist — while holding the exclusive lock. The safe sequence: add a CHECK constraint as NOT VALID (instant; only future writes are checked), validate it concurrently, then attach NOT NULL, which can reuse the validated constraint and skip the scan:

SET lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_total_cents_not_null
  CHECK (total_cents IS NOT NULL) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_total_cents_not_null;

ALTER TABLE orders ALTER COLUMN total_cents SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_total_cents_not_null;

Since PostgreSQL 12, SET NOT NULL can use an existing validated CHECK constraint on the column as proof instead of rescanning the table. The VALIDATE step takes only a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue throughout.

Common pitfalls

  • Long-running transactions are lock magnets. An idle-in-transaction session that touched your table hours ago still holds its lock. Check pg_stat_activity and pg_locks before running DDL.
  • CREATE INDEX CONCURRENTLY can’t run in a transaction. Most migration frameworks wrap migrations in a transaction by default — disable that for these statements. A failed CIC also leaves an INVALID index behind; drop it and retry.
  • Unbatched backfills. A single UPDATE over millions of rows bloats the table and floods replication. Batch, throttle, and pause on replication lag.
  • Foreign keys on large tables. ADD CONSTRAINT ... FOREIGN KEY takes an exclusive lock while it scans the referenced table. Use the same NOT VALID / VALIDATE split.
  • Dual-write drift. If writes to old and new structures aren’t in the same transaction, they can diverge. Keep them atomic and reconcile counts after backfill.

Wrapping up

The mechanics are simple: know which statements take ACCESS EXCLUSIVE, set lock_timeout so DDL fails fast instead of queueing, and structure changes as expand → migrate → contract so old and new schema versions coexist until every deployment has moved. For teams running frequent migrations, pgroll automates expand-and-contract steps as reversible, low-lock operations. For the lock-mode details behind everything above, the ALTER TABLE documentation and the explicit locking chapter are the authoritative references.

Leave a Reply

Your email address will not be published. Required fields are marked *