Zero-Downtime Database Migrations: The Expand/Contract Pattern

The problem

Deploying a new service version alongside a schema change creates a window of incompatibility. During a rolling deploy (ECS tasks, Kubernetes pods replacing one at a time), old and new code run simultaneously against the same database. If the schema change is breaking — renaming a column, dropping a table, changing a type — the old code fails while the new code works, or vice versa.

The safest way to handle this is to never apply a breaking schema change while old code is still running.

A worked example: renaming a column

You need to rename user_name to display_name in a users table.

The wrong approach: rename in a single migration alongside the code deploy. During the rolling deploy, old pods look for user_name (which no longer exists) and crash.

The expand/contract approach:

Phase 1: Expand

Add the new column. Populate it from the old column. Update writes to write to both columns. Old code continues to read user_name — unaffected. New code can read display_name — it exists.

-- Migration 1: add new column
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);
UPDATE users SET display_name = user_name;

# New code: write to both columns during transition
def update_user_name(user_id, name):
    db.execute(
        "UPDATE users SET user_name = %s, display_name = %s WHERE id = %s",
        (name, name, user_id)
    )

# New code: read from new column
def get_display_name(user_id):
    return db.query("SELECT display_name FROM users WHERE id = %s", user_id)

Deploy the new code. Old pods still work (they read user_name). New pods work (they read display_name, which is populated). The database has both columns.

Phase 2: Contract

Once all old pods are gone (rollout complete, no old code running), drop the old column.

-- Migration 2: drop old column (run after all new code is deployed)
ALTER TABLE users DROP COLUMN user_name;

The new code is already only reading display_name. The drop does not affect any running code.

What makes a schema change backward-compatible

| Change | Safe to deploy without expand/contract? | |--------|----------------------------------------| | Add a nullable column | Yes — old code ignores it | | Add a NOT NULL column with a default | Yes — old writes satisfy the constraint | | Add a NOT NULL column without default | No — old writes fail | | Rename a column | No — use expand/contract | | Drop a column | No — old code references it | | Add an index | Yes — non-blocking in Postgres/MySQL with CONCURRENT option | | Add a foreign key | Depends — blocking if validating existing rows |

Additive changes (new nullable columns, new tables, new indexes) are generally safe to deploy alongside code. Destructive changes (drops, renames, type changes, adding NOT NULL) require expand/contract.

V1__add_display_name.sql     ← expand: runs before new code deploy
V2__drop_user_name.sql       ← contract: runs after new code deploy completes

Blue-green deployment for incompatible changes

When a schema change is fundamentally incompatible — a type change, a table restructure, data format rewrite — expand/contract does not help because there is no intermediate state where both schemas are valid.

In this case, blue-green deployment separates the environments completely:

1. Provision a Green environment with new code and new schema.
2. Replicate data from Blue to Green. Enable write-ahead replication so Blue writes are mirrored to Green.
3. Switch traffic from Blue to Green.
4. Keep Blue running briefly for rollback capability. Stop replication.
5. Decommission Blue.

The cost is provisioning a full parallel environment. The benefit is a clean cutover with no compatibility window. This is appropriate for major version migrations (Postgres 13 → 16, Rails 5 → 7, database engine swap).