Zero-Downtime Migrations: A Step-by-Step Playbook

If you've ever typed ALTER TABLE on a production database and held your breath, this article is for you.

Database migrations are one of the most dangerous routine operations in software engineering. Done wrong, a simple column rename can take down your entire platform — locking tables, timing out connections, and turning your Monday morning deploy into an all-hands incident.

The good news: zero-downtime migrations are entirely achievable, even on large, high-traffic databases. They just require a different mental model – one where your schema and your code evolve together but independently.

This is the playbook my team uses. It's boring, methodical, and it works.

Why Migrations Go Wrong

Before the playbook, let's understand the failure modes.

Problem 1: The Lock

Most databases acquire an ACCESS EXCLUSIVE lock when you alter a table. This blocks every read and write until the migration finishes. On a large table, "finishes" can mean minutes or hours.

-- This looks innocent. It is not.
ALTER TABLE users ADD COLUMN last_seen_at TIMESTAMP;

-- On a 50M row table with active traffic?
-- You just blocked every query touching `users`.

Problem 2: Deploying Code and Schema Together

The classic mistake: you rename a column and deploy your app at the same time. For one brief window, your new code is looking for email_address while the database still has email. Errors spike. Users see failures. You roll back both and start over.

Problem 3: The Irreversible Change

You drop a column. The deploy succeeds. Then you realize the old version of your app (still running on 2 of your 10 servers during a rolling deploy) needed that column. Now you have 500 errors per second and no way to un-drop.

The Mental Model: Expand and Contract

The solution to all three problems is a pattern called Expand and Contract (sometimes called parallel change).

The idea is simple:

Never make a breaking schema change in a single step. Instead, expand the schema to support both old and new states, update your application code, then contract the schema by removing what's no longer needed.

Every migration becomes a 3-phase process spread across multiple deploys:

Phase 1: EXPAND   → Add new structure, keep old structure intact
Phase 2: MIGRATE  → Move data, update app code to use new structure
Phase 3: CONTRACT → Remove old structure once it's no longer referenced

Each phase is independently deployable. Each phase is independently rollback-safe.

Let's walk through it concretely.

Phase 1: Expand

Goal: Make the database compatible with both the old app and the new app simultaneously.

Say you want to rename users.name to users.full_name.

In the Expand phase, you add the new column without removing the old one:

-- Migration: 001_add_full_name_column.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

Why this is safe: Adding a nullable column (or one with a default) doesn't require a full table rewrite in modern databases. PostgreSQL 11+ handles this instantly via a catalog change. MySQL 8.0+ with ALGORITHM=INSTANT does the same.

At this point, your old app still reads and writes name. The new column exists but is empty. Nothing breaks.

Pro tip for large tables — avoid long locks entirely:

-- PostgreSQL: Use concurrent index builds, not blocking ones
CREATE INDEX CONCURRENTLY idx_users_full_name ON users(full_name);

-- MySQL: Use pt-online-schema-change or gh-ost for large table alters

Phase 2: Migrate (Data + Code)

This is the meaty phase. It has two parts that happen together.

Part A: Backfill the Data

Copy data from the old column to the new one. Don't do this in a single UPDATE — it locks the table and may run for hours.

Instead, backfill in batches:

import psycopg2
import time

conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()

BATCH_SIZE = 1000
last_id = 0

while True:
    cursor.execute("""
        UPDATE users
        SET full_name = name
        WHERE id > %s
          AND full_name IS NULL
        ORDER BY id
        LIMIT %s
        RETURNING id
    """, (last_id, BATCH_SIZE))

    rows = cursor.fetchall()
    conn.commit()

    if not rows:
        break

    last_id = rows[-1][0]
    print(f"Backfilled up to id={last_id}")
    time.sleep(0.05)  # Breathing room for other queries

print("Backfill complete.")

This approach:

• Never locks the table for more than a few milliseconds per batch
• Can be paused and resumed
• Can run in production without impacting traffic

Part B: Update Your Application Code

Now deploy the application code that writes to both columns and reads from the new column:

# Before (Phase 1 app code)
def update_user_name(user_id, name):
    db.execute("UPDATE users SET name = %s WHERE id = %s", (name, user_id))

def get_user(user_id):
    return db.fetchone("SELECT name FROM users WHERE id = %s", (user_id,))


# After (Phase 2 app code) — dual-write, read from new column
def update_user_name(user_id, name):
    db.execute("""
        UPDATE users
        SET name = %s, full_name = %s
        WHERE id = %s
    """, (name, name, user_id))

def get_user(user_id):
    return db.fetchone("SELECT full_name FROM users WHERE id = %s", (user_id,))

Why dual-write matters: During a rolling deploy, some servers run the old code, some run the new. Dual-writing ensures both versions stay in sync with no data loss regardless of which server handles a request.

Verify Before Moving On

Before Phase 3, confirm that:

1. The backfill is 100% complete (no NULL values in full_name for rows that should have data)
2. All application servers are on the Phase 2 code
3. No old-code servers are still running (check your deployment dashboard)

-- Verify backfill completeness
SELECT COUNT(*)
FROM users
WHERE name IS NOT NULL
  AND full_name IS NULL;

-- Should return 0

Phase 3: Contract

Only when Phase 2 has been fully deployed and verified do you remove the old column.

-- Migration: 003_drop_old_name_column.sql
ALTER TABLE users DROP COLUMN name;

And clean up your application code:

# Phase 3 app code — old column gone, no more dual-write
def update_user_name(user_id, name):
    db.execute("UPDATE users SET full_name = %s WHERE id = %s", (name, user_id))

def get_user(user_id):
    return db.fetchone("SELECT full_name FROM users WHERE id = %s", (user_id,))

The key insight: At this point, nothing in your codebase references name anymore. Dropping it is completely safe. If something breaks, it was already broken before you ran this migration.

The Rollback Plan

Every phase should have an explicit rollback strategy.

The most important rule: never skip Phase 2. The temptation to combine "add new column, migrate data, drop old column" into a single migration script is real — and it's how incidents happen.

Real-World Patterns Cheat Sheet

Here are common migration scenarios mapped to the Expand/Contract approach:

Rename a Column

• Expand: Add new column
• Migrate: Backfill data, dual-write in code
• Contract: Drop old column

Add a NOT NULL Column

-- Phase 1: Add as nullable
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NULL;

-- Phase 2: Backfill, then add constraint
UPDATE orders SET shipped_at = created_at WHERE status = 'shipped';

-- Phase 3: Enforce the constraint
ALTER TABLE orders ALTER COLUMN shipped_at SET NOT NULL;

Split a Column into Two

• Expand: Add both new columns
• Migrate: Parse and populate from old column in batches
• Contract: Drop old column

Change a Column Type (e.g. INT → BIGINT)

• Expand: Add new column with new type
• Migrate: Copy and cast data, dual-write in code
• Contract: Drop old column, rename new one (or keep new name in code)

Tools Worth Knowing

The 3 AM Test

Before you run any migration in production, ask yourself:

If this migration goes wrong at 3 AM, can I roll it back in under 5 minutes without data loss?

If the answer is no — split it into smaller phases until it is.

Zero-downtime migrations aren't magic. They're discipline. Each phase is small, safe, and independently reversible. The whole process takes longer than a single scary ALTER TABLE, but it's the difference between a boring Tuesday deploy and an incident retrospective.

Your on-call rotation will thank you.

Summary

1. Never change schema and code atomically. Decouple them across phases.
2. Expand first — add new structure, don't remove old structure.
3. Migrate safely — batch backfills, dual-write during transition.
4. Contract last — only drop old structure after all code has moved on.
5. Always have a rollback plan for each phase independently.
6. Use the right tools — gh-ost, CREATE INDEX CONCURRENTLY, and batch scripts are your friends.