Zero-Downtime Database Migrations: Patterns That Actually Work

Zero-Downtime Database Migrations: Patterns That Actually Work

Changing a database schema in production without downtime is one of the hardest problems in software engineering. Here are the patterns that work at scale.

The Expand-Contract Pattern

Every breaking schema change becomes three deployments:

–

Expand: Add the new column/table alongside the old one. Application writes to both.

–

Migrate: Backfill existing data. Verify consistency.

–

Contract: Remove the old column/table. Application reads only from new.

Renaming a Column (The Safe Way)

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Backfill (in batches!)
UPDATE users SET full_name = name
WHERE full_name IS NULL LIMIT 10000;

-- Step 3: Deploy code that writes both, reads new
-- Step 4: Verify all rows migrated
-- Step 5: Deploy code that only uses full_name
-- Step 6: Drop old column
ALTER TABLE users DROP COLUMN name;

Large Table Migrations

For tables with billions of rows, use ghost table tools like gh-ost or pt-online-schema-change. They create a shadow table, replicate changes via binlog, then swap atomically. Zero locks, zero downtime.