Zero-Downtime Database Migrations at Scale

The ALTER TABLE reports "complete." The replicas haven't caught up: reads on them are stale, and queries against the altered table itself stall. Picture a transactions table with hundreds of millions of rows and a DBA adding a NOT NULL column whose default is gen_random_uuid(). Because that default is volatile, PostgreSQL rewrites every row, and every one of those rewritten rows is WAL the replicas must replay after the primary has already moved on.

The Replication-Lag Cascade

A single ALTER TABLE that looks harmless is the trigger:

ALTER TABLE transactions
    ADD COLUMN idempotency_key VARCHAR(64) NOT NULL DEFAULT gen_random_uuid()::text;

The root cause: a column added with a volatile default like gen_random_uuid() forces PostgreSQL^⁠[1] to rewrite the entire table and its indexes — every existing row gets a distinct value, so the value can't be stored once in the catalog the way a constant default can.

(On PostgreSQL 11+, a nullable ADD COLUMN with no default, or with a constant default, is metadata-only and near-instant — see the quick-start table below. The trap is the volatile default, NOT NULL or not, which silently triggers the full rewrite.) On the primary, every read and write of transactions waits on the ACCESS EXCLUSIVE lock until the rewrite commits. On each hot standby, the replayed lock blocks queries on the table, or cancels them with a recovery conflict, until the commit record replays^⁠[2]. WAL replays in order, so if a replica falls behind on the rewrite's WAL, every later change waits too and other tables go stale.

The cascade effects — replication lag, lock contention, schema coupling — are where teams get into trouble.

The Quick-Start: Know Your Lock Level

Before running any DDL, check what lock it acquires. AccessExclusive blocks all reads and writes for as long as it is held: briefly for a metadata-only change, for the whole rewrite otherwise.

Sources: PostgreSQL ALTER TABLE notes^⁠[1], MySQL online DDL operations^⁠[3]. For a change that needs a table copy on MySQL, use gh-ost; on PostgreSQL, turn the rewrite into expand-contract.

The Expand-Contract Pattern

The expand-contract pattern (also called "parallel change") splits a breaking schema change into safe, independently deployable phases. Each phase is independently rollback-able, so if Deploy 2 has a bug, you roll back to Deploy 1 without any schema changes — no data loss, no recovery scripts.^⁠[4]

graph LR
    subgraph Phase1 ["Phase 1: Expand"]
        E1["ADD COLUMN new_col<br/>(nullable, brief lock)"]
    end
    subgraph Phase2 ["Phase 2: Transition"]
        E2["Deploy dual-write code"] --> E3["Backfill old → new<br/>(batched)"]
    end
    subgraph Phase3 ["Phase 3: Contract"]
        E4["Drop old column<br/>(brief lock, lock_timeout set)"]
    end

    Phase1 -->|"Deploy 1<br/>rollback-safe"| Phase2
    Phase2 -->|"Deploy 2<br/>rollback-safe"| Phase3

Phase 1 (Expand): Add the new column or table without removing the old one. PostgreSQL's ADD COLUMN (nullable) is nearly free in Pg 11+ — it only stores the definition in the catalog, not in every row. This phase is fully backward-compatible: old code reads/writes the old column, new code is not yet deployed.

Phase 2 (Transition): Deploy application code that writes to both columns and reads from the new one. The old column is still populated by dual-write, so any old code instances that get redeployed by mistake still work. This phase is where you backfill existing rows into the new column (see the batched backfill pattern below).

Phase 3 (Contract): Once all application instances are running new code and no code reads the old column anymore, drop the old column. On PostgreSQL, DROP COLUMN only hides the column^⁠[1], so it needs just a brief lock (set lock_timeout), and pg_repack can reclaim the space online later. On MySQL before 8.0.29 the drop rebuilds the table in place^⁠[3]; gh-ost is the throttleable alternative.

Backfill Large Tables Safely

Never run a single UPDATE on billion-row tables — it creates a massive transaction that locks and spikes disk I/O. The throttled-batch pattern is the entire backfill discipline in one diagram:^⁠[4]

graph TD
    Start[Start backfill] --> NextBatch[Read next 1000-10000<br/>rows by primary key]
    NextBatch --> Empty{Any rows<br/>returned?}
    Empty -->|No| Done[Backfill complete]
    Empty -->|Yes| Update[UPDATE chunk<br/>in single TX]
    Update --> CheckLag[Query replay_lag from<br/>pg_stat_replication]
    CheckLag --> Lag{Lag over<br/>threshold?}
    Lag -->|Yes — paused| Wait[Sleep + recheck<br/>every 5 seconds]
    Wait --> CheckLag
    Lag -->|No — within budget| Throttle[Sleep N ms<br/>between batches]
    Throttle --> NextBatch
    Update -->|Failure or context cancelled| Abort[Abort + log<br/>last completed ID<br/>so resume is idempotent]
    style Done fill:#dfd
    style Wait fill:#ffd
    style Abort fill:#fdd

Batch in smaller chunks with replication-lag monitoring:

type BackfillConfig struct {
	BatchSize         int           // rows per batch (tune: 1,000–10,000)
	SleepBetweenBatch time.Duration // throttle between batches
	MaxReplicationLag time.Duration // pause if lag exceeds this
}
 
// sleepCtx waits for d, returning early if ctx is cancelled.
func sleepCtx(ctx context.Context, d time.Duration) error {
	t := time.NewTimer(d)
	defer t.Stop()
	select {
	case <-ctx.Done():
		return ctx.Err()
	case <-t.C:
		return nil
	}
}
 
func Backfill(ctx context.Context, pool *pgxpool.Pool, cfg BackfillConfig) error {
	log := slog.Default()
	var lastID int64 = 0
 
	for {
		// Check replication lag before each batch. If the check fails, stop:
		// carrying on would backfill with the throttle silently disabled.
		// Resuming is safe — the IS NULL filter skips rows already done.
		var lagSeconds float64
		if err := pool.QueryRow(ctx, `
			SELECT COALESCE(MAX(EXTRACT(EPOCH FROM replay_lag)), 0)
			FROM pg_stat_replication
		`).Scan(&lagSeconds); err != nil {
			return fmt.Errorf("replication lag check after id %d: %w", lastID, err)
		}
		lag := time.Duration(lagSeconds * float64(time.Second))
		if lag > cfg.MaxReplicationLag {
			log.Warn("replication lag over budget, pausing", "lag", lag)
			if err := sleepCtx(ctx, 5*time.Second); err != nil {
				return err
			}
			continue
		}
 
		// Batch update: CTE to get max ID and count in one round-trip
		var maxID int64
		var batchCount int64
		err := pool.QueryRow(ctx, `
			WITH batch AS (
				SELECT id FROM transactions
				WHERE id > $1 AND idempotency_key IS NULL
				ORDER BY id LIMIT $2
			),
			updated AS (
				UPDATE transactions t
				SET idempotency_key = gen_random_uuid()::text
				FROM batch WHERE t.id = batch.id
				RETURNING t.id
			)
			SELECT COALESCE(MAX(id), 0), COUNT(*) FROM updated
		`, lastID, cfg.BatchSize).Scan(&maxID, &batchCount)
 
		if err != nil {
			return fmt.Errorf("batch update after id %d: %w", lastID, err)
		}
 
		if maxID == 0 {
			log.Info("backfill complete")
			return nil
		}
 
		lastID = maxID
		log.Info("batch complete", "last_id", lastID, "count", batchCount)
		if err := sleepCtx(ctx, cfg.SleepBetweenBatch); err != nil {
			return err
		}
	}
}

During the transition, your application writes to both columns, ensuring both are populated regardless of which code version runs:

func (r *TransactionRepo) Insert(ctx context.Context, tx Transaction) error {
	_, err := r.db.Exec(ctx, `
		INSERT INTO transactions (amount, idempotency_key, legacy_key)
		VALUES ($1, $2, $3)
	`, tx.Amount, tx.IdempotencyKey, tx.IdempotencyKey) // write to both
	return err
}
 
// Feature flag controls which column we read from
func (r *TransactionRepo) Find(ctx context.Context, key string) (*Transaction, error) {
	if r.flags.UseNewColumn() {
		return r.findNewColumn(ctx, key)
	}
	return r.findLegacyColumn(ctx, key)
}

Online DDL Tools: pg_repack and gh-ost

The two tools are often named together, but only one of them changes schemas.

PostgreSQL: pg_repack^⁠[5] removes table and index bloat online, like VACUUM FULL or CLUSTER without the long exclusive lock. It copies the table into a new one with the same schema, replays changes captured by a trigger, and swaps the tables, holding ACCESS EXCLUSIVE only briefly at setup and at the swap. It cannot apply a type change or a new column, so on PostgreSQL those go through expand-contract; pg_repack's job is reclaiming the bloat a dropped column or a large backfill leaves behind. It needs the extension installed, a primary key (or a unique index on a NOT NULL column), and superuser or table-owner rights.

# Install: apt install postgresql-<version>-repack, then CREATE EXTENSION pg_repack;
 
# Online VACUUM FULL: reclaim space after a column drop or a big backfill
pg_repack --host primary.db --dbname production --table transactions --no-order
 
# Or rebuild one index online (--index cannot be combined with --table)
pg_repack --host primary.db --dbname production \
  --index idx_transactions_idempotency_key

MySQL: gh-ost^⁠[6] — GitHub's online schema tool. Creates a ghost table with the new schema, copies rows in the background while tailing the binary log, then swaps. Use it where native ALTER TABLE can only run with ALGORITHM=COPY, which blocks writes for the whole copy^⁠[3].

# --allow-on-master: by default gh-ost expects to connect to a replica
gh-ost --host primary.db --allow-on-master --database production --table orders \
  --alter="MODIFY COLUMN customer_id BIGINT NOT NULL" \
  --chunk-size=2000 --max-lag-millis=1500 --execute
 
# Pause/resume a running migration (default socket: /tmp/gh-ost.<db>.<table>.sock)
echo "throttle" | nc -U /tmp/gh-ost.production.orders.sock
echo "no-throttle" | nc -U /tmp/gh-ost.production.orders.sock

For indexes, PostgreSQL's CREATE INDEX CONCURRENTLY is the built-in equivalent — it takes a ShareUpdateExclusive lock^⁠[7] instead of blocking reads/writes, though it does more total work and takes significantly longer than a regular blocking build^⁠[8]:

-- Reads/writes continue (slower than blocking index build, but zero downtime)
CREATE INDEX CONCURRENTLY idx_transactions_idempotency_key
    ON transactions (idempotency_key)
    WHERE idempotency_key IS NOT NULL;

Foreign keys also block for validation. Add as NOT VALID first, then validate separately during low-traffic hours:

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
 
-- Validate takes ShareUpdateExclusive lock (reads/writes continue)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customers;

Migration Best Practices

Set lock_timeout before running DDL — if you can't acquire the lock in 5 seconds, fail fast instead of blocking the entire connection pool:

SET lock_timeout = '5s';
SET statement_timeout = '30min';
ALTER TABLE transactions ADD COLUMN idempotency_key VARCHAR(64);

If a lock_timeout fires, retry during lower-traffic windows. This is vastly preferable to indefinitely blocking the connection pool, which causes application 503s.

Check for blocking transactions before migrations:

SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '30 seconds'
ORDER BY duration DESC;

A session left idle in a transaction holds its locks until the transaction ends^⁠[7], so the DDL waits behind it. Identify the application owner, confirm it's safe to terminate, and kill it with pg_terminate_backend(pid) if necessary.

Monitor replication lag during and after migrations. Alert above a threshold you choose (e.g., 30 seconds):

SELECT client_addr, replay_lag
FROM pg_stat_replication
ORDER BY replay_lag DESC;

Replication lag during a migration is your canary. If lag is climbing, your migration is generating writes faster than replicas can keep up — the lag in the opening scenario is exactly that signal. A rewriting ALTER can't be throttled once it starts, which is why the rewrite belongs in the batched, lag-throttled backfill above rather than in one DDL statement.

Use a dark-launch approach during the transition phase: write to both columns but read from the old one. Shadow-read from the new column to detect bugs before users hit them:

oldResult, err := r.readOld(ctx, id)
if err != nil {
	return nil, fmt.Errorf("read old column: %w", err)
}
// The shadow read must never fail the request, but its failures are data too.
if newResult, err := r.readNew(ctx, id); err != nil {
	log.Warn("shadow read failed", "id", id, "error", err)
} else if !reflect.DeepEqual(oldResult, newResult) {
	log.Error("mismatch", "id", id, "old", oldResult, "new", newResult)
}
return oldResult, nil // always return old during dark launch

This catches data inconsistencies that would otherwise ship to production undetected.

For blue-green deployments: expand-contract maps directly. Add new columns (expand), deploy code that dual-writes (transition), then drop old columns (contract) only after confirming blue version is fully drained. Check the load balancer metrics — the blue version must have zero in-flight requests for at least 15 minutes before the contract phase runs.

Index builds and replicas: a hot standby rejects all DDL^⁠[2], so on physical replicas an index arrives only through WAL replay of the primary's build. CREATE INDEX CONCURRENTLY holds ShareUpdateExclusive, not one of the ACCESS EXCLUSIVE locks that block standby queries, so replica reads continue; the build's WAL still has to replay, so schedule large builds during low-traffic windows and watch replica lag.

Production Checklist

Before running any migration on a production table > 1M rows:

• Determine the exact lock level (consult the quick-start table above)
• Test on a production-sized replica with real load patterns
• Time the DDL and one backfill batch on a production-sized staging copy (EXPLAIN doesn't accept DDL)
• If it rewrites or copies the table: expand-contract (PostgreSQL) or gh-ost (MySQL)
• Set lock_timeout and statement_timeout on the migration connection
• Have a rollback script ready (DROP COLUMN, reverse migration)
• Schedule during low-traffic windows with stakeholder approval
• Don't run during deployments (code + schema changes = chaos)
• Monitor replication lag (e.g., alert above 30 seconds)
• Run ANALYZE on the table post-migration to update planner statistics
• Keep rollback ready post-migration (e.g., 24-48 hours)

Feature-Flag-Driven Migrations

Schema migrations and code deploys are two independent change vectors. Coupling them — "this PR ships the new column AND the new read path" — turns rollbacks into branch-revert exercises that take 20 minutes when the incident clock is already burning. Feature flags decouple them. The schema lives ahead of the code, dual-writes happen behind a flag, and the read switch is a runtime toggle, not a deploy.

The pattern: every step that changes user-visible behaviour gets a kill switch. The flag service (LaunchDarkly, Unleash, or a homegrown table in Postgres) returns a boolean per request. The application reads from the new column only when the flag returns true for that request's percentage rollout cohort. If the new path explodes — wrong index, bad cardinality, missing constraint — flip the flag back to zero. No deploy, no rollback PR, no waiting for CI. The schema is unchanged; the read just routes back to the legacy column that's still being dual-written.

-- Feature flag table read by the application on each request
CREATE TABLE IF NOT EXISTS feature_flags (
    name              TEXT PRIMARY KEY,
    rollout_percent   SMALLINT NOT NULL DEFAULT 0
        CHECK (rollout_percent BETWEEN 0 AND 100),
    enabled_for_users BIGINT[] NOT NULL DEFAULT '{}',
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
-- Start at 0% — schema is live, code is dual-writing, no reads use it
INSERT INTO feature_flags (name, rollout_percent)
VALUES ('read_idempotency_key_column', 0)
ON CONFLICT (name) DO NOTHING;
 
-- Bump to 1% canary, then 10%, 50%, 100% as p99 latency holds steady
UPDATE feature_flags
SET rollout_percent = 10, updated_at = now()
WHERE name = 'read_idempotency_key_column';

The dual-write phase runs at zero rollout for 24-48 hours before the first canary. That window catches drift before any user reads the new column: a nightly diff job samples 100,000 rows, compares legacy and new values, and pages if mismatches exceed a chosen tolerance — this example alerts at 0.01%, tune it to the acceptable staleness for your column. Catching a dual-write bug at 0% rollout is a Slack message; catching it at 50% rollout is an incident.

Backfill Backpressure with Chunked UPDATE

The Go backfill loop earlier handles batching, but production-grade backfills need backpressure that responds to more than just replication lag. Hot-key contention, vacuum I/O, and connection pool saturation all degrade application latency in ways replication lag misses. The chunked UPDATE below watches two latency signals between batches and adapts batch size dynamically.

-- Adaptive backfill driver: shrink chunks when latency rises, grow when stable.
-- Run it outside an explicit transaction block so each chunk can COMMIT.
-- Needs PostgreSQL 13+ (gen_random_uuid, mean_exec_time) and, for signal 2,
-- the pg_stat_statements extension.
DO $$
DECLARE
    chunk_size       INT := 5000;
    last_id          BIGINT := 0;
    batch_max_id     BIGINT;
    rows_updated     INT;
    mean_ms          DOUBLE PRECISION;
    active_queries   INT;
BEGIN
    LOOP
        -- Backpressure signal 1: long-running queries touching the table
        SELECT count(*) INTO active_queries
        FROM pg_stat_activity
        WHERE state = 'active'
          AND pid <> pg_backend_pid()
          AND query_start < now() - interval '5 seconds'
          AND query ILIKE '%transactions%';
 
        IF active_queries > 3 THEN
            PERFORM pg_sleep(5);
            chunk_size := GREATEST(chunk_size / 2, 500);
            CONTINUE;
        END IF;
 
        -- Backpressure signal 2: mean read latency on the table. It is
        -- cumulative since the last pg_stat_statements_reset(), so reset
        -- before starting or the signal barely moves.
        SELECT COALESCE(MAX(mean_exec_time), 0) INTO mean_ms
        FROM pg_stat_statements
        WHERE query LIKE 'SELECT%FROM transactions%'
          AND calls > 100;
 
        IF mean_ms > 50 THEN
            chunk_size := GREATEST(chunk_size / 2, 500);
        ELSIF mean_ms < 10 THEN
            chunk_size := LEAST(chunk_size * 2, 20000);
        END IF;
 
        -- The actual chunk: keyset on the primary key, not LIMIT-OFFSET
        WITH batch AS (
            SELECT id FROM transactions
            WHERE id > last_id AND idempotency_key IS NULL
            ORDER BY id
            LIMIT chunk_size
            FOR UPDATE SKIP LOCKED
        ), updated AS (
            UPDATE transactions t
            SET idempotency_key = gen_random_uuid()::text
            FROM batch
            WHERE t.id = batch.id
            RETURNING t.id
        )
        SELECT count(*), max(id) INTO rows_updated, batch_max_id FROM updated;
 
        EXIT WHEN rows_updated = 0;
        -- Advance past this chunk only: dual-written rows at the tail of the
        -- table already have a key and must not move the cursor.
        last_id := batch_max_id;
 
        COMMIT;  -- one transaction per chunk, not one for the whole backfill
        PERFORM pg_sleep(0.05);
    END LOOP;
END $$;

FOR UPDATE SKIP LOCKED is the critical primitive. Without it, the backfill blocks on rows currently being written by application traffic and your batches stall. With it, the backfill never queues behind application writers: it steps over rows they hold, those rows stay NULL, and a second run of the same block picks them up.

The adaptive chunk sizing is more important than the absolute chunk number. A backfill that runs 5,000-row chunks during a quiet 3am hour and 500-row chunks during a checkout spike trades some wall-clock time for bounded latency impact. A fixed size is either too slow off-peak or too aggressive on-peak.

The Reversal Playbook

Picture this: the migration is half done. Forty percent of the rows have new values, sixty percent are null, the dual-write code is deployed, and the canary is showing a severe latency regression on the new index — say, 4x. You need to roll back without losing the partial progress and without leaving the schema in a state that confuses the next deploy.

The reversal rules: never DROP COLUMN during a partial backfill — the column may be referenced by trigger functions, materialised views, or pg_dump jobs that will fail loudly at 4am.^⁠[4] Never reset the backfill to NULL and start over — you've already paid the I/O cost; preserve it. Always pause first, diagnose second, decide third.

-- Step 1: route reads back to the legacy column (schema untouched)
UPDATE feature_flags
SET rollout_percent = 0, updated_at = now()
WHERE name = 'read_idempotency_key_column';
 
-- Step 2: kill any in-flight backfill statements (preserves committed batches)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query ILIKE '%UPDATE transactions%idempotency_key%'
  AND state = 'active'
  AND application_name = 'backfill_worker';
 
-- Step 3: snapshot the partial state for forensics
CREATE TABLE migration_snapshot_2026_05_01 AS
SELECT id, idempotency_key, created_at
FROM transactions
WHERE idempotency_key IS NOT NULL;
 
-- Step 4: confirm dual-write is still populating new rows correctly
SELECT count(*) FILTER (WHERE idempotency_key IS NULL) AS unfilled,
       count(*) FILTER (WHERE idempotency_key IS NOT NULL) AS filled,
       max(created_at) FILTER (WHERE idempotency_key IS NULL) AS last_unfilled
FROM transactions
WHERE created_at > now() - interval '1 hour';

If last_unfilled is older than your deploy completion timestamp, dual-write is healthy and the rollback is clean: just leave the partial backfill in place, fix the underlying issue (usually a missing index or a wrong default), and resume. If last_unfilled is recent, the dual-write code path itself is broken — a far more serious incident that requires reverting the application deploy before touching the schema.

The decision tree: broken read path -> flip flag, leave schema alone, fix forward. Broken write path -> flip flag, revert app deploy, leave schema alone. Broken schema (wrong type, wrong nullability) -> flip flag, revert app deploy, then add a new column with the correct definition rather than mutating the broken one. PostgreSQL's catalog accumulates dead columns cheaply; mutating a column that's already been written to a million times is the expensive operation. The discipline of "always add, never mutate" turns half-completed migrations into recoverable states instead of all-night incidents.

Frequently Asked Questions

Keep Reading

• Database Indexing Strategies — B-tree internals, composite index design, and bloat monitoring for newly-created indexes
• PostgreSQL Query Planner Internals — Read EXPLAIN output and verify the planner uses your new indexes after migration
• Graceful Shutdown in Production Go Services — Drain in-flight requests during rolling deploys that accompany database migrations
• Postgres EXPLAIN Cheat Sheet — Verify post-migration plans against pre-migration baseline; catch regressions before they hit production
• SRE: SLOs, SLIs, and Error Budgets — Multi-window burn-rate alerting watches the migration's error budget consumption in real time