Safe SQL Schema Migrations with the Expand-Contract Pattern

Database migrations are versioned, scripted schema changes, and the hard part isn't the SQL syntax but safely applying it to live, stateful data that concurrent old and new application code both depend on.

Overview

A schema change is not atomic with an application deploy; during rolling deploys, old and new code run simultaneously against the same database, so a breaking migration creates a real outage window.
Migration tooling (Flyway, Liquibase, Rails, Django, Prisma) all share the same underlying architecture: ordered migration files plus a schema history table inside the target database itself.
The single most important pattern for avoiding downtime is expand-contract (parallel change): add new structure alongside old, dual-write/dual-read during a transition, then remove the old structure only once nothing depends on it.
Schema migrations (structural) and data migrations (row-touching) look similar in code but have very different risk profiles - data migrations on large tables can cause lock contention and replication lag.
Database engine differences matter operationally: PostgreSQL supports transactional DDL (a failed migration rolls back cleanly); MySQL historically does implicit commits on DDL, leaving partial failures to reconcile manually.

Key Concepts

Migration Domain Vocabulary

Migration: a single ordered unit of schema change, typically an up script (and optionally a down script).
Migration history / schema version table: a table the tool maintains inside the database itself (e.g. flyway_schema_history, Rails' schema_migrations) recording which migrations ran, enabling idempotent, safe re-runs.
Idempotent migration: safe to run more than once without error - important because CI/CD pipelines sometimes retry failed steps.
Forward-only migrations: fix mistakes with a new migration rather than a down-script; increasingly preferred because down-migrations are rarely exercised and rot over time.
Backward-compatible vs. breaking migration: whether old application code can tolerate the new schema shape while it's still running.
Schema migration vs. data migration: structural change (tables/columns/constraints/indexes) vs. changes to existing row values (backfills, reshaping data) - the latter carries much higher operational risk at scale.

Migration Tooling Architecture

Migration files are ordered unambiguously, usually via a timestamp prefix (e.g. 20240315120000_add_users_email_index.sql).
On each run, the tool diffs available migrations against the history table, applies what's missing in order, and records each application - ideally in the same transaction as the schema change.
Common failure mode: two developers write migrations with colliding timestamps or an untested ordering; well-behaved tools fail loudly rather than guess.
Transactional DDL differs by engine: Postgres can roll back a multi-statement failed migration entirely; MySQL's implicit commits on DDL can leave a partially-applied schema after a failure.

Locking Behavior

Many DDL statements take an ACCESS EXCLUSIVE-level lock (Postgres) or equivalent metadata lock, blocking all reads/writes to the table for the operation's duration.
Adding a column with a default value used to require a full table rewrite under lock; Postgres 11+ made this instant for simple, non-volatile defaults - but adding an index, adding NOT NULL without a prior CHECK, or changing a column type can still require a full scan/rewrite depending on version and engine.
Mitigations: CREATE INDEX CONCURRENTLY (Postgres), or online DDL tools like pt-online-schema-change and gh-ost (MySQL) to avoid blocking a hot table.

The Expand-Contract Pattern

Three phases across separate deploys: expand (add new structure, dual-write), migrate (backfill + switch reads), contract (remove old structure).
Trades a single risky deploy for three coordinated, lower-risk ones - the cost is more deploy cycles and dual-write code that can itself introduce bugs if inconsistent.
NOT NULL constraints follow a similar staged approach: add as CHECK ... NOT VALID first, VALIDATE CONSTRAINT separately (no long lock), then convert to hard NOT NULL once confirmed safe.

Trade-offs / Caveats

Expand-contract is overhead: small, low-traffic tables may not justify three coordinated deploys - a direct ALTER TABLE ... RENAME may be fine.
Down-migrations are rarely tested in practice and often reference columns that no longer exist by the time they're needed; many teams pair forward-only migrations with feature flags instead of relying on rollback scripts.
Data migrations must not be treated like schema migrations: an unbatched UPDATE across a hundred-million-row table can bloat write-ahead logs enough to threaten replica lag or fill disk.
Testing migrations only against an empty local database hides failure modes - e.g. a NOT NULL column without a default succeeds on empty data but fails immediately against real production rows.

Example in Practice

Renaming email to email_address on a live users table, expand-contract style:

-- Expand
ALTER TABLE users ADD COLUMN email_address TEXT;

# Migrate: batched backfill, not a single UPDATE
BATCH_SIZE = 5_000
def backfill_email_address(engine):
    with engine.connect() as conn:
        while True:
            result = conn.execute(text("""
                UPDATE users SET email_address = email
                WHERE id IN (
                    SELECT id FROM users WHERE email_address IS NULL LIMIT :batch_size
                )"""), {"batch_size": BATCH_SIZE})
            conn.commit()
            if result.rowcount == 0:
                break
            time.sleep(0.1)

Between expand and contract, the app is deployed to dual-write both columns, then switched to read only email_address once backfill and monitoring confirm consistency. Only after an observation period does contract run:

-- Contract
ALTER TABLE users DROP COLUMN email;