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):