A database schema lives longer than any application version that touches it. Tables and columns accrue over years; migrations must apply to running production systems without downtime and without revealing latent bugs months later. The discipline is small but absolute: never break, always reverse, never assume the old code is gone.

This topic covers the moves that keep production databases evolvable.

Why Schema Migrations Are Hard

Three properties combine:

1. Zero downtime — the schema change must apply while traffic flows. Most DDL takes locks; most locks block traffic.
2. Code overlap — old and new application versions run concurrently during a deploy. The schema must be valid for both.
3. No undo on production data — once a column is dropped, the data is gone. Rollforward can resume; rollback cannot restore.

A migration that violates any of these becomes an incident. The discipline below is what makes the three tractable.

Expand / Contract: The Safe Sequence

The safe shape for any non-trivial schema change is expand → migrate → cut over → contract, often called Parallel Change in Martin Fowler’s vocabulary:

   ──── expand ────────────── migrate ──── cut over ──── contract ────→
        add new column        copy old → new  switch writes  remove old

The property gained: at every step, both old and new application code can run safely. A deploy that fails mid-sequence finds the system in a stable state; the sequence continues or rolls back without breaking row-level consistency.

Backfills Without Timeouts

A backfill (“copy the value of legacy_user.email into users.email for all 50 million rows”) is the most common migration step writers get wrong — they issue:

UPDATE users SET email = (SELECT email FROM legacy_user WHERE legacy_user.id = users.legacy_id);

This single statement takes an exclusive lock for the duration of the update; on 50M rows, that can be hours, and the table is unresponsive the entire time. Three correct approaches:

1. Chunked updates — process in batches of 1,000 to 10,000 rows, with LIMIT plus an indexed cursor (e.g., WHERE id BETWEEN x AND x+9999), with a sleep between batches. Locks are short; reads remain responsive.
2. Trigger-based backfill — for high-write-rate tables, install triggers on the old column that synchronously update the new; the backfill then only handles historical rows. Catches ongoing writes; runs to completion of the historical chunk.
3. Background job — a worker service chews through rows independently of the database’s transaction model. Scales for hundreds of millions of rows; observability is critical (rate of progress, rows completed/skipped).

The discipline: never run a query that will lock a large table for longer than your service’s health-check timeout.

Locks the Database Takes Silently

DDL doesn’t always take a LOCK TABLE in the noisy sense — but it’s rarely free. Common gotchas:

Two rules of thumb:

1. Online ops are a flag, not a default. Specify the online variant explicitly (LOCK=NONE, ALGORITHM=INPLACE or CONCURRENTLY) — the database will reject the operation if it can’t be done online, which is the desired outcome.
2. Type changes are full rewrites. INT → BIGINT, VARCHAR(50) → VARCHAR(100), TEXT → JSONB — assume the table is rewritten, every row is touched, the operation is as expensive as the backfill pattern.

Online Schema Change Tooling

For very large tables, even online DDL is too coarse — it consumes too much disk I/O / CPU / undo log to do safely during peak. Three families of tools:

The pattern each follows: never operate directly on the live table; use a shadow copy with continuous sync, swap at the end in a near-instant metadata operation. The downside is that they consume ~2x disk (live + shadow) for the duration of the migration.

Reversibility as a Design Constraint

A migration that adds a column is reversible (drop it). A migration that drops a column without a recent backup is not reversible. A migration that changes a column type is reversible if the new type round-trips to the old; irreversible otherwise.

The discipline: every migration’s “rollback” must be a separate, written migration — not a “we’ll figure it out if needed”. Before merging any migration that drops or transforms data, the corresponding contract must write a restore-migration script.

This is the deepest motivation for expand/contract: Contract — the irreversible step — is a separate release. A failed deploy’s rollback skates back to the start of Expand safely; Contract only fires once you’re confident nobody needs the old shape.

Practice Trajectory

1. Pick a recent schema migration in your project. Decompose it as expand/contract — what would each step have looked like? What got skipped?
2. Estimate lock time for ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP NULL. Specify the MySQL / Postgres migration syntax that takes the minimal lock.
3. Sketch a backfill for UPDATE events SET user_id = (SELECT user_id FROM sessions WHERE sessions.id = events.session_id) on 100M event rows. Identify the chunk key, batch size, sleep interval, and how to verify completion.
4. Write the deploy runbook for: “rename customers.email to customers.primary_email.” Identify the four releases (expand, dual-write, cut over, contract) and the rollback at each.
5. Audit a recent irreversible migration (a column drop or type change). What backup or written restore-migration would have made it reversible?

When It’s the Right Tool