Schema Migrations and Deploy Coupling: Two Artifacts, One Consistency Problem

Overview

A code deploy and a schema migration are two writes to two different stores of truth, executed on different clocks by different tools, while production traffic reads and writes both continuously. The migration changes shared state that every version of the code — old, new, and rolling — must interpret correctly at every instant; the code change assumes a schema that may not exist on all replicas yet. That is a distributed consistency problem wearing a database-ops costume, and the industry’s answer — expand-contract, dual-write, backfill — is the same answer distributed systems give to any replica-consistency problem: make every intermediate state valid for both old and new readers, let stragglers drain, then converge and clean up.

This page maps the full chain: how schema migrations feed deploy ordering, how expand-contract decomposes a breaking change into separately deployable steps, how dual-write plus backfill moves data without downtime, and why rollback is usually a fiction for schemas. The failure propagation is distinctive because it runs backwards in time: a migration committed today fires under next week’s deploy, and an old process from last week reads a new schema the moment its connection lands on a migrated primary. The levers are correspondingly procedural — migration gates, backward-compatible steps, lock budgets — because the failure is not one bad statement but an ordering violation between fleets of processes.

The Systems, Independently

Schema migrations. A migration is a versioned, ordered transformation of database schema (and sometimes reference data), applied by a migration tool that tracks which versions have run (schema_migrations table, checksum-guarded). Migrations differ from ordinary DDL execution in three ways: they are ordered (version 42 assumes 41 ran), they are applied to a live system under traffic, and they are quasi-irreversible (a DROP COLUMN destroys data; “down” migrations are aspirational fiction in most shops). Under the hood each statement acquires locks: PostgreSQL DDL takes ACCESS EXCLUSIVE on the target table (even ADD COLUMN, before v11, rewrote the table), MySQL’s online DDL permits some in-place operations but still blocks on metadata locks held by long transactions. Index creation has its own flags (CREATE INDEX CONCURRENTLY, online DDL) because a plain CREATE INDEX locks writes for the build’s duration.

Deploy ordering, dual-write, backfill, rollback. The deploy pipeline ships application versions; when a version needs new columns or new tables, ordering matters: schema first (additive) → deploy code → backfill → (later) cleanup. Dual-write means the new code writes both old and new representations during the transition window so old readers (and any straggler process) keep working; backfill populates the new representation for pre-existing rows, batched and throttled to stay under replication-lag and lock budgets; rollback for the code means redeploying the previous version, which is only safe if the previous version still works against the current schema — the property that backward-compatible migration steps exist to guarantee.

How the Chain Feeds Itself

The canonical decomposition of one breaking change into four deployable steps: (1) Expand — additive migration (ADD COLUMN nullable, new table, new index concurrently); old code ignores new structures, new structures tolerate old code. (2) Deploy — application version N+1 reads old columns but writes both old and new (dual-write), behind a flag if rollout control is wanted. (3) Backfill — a throttled job copies old rows’ data into the new representation in bounded batches (UPDATE ... LIMIT loops or key-range batches), monitoring replica lag and pausing over budget. (4) Contract — after observability confirms the new path, a deploy stops writing the old column, and a final migration drops it (or marks it deprecated).

Shared invariants across the whole dance: every intermediate schema must serve the running fleet — the invariant is not “schema matches code” but “schema serves every code version currently alive, including the one in the middle of a rolling restart and the one pinned on a canary”; every step is independently rollback-safe — code steps roll back to the previous version, schema steps never destroy data until the contract phase; order is a partial order, not a sequence — across many services, each service’s contract step depends on all readers of a column having stopped, which is a dependency-DAG condition, not a date on a change ticket.

The Shared Principle

Expand-contract is two-phase commit at the artifact level: prepare a compatible intermediate state (expand), let all participants converge (deploy + backfill), then commit the switch and reclaim (contract) — and the alternative, mutating in place, is exactly the non-atomic write that leaves the system half-transformed on crash. The deeper shared principle with the transaction world: both are protocols for changing shared state while concurrent readers with stale views must never observe a contradiction. A migration fleet mid-rollout is a replicated system mid-propagation: the schema is the “new value,” the fleet’s cached assumptions (prepared statements, ORM metadata, query plans) are stale replicas, and the transition window is bounded by the slowest replica to converge — usually the last pod to restart or the read replica with the most lag.

Where the Coupling HOLDS and BREAKS

Failure Propagation

Old code on new schema. Drop a column at 14:00; the deploy that stops using it lands at 15:30; between those times every prepared query in every not-yet-restarted pod that selects the column throws column does not exist (or silently misbehaves if the ORM re-maps). The failure is total, instant, and ordered last-in-first-out: the oldest processes — often the canary, the cron jobs, and the worker fleet nobody included in the rollout plan — fail first. Read replicas add a second front: the migration commits on the primary, replicas apply it seconds later, and a read-your-writes session can hit a replica where the column still exists with the opposite semantics — the error direction nobody tests. Long locks propagate differently but just as fast: one ALTER TABLE on a 500M-row table holds ACCESS EXCLUSIVE for the rewrite’s duration; every subsequent query queues behind it; connection pools saturate (500 queued connections × 30 s timeouts); and the outage cascades to unrelated tables as the pool exhausts — the lock is local, the failure is global.

Backfill and rollback pathologies. An unthrottled backfill is a self-inflicted denial of service: one giant UPDATE takes row locks across the table, blocks foreground writes, generates WAL at backup-job rates, and pushes replicas minutes behind — which then breaks the read-your-writes guarantee for the very dual-write it exists to enable. Rollback failures compound: after contract-phase DROP COLUMN, the previous code version cannot run (it selects a column that no longer exists), so “roll back the deploy” now requires a forward data-restoration migration from backups — hours, if the data was recoverable at all. The rule the industry relearned in every post-mortem: schema rollbacks are roll-forwards through the contract phase, planned before the expand step ships. One more interleaving deserves its own warning: deploys and migrations racing each other. A deploy that starts while the migration tool is mid-batch can see half-migrated state, and a migration that starts while a rollback is running can re-add a structure the rollback just removed — so mature pipelines serialize the two behind one gate: deploys wait for an in-flight migration lock, and migrations are deployed like code, reviewed, versioned, and scheduled, never run ad hoc from a laptop.

A Concrete Worked Example

Adding a NOT NULL column to a 500M-row orders table. Naive path: ALTER TABLE orders ADD COLUMN status_new TEXT NOT NULL DEFAULT 'pending'; On PostgreSQL before v11 this rewrites 500M rows under ACCESS EXCLUSIVE; even on modern versions the default is evaluated per row. At 100k rows/s rewrite speed that is ~83 minutes of exclusive lock; with a 30 s lock_timeout the statement fails — but if the timeout was not set, the queue behind it grows for 83 minutes and the table is effectively down for an hour plus. The expand-contract path: (1) ADD COLUMN status_new TEXT — metadata-only, milliseconds, old code unaffected; (2) deploy dual-write; (3) backfill in 50k-row batches with 50 ms sleeps — each batch is ~0.5 s of work every ~1 s, yielding ~33% duty cycle, finishing 500M rows in ~2.8 hours while replica lag stays under 10 s; (4) ADD CONSTRAINT ... CHECK (status_new IS NOT NULL) NOT VALID then VALIDATE CONSTRAINT (both non-blocking patterns), then a final deploy stops writing the old column and a cleanup migration drops it. Same end state, zero downtime, four shippable units — the arithmetic of the duty cycle is the whole design.

Design Levers

• Migration gates in CI. Run migrations against a prod-sized snapshot, hard-timeout every statement (lock_timeout, statement_timeout), lint for known-unsafe operations (plain CREATE INDEX, type changes on large tables, NOT NULL with rewrites), and require an annotated rollout plan for anything touching tables over a size threshold.
• Backward-compatible steps as the only steps. Every migration lands in the expand phase: additive, nullable, defaulted, non-validating first, validated later — and the contract phase is scheduled by evidence (no reader of the old column for N days) rather than by calendar.
• Lock budgets and batching. Every long operation is chunked with sleeps, monitored against replica lag, and resumable from checkpoints; “migration duration” is a throughput budget like any other background job.
• Dual-write with reconciliation, not trust. During the window, a periodic job compares old and new representations and alerts on divergence — dual-write bugs (the classic: two writes not in one transaction) are found by diff, not by users.
• Treat the fleet as the unit of consistency. The rollout plan enumerates every process that reads or writes the table — services, workers, cron, ad-hoc scripts, dashboards — because the migration’s compatibility window is defined by the slowest of them.
• Use the tooling that moves the rewrite out of the lock. When a table truly must be rebuilt, the specialized tools do it without holding the exclusive lock: gh-ost and pt-online-schema-change for MySQL shadow-copy the table while triggers or binlog capture changes, then swap atomically; PostgreSQL’s logical replication and pg_repack-style tools offer the same shadow-copy-then-cutover shape. The shared pattern is the one this page already teaches — build the new state beside the old, sync incrementally, cut over in one atomic step — applied by the tool so that your migration script is small and your cutover is milliseconds of lock instead of hours.

Interview Questions

1. Your service needs to change a column from INT to BIGINT with zero downtime. Give the full expand-contract plan, including how dual-write handles in-flight rows and when the old column can be dropped.
2. Why is a schema “rollback” after a dropped column usually impossible, and what must be true of every earlier step for the deploy to still be rollback-safe?
3. An ALTER TABLE on a large table froze all writes for 40 minutes. Reconstruct the propagation chain from lock acquisition to pool exhaustion, and name the three settings that would have prevented it.
4. Where exactly do expand-contract and two-phase commit agree and diverge? Explain why divergence forces the migration protocol to be forward-only.
5. A teammate proposes running the backfill as one UPDATE over 500M rows “to finish faster.” Give the arithmetic and the failure chain that refutes them, and the design of the batched alternative.

Key Takeaways

• A schema migration plus a code deploy is one distributed consistency problem: the invariant is that every intermediate state serves every process version alive, which is why expand-contract decomposes changes into independently deployable, forward-only steps.
• Expand-contract is prepare/converge/commit without an abort branch — compensation and forward data-restoration replace undo, so the contract phase must be planned before the expand phase ships.
• Failure propagates backwards in time: old processes fail on new schemas first, and one exclusive lock cascades through connection pools into unrelated tables — local statement, global outage.
• Backfills are production workloads: batch size, sleep, duty cycle, and replica-lag budgets are the design; an unthrottled backfill is a self-inflicted incident that also breaks dual-write’s own consistency.
• Order is a cross-service partial order that no build system sees; migration gates, lock budgets, and enumerated reader inventories are how humans encode it.

Cross-References

• ./event-sourcing-wal.md — Event Sourcing and the WAL — the log as the source of truth; migrations are rewrites of the fold function the log feeds.
• ./transactions-concurrency-control.md — Transactions and Concurrency Control — the lock and isolation mechanics that DDL borrows and that DDL-duration amplifies.
• ./dependency-resolution.md — Dependency Resolution — migration ordering as a topological sort over an implicit cross-service DAG.
• ../followup-tree/acid-transactions.md — ACID Transactions — question-escalation chains for the transactional foundations this page extends.
• ../followup-tree/zero-downtime-migrations.md — Zero-Downtime Migrations — the follow-up drill-down for expand-contract and backfill mechanics.
• ../../dbms/transactions/distributed.md — Distributed Transactions — the two-phase commit protocol expand-contract is shaped by.