Chapter 22 — Key Takeaways

The big idea

Schemas always change; the professional approach is the migration — a versioned, ordered, reviewed, reversible script in source control, applied identically to every environment by a tool. Migrations make schema change boring — which in production is exactly the goal.

Migrations

  • Each migration: an up (apply) and ideally a down (revert), numbered/ordered, tracked in a schema_migrations table.
  • Properties: version-controlled, ordered/repeatable, reviewed, reversible → no drift, no untracked prod edits.
  • Tools: Flyway (SQL), Liquibase (changelogs), Alembic (Python/SQLAlchemy), or framework built-ins.

Safe vs. dangerous changes

Safe (brief lock) Dangerous (lock-heavy / irreversible)
add nullable column / new table drop column (data gone)
CREATE INDEX CONCURRENTLY rename column/table (breaks running code)
add constraint NOT VALID then VALIDATE change column type (table rewrite)
add NOT NULL / validating constraint (scan)

Expand-contract (zero-downtime breaking changes)

Schema and code deploy at different times, so both must work during the transition. Three phases:

  1. Expand — add the new structure alongside the old (additive, safe).
  2. Migrate — backfill + dual-write (write both, read new); old still works.
  3. Contract — remove the old once nothing references it (safe).

Handles renames, type changes, column splits, table extractions — no moment where schema and code disagree. (Case Study 1.)

Data migrations

  • Batch large backfills (N rows/commit) — avoid long locks and giant transactions. (Case Study 2.)
  • Make them idempotent/re-runnable (WHERE new_col IS NULL).
  • Separate add-column / backfill / enforce-NOT NULL into different steps.

Testing & safety

  • Test on production-sized data, including the down path (instant on 100 rows ≠ on 100M).
  • Use transactions where possible (most DDL is transactional; CONCURRENTLY is not).
  • Set lock_timeout so a blocked migration fails fast instead of freezing the app.
  • Plan rollback: a down migration, or restore-from-backup for irreversible drops.

The recurring disasters this prevents

Ad-hoc prod edits (missing WHERE, Ch. 13), locking ALTERs at peak (Ch. 14), env drift (Ch. 2), and unbatched mega-updates (Case Study 2).

You can now…

  • ☐ Write versioned up/down migrations and explain why they beat ad-hoc edits.
  • ☐ Classify changes as safe vs. dangerous on a live table.
  • ☐ Apply expand-contract for zero-downtime breaking changes.
  • ☐ Write batched, re-runnable data migrations.
  • ☐ Test and plan rollback; use lock_timeout.

Looking ahead — Part IV

Database Design is complete. Your schema is well-designed and safely evolvable. Chapter 23 — Indexing opens Part IV (Performance & Internals): the single most impactful performance tool. Theme #5 — performance is basic competence — takes over.

One sentence to carry forward: Treat every schema change as a versioned, tested migration, make breaking changes backward-compatible with expand-contract, and batch your data migrations — and production schema change becomes routine instead of terrifying.