Database Migrations Without Downtime: The Expand-Contract Pattern

Database migrations are terrifying. One wrong move and you’ve locked tables, broken queries, or corrupted data. The traditional approach — maintenance window, stop traffic, migrate, restart — works but costs you availability and customer trust.

The expand-contract pattern lets you migrate schemas incrementally, with zero downtime, while your application keeps serving traffic.

The Core Idea

Never make breaking changes in a single step. Instead:

Expand: Add the new structure alongside the old
Migrate: Move data and update application code
Contract: Remove the old structure

Each step is independently deployable and reversible.

Example: Renaming a Column

You want to rename user.username to user.handle. The naive approach:

1
2
-- DON'T DO THIS IN PRODUCTION
ALTER TABLE users RENAME COLUMN username TO handle;

This breaks instantly. Every query using username fails.

The Expand-Contract Way

Step 1: Expand — Add new column

1
2
ALTER TABLE users ADD COLUMN handle VARCHAR(255);
UPDATE users SET handle = username;

Now both columns exist with the same data.

Step 2: Dual-write — Update application

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Write to both columns
def update_user(user_id, new_handle):
    db.execute("""
        UPDATE users 
        SET username = %s, handle = %s 
        WHERE id = %s
    """, (new_handle, new_handle, user_id))

# Read from new column, fall back to old
def get_handle(user_id):
    result = db.query("SELECT handle, username FROM users WHERE id = %s", user_id)
    return result.handle or result.username

Deploy this. Both old and new application versions work.

Step 3: Backfill — Ensure all data migrated

1
2
UPDATE users SET handle = username WHERE handle IS NULL;
ALTER TABLE users ALTER COLUMN handle SET NOT NULL;

Step 4: Switch reads — Update application

1
2
3
def get_handle(user_id):
    result = db.query("SELECT handle FROM users WHERE id = %s", user_id)
    return result.handle

Step 5: Stop dual-write — Update application

1
2
def update_user(user_id, new_handle):
    db.execute("UPDATE users SET handle = %s WHERE id = %s", (new_handle, user_id))

Step 6: Contract — Remove old column

1
ALTER TABLE users DROP COLUMN username;

Six steps instead of one, but zero downtime and full reversibility at each stage.

Adding a NOT NULL Column

You can’t add a NOT NULL column to a table with existing rows (no default value to use).

Step 1: Add nullable column

1
ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(50);

Step 2: Backfill with default

1
UPDATE orders SET shipping_method = 'standard' WHERE shipping_method IS NULL;

For large tables, batch this:

1
2
3
UPDATE orders 
SET shipping_method = 'standard' 
WHERE id IN (SELECT id FROM orders WHERE shipping_method IS NULL LIMIT 10000);

Step 3: Add NOT NULL constraint

1
ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL;

Step 4: Add default for new rows

1
ALTER TABLE orders ALTER COLUMN shipping_method SET DEFAULT 'standard';

Changing Column Types

Converting price from INTEGER (cents) to DECIMAL (dollars):

Step 1: Add new column

1
ALTER TABLE products ADD COLUMN price_decimal DECIMAL(10,2);

Step 2: Backfill

1
UPDATE products SET price_decimal = price / 100.0;

Step 3: Dual-write in application

1
2
3
4
5
6
7
def update_price(product_id, price_dollars):
    price_cents = int(price_dollars * 100)
    db.execute("""
        UPDATE products 
        SET price = %s, price_decimal = %s 
        WHERE id = %s
    """, (price_cents, price_dollars, product_id))

Step 4: Switch reads to new column

Step 5: Stop writing to old column

Step 6: Drop old column

1
2
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_decimal TO price;

Large Table Migrations

On tables with millions of rows, even UPDATE statements can lock for minutes. Use batched updates:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
def backfill_in_batches(batch_size=10000):
    while True:
        result = db.execute("""
            UPDATE products 
            SET price_decimal = price / 100.0 
            WHERE id IN (
                SELECT id FROM products 
                WHERE price_decimal IS NULL 
                LIMIT %s
            )
            RETURNING id
        """, (batch_size,))
        
        if result.rowcount == 0:
            break
        
        time.sleep(0.1)  # Let other queries breathe

For truly massive tables, use tools like pg_repack, pt-online-schema-change, or gh-ost.

Foreign Key Considerations

Adding a foreign key to an existing column:

1
2
3
-- This locks both tables while validating all rows
ALTER TABLE orders ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

Instead:

1
2
3
4
5
6
7
-- Add constraint without validation (instant)
ALTER TABLE orders ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    NOT VALID;

-- Validate in background (no lock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

Index Creation

Creating indexes locks writes on most databases. Use concurrent creation:

1
2
3
4
5
-- PostgreSQL: no write lock
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL 5.6+: no lock for most index types
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;

Migration Tooling

Use a migration framework that tracks what’s been applied:

Alembic (Python/SQLAlchemy):

1
2
3
4
5
def upgrade():
    op.add_column('users', sa.Column('handle', sa.String(255)))

def downgrade():
    op.drop_column('users', 'handle')

Flyway (Java/JVM):

1
2
-- V2__add_handle_column.sql
ALTER TABLE users ADD COLUMN handle VARCHAR(255);

golang-migrate:

1
2
3
4
5
-- 000002_add_handle.up.sql
ALTER TABLE users ADD COLUMN handle VARCHAR(255);

-- 000002_add_handle.down.sql
ALTER TABLE users DROP COLUMN handle;

The Deployment Dance

Coordinate application deploys with migrations:

Deploy migration (expand phase)
Deploy application with dual-write
Run backfill
Deploy application reading from new structure
Deploy application with single-write
Deploy migration (contract phase)

Each deployment is independent. If step 3 fails, you can roll back to step 2. If step 5 causes issues, roll back to step 4.

When Downtime Is Acceptable

Sometimes a maintenance window is the right choice:

Schema changes to rarely-used internal tools
Fundamental restructuring that can’t be incrementalized
Emergency fixes where speed matters more than availability

But for production systems serving customers? Expand-contract every time.

Database migrations don’t have to be scary. The expand-contract pattern transforms a single terrifying change into a series of small, safe, reversible steps.

It’s more work upfront. But the first time you rename a column at 2pm on a Tuesday with zero customer impact, you’ll understand why it’s worth it.

The Core Idea#

Example: Renaming a Column#

The Expand-Contract Way#

Adding a NOT NULL Column#

Changing Column Types#

Large Table Migrations#

Foreign Key Considerations#

Index Creation#

Migration Tooling#

The Deployment Dance#

When Downtime Is Acceptable#

📬 Get the Newsletter