Zero-Downtime Database Migrations: The Expand-Contract Incident Runbook

At 03:15 AM during a routine release, a deployment pipeline executed a single SQL statement on a live table with 80 million rows:

SQL

ALTER TABLE users RENAME COLUMN phone_number TO mobile_number;

Within 40 seconds, error rates spiked across all API gateways. Active app instances running the previous code version crashed immediately upon trying to read or write the phone_number field. The database connection pool saturated, API responses timed out, and the team was forced into an emergency rollback that took 25 minutes to recover.

Direct schema mutations on live databases are the leading cause of deployment-induced outages. This runbook details the Expand and Contract Pattern (Parallel Run) to execute zero-downtime database migrations safely under heavy traffic.

1. Incident Breakdown: Why Live Alterations Break Applications

When deploying code alongside database changes, there is always a window where Version N of your application and Version N+1 run concurrently.

[Version N Code]     ──► Expects column 'phone_number'
                              ▲
                              │  (CRASH: Column missing or renamed!)
                              ▼
[Database Schema]    ──► Column renamed to 'mobile_number'
                              ▲
                              │  (OK)
                              ▼
[Version N+1 Code]   ──► Expects column 'mobile_number'

If a schema change is destructive (renaming columns, dropping tables, changing data types, or adding non-null constraints without defaults), Version N instances will fail instantly.

2. The Expand-Contract Execution Strategy

To eliminate downtime, decouple schema structural changes from application code deployments across three distinct phases.

PHASE 1: EXPAND          PHASE 2: DUAL-WRITE & BACKFILL     PHASE 3: CONTRACT
┌──────────────────┐     ┌────────────────────────────┐     ┌──────────────────┐
│ Add new column   │ ──► │ App writes to BOTH columns │ ──► │ Remove old column│
│ (Old code reads  │     │ Backfill historical rows   │     │ (New code reads  │
│  from old col)   │     │ (Switch reads to new col)  │     │  from new col)   │
└──────────────────┘     └────────────────────────────┘     └──────────────────┘

3. Step-by-Step Runbook: Renaming a Column on a Live Table

Phase 1: Expand (Non-Blocking Add)

Add the new column without dropping or altering the old one. Set a strict lock timeout to prevent schema modification locks from blocking incoming HTTP requests.

SQL

-- Migration 001_add_mobile_number.sql
SET LOCAL lock_timeout = '2s';

ALTER TABLE users ADD COLUMN mobile_number VARCHAR(20);

• Deployment state: Deploy Migration 001. Old application instances continue operating normally on phone_number.

Phase 2: Dual-Write & Historical Backfill

Deploy an updated application release (Version N+1) configured to read from phone_number (falling back to mobile_number) while writing to both columns simultaneously.

TypeScript

// Application Level Dual-Write
export async function updateUserPhone(userId: string, phone: string): Promise<void> {
  await db.query(
    `UPDATE users 
     SET phone_number = $1, 
         mobile_number = $1, 
         updated_at = NOW() 
     WHERE id = $2`,
     [phone, userId]
   );
}

Run a background worker script to populate historical records in controlled, rate-limited batches:

SQL

-- Execute in a background job loop
UPDATE users 
SET mobile_number = phone_number 
WHERE mobile_number IS NULL AND phone_number IS NOT NULL
LIMIT 5000;

Phase 3: Contract (Safe Deprecation)

Once 100% of rows are backfilled and verified, deploy Version N+2 of the application, which reads and writes exclusively to mobile_number.

After monitoring for 24–48 hours, remove the legacy column in a final, isolated deployment:

SQL

-- Migration 002_drop_phone_number.sql
SET LOCAL lock_timeout = '2s';

ALTER TABLE users DROP COLUMN phone_number;

4. Emergency Rollback Playbook

If an anomaly occurs during Phase 2 (Dual-Write), follow these recovery steps:

1. Do not rollback database migrations immediately. Because Phase 1 only added a nullable column, the schema remains 100% backward compatible with Version N.
2. Revert application code to Version N: Traffic will safely fall back to reading and writing phone_number.
3. Clean up orphaned data: Run a lightweight cleanup script to drop mobile_number once traffic stabilizes.

5. Migration Safety Checklist for Tech Leads

• [ ] Lock Timeouts Configured: Every migration script includes SET LOCAL lock_timeout = '2s'; to avoid holding exclusive table locks.
• [ ] No Multi-Operation Migrations: Renaming, adding, and dropping columns are split into separate pull requests across independent release cycles.
• [ ] Batch Processing Active: All data backfilling queries use explicit batching (LIMIT 5000) with delay intervals to prevent CPU spikes and replication lag on database replicas.
• [ ] Verification Queries Passed: Automated check scripts confirm 0 rows have mobile_number IS NULL before executing Phase 3.

Executing zero-downtime migrations shifts database management from a high-stress deployment event into a routine, continuous engineering process.