Zero-Downtime Database Migrations: The Expand and Contract Pattern in Production

In an early-stage hobby project, updating a database schema is trivial: you stop the application, run ALTER TABLE users RENAME COLUMN username TO handle;, deploy the new code, and start the app back up. Total downtime: 30 seconds.

In a modern production application with continuous deployment, high traffic, and multiple microservice replicas, stopping the database is not an option.

Furthermore, during a rolling update, instances of the old application version and the new application version run simultaneously for several minutes! If you rename a database column abruptly:

• Old application pods immediately crash because the old column is gone.
• New application pods might fail if the migration hasn't finalized.

To achieve continuous deployment without a single dropped query, high-performing engineering teams use the Expand and Contract Pattern (also known as Parallel Run).

The 4-Phase Expand and Contract Lifecycle

Phase 1: Expand       -> Add new column alongside old column (both exist).
Phase 2: Dual-Write   -> Application writes to BOTH columns, reads from old.
Phase 3: Backfill     -> Background script copies historical data to new column.
Phase 4: Contract     -> Switch reads to new column, stop writing to old, drop old column.

Step 1: Expand (Add New Column Safely)

-- Migration 001_expand.sql
-- In PostgreSQL 11+, adding a column with a DEFAULT or NULL does NOT rewrite the table!
ALTER TABLE customers 
ADD COLUMN contact_phone VARCHAR(32) NULL;

Step 2: Deploy Dual-Writing Application Code

Deploy application version v2.0. Reads continue from phone_number. Any new customer inserts or updates write to both columns.

# Application Code (v2.0)
def update_customer_phone(db_session, customer_id: str, new_phone: str):
    db_session.execute(
        """
        UPDATE customers 
        SET phone_number = :phone,
            contact_phone = :phone
        WHERE id = :id
        """,
        {"phone": new_phone, "id": customer_id}
    )
    db_session.commit()

Step 3: Backfill Historical Data in Batches

Never execute a blanket unbatched UPDATE on millions of rows! Always backfill in small, index-driven chunks:

# backfill_script.py
import time
from sqlalchemy import text

def backfill_in_batches(session, batch_size=5000):
    last_id = 0
    total_migrated = 0

    while True:
        result = session.execute(
            text("""
                UPDATE customers
                SET contact_phone = phone_number
                WHERE id IN (
                    SELECT id FROM customers
                    WHERE id > :last_id AND contact_phone IS NULL
                    ORDER BY id ASC
                    LIMIT :batch_size
                )
                RETURNING id;
            """),
            {"last_id": last_id, "batch_size": batch_size}
        )
        updated_ids = [row[0] for row in result.fetchall()]
        session.commit()

        if not updated_ids:
            print("Backfill complete!")
            break

        last_id = max(updated_ids)
        total_migrated += len(updated_ids)
        print(f"Migrated {total_migrated} rows...")
        time.sleep(0.1)

Step 4: Contract (Cut Over and Cleanup)

1. Deploy application version v2.1: Reads and writes now point exclusively to contact_phone.
2. Drop the legacy column:

-- Migration 002_contract.sql
ALTER TABLE customers DROP COLUMN phone_number;

Critical PostgreSQL Index Caveat: CONCURRENTLY

When adding indexes to large production tables during migrations, standard CREATE INDEX acquires an ACCESS EXCLUSIVE lock, blocking all INSERT, UPDATE, and DELETE queries until the index builds!

Always create indexes concurrently:

-- SAFE in Production: Does not block writes!
CREATE INDEX CONCURRENTLY idx_customers_contact_phone ON customers(contact_phone);