How to Setup a Zero-Downtime Database Migration: Expand and Contract Pattern

In the early stages of development, schema updates are simple. You can stop the application server, apply migration scripts, and restart the service. However, for active production systems, even short database lock intervals can disrupt users. Running migrations on active databases requires implementing zero-downtime migration patterns. In this engineering guide, we implement the expand-and-contract pattern to update live database tables.

The Expand-and-Contract Pattern

The expand-and-contract pattern allows you to run schema migrations in stages, ensuring that the database remains compatible with both old and new application code versions during deployment.

We deploy these resilient database patterns daily across our cloud platform engineering projects to ensure data integrity.

The Step-by-Step Migration Process

Consider a migration where you need to split a name column into separate firstName and lastName columns on a live table:

Expand & Contract Phase Map:
[Phase 1: Expand DB] ──► [Phase 2: Dual Write] ──► [Phase 3: Backfill Data] ──► [Phase 4: Read Shift] ──► [Phase 5: Contract DB]
(Add new columns)        (Code writes to both)    (Migrate legacy data)   (Shift read queries)     (Drop legacy column)

1. Step 1: Expand the Schema (Add New Columns): Create the new columns on the table, keeping them optional to avoid validation errors from older code versions.

   ALTER TABLE users ADD COLUMN first_name VARCHAR(255);
   ALTER TABLE users ADD COLUMN last_name VARCHAR(255);

2. Step 2: Start Dual Writing: Deploy an application update that writes new user submissions to both the legacy and the new columns.

   // Prisma dual-write example
   await prisma.user.update({
     where: { id },
     data: {
       name: `${firstName} ${lastName}`, // Legacy column
       first_name: firstName,             // New column
       last_name: lastName                // New column
     }
   });

3. Step 3: Backfill Legacy Records: Run a background migration script to parse the legacy data and populate the new columns for existing records:

   UPDATE users SET 
     first_name = split_part(name, ' ', 1),
     last_name = split_part(name, ' ', 2)
   WHERE first_name IS NULL;

4. Step 4: Shift Read Queries: Deploy another application update that configures all read queries to use the new columns instead of the legacy field.
5. Step 5: Contract the Schema (Drop Legacy Columns): Once you confirm that no active code paths reference the legacy field, run a final migration script to drop the column:

   ALTER TABLE users DROP COLUMN name;

Step-by-Step Zero-Downtime Database Migration Checklist

Enforce zero-downtime database migrations by following this 10-step checklist:

1. Map Database Dependencies: Review code repositories to identify all read and write queries referencing target columns.
2. Add New Columns: Run migration scripts to add new fields while keeping them optional.
3. Configure Dual Writing: Deploy application updates that write incoming data to both legacy and new columns.
4. Prepare Backfill Script: Write background migration scripts to copy historical data to the new columns.
5. Execute Backfill Batches: Run backfill scripts in small batches (e.g., 5,000 records) to avoid table lock conflicts.
6. Verify Backfill Accuracy: Run data consistency checks to ensure legacy data matches new column values.
7. Update Read Queries: Deploy application updates to query values from the new columns.
8. Remove Dual Write Code: Update code paths to stop writing values to legacy columns.
9. Drop Legacy Columns: Execute final migration scripts to drop legacy columns from the database.
10. Monitor Database Performance: Set up performance trackers to log table queries and ensure database stability.

Summary of Recommendations

Running database migrations without downtime requires splitting schema updates into distinct phases. Implementing the expand-and-contract pattern protects your production data and ensures service availability during deployments.

Data Migration & Schema Evolution (Deep-Dive Analysis #1): Architectural Strategy

Managing schema updates on high-traffic tables requires careful management of locks. When you add a column or run update queries, the database engine requests table-level locks that can block incoming read and write transactions. To prevent transaction delays, developers should run migrations during off-peak hours and configure strict lock timeout settings: SET lock_timeout = '2s';. This configuration ensures that if a migration cannot acquire a lock within 2 seconds, it aborts, protecting user requests from being blocked.

Data Migration & Schema Evolution (Deep-Dive Analysis #2): Operational Guidelines

Additionally, developers must verify data consistency between legacy and new columns before dropping old tables. We achieve this by running verification scripts that compare row checksums across both datasets. If any discrepancies are found, the migration is paused until the differences are resolved. By combining batch updates, lock timeouts, and validation checks, you can execute schema migrations without downtime.

Mathematical Modeling Analysis

We analyze database lock contention by modeling transaction arrivals as a Poisson Process. If a migration lock holds a table for t seconds, the probability of blocking user transactions increases exponentially. By splitting historical data backfills into batches with wait intervals (pg_sleep), we keep the migration lock times short, ensuring that transaction latency remains stable during database updates.