Zero-Downtime Database Migrations: The Expand-Contract Pattern in Practice

Deploying application code is easy. You build a container, push it to your orchestrator, route traffic to the new instances, and terminate the old ones. Continuous Delivery pipelines make this dance happen dozens of times a day.

Now, try the same thing with a database schema change.

If you drop a column that an older version of your application still depends on, your production environment goes down. If you add a NOT NULL constraint without a default value, every active insert query fails instantly. In distributed, fast-moving environments, coupling database modifications directly to monolithic deployment steps is a recipe for incidents.

To achieve true zero-downtime deployments, you must decouple application releases from database schema changes. The industry-standard approach for achieving this is the Expand-Contract Pattern (also known as the Parallel Run pattern).

In this guide, we will break down how to implement the expand-contract pattern in a modern CI/CD pipeline, walking through real-world examples including renaming columns, altering constraints, and managing multi-phase deployments safely.

The Core Philosophy: Decoupling Code and Data

The fundamental rule of zero-downtime migrations is simple:

Never make a breaking change to a database schema in a single migration script.

Instead, every complex schema modification is broken down into safe, additive phases that allow both the old version and the new version of your application to run concurrently against the database without errors.

The Expand-Contract lifecycle consists of three distinct phases:

1. Expand (Additive): Introduce new database structures (columns, tables, indexes) alongside the old ones. The application is updated to write to both (or write to the new and read from the old), but nothing is removed.
2. Migrate (Backfill): Copy historical data from the old structures to the new structures asynchronously.
3. Contract (Destructive): Remove the old database structures once all application instances are running the updated code and no legacy queries remain.

Let’s see how this works in practice with a classic scenario: renaming a column.

Real-World Scenario 1: Safely Renaming a Column

Imagine you have a users table with a column named handle, and you want to rename it to username.

If you simply write a migration that executes ALTER TABLE users RENAME COLUMN handle TO username;, any running instance of your application trying to execute SELECT handle FROM users will crash with a missing column error.

Here is how you handle it safely using four sequential deployment steps.

Phase 1: Expand (Add the new column)

First, create a migration that adds the new username column, making it nullable and keeping the old handle column completely untouched.

-- Migration 001_expand_add_username.sql
ALTER TABLE users ADD COLUMN username VARCHAR(255) NULL;

Phase 2: Dual-Write Application Logic

Deploy an application update that handles dual-writing. During this phase:

• Writes: The application writes the exact same value to both handle and username.
• Reads: The application continues to read primarily from handle (falling back to username if needed).

# Application Code (Phase 2)
def update_user_handle(user_id, new_handle):
    db.execute(
        "UPDATE users SET handle = :val, username = :val WHERE id = :id",
        {"val": new_handle, "id": user_id}
    )

def get_user_username(user_id):
    user = db.fetch("SELECT handle, username FROM users WHERE id = :id", {"id": user_id})
    # Fallback safety
    return user.username if user.username else user.handle

Phase 3: Backfill Historical Data

Run an asynchronous background script or migration to populate the username column for all rows where it is currently NULL.

-- Migration 002_backfill_username.sql
-- Run in small batches to avoid lock contention on large tables
UPDATE users 
SET username = handle 
WHERE username IS NULL 
LIMIT 5000;

Once the backfill is complete, deploy another application update that switches reads completely over to the username column, while maintaining the dual-write to both columns just in case a rollback is required.

Phase 4: Contract (Remove the old column)

After your metrics confirm that 100% of your application traffic has been running the latest code for a safe buffer period (e.g., 24 to 48 hours), you can finally remove the dual-write logic from your application and drop the old column.

-- Migration 003_contract_drop_handle.sql
ALTER TABLE users DROP COLUMN handle;

Real-World Scenario 2: Adding NOT NULL Constraints

Adding a NOT NULL constraint to an existing table with millions of rows is notoriously dangerous. If you run ALTER TABLE orders ALTER COLUMN status SET NOT NULL;, the database engine typically must scan every single row to verify compliance, locking the table and blocking all incoming writes.

The Safe Approach:

1. Add a Check Constraint with NOT VALID: Modern relational databases like PostgreSQL allow you to add a check constraint without instantly validating existing rows.

-- Phase 1: Add constraint without validating existing rows immediately
ALTER TABLE orders ADD CONSTRAINT check_status_not_null CHECK (status IS NOT NULL) NOT VALID;

2. Validate the Constraint Background: Once the table is no longer locked, instruct the database to validate the constraint in the background.

-- Phase 2: Validate asynchronously
ALTER TABLE orders VALIDATE CONSTRAINT check_status_not_null;

3. Promote to Native NOT NULL: Once validated, you can safely swap it for a native column constraint during a low-traffic maintenance window or via standard schema evolution.

Orchestrating Migrations in Modern CI/CD Pipelines

Executing these multi-phase patterns requires shifting how your CI/CD pipeline thinks about database migrations. You can no longer treat database migration as a simple helm upgrade or a blanket npm run migrate command executed blindly at startup.

Recommended CI/CD Workflow Architecture

[Code Commit] 
     │
     ▼
[CI Build & Test] 
     │
     ▼
[Phase N: Expand Migration] ──► (Run automatically in staging/prod)
     │
     ▼
[Deploy Application v2]     ──► (Supports old & new schema via dual-writes)
     │
     ▼
[Soak Period / Backfill]    ──► (Verify metrics, run backfill scripts)
     │
     ▼
[Phase N+1: Contract]       ──► (Executed in a subsequent deployment cycle)

Best Practices for Pipeline Safety

• Never run destructive migrations in the same pipeline as application deployments. Separate your CREATE and DROP commands across separate deployment cycles.
• Design for Backward and Forward Compatibility: Always ensure that Version $N$ of your application can run on Version $N-1$ of the database schema, and Version $N+1$ of the application can run on Version $N$ of the schema.
• Utilize Feature Flags: If a schema change alters business logic flow, wrap the new code paths in feature flags. This allows you to deploy the code containing the dual-write logic before you actually start writing data to the new schema.
• Implement Automated Schema Linting: Use tools like sqlfluff or specialized CI linters to catch dangerous operations (such as adding unconstrained unique keys or dropping columns) before code ever reaches pull request reviews.

Conclusion

Zero-downtime database migrations require a shift in mindset: code changes are ephemeral, but data is permanent. By embracing the Expand-Contract Pattern, you stop treating database updates as all-or-nothing atomic operations and start treating them as orchestrated, multi-step evolutionary processes.

By splitting your migrations into expand, backfill, and contract phases, and aligning them carefully with your application release pipelines, you can ship complex schema modifications to high-throughput production environments in the middle of a Tuesday afternoon without dropping a single packet.