Zero-Downtime Schema Evolution Patterns

A schema change that takes an exclusive lock on a hot table for two seconds is, to the users hitting that table, an outage. Evolving a live database without downtime is not about a clever ALTER statement; it is about sequencing — never changing schema and code in the same instant, never making the running version depend on a column the new version has not finished populating, and never holding a lock long enough for the connection pool to back up. Done in the right order, you add structure, dual-write into it, backfill it under throttle, flip reads, then remove the old structure — and at no point is the application unable to read or write what it needs. This part of the guide serves backend and platform engineers who own the deploy, and DBAs who must guarantee the primary survives every release.

Every technique here is an instance of one contract: the database must always be readable and writable by the code that is currently in flight, including the version you are about to replace. That contract builds on the correctness rules in Database Migration Fundamentals, and it is enforced mechanically by the gates in CI/CD & Migration Automation. The framework-specific mechanics — how Prisma or Drizzle emit the DDL you are about to sequence — live in ORM & Framework Migration Workflows. Here the question is purely operational: in what order do you touch schema, data, and traffic so the system never stalls?

Core Principles

Four invariants govern every zero-downtime change. Each child section below is a disciplined application of one or more of them.

The running version must keep working unchanged. Schema deploys before the code that needs it, and must stay compatible with the previous code still serving traffic. A migration may only add — a new nullable column, a new index, a new table — never drop or rename in the same step. This forward-compatibility window is exactly what the Expand and Contract Methodology formalizes.

No statement holds a blocking lock longer than the budget. An ALTER TABLE that rewrites the table takes ACCESS EXCLUSIVE (PostgreSQL) or blocks writes (MySQL) for the duration. The fix is online DDL — CREATE INDEX CONCURRENTLY, ALGORITHM=INPLACE, LOCK=NONE — paired with a low lock_timeout so a migration that would block fails fast instead of queueing the pool behind it.

Data moves under throttle, never in one shot. A backfill that updates ten million rows in one transaction bloats the WAL, stalls autovacuum, and blows out replica lag. Backfill Optimization replaces it with idempotent, keyset-paginated batches that pause when the slowest follower falls behind.

Cutover is a switch, not a leap. Reads move to the new shape behind Feature Flag Rollouts and, where traffic allows, through Read/Write Splitting Tactics, so a regression is reverted by flipping a flag rather than by running a second migration under incident pressure.

Phase-by-phase Overview

A change moves through four phases. Each has one job and one gate that must be green before the next begins.

Prepare — assert the change is additive and backward compatible against the live schema before it merges. A dry run that detects a column already present makes the migration safe to re-run.

-- PostgreSQL · run as migration role inside a transaction (this is a read-only dry run)
-- Context: safe anytime; performs no DDL, only reports whether the add is needed.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'orders' AND column_name = 'tenant_id'
  ) THEN
    RAISE NOTICE 'tenant_id absent — additive ALTER is safe to apply.';
  ELSE
    RAISE NOTICE 'tenant_id already present — migration is a no-op.';
  END IF;
END $$;

Deploy — apply the additive change as a discrete, forward-only step that runs before the new application image. Index builds run outside a transaction and under a lock budget.

-- PostgreSQL · run as migration role · CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction
-- Context: no BEGIN/COMMIT around this statement; lock_timeout bounds the brief metadata lock.
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS tenant_id UUID;          -- metadata-only on PG 11+
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_tenant ON orders (tenant_id);

Backfill — populate the new column in throttled, idempotent batches after the schema is live, halting if the replica falls behind.

# Run as a separate post-deploy job, not inline with the deploy step.
# Context: rate-limited worker; safe to re-run; pauses if replica lag > 2s.
./bin/backfill --table orders --column tenant_id --batch 1000 --max-lag-seconds 2

Validate — confirm parity and query health before contracting; only then drop the old structure.

# Post-deploy verification gate. Non-zero exit blocks the contract step.
# Context: read-only check against the primary; run before any DROP.
./bin/assert-zero-null --table orders --column tenant_id \
  && ./bin/healthcheck --error-rate-max 1% --p95-latency-max 250ms --window 5m

Tool & Database Matrix

How aggressively you can sequence each phase depends on what the engine guarantees about DDL and locking.

The practical split: PostgreSQL lets you wrap a multi-statement expand in one transaction; MySQL does not, so on MySQL every step must be independently re-runnable. The reasoning behind that divergence lives in Transactional vs Non-Transactional Databases.

CI/CD Integration Pattern

Sequencing discipline only holds if a machine enforces it. Wire the expand/contract contract as a required, blocking check so a destructive or non-online migration cannot merge.

# .gitlab-ci.yml — refuse any migration that breaks the forward-compatibility window
schema_evolution_gate:
  stage: verify
  rules:
    - changes: [ "migrations/**/*" ]
  script:
    # 1. The migration is additive — no DROP/RENAME of a column the running version reads
    - ./bin/assert-additive-only --dir migrations/
    # 2. It holds no exclusive lock beyond the budget on a production-like snapshot
    - ./bin/assert-lock-budget --database ci_db --max-exclusive-ms 200
    # 3. Any backfill it ships is throttled and idempotent
    - ./bin/assert-backfill-throttled --dir migrations/
  allow_failure: false   # blocks the merge

The full gating apparatus — backward-compatibility diffs, checksum verification, and lock-budget assertions wired as required checks — is built out in CI/CD & Migration Automation.

Failure Modes & Rollback Contract

These are the characteristic ways a live schema change goes wrong. Naming each is how you build the safeguard that catches it.

• Lock queue pile-up — an ALTER waits behind a long query, and every new connection queues behind the ALTER. Root cause: no lock_timeout, so the migration blocked instead of failing fast.
• Replication lag spike — an unthrottled backfill outruns the replica’s apply rate, stalling reads served from followers. Root cause: batch size tuned for the primary, not the slowest follower.
• Dual-write divergence — the legacy and new tables drift because one write path succeeded and the other failed silently. Root cause: writes that were not atomic or not reconciled.
• Premature contract — the old column is dropped before the backfill finished, and the still-running previous version reads NULL. Root cause: contracting on elapsed time instead of proven parity.
• Read cutover regression — reads flip to the new shape and latency spikes because the supporting index was never built. Root cause: cutover not gated on index readiness.
• Destructive rollback — reversal runs DROP COLUMN and loses backfilled data. Root cause: treating rollback as schema reversal instead of path disablement.

The rollback contract that prevents the last two: every deploy is additive and forward-only, and reversal disables the new path — flips the flag, stops dual-writing, restores the prior image — while leaving the expanded schema in place. Nothing is dropped until parity is proven and the new path has been stable.

Why a Two-Second Lock Becomes a Two-Minute Outage

The reason a brief lock is disproportionately dangerous is queueing, not the lock itself. Suppose a table serves 500 requests per second and each request holds a connection for 4 milliseconds; at steady state only two connections are busy at any instant. Now an ALTER TABLE takes an ACCESS EXCLUSIVE lock and holds it for two seconds. Every query against that table blocks behind the lock, but requests keep arriving at 500 per second — so after two seconds roughly a thousand requests are stacked up, each holding a connection while it waits. A pool sized for a peacetime concurrency of two has been asked to hold a thousand, and it saturated somewhere around connection 100. From that point every new request fails immediately with a pool-timeout or “too many connections” error, health checks start failing, and the load balancer pulls the instance. The two-second lock is over, but the pool is still draining the backlog and the retry storm the failures triggered. This is why the duration of the lock matters far less than whether the lock is taken at all under load, and why the single most valuable guardrail is a lock timeout: SET lock_timeout = '2s' in PostgreSQL or SET SESSION lock_wait_timeout = 2 in MySQL makes the migration abandon its attempt rather than join the queue, so a contended ALTER fails fast and cheap instead of taking the service with it.

The corollary is that lock acquisition is the risk, not lock hold. A migration that waits behind a long-running transaction to get its lock is just as dangerous as one that holds a lock for a long time, because while it waits it queues every subsequent query behind it — PostgreSQL’s lock queue is fair, so a pending ACCESS EXCLUSIVE request blocks the reads that arrive after it even though they would not conflict with the current holder. That is why you never run a blocking DDL statement while a long analytics query or an idle-in-transaction session is open on the same table, and why the pre-flight check for any contract step is “is any transaction older than my lock timeout currently touching this table?” A migration should set a short lock timeout, a short statement timeout, and retry the acquisition a few times with backoff — so it either slips in during a quiet microsecond or gives up without ever having made the table unavailable.

Signals That Tell You Each Phase Is Safe to Advance

Every transition in the expand-backfill-contract sequence is gated on a measurable signal, never on a stopwatch. The mistake that turns a textbook-correct plan into an incident is advancing on elapsed time — “we deployed the backfill an hour ago, it must be done” — instead of on evidence. Each phase has a specific thing to measure. After expand, the signal is that the new column or index exists on the primary and every replica, confirmed by querying information_schema (or pg_index.indisvalid for a concurrently built index) on each node rather than assuming replication has caught up. After backfill, the signal is a count query proving zero rows remain in the un-migrated state — SELECT count(*) FROM orders WHERE new_col IS NULL AND should_be_set returning zero — combined with a checksum or row-count comparison between the old and new shapes so you know the data is not just present but correct. After cut reads, the signal is that error rates and p99 latency on the new read path are flat, held long enough to span a full traffic cycle including your peak, so a defect that only shows under load has a chance to surface before you remove the fallback.

The contract step deserves the strictest gate because it is the only irreversible one. Before dropping the old column you want three independent confirmations: parity holds (the backfill count is zero and stays zero across new writes), no application version still in the fleet references the old column (check your deploy manifest, not your intentions), and the new path has been serving reads without a fallback for long enough that you would already know if it were broken. Treat the contract migration itself as a normal blocking DDL with a short lock timeout — dropping a column is fast, but it still needs the same ACCESS EXCLUSIVE lock as any other structural change and will queue behind a long transaction exactly like an ADD COLUMN would. When all three signals agree, contract; when any one is ambiguous, hold, because the cost of waiting another day is a day, and the cost of contracting against a lagging follower or an un-upgraded canary is an outage plus a data-recovery exercise.

Where Online-DDL Tools Fit — and Where They Do Not

Tools such as pg_repack, pt-online-schema-change, and gh-ost automate the mechanical half of this work: they build a shadow copy of the table in the new shape, copy rows across in throttled chunks, keep the copy current with triggers or by tailing the replication log, and swap it in with a brief metadata lock at the end. They are invaluable for the operations that native DDL still cannot do online — a full-table rewrite, a type change that forces a rewrite, or an index build on a MySQL version that lacks a true online path. But they automate only the expand-and-copy mechanics; they do not decide whether your change is backward-compatible, they do not sequence your application deploy, and they do not know when it is safe to cut reads. A tool will happily copy a table into a shape your old code cannot read, and the swap at the end is still a lock — a short one, but one that queues behind a long transaction exactly like a hand-written ALTER. So the sequencing discipline in this section sits above the tooling: you still design the change to be additive, you still deploy code and schema in a compatible order, and you still gate the cutover on measured parity. The tool replaces the risky part of the backfill; it does not replace the plan.

The other reason to understand the manual sequence even when a tool does the copying is failure recovery. When gh-ost aborts halfway, or a concurrent index build leaves an INVALID index behind, you are back to the primitives — a partly populated shadow table, a trigger still firing, a lock you need to release — and the only way to reason about the safe next step is to know what state each phase leaves behind. The tooling is an accelerator for teams who already understand expand-backfill-contract, not a substitute for understanding it.

What This Section Covers

The work splits into six focused areas, each owning one part of the lifecycle. Backfill Optimization covers moving historical data into the new shape without starving production — keyset pagination, adaptive batch sizing, and pausing when replica lag breaches its budget. Dual-Write Synchronization covers keeping the old and new tables in step while both are live, using atomic writes or an outbox so neither path silently drops a row. Expand and Contract Methodology covers the over-arching sequence itself — adding structure, migrating onto it, and removing the old structure only after parity holds. Online Index Management covers building and dropping indexes on live tables without a blocking lock — CREATE INDEX CONCURRENTLY, MySQL’s ALGORITHM=INPLACE, LOCK=NONE, and recovering the invalid index a failed concurrent build leaves behind. Feature Flag Rollouts covers decoupling the schema change from its activation so reads and writes flip by configuration rather than redeploy. Read/Write Splitting Tactics covers routing reads to replicas during the validation window so the primary absorbs the migration while followers absorb the load.

Frequently Asked Questions

Why must schema deploy before the application code that uses it? Because during a rolling deploy both versions run at once. If the code shipped first, it would read a column that does not exist yet and error; if the schema is additive and ships first, the old code ignores the new column and the new code finds it ready. The schema change must be compatible with the previous version still serving traffic — that is the entire reason the change must be additive.

When is it safe to run the contract step and drop the old column? Only after the backfill is verifiably complete (zero rows where the new column is unexpectedly NULL), the read path has been cut over and stable for long enough to trust, and no in-flight version still reads the old column. Contract on proven parity, never on elapsed time — a premature DROP against a lagging follower or an un-upgraded replica is a classic self-inflicted outage.

How do I roll back a live schema change without losing data? Disable the new path rather than reversing the schema: flip the feature flag back, stop dual-writing, and restore the previous application image. Leave the expanded column in place. Because every deploy was additive, the old shape is still intact and the rollback is a configuration change, not a destructive migration run under incident pressure.