Schema Evolution

Schema Evolution

Concept of the skill

Schema evolution is the discipline of changing a database schema over time while deployed application code keeps serving traffic. The unit of work is not one SQL statement; it is a compatibility-preserving path from the current schema to the target schema across one or more deploys. The central technique is expand/contract (Ambler & Sadalage 2006; also called Fowler's parallel change): three phases. Phase 1 EXPAND — add the new column/table/index/view/ghost table; old shape unchanged; old code continues working; new code can use the new shape if deployed; rollback = drop the new shape. Phase 2 MIGRATE (multi-deploy) — deploy code that dual-writes; backfill existing data (batched, resumable); deploy code that dual-reads with fallback to old; verify production traffic on new with a consistency check; deploy code that single-reads new; rollback = revert code, old shape still present. Phase 3 CONTRACT (normally one-way) — drop old column/table/index; no code references old shape; old-shape application rollback is gone unless explicitly preserved by a retained table, a snapshot/restore, or a managed revert window (Vitess/PlanetScale keep the old table for a bounded window; Postgres DROP COLUMN is logically invisible but not immediately physically reclaimed).

Replaces "schedule a maintenance window for the migration" with continuous evolution while serving traffic. Solves the problem that schema and deployed code are co-evolving systems — schema-first can break old code, code-first can assume a shape that has not landed, and rollback can reintroduce old code after a new schema already exists — and that desynchronization is the primary danger. Expand/contract is the protocol that keeps both sides compatible during transition: additively introduce the new shape, migrate behavior and data with observation, then contract the old; at every intermediate point, both old and new code can run against both old and new schema. That intermediate state requires the two named guarantees at once (DDIA ch.4): backward compatibility (new code reads old-written data) and forward compatibility (old code reads/ignores new-written data). Sub-purposes: (1) sequence migrations and deploys safely — the ordering across migrations and deploys, the compatibility envelope each intermediate state must satisfy; (2) preserve rollback through expand and migrate, accepting irreversibility only at contract; (3) avoid the common-incident pattern of premature contract — dropping the old shape before all code and data have migrated; (4) make constraint-tightening safe via the right per-constraint mechanism (NOT VALID + VALIDATE for FK/CHECK/NOT NULL; a concurrent unique index for UNIQUE); (5) keep the lock-acquisition step itself from taking down the app via a bounded acquisition budget (Postgres lock_timeout + retry against the FIFO lock queue; the engine-equivalent online-change control elsewhere); and (6) give the dual-write/dual-read transition a named source of truth plus a measured production consistency check so it is not vague duplication.

Distinct from database-migration, which owns the mechanics of applying one concrete migration — the ALTER TABLE statement itself, its lock profile and transaction wrapping, CONCURRENTLY indexes, the NOT VALID + VALIDATE constraint pattern, batched backfill implementation, the bounded lock-acquisition wrapper (lock_timeout + retry, or engine equivalent) on a single DDL statement, unpooled migration connections, and an engine's ghost-table cutover (gh-ost / pt-osc). This skill owns the sequencing across migrations and application deploys, the multi-step expand/migrate/contract discipline, and the boundary gates between phases. Distinct from entity-relationship-modeling, which owns the design of a schema at a point in time — this skill owns how that schema changes between points in time; the two compose (entity-relationship-modeling decides the target shape; this skill decides the safe path from current to target). Distinct from indexing-strategy, which owns which indexes the database has — adding/dropping an index is one type of schema change governed by this skill's discipline (CREATE INDEX CONCURRENTLY is part of the expand vocabulary). Distinct from transaction-isolation (runtime guarantee semantics), transaction-isolation (concurrency level), query-optimization (single-query tuning), and sharding-strategy (cross-node partitioning) — all of which can constrain a plan but answer different questions. Tools such as pgroll and Reshape automate this skill's discipline (view-based multi-version expand/contract); they do not replace the need to understand it — they are the mechanism database-migration executes, sequenced by the discipline this skill teaches. Schema evolution is to a database what stage carpentry is to a Broadway musical — the show does not stop; you do not bolt a new staircase to the stage during the second act; you build the new staircase upstage while the old staircase serves the cast (expand), gradually rehearse the cast to use the new one while the old still works (migrate), and only after every performer has memorized the new route do you remove the old staircase (contract). Removing the old before everyone has migrated is the production-incident equivalent of a missed cue; swinging a heavy set piece across the stage mid-scene without warning the cast — even briefly — is the lock-queue freeze. The wrong mental model is that a schema change is a single migration — write the ALTER TABLE, deploy it, done. It is not, except for the small class of single-step app-safe additive changes (add nullable column, add table, add index with the engine's online build, change DEFAULT). Drop column, rename, type change, add NOT NULL without default, add FK, add UNIQUE constraint — each requires expand/migrate/contract spanning multiple deploys over days or weeks. Adjacent misconceptions: (a) that "online DDL" or "the migration linter passed" means the change is safe — online DDL reduces database blocking and a linter inspects a migration file; neither proves that old and new application versions can both run, that background workers have switched, that reads no longer depend on old columns, or that rollback is still possible. (b) That "single-step safe" means no production lock risk — even a metadata-only ALTER acquires an ACCESS EXCLUSIVE lock, and because lock acquisition is a FIFO queue, a DDL that waits behind one long-running transaction blocks every read and write queued behind it; the fix is a bounded lock-acquisition budget (Postgres lock_timeout, often sub-2-second, sometimes 50ms, so the DDL aborts instead of blocking the queue, plus an automatic retry-with-backoff because Postgres never auto-retries a timed-out statement). (c) That the Postgres recipe is universal — MySQL/InnoDB uses ALGORITHM=INSTANT/INPLACE/COPY with LOCK clauses and has its own INSTANT limitations; gh-ost and pt-online-schema-change copy into a ghost table and cut over with an atomic rename; the portable invariant is "use the engine's online-change mechanism with a bounded lock window," not "run lock_timeout." (d) That a database-specific physical improvement turns a destructive change into a one-step app-compatible change — MySQL INSTANT drop, Postgres fast metadata drop-column, view-based multi-version tooling, or ghost-table cutover may reduce physical DDL cost while still requiring contract evidence before the old application-visible shape disappears. (e) That all constraint-tightening uses one mechanism — FK/CHECK/NOT NULL use NOT VALID then VALIDATE to avoid the validation scan-lock, but a UNIQUE constraint is NOT created NOT VALID: prove duplicate absence, CREATE UNIQUE INDEX CONCURRENTLY (handling the invalid-index-on-failure recovery), then ADD CONSTRAINT ... USING INDEX where supported. (f) That contract-readiness is obvious — "we'll know when we're ready" is not a gate; contract requires runtime evidence that every code path (web tier, background workers, queue/stream consumers, cron jobs, batch ETL, analytics/reporting queries, replicas, materialized views, downstream services, ad-hoc operator scripts) has stopped referencing the old shape, plus a data-verified backfill and an explicit decision that rollback through the old shape is no longer required. (g) That rollback is always available — expand and migrate preserve rollback; contract is the irreversible boundary unless you deliberately keep a revert path (retained old table, snapshot/restore, or a managed revert window like Vitess/PlanetScale deploy requests); "I dropped it, it's instantly unrecoverable" and "rollback is always free" are both wrong — the truth is "old-shape application rollback is gone unless explicitly preserved." (h) That dual-write means "write to both and hope" — pick a source of truth, dual-write to both, backfill, dual-read comparing new against the source of truth with a production mismatch metric and alert, flip the source of truth only when the mismatch rate is acceptably low, rolled out incrementally (Stripe's online-migration discipline). (i) That backfill can run in one statement — a single UPDATE on a large hot table locks it, fills WAL, and times out; batched-and-resumable is the production discipline. (j) That a zero-downtime migration tool removes the need to understand this — pgroll/Reshape automate expand/contract via database views, but you still operate, debug, and trust them by knowing the sequence they run. (k) That deploy ordering is "obvious" — migration-first vs code-first is a per-change decision; expand/contract makes the ordering explicit and survivable at every intermediate point, and getting it wrong is how deploys break.

Coverage

The discipline of changing a database schema over time without breaking deployed application code. Covers the expand/contract (parallel change) pattern as the foundational technique; the backwards-and-forwards compatibility envelope each intermediate state must satisfy; the catalog of schema-change types and their safety profiles (additive, constraint-tightening, destructive, renaming, type-change); the distinction between physical online change and application compatibility; the lock-acquisition hazard that makes a bounded lock-acquisition budget (lock_timeout + retry in Postgres, the engine equivalent elsewhere) mandatory even for metadata-only changes; the cross-engine online-change mechanisms (Postgres CONCURRENTLY / NOT VALID, MySQL/InnoDB INSTANT/INPLACE/COPY Online DDL, gh-ost / pt-osc ghost-table cutover, Vitess/PlanetScale deploy requests and revert windows); the backfill strategies (no-backfill, batched, background, lazy/read-repair, dual-write-only); the dual-write / dual-read transition patterns with named source-of-truth and production consistency-check rules; the deploy-ordering rules (migration-first vs code-first vs coordinated multi-step); the boundary gates between phases (enter migrate, switch reads, enter contract); the view-based multi-version tools (pgroll, Reshape) and the migration-lint enforcement layer (Strong Migrations, Squawk, Atlas) that encode the discipline; and the relationship to the underlying database-migration tooling that executes individual steps.

Philosophy of the skill

Schema and deployed code are co-evolving systems. The danger is desynchronization — schema changes break running code; code changes assume a schema that hasn't been deployed; rollback can put old code back into contact with a new schema. Expand/contract is the protocol that keeps every intermediate state survivable: additively introduce the new shape, migrate, then contract the old.

The compatibility envelope has two named directions, and the middle state of every expand/contract requires both at once (Kleppmann, Designing Data-Intensive Applications, ch. 4):

• Backward compatibility — newer code can read data that older code wrote.
• Forward compatibility — older code can read (or safely ignore) data that newer code wrote.

Additive-first works precisely because it satisfies both: old code ignores the new column (forward compat), and new code falls back to the old column until backfill completes (backward compat). Drop-first breaks forward compatibility instantly — old code that still references the dropped column errors the moment the migration lands.

The discipline is not in the individual ALTER TABLE statement (that's database-migration's concern). It is in the sequencing across migrations and deploys, the compatibility envelope at every intermediate point, and the rollback discipline that keeps the system recoverable when a step fails. A plan is safe only if it names which code versions may run against which schema versions at each point, and what rollback means before and after each irreversible step.

The most important distinction to hold onto: physical online change is not application compatibility. A database may add, drop, rename, or swap structures without long write locks, but that does not prove the application can tolerate the change. Database online DDL and migration linters are enforcement aids; expand/contract is the design discipline. The most consequential moments are the boundaries — entering migrate (the dual-write/dual-read transition), switching reads to the new shape, and entering contract (the irreversible drop). Knowing the explicit criteria for crossing each boundary is the operational hygiene that separates evolved systems from broken ones.

Compatibility Envelope

Plan each schema evolution as a sequence of compatible states. The envelope is valid only if old and new application versions can overlap during rolling deploy, canary, retry, worker drain, and rollback.

Backward compatibility means new code can read or reconstruct the old shape until data and DDL have caught up. Forward compatibility means old code can ignore or survive the expanded/new shape when a migration lands before all code is replaced, or when rollback reintroduces old code. When either direction is false, the plan needs another expand/migrate step — not a more carefully timed deploy.

The Expand / Migrate / Contract Phases

   ┌────────────────────────────────────────┐
   │ Phase 1: EXPAND                         │
   │ • Add new column / table / index / view │
   │   / ghost table — old shape unchanged   │
   │ • Old code continues working unchanged  │
   │ • New code can use new shape if deployed│
   │ • Rollback: drop / stop using new shape │
   └────────────────────────────────────────┘
                    │
                    ▼
   ┌────────────────────────────────────────┐
   │ Phase 2: MIGRATE (multi-deploy)         │
   │ • Deploy code that dual-writes          │
   │ • Backfill existing data (batched)      │
   │ • Deploy code that dual-reads (new w/    │
   │   fallback to old) + consistency check  │
   │ • Verify production traffic on new       │
   │ • Deploy code that single-reads new      │
   │ • Rollback: revert code; old shape OK   │
   └────────────────────────────────────────┘
                    │
                    ▼
   ┌────────────────────────────────────────┐
   │ Phase 3: CONTRACT (normally one-way)    │
   │ • Drop old column / table / index / view│
   │ • No code references old shape          │
   │ • Rollback: old-shape app rollback gone │
   │   unless preserved (retained table /    │
   │   snapshot / managed revert window)     │
   └────────────────────────────────────────┘

The time between expand-complete and contract-start is normally days or weeks, not minutes. The wait is not ceremony; it is how rolling deploys, scheduled jobs, long-running workers, caches, read replicas, analytics jobs, and rollback paths finish moving.

Change-Type Safety Matrix

The matrix is a starting point, not a waiver. Always verify the target engine version, table size, lock profile, replication behavior, migration-runner transaction behavior, and application-version overlap.

Three orthogonal axes hide in this matrix — do not collapse them. (1) The "single-step app-safe?" column is the application-compatibility axis: it answers "can this change be applied without breaking a currently-deployed code version?" — NOT "does it rewrite the table?" and NOT "does it avoid a lock?". An additive nullable column is app-safe because no deployed code breaks; a drop-column is not, because deployed code still references it. (2) Physical rewrite is a separate axis: some app-safe changes still rewrite or scan the table, and conversely an engine can make a change physically instant (e.g. MySQL ALGORITHM=INSTANT drop/rename) while it remains application-destructive — so "instant" at the storage layer never licenses skipping expand/contract. (3) Lock acquisition is a third axis: every entry above, even a metadata-only one, must still acquire its lock, and acquiring it is its own coordination problem (see The Lock Queue) regardless of rewrite cost. On mechanism: constraint-tightening is not one recipe — FK / CHECK / NOT NULL use NOT VALID + VALIDATE, but a UNIQUE constraint is built via a concurrent unique index, not NOT VALID. The matrix keeps these distinctions on purpose.

Two common constraint corrections worth restating:

• Postgres NOT VALID applies to foreign-key, CHECK, and (on supporting versions) not-null constraints; it is not the online path for UNIQUE.
• For Postgres UNIQUE, first prove duplicate absence, then CREATE UNIQUE INDEX CONCURRENTLY, handle the invalid-index failure if the build aborts, and attach it with ALTER TABLE ... ADD CONSTRAINT ... USING INDEX where supported.

The Lock Queue — Why a "Safe" DDL Can Still Freeze the App

Portable invariant: every schema change must acquire its lock under a bounded acquisition budget — fail fast and retry rather than block the queue indefinitely. Each engine expresses this differently; below is the Postgres instance, then the cross-engine equivalents.

A change marked single-step app-safe avoids rewriting the table, but it still acquires an ACCESS EXCLUSIVE lock for a metadata edit that should take a millisecond. The hazard is not the edit — it is acquiring the lock:

• Lock acquisition is a FIFO queue. A plain SELECT takes ACCESS SHARE; ACCESS EXCLUSIVE conflicts with it, and many ALTER TABLE subforms request ACCESS EXCLUSIVE unless a subform documents a weaker lock. A DDL waiting for ACCESS EXCLUSIVE does not jump ahead; it waits behind whatever currently holds a conflicting lock. While it waits, every later query that needs a conflicting lock — including ordinary SELECTs — queues up behind the DDL. The outage is caused by the waiting lock request, not by the eventual metadata change.
• The trigger is a single long transaction. A slow analytics query, a forgotten idle in transaction connection, or a long-running report holds its lock; the "instant" ALTER stalls behind it; the whole application stalls behind the ALTER. The migration never rewrote anything — it just sat in the queue and took the queue down with it.

Controls (these belong to the single-statement mechanics that database-migration owns, but always applying them is part of evolving a live schema):

1. Put a low lock_timeout (or the engine's lock-acquisition timeout) on every production DDL — commonly under 2 seconds, sometimes as aggressive as 50 ms — including "metadata-only" changes. If the DDL cannot get its lock quickly, it aborts instead of blocking the queue. Aborting a deploy is recoverable; freezing production is an incident.
2. Retry timed-out DDL from the migration runner with backoff and jitter. Postgres aborts the timed-out statement and does not auto-retry — retrying is the runner's job. Once the blocking transaction finishes, the retried DDL usually succeeds in a millisecond or two.
3. Don't hide several unrelated DDL statements inside one broad transaction. Split DDL by lock profile so a timed-out statement is independently retryable and does not hold locks open for the others; or explicitly acquire all required locks at the start with the same low timeout so failure happens before work has been done.
4. Keep long transactions out of the migration window. The classic culprit is idle in transaction; also long analytical reads, long replica snapshots, and worker transactions. Detect them before deploying and postpone or terminate per the team's production policy.

This is why tools like Strong Migrations, pgroll, and gh-ost wrap DDL in a bounded lock window by default — the lock queue, not the rewrite, is the most common surprise outage in an otherwise "safe" change.

Cross-Engine Reality — The Invariant Is Portable, the Mechanism Is Not

The expand/contract discipline is engine-agnostic, but the online-change mechanism that executes each step is engine-specific. Do not assume the Postgres recipe transfers verbatim.

The portable rule: name the engine's online-change mechanism and its bounded-lock control before sequencing the change. The expand/migrate/contract phases sit above all of these; the engine decides how each phase's DDL runs safely under live traffic.

PostgreSQL reality checks (depth)

• ALTER TABLE acquires ACCESS EXCLUSIVE unless a subform documents a weaker lock; multiple subcommands use the strictest lock among them.
• NOT VALID lets foreign-key, CHECK, and (on supporting versions) not-null constraints be added without immediately scanning old rows; VALIDATE CONSTRAINT verifies existing rows later under a less disruptive lock.
• SET NOT NULL historically required a full scan or a validated proof that no NULLs exist. Current Postgres supports not-null constraints with NOT VALID; 12–17 deployments still need the CHECK-proof pattern before SET NOT NULL.
• UNIQUE and PRIMARY KEY do not use NOT VALID; use the concurrent unique-index path, then attach with USING INDEX where supported. A concurrent unique build can enforce uniqueness against other transactions before the index is available for reads; a failed build can leave an invalid unique index still imposing write overhead — recovery is drop/retry or reindex concurrently.

PostgreSQL view-based multi-version tools

• pgroll and Reshape automate parts of expand/contract by exposing old and new schema versions at once through schemas/views over the physical tables.
• For breaking column changes they create the new physical shape, backfill/transform values, and use triggers or generated up/down expressions so writes through either version stay synchronized for the migration window.
• They decouple app rollout from the database change: old app versions connect to the old view/schema, new ones to the new; rollback can remove the new version while the old still exists.
• Completion remains contract. Do not remove the old schema version until runtime evidence shows no clients, workers, scripts, or rollback paths still depend on it.

Dual-Write / Dual-Read — The Transition Discipline (not "write to both and hope")

The migrate phase is where most non-trivial changes actually live, and "dual-write" is not vague duplication. Dual-writing is a transition tool, not a permanent architecture. The production-grade pattern (Stripe's Online Migrations at Scale) has named rules:

1. Pick a source of truth. At the start of migrate, the old shape is the source of truth; the new shape is being populated and is not yet trusted. Name the source-of-truth rule before implementation:
   • Old primary: write old first, derive new from old, read old fallback until new coverage is proven.
   • New primary: write new first, derive old for rollback/old readers, switch reads to new after consistency passes.
   • Adapter primary: a single write-through function updates both shapes so callers do not fork logic.
2. Dual-write. New code writes to both old and new shapes on every mutation, keeping them in lock-step for all new rows.
3. Backfill existing rows into the new shape (batched, resumable — see Backfill Modes).
4. Dual-read with verification. Read from the new shape but compare against the source of truth (old shape). Give dual-read an explicit fallback order and a metric for fallback usage (e.g. read new column; if null/missing, fall back to old and emit a counter — the counter must trend to zero before contract). Log discrepancies; emit a production consistency metric (mismatch rate); alert when it exceeds a threshold. This is the gate, not a guess.
5. Flip the source of truth to the new shape only once the mismatch rate is acceptably low and stable. Run dark reads / production experiments that compute old and new results while serving the old result first, and roll the flip out incrementally (a percentage of traffic / by tenant / shard / canary) so a defect is caught on a slice, not the whole fleet.
6. Stop dual-writing the old shape once the new shape is the source of truth and reads are exclusively new — this is the precondition for contract.

Never let silent divergence accumulate: add consistency checks that compare old/new values for changed rows and sampled historical rows; for high-volume systems, use sampled checks plus targeted full checks on high-risk partitions. The discipline that distinguishes this from "hope": a named source of truth at every moment, a measured consistency check in production, and incremental rollout — not a single cutover deploy.

Backfill Modes

Backfills should be idempotent, bounded, observable, resumable, and safe to pause. A single unbounded UPDATE on a large hot table is a production operation, not just a migration line — it locks the table, fills WAL, and times out.

The Deploy-Ordering Rule

The deploy-ordering choice is a design decision per change. The expand/contract pattern's value is that it makes the ordering explicit and survivable at every intermediate point. If the plan depends on "the deploy will happen immediately after the migration," the plan is not robust enough for production.

Tooling — Helpful, Not Sufficient (Lint, Online-Rewrite, and Multi-Version)

The expand/contract discipline can be executed by hand (the matrix above) or delegated to tooling that encodes it. There are two generations of tools, and they automate different amounts of the work:

pgroll's model is the clearest expression of this skill's thesis made executable: the compatibility window becomes a first-class, tool-managed object instead of a hand-coordinated sequence of deploys. The expand → migrate → contract phases still exist; pgroll just owns the triggers, the backfill, and the view swap, and lets old and new application deployments target their own schema version simultaneously.

Caveat — tooling changes WHO runs expand/contract, not WHETHER it happens. Linters answer "did this migration file contain known hazards?"; online-rewrite and ghost-table tools answer "can this physical change run with less blocking?"; multi-version tools automate the steps. None of them answers "can every deployed application version, worker, rollback, and consumer survive every intermediate state?" — that remains schema evolution's job. Understanding the manual sequence is what lets you operate, debug, and trust these tools — and what tells you when a change is outside their safe envelope. pgroll and Reshape are Postgres-specific; the principle is portable across every relational store.

Boundary Gates — The Explicit Criteria for Crossing Each Phase

Every phase boundary has an explicit gate. "We'll know when we're ready" is not a gate.

Gate 1 — Enter Migrate (start dual-write / backfill)

• The new shape exists in production (expand is complete and verified).
• The new shape is additive and ignorable — currently-deployed old code is unaffected by its presence (forward compatibility holds).
• Old code still works against the expanded schema; new code can be deployed without requiring contract.
• Code that dual-writes to both shapes is ready to deploy; the source of truth is named (old shape, at this point).
• Read/write adapters or feature flags have explicit fallback behavior.
• A backfill job exists that is batched and resumable, with progress / error / consistency metrics.
• Rollback is still trivial: dropping the new shape (or reverting the app) restores the prior state.

Gate 2 — Switch Reads to the New Shape

• New writes are reaching the new shape for live traffic.
• Backfill is complete and verified — no existing rows have inconsistent state (or the missing-data policy is explicit).
• Dual-read verification has run in production and the mismatch rate (new vs source-of-truth old) is acceptably low and stable over a named observation window.
• Dark reads / read experiments have compared old/new results in production without serving bad new results.
• The read switch is rolled out incrementally (percentage / tenant / shard / canary), not as a single fleet-wide cutover; fallback reads are observable and trending to zero.
• Observability for the new read path is in place; a regression would be visible.
• Rollback is still available: revert the reading code; the old shape is intact and still written.

Gate 3 — Enter Contract (drop the old shape)

• All deployed code reads exclusively from the new shape (verified via code inspection or runtime monitoring).
• All deployed code writes exclusively to the new shape (dual-write to the old shape has stopped, or old writes are demonstrably redundant and ready to stop).
• Every non-web code path is accounted for, not just the request-serving app. Background workers, queue/stream consumers, cron and scheduled jobs, batch ETL, analytics and reporting queries, replicas/read-replicas, materialized views, downstream services reading the same database, and ad-hoc operator sc

…(truncated)