PostgreSQL Zero Downtime Migration: Expand and Contract in Practice

Why a fast ALTER TABLE can still cause an outage: the lock queue

Most ALTER TABLE forms do almost no work once they start. Adding a nullable column without a default, for example, only updates the catalog and finishes in milliseconds. The danger is in acquiring the lock. These forms need an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode, including the ACCESS SHARE lock that a plain SELECT takes. A zero-downtime migration is therefore mostly about getting that lock safely, not about how long the DDL runs.

PostgreSQL grants lock requests on a table roughly in arrival order. Suppose a reporting query or an idle-in-transaction session holds ACCESS SHARE on orders for ten minutes. Your ALTER TABLE cannot get ACCESS EXCLUSIVE, so it waits. Every query that arrives after it, even a read, conflicts with the queued exclusive request and waits behind it rather than skipping ahead. The table becomes unavailable to the application while the DDL itself has not run at all. Connection pools then fill up with stuck sessions, and the outage spreads beyond that one table.

To see this as it happens, join pg_locks to pg_stat_activity on pid and filter for rows where granted is false. That gives you the waiting sessions, their queries and how long they have been waiting. Calling pg_blocking_pids(pid) on a waiting session returns the PIDs blocking it. Following that chain usually leads to one long-running or idle transaction at the head of the queue, and that transaction is what you need to deal with.

Copy-- 1. Who is waiting for a lock, on what, and who is blocking them.
--    Rows with granted = false are requests stuck in the queue.
SELECT w.pid                     AS waiting_pid,
       w.mode                    AS requested_mode,
       w.relation::regclass      AS relation,
       now() - a.query_start     AS query_running_for,
       a.query                   AS waiting_query,
       pg_blocking_pids(w.pid)   AS blocked_by
FROM pg_locks w
JOIN pg_stat_activity a ON a.pid = w.pid
WHERE NOT w.granted
ORDER BY a.query_start;

-- 2. Inspect the blockers themselves. A long-running query or an
--    'idle in transaction' session with an old xact_start is usually
--    the head of the queue holding up the ALTER TABLE and everything
--    queued behind it.
SELECT b.pid,
       b.state,
       now() - b.xact_start      AS transaction_age,
       b.usename,
       b.application_name,
       b.query                   AS last_query
FROM pg_stat_activity b
WHERE b.pid IN (
        SELECT unnest(pg_blocking_pids(l.pid))
        FROM pg_locks l
        WHERE NOT l.granted
      )
ORDER BY b.xact_start;

Joins pg_locks to pg_stat_activity to list lock requests that have not been granted, uses pg_blocking_pids() to show which sessions block them, and then inspects those blockers so you can find the transaction at the head of the queue.

Running DDL safely with lock_timeout, statement_timeout and retries

The migration session can't remove that transaction, but it can refuse to queue behind it. Set lock_timeout at the start of the session, or with SET LOCAL inside the migration's transaction, to something short like '2s'. If the ALTER TABLE can't acquire its lock within that window, it errors out. It leaves the queue, and the reads and writes waiting behind it proceed. A two-second stall becomes the worst case instead of an open-ended one.

statement_timeout covers a different failure. The lock was granted, but the statement runs longer than expected, for example because a type change triggers a full table rewrite. Keep it tight for metadata-only DDL. Long backfills should run in separate sessions with their own limits.

Retrying with backoff

A lock timeout usually means you hit a bad moment, not that the migration is broken. Wrap the DDL in a retry loop:

• Catch only the lock-timeout error, which is SQLSTATE 55P03, lock_not_available.
• Wait with exponential backoff plus jitter, so you don't collide with the same periodic job on every attempt.
• Give up after a fixed number of attempts.

Make each attempt idempotent, or run it in its own transaction, so a partial failure leaves nothing behind.

Pre-flight check

Before the first attempt, query pg_stat_activity for two kinds of sessions:

• Sessions whose state is idle in transaction.
• Sessions whose xact_start is older than a threshold.

If either touches the target table, abort or wait instead of trying anyway.

The trade-off

On hot tables, or in systems where long transactions are routine, the retry budget can run out on every attempt. When that happens, the fix is either a low-traffic window or finding and shortening the long transactions.

Copyimport random, time
import psycopg2
from psycopg2 import errors

DSN = "dbname=app user=migrator host=db.internal"  # placeholder
DDL = "ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text"  # idempotent, metadata-only

# Pre-flight: other sessions holding a lock on the target table that are
# idle in transaction or have a transaction open longer than 30s.
PREFLIGHT = """
SELECT a.pid, a.state, now() - a.xact_start AS xact_age
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE l.relation = 'orders'::regclass
  AND a.pid <> pg_backend_pid()
  AND (a.state = 'idle in transaction'
       OR a.xact_start < now() - interval '30 seconds')
"""

def migrate(max_attempts=6, base_delay=0.5):
    conn = psycopg2.connect(DSN)
    with conn, conn.cursor() as cur:
        cur.execute(PREFLIGHT)
        if blockers := cur.fetchall():
            raise SystemExit(f"abort: long/idle transactions on orders: {blockers}")
    for attempt in range(1, max_attempts + 1):
        try:
            with conn, conn.cursor() as cur:  # one transaction per attempt; rolled back on error
                cur.execute("SET LOCAL lock_timeout = '2s'")       # leave the lock queue fast
                cur.execute("SET LOCAL statement_timeout = '5s'")  # catch unexpected rewrites
                cur.execute(DDL)
            return
        except errors.LockNotAvailable:  # SQLSTATE 55P03 only; other errors propagate
            if attempt == max_attempts:
                raise
            delay = base_delay * 2 ** (attempt - 1) * random.uniform(0.5, 1.5)  # backoff + jitter
            print(f"lock timeout on attempt {attempt}, retrying in {delay:.1f}s")
            time.sleep(delay)

migrate()

A psycopg2 migration runner that checks pg_stat_activity for long-running or idle-in-transaction sessions holding locks on the target table, then retries the DDL with lock_timeout and statement_timeout set via SET LOCAL, using jittered exponential backoff on lock_not_available (55P03) only.

The expand and contract pattern on a real column change

Take a concrete case: orders.total is an integer that needs to become bigint. An in-place ALTER COLUMN ... TYPE rewrites the table under an exclusive lock. The safe path is a sequence of small deploys, and each one must satisfy one rule: the current and previous application versions must both work against the schema at every step. During a rolling deploy, both versions serve traffic at the same time, and a rollback brings the old one back.

1. Expand: add total_v2 bigint, either nullable or with a constant default. Old code ignores the new column.
2. Dual-write: every insert and update writes both columns.
3. Backfill: copy existing rows in small batches so no single transaction holds locks for long.
4. Switch reads: read from total_v2, but keep writing both columns so a rollback still finds correct data in total.
5. Stop writing total: this is safe only once no deployed version reads it.
6. Contract: drop total in a later deploy.

A rename follows the same steps with an unchanged type.

The overhead is real: one change becomes four to six deploys. It is worth paying for teams whose systems cannot tolerate even brief downtime, such as payment flows, public APIs and multi-region services. For these systems, a short lock during peak traffic counts as an outage.

Copy-- Step 1, expand: a nullable column is a catalog-only change, so there is no table rewrite.
-- lock_timeout makes the DDL give up instead of queueing behind long transactions.
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN total_v2 bigint;

-- Step 2, dual-write with a trigger: covers every writer, including scripts and other services.
-- It copies total into total_v2 on every insert and on any update of total.
CREATE FUNCTION orders_sync_total_v2() RETURNS trigger AS $$
BEGIN
  NEW.total_v2 := NEW.total;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_total_v2
  BEFORE INSERT OR UPDATE OF total ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_total_v2();

-- Step 3, backfill in small batches so each transaction holds row locks only briefly.
-- Run this repeatedly from a script until it reports UPDATE 0.
UPDATE orders
SET total_v2 = total
WHERE id IN (
  SELECT id FROM orders
  WHERE total_v2 IS NULL AND total IS NOT NULL
  LIMIT 5000
);

-- Steps 4 and 5 are application deploys: read total_v2, then stop writing total.
-- Drop the trigger at step 5, before the app stops writing total.
-- Otherwise the trigger's copy (NEW.total_v2 := NEW.total) can overwrite total_v2.
-- Step 6, contract: run this in a later deploy, after no running version reads total.
SET lock_timeout = '3s';
DROP TRIGGER orders_sync_total_v2 ON orders;
DROP FUNCTION orders_sync_total_v2();
ALTER TABLE orders DROP COLUMN total;

A PostgreSQL sequence for widening orders.total to bigint with expand and contract, using a nullable new column, a trigger-based dual write, a batched backfill, and a separate contract step that drops the old column.

Online schema change building blocks: columns, backfills, constraints and indexes

Each building block below replaces one long, heavily locked operation with several short ones.

Columns. Adding a nullable column with no default only changes the catalog, so the brief ACCESS EXCLUSIVE lock is released almost immediately. Whether adding a default forces a full table rewrite depends on your PostgreSQL version and on whether the default is volatile, such as clock_timestamp(). Check your version's ALTER TABLE documentation before relying on either behaviour.

Backfills. A single whole-table UPDATE causes several problems at once:

• It holds row locks on every touched row until commit.
• It emits a WAL spike that replicas must replay, which causes replica lag.
• It leaves a large block of dead tuples, which become bloat.

Instead, update one primary-key range at a time (WHERE id >= $1 AND id < $2 AND new_col IS NULL), commit each batch, store the last key processed, and pause between batches. The IS NULL filter makes reruns idempotent, and the stored key makes the job resumable.

Constraints. Add CHECK or foreign-key constraints as NOT VALID. New writes are checked immediately. VALIDATE CONSTRAINT then scans existing rows under a lock that still allows reads and writes. A validated CHECK (col IS NOT NULL) is a route to SET NOT NULL. Verify whether your version can skip the full scan when such a constraint exists.

Indexes. CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY cannot run inside a transaction block, so disable your migration tool's wrapping transaction for these steps. A failed concurrent build leaves an INVALID index behind. Find it with pg_index.indisvalid = false, drop it, then retry.

Copy-- 1. Expand: nullable column, no default -> catalog-only change, brief lock.
ALTER TABLE orders ADD COLUMN region text;

-- 2. Backfill one primary-key range per transaction.
--    The job stores the last upper bound (10001 here), sleeps, then runs the next range.
--    `region IS NULL` makes a rerun of the same range a no-op.
BEGIN;
UPDATE orders
   SET region = 'unknown'
 WHERE id >= 1 AND id < 10001
   AND region IS NULL;
COMMIT;

-- 3. Two-phase constraint: enforce on new writes now, check old rows later.
ALTER TABLE orders
  ADD CONSTRAINT orders_region_not_null CHECK (region IS NOT NULL) NOT VALID;

-- Scans existing rows under a lock that still allows reads and writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

-- Check your version's docs: some versions skip the full scan when a
-- validated CHECK (region IS NOT NULL) already exists.
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_not_null;

-- 4. Run outside any transaction block (disable the migration tool's wrapper).
CREATE INDEX CONCURRENTLY orders_region_idx ON orders (region);

-- 5. After a failed concurrent build, find leftover INVALID indexes.
SELECT n.nspname AS schema_name, c.relname AS index_name
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE NOT i.indisvalid;

-- Drop it without blocking writes, then retry step 4.
DROP INDEX CONCURRENTLY IF EXISTS orders_region_idx;

A complete expand sequence on a placeholder orders table: add a nullable column, backfill one key range per batch, add a NOT VALID check and validate it to reach SET NOT NULL, build the index concurrently, and find any INVALID index left by a failed build.

Ordering migrations against deploys and handling drops and renames

Sequencing is what keeps old and new application code compatible with the schema at every moment. Expand migrations run before the deploy that depends on them, so the new code never sees a missing column. Contract migrations run only after every instance of the old code is gone. That includes canaries, background workers, cron jobs and any long-lived pods still draining. "Deploy finished" is not the same as "old code is no longer running."

Put one risky statement in each migration file. If a single file adds a column, builds an index and validates a constraint, a timeout on the third step leaves you unsure what was applied and makes a retry unsafe. One statement per file fails cleanly and can be re-run.

Why quick DDL still breaks things

Dropping or renaming a column takes milliseconds, but running instances may still depend on the old shape:

• Some ORMs load a table's column list at startup and keep generating INSERT and SELECT statements that name the old column.
• Prepared statements built on SELECT * can fail with "cached plan must not change result type" once the table's columns change.
• Code that reads results by position silently gets the wrong values.

To drop a column safely, first deploy code that stops referencing it. Only then drop it. A rename should follow the full expand and contract sequence: add the new column, write to both, switch reads, then drop the old one. Do not use RENAME COLUMN on a live table.

Review generated SQL

Many frameworks wrap every migration in a transaction. That rules out CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block. Wrapping also means locks are held until the slowest statement in the migration commits. Auto-generated DDL can also quietly emit a column type change that rewrites the table. Read the SQL your tool generates before it ships.

Plan rollback before contracting

Rollback is asymmetric. Up to the contract step you can redeploy old code against the expanded schema and nothing breaks. After a drop, the data in that column is gone, and rolling back means restoring from backup. Confirm the expanded state has been stable in production before you remove anything.

When to avoid it and what the alternatives are

The full sequence of expand, backfill, dual-write and contract costs several deploys and a lot of coordination. It is not always worth it. On a table with a few thousand rows, a plain ALTER TABLE holds its lock for milliseconds. Setting lock_timeout is enough protection there. Internal tools and batch systems that already have an agreed maintenance window can also accept a short outage. That is cheaper than weeks of compatibility code.

Some changes have no cheap online path. Changing a column from integer to bigint rewrites the whole table under an exclusive lock. Adding a primary key to a large table needs a unique index and non-null columns, and the final step still takes a strong lock. You have two honest options for changes like these:

"Online" does not mean free. A concurrent index build, a batched backfill or a constraint validation still reads and writes a large amount of data. That adds I/O, CPU and WAL volume, which can lag replicas. Concurrent operations also wait for long-running transactions to finish. Check for idle-in-transaction sessions before you start, and schedule heavy steps for low-traffic periods.

Impact on development cost and engineering time

A rename that one statement could handle becomes at least three deploys. The first adds the new column and dual-writes. The second backfills and switches reads. The third drops the old column. Each step must reach every running instance before the next one starts, so the change spreads over days. The common failure is an unfinished contract phase that leaves dual-write code and orphaned columns behind for months.

Illustrative estimate (assumed numbers, not a benchmark)

Extra cost = risky migrations per year × extra hours each Avoided cost = incidents avoided × (responders × hours each + outage cost)

Assume 20 risky migrations at 6 extra hours each, which adds 120 hours. Assume one avoided incident with 5 responders spending 8 hours each on response and the postmortem, which saves 40 hours. On engineering time alone, the approach does not pay for itself. Whether it breaks even depends on what an outage costs your business. Plug in your own figures.

Most of the overhead is paid once. A shared migration helper that sets lock_timeout and retries means nobody tunes timeouts by hand. A review checklist catches table rewrites and missing CONCURRENTLY in generated SQL. Teams also do fewer emergency rollbacks.

The investment pays off when tables are large and busy, deploys are frequent, and downtime has a real cost. Small internal services with a maintenance window rarely need it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql zero downtime migration, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Consumers learn about a breaking change at review time rather than from production errors.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.