Schema Migrations

Why Migrations Are Special Deployments

 deploys roll back in seconds. SCHEMA changes entangle:

 - old code + new schema must coexist DURING rolling deploys
 - migrations run ONCE (not per-instance!) — different machinery
 - large-table operations take time measured in coffee-to-incident
 - rollback often can't "un-migrate" data already transformed

 so migration discipline = keeping CODE and SCHEMA versions
 compatible across every transition moment.

The Migration Lifecycle

 tooling first: versioned, ordered, tracked migrations
   (flyway/liquibase/alembic/rails/golang-migrate class):
   
   V17__add_orders_tax.sql applied exactly once, recorded.

 per-migration checklist:
 □ tested against PRODUCTION-SIZED data (staging clones!)
 □ lock impact analyzed: does it rewrite or block? (online-
   schema-changes lesson's taxonomy applies verbatim)
 □ rollback STORY explicit: down-script possible? or
   forward-fix-only accepted knowingly?
 □ deploy ORDER coordinated with app release that uses it

The Compatibility Dance With Deploys

 the safe sequence for any schema+code change:

 1. EXPAND: additive schema change (nullable column, new table)
    → old code unaffected ✓ deployable safely
 2. DEPLOY: new code reading/writing BOTH shapes as needed
 3. BACKFILL: populate new columns for existing rows (batched!)
 4. SWITCH: fully onto new shape behind flag if risky
 5. CONTRACT (LATER): drop old column after observation window

 [schema]──expand────────────►──────────contract──►
 [code v1]──ok──┐┌─v2 dual-write/read─┐┌─v3 clean─┐

 jumping expand+contract in one deploy = classic outage:
 new code + missing column / old code + dropped column.

Operational Realities

Concern	Practice
huge table backfill	batched id-cursor jobs; watch replica lag
long locks	online tools / engine-native instant DDL
multi-service schemas	owner service migrates; consumers via contracts only
failed migration	tool marks state; fix-forward script; never half-applied ambiguity
environments drift	migrations are the ONLY path; no hand-DDL anywhere

 the drift rule deserves teeth:
 production databases accept DDL from the migration system
 ALONE. console heroes create untracked state that detonates
 on the next environment rebuild.

Interview Framing

“Add a required field to a billion-row orders table” scored answer: expand→deploy→backfill(batched)→switch→contract sequence drawn, migration-tooling mechanics named, prod-sized testing + lock analysis included, contract-phase-delayed discipline emphasized. This question appears in every serious interview because it fuses deployment safety with database craft — walk the five steps like choreography, not theory.