Zero-Downtime Database Migrations: The Expand-Contract Pattern

When does a DB migration get scary? When old code and new schema disagree at deploy time. For one moment, half your fleet runs old code and half runs new code, and the schema fits one but not the other. That moment is where systems break.

The fix is simple in concept: eliminate that moment. The pattern that does it is called expand-contract.

The wrong approach — and why it breaks

You want to rename a column. The naive path:

1. Migration: ALTER TABLE users RENAME COLUMN name TO full_name
2. Update code: use user.full_name everywhere
3. Deploy

Looks simple. Production breaks for several reasons:

• During the 30-second deploy, old instances still try to read name → DB errors out
• The migration takes time on a 10M-row table → 5 minutes during which writes fail
• If you need to roll back, "undo the migration" is rarely practical

The right approach — expand-contract

Spread the change across three deploys.

Phase 1: Expand

Add the new column. Don't touch the old one.

ALTER TABLE users ADD COLUMN full_name TEXT;

In this deploy:

• Old code still reads name
• New code (not yet deployed) will use full_name
• No user is affected

The schema is compatible with both code versions — that's what "expand" means.

Phase 2: Backfill + dual-write

Two parallel things:

a. Backfill — copy old data into the new column. Async, batched. A million rows might take 30 minutes — wait it out:

UPDATE users SET full_name = name WHERE full_name IS NULL;

Don't do this in one go in production — use 1000-row batches, off-hours. Tools like pt-online-schema-change (MySQL) or gh-ost are useful here.

b. Dual-write — new code writes to both columns:

user.name = value
user.full_name = value
db.save(user)

This deploy:

• Old users on old code still read/write the old column
• New code dual-writes, keeping both columns in sync
• No inconsistency window

Phase 3: Cutover

Switch reads to the new column:

return user.full_name  # not name

Deploy. Old code is now retired. Wait a week, watch monitoring stay clean.

Phase 4: Contract

Now drop the old column:

ALTER TABLE users DROP COLUMN name;

No code reads or writes name. Dropping is safe. Contract.

Long-running migrations

For a 10M+ row table, an ALTER TABLE can hold a lock for minutes. Unacceptable in production.

Tools to know:

• MySQL/MariaDB: pt-online-schema-change builds a new table side-by-side, keeps it in sync via triggers, then atomically swaps
• PostgreSQL: most schema changes are already lock-free or have short locks; CONCURRENTLY keyword for indexes is your friend
• AWS Aurora / RDS: gh-ost is replication-aware and the safest option

You can do without tools, carefully:

• Creating an index: CREATE INDEX CONCURRENTLY (PostgreSQL)
• Adding a nullable column: fast, short lock
• Adding a column with a default: PostgreSQL 11+ is fast; older or MySQL is slow

Foreign keys and constraints

These are migration's hidden nightmare. Adding a new constraint:

ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

If existing data violates the constraint, the ALTER fails. Clean the data first, add the constraint second. Same expand-contract:

1. Find and fix or delete the offending rows
2. Then add the constraint

Rollback plan per phase

Every phase should have an undo path:

• Phase 1 (expand): drop the new column — easy
• Phase 2 (dual-write): revert the new code — old code still works
• Phase 3 (cutover): revert the new code — old code still works (still reads old column)
• Phase 4 (contract): no rollback. By this point you've waited a week and the monitoring confirms all-clear.

A real example

In our production platform we changed user_session.expires_at from Unix timestamp to TIMESTAMP WITH TIME ZONE. The table had 50 million rows.

• Phase 1: added expires_at_v2 TIMESTAMP WITH TIME ZONE (5-second lock)
• Phase 2: ran a pt-online-schema-change-style script overnight in batches over 3 days
• Phase 3: new code reads/writes expires_at_v2, also writes expires_at for safety (1 week)
• Phase 4: new code reads/writes only expires_at_v2 (held 2 weeks)
• Phase 5: dropped expires_at

Total: zero incidents. Not a single user affected.

Closing

Zero-downtime migration is achievable, but it doesn't fit in "one deploy." The real cost is patience — a change spread over 3-4 deploys takes weeks longer than a fast-track migration but carries zero downtime risk.

Trade-off: speed vs. safety. In production, safety wins every time. You can recover speed; you can't recover downtime.