Zero-Downtime Database Migrations: The Expand-Contract Playbook

Database migrations are one of the few operations that can take down a healthy production system in seconds. A single ALTER TABLE can lock a hot table, exhaust connections, or leave application code and schema in incompatible states. For teams shipping continuously, the goal is not to avoid migrations but to make them boring: deploy schema changes and application changes independently, with no maintenance window and no user-visible errors.

This guide covers a practical playbook for zero-downtime migrations. It focuses on the expand-contract pattern, also called parallel change, and shows how to apply it to common changes such as adding columns, renaming columns, changing types, splitting tables, and enforcing constraints. The examples use PostgreSQL, but the principles apply to MySQL, SQL Server, and most relational databases.

Why Migrations Break Production

Most migration incidents come from four forces:

• Locks: DDL statements often take strong locks. A long-running query can block the DDL, and the DDL can block every subsequent query. PostgreSQL’s ALTER TABLE can require an ACCESS EXCLUSIVE lock, even if the operation itself is fast.
• Version skew: During deployment, old and new application code run at the same time. If the schema change is not backward compatible, one version fails.
• Backfills: Updating millions of rows in one transaction creates bloat, replication lag, and lock contention. It can also fail halfway.
• Rollbacks: A migration that cannot be reversed safely forces teams to choose between broken code and data loss.

The expand-contract pattern removes these risks by making every step compatible with both old and new application versions.

The Expand-Contract Pattern

Expand-contract breaks a schema change into three phases:

1. Expand: Add new structures without removing or modifying existing ones. Both old and new code can run.
2. Migrate: Backfill data and switch application behavior in controlled steps. Old structures remain available for rollback.
3. Contract: Remove old structures only after no running code depends on them.

Each phase is a separate deployment. This separation is the key. You never combine a destructive schema change with an application release that depends on it.

Example: Renaming a Column Without Downtime

Renaming users.email to users.email_address looks simple, but a direct ALTER TABLE users RENAME COLUMN email TO email_address instantly breaks old application instances that still query email. Instead, use parallel columns.

Phase 1: Expand

ALTER TABLE users ADD COLUMN email_address text;

Make the new column nullable, or give it a default that matches old behavior. Do not add a NOT NULL constraint yet. Deploy application code that writes to both columns:

UPDATE users SET email_address = $1 WHERE id = $2;

In practice, you would update both columns in the same transaction or use a database trigger to keep them in sync. The old column remains the source of truth for reads.

Phase 2: Backfill

Copy existing data from email to email_address in small batches. Avoid a single UPDATE users SET email_address = email; on a large table. Batch by primary key and sleep between batches to let replication catch up:

UPDATE users SET email_address = email WHERE id IN (SELECT id FROM users WHERE email_address IS NULL ORDER BY id LIMIT 1000);

Repeat until no rows remain. Monitor replication lag, lock waits, and transaction duration. If backfill is too slow, increase batch size carefully or run it during lower traffic.

Phase 3: Switch Reads

Once backfill is complete and dual writes are verified, deploy code that reads from email_address. Keep writing to both columns for now. This gives you a rollback path: if the new column has bad data, you can switch reads back to email.

Phase 4: Stop Dual Writes

After the new read path is stable, deploy code that writes only to email_address. The old column becomes stale but harmless.

Phase 5: Contract

Wait at least one full deployment cycle, or longer if you have long-running background jobs. Then drop the old column:

ALTER TABLE users DROP COLUMN email;

In PostgreSQL, dropping a column is usually fast because it only updates the catalog. The physical space is reclaimed later by vacuum. In other databases, dropping a column may rewrite the table, so schedule it carefully.

Common Migration Patterns

Adding a NOT NULL Column

Adding NOT NULL directly requires a default for existing rows and can lock the table. Use a multi-step approach:

1. Add the column as nullable.
2. Deploy application code that writes a value for every new row.
3. Backfill existing rows in batches.
4. Add a CHECK (column IS NOT NULL) NOT VALID constraint. This validates new rows without scanning existing ones.
5. Validate the constraint with ALTER TABLE ... VALIDATE CONSTRAINT, which takes a weaker lock.
6. Optionally, convert to a real NOT NULL constraint if your database supports it efficiently.

Changing a Column Type

Changing integer to bigint can rewrite the entire table and lock it. Use a new column:

1. Add new_id bigint.
2. Dual write to both columns.
3. Backfill in batches.
4. Switch reads to the new column.
5. Stop writing to the old column.
6. Drop the old column and rename the new one during a low-traffic window.

For very large tables, consider logical replication or table partitioning to avoid a full rewrite.

Splitting a Table

When extracting columns into a new table, treat it as an expand-contract migration with a join. First create the new table, then dual write, backfill, switch reads to join the tables, and finally remove the old columns. Keep foreign keys and unique constraints consistent throughout.

Adding an Index

In PostgreSQL, use CREATE INDEX CONCURRENTLY to avoid blocking writes. It cannot run inside a transaction, and it can fail leaving an invalid index. Check for invalid indexes and drop them before retrying. In MySQL, ALGORITHM=INPLACE, LOCK=NONE can help, but not all index changes support it.

Tooling and Automation

Migration tools help, but they do not remove the need for a safe process. Popular options include:

• Flyway and Liquibase: Versioned SQL migrations with rollback support. Good for teams that want explicit control.
• Alembic: Python migrations often used with SQLAlchemy. Supports autogeneration but requires review.
• Rails Active Record Migrations: Convenient but can hide dangerous operations. Use strong_migrations to catch unsafe changes.
• gh-ost and pt-online-schema-change: Online schema change tools for MySQL. They create a shadow table, copy data, and swap it in.
• pg_repack: Rebuilds PostgreSQL tables and indexes with minimal locking.

Whatever tool you choose, enforce these rules in CI:

• No destructive changes in the same migration as application code.
• No NOT NULL without a safe backfill plan.
• No long-running transactions.
• Every migration must be backward compatible for at least one deployment.

Rollback Strategies

Zero-downtime migrations are also about fast recovery. With expand-contract, rollback usually means deploying the previous application version, not reversing the schema. Because the old structures still exist during expand and migrate phases, rollback is safe. Only in the contract phase do you lose the ability to roll back without restoring from backup.

For data migrations, keep the old columns until you are confident. If a backfill introduces bad data, you can re-run it from the source of truth. If you must reverse a contract, have a tested restore procedure and know your recovery point objective.

Operational Checklist

• Measure table size and lock impact. Use pg_relation_size and query pg_locks to understand risk.
• Set lock_timeout and statement_timeout. This prevents a migration from blocking the entire database indefinitely.
• Deploy schema changes separately. Run migrations before application code when expanding, and after application code when contracting.
• Monitor replication lag. Pause backfills if replicas fall too far behind.
• Test on a production-sized copy. Staging with 10,000 rows will not reveal lock behavior on 100 million rows.
• Have a rollback plan for every step. If you cannot roll back, document why and get approval.

Conclusion

Zero-downtime database migrations are not magic. They are a discipline: expand first, migrate data and behavior gradually, contract only when safe. By treating schema changes as a sequence of backward-compatible steps, you can deploy continuously without maintenance windows, reduce the blast radius of errors, and keep rollback options open. The next time you need to rename a column or change a type, do not reach for a single ALTER TABLE. Reach for the expand-contract playbook instead.