Zero-Downtime Migrations — Complete Guide
In this tutorial, you will learn about Zero. We cover key concepts, practical examples, and best practices to help you master this topic.
Learn zero-downtime database migrations: change schemas without application downtime using expand-contract patterns, online schema change tools, feature flags, and phased deployment strategies for high-availability production systems.
What You Learn
You will learn how to perform database schema changes without taking the application offline: the expand-contract pattern for non-breaking changes, using gh-ost and pt-online-schema-change for large tables, feature flag gating for migration safety, and phased deployment strategies that eliminate downtime during schema evolution.
Why It Matters
Traditional migrations lock tables, block queries, and cause downtime. For 24/7 applications, even seconds of downtime translate to revenue loss and poor user experience. Zero-downtime migrations allow schema changes without interrupting service, enabling continuous deployment and high availability.
Real-World Use
DodaTech's Durga Antivirus Pro processes 10,000 file scans per minute. A traditional migration to add an index would lock the scans table for 45 seconds. Using gh-ost, the index was added online. Zero queries were blocked. The application continued processing scans without interruption.
The Problem: Locking Migrations
-- Traditional migration: table is locked during execution
ALTER TABLE orders ADD COLUMN discount NUMERIC(5,2) DEFAULT 0;
-- While this runs:
-- - All writes to orders table are blocked
-- - Reads may be blocked depending on database engine
-- - Application may experience errors or timeouts
-- On MySQL, the table is locked for the duration
-- On PostgreSQL, reads continue but writes are blocked
-- On a 10M row table, this can take minutes
// Application errors during locking migration
async function createOrder(data) {
// This query will timeout during migration
const result = await db.query(
'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id',
[data.userId, data.total]
);
// Error: Query timed out - table is locked
return result;
}
Expected output: Traditional ALTER TABLE locks the table. Application queries that hit the locked table timeout or fail. For high-traffic applications, this causes errors for all users during the migration window.
Expand-Contract Pattern
# Phase 1: Expand - Add new columns alongside old ones
# migration_phase1.py
"""Phase 1: Add new schema (non-breaking)."""
def upgrade():
# Add new column as nullable (no lock contention for this operation)
op.add_column('users',
sa.Column('email_v2', sa.String(255), nullable=True)
)
op.add_column('users',
sa.Column('email_verified_v2', sa.Boolean(), nullable=True)
)
def downgrade():
op.drop_column('users', 'email_v2')
op.drop_column('users', 'email_verified_v2')
# Phase 2: Backfill - Copy data (application still uses both columns)
# migration_phase2.py
"""Phase 2: Backfill new columns from old ones."""
def upgrade():
connection = op.get_bind()
connection.execute(sa.text("""
UPDATE users
SET email_v2 = email,
email_verified_v2 = email_verified
WHERE email_v2 IS NULL
"""))
def downgrade():
connection = op.get_bind()
connection.execute(sa.text("""
UPDATE users SET email_v2 = NULL, email_verified_v2 = NULL
"""))
# Phase 3: Contract - Remove old columns (future release)
# migration_phase3.py
"""Phase 3: Remove old columns after app is updated."""
def upgrade():
# Only run after application has been updated to use v2 columns
op.drop_column('users', 'email')
op.drop_column('users', 'email_verified')
op.alter_column('users', 'email_v2', new_column_name='email')
op.alter_column('users', 'email_verified_v2', new_column_name='email_verified')
def downgrade():
# Recreate old columns and copy data back
op.alter_column('users', 'email', new_column_name='email_v2')
op.alter_column('users', 'email_verified', new_column_name='email_verified_v2')
op.add_column('users', sa.Column('email', sa.String(255)))
op.add_column('users', sa.Column('email_verified', sa.Boolean()))
op.execute("UPDATE users SET email = email_v2, email_verified = email_verified_v2")
Expected output: Expand-contract pattern spreads schema changes across multiple releases. Phase 1 adds new columns without removing old ones (no breaking change). Phase 2 backfills data. Phase 3 removes old columns after the application is updated. No single release causes downtime.
Online Schema Change with gh-ost
# gh-ost: Online schema migration for MySQL
# Add a column without locking the table
gh-ost \
--host=127.0.0.1 \
--port=3306 \
--user=migration_user \
--password=secret \
--database=myapp \
--table=orders \
--alter="ADD COLUMN discount NUMERIC(5,2) DEFAULT 0" \
--execute
# Output:
# Starting gh-ost...
# Copying rows from orders to _orders_gho...
# Copy progress: 45% (4,561,234/10,000,000)
# Copy progress: 78% (7,812,456/10,000,000)
# Copy progress: 100% (10,000,000/10,000,000)
# Renaming _orders_gho to orders...
# Rename completed in 0.002 seconds
# gh-ost completed successfully
# Application behavior during gh-ost migration
import pymysql
connection = pymysql.connect(host='127.0.0.1', user='app', database='myapp')
# During gh-ost migration:
# - Original table 'orders' is still accessible
# - Writes go to both original and ghost table via triggers
# - Reads come from original table
with connection.cursor() as cursor:
# This INSERT works normally during migration
cursor.execute(
"INSERT INTO orders (user_id, total) VALUES (%s, %s)",
(123, 99.99)
)
connection.commit()
# After gh-ost completes (sub-second rename):
# The ghost table replaces the original
# No downtime, no locked tables, no errors
Expected output: gh-ost creates a ghost table with the new schema, copies rows in the background, applies live changes via triggers, and swaps tables in under a second. The application experiences zero downtime.
Online Schema Change with pt-online-schema-change
# pt-online-schema-change: Percona Toolkit alternative
# Add index without locking
pt-online-schema-change \
--alter="ADD INDEX idx_created_at (created_at)" \
--host=127.0.0.1 \
--user=migration_user \
--password=secret \
--database=myapp \
--table=orders \
--execute \
--chunk-time=1 \
--max-load=Threads_running=50
# Output:
# Using database myapp
# Creating new table...
# Created table _orders_new
# Altering new table...
# Creating triggers...
# Copying rows...
# Chunk 1: 1000 rows copied (10%)
# Chunk 2: 1000 rows copied (20%)
# ...
# Chunk 10: 1000 rows copied (100%)
# Swapping tables...
# Swapped successfully
# Dropping old table...
# pt-online-schema-change completed
Expected output: pt-online-schema-change creates a new table with the index, copies data in chunks, monitors server load, and swaps tables atomically. The --max-load flag prevents overloading the server during copy.
Feature Flag Gating
// Feature-flag gated schema changes
// config/feature-flags.js
const features = {
newEmailSchema: process.env.NEW_EMAIL_SCHEMA === 'true',
};
// migration_runner.js
async function runMigrationWithFeatureFlag() {
// Step 1: Deploy migration (adds new columns as nullable)
await runMigration('add_new_email_columns');
// Step 2: Deploy application with feature flag OFF
// Application still uses old columns
await deployApplication();
// Step 3: Enable feature flag gradually
await enableFeatureFlag('newEmailSchema', '10%');
await monitor('24h', 'no errors');
await enableFeatureFlag('newEmailSchema', '50%');
await monitor('24h', 'no errors');
await enableFeatureFlag('newEmailSchema', '100%');
// Step 4: Deploy cleanup migration (drops old columns)
await runMigration('remove_old_email_columns');
}
// Application code with feature flag
function getUserEmail(user) {
if (features.newEmailSchema) {
return user.email_v2;
}
return user.email;
}
Expected output: Feature flags allow gradual rollout of schema-dependent code. Migration adds new columns without removing old ones. Application toggles between old and new schema using the flag. Cleanup migration runs after the flag is fully enabled.
Phased Deployment Strategy
# Phased deployment for zero-downtime migration
phases:
- name: phase1-expand
description: Add new columns as nullable
migration: migration_001_add_new_columns
application_change: none
risk: low
rollback: drop new columns
- name: phase2-backfill
description: Backfill data in batches
migration: migration_002_backfill
application_change: none
risk: medium (data migration)
rollback: re-run backfill if failed
- name: phase3-feature-flag-deploy
description: Deploy app with feature flag (disabled)
migration: none
application_change: read new schema behind flag
risk: low (flag is off)
rollback: redeploy previous version
- name: phase4-enable-feature
description: Enable feature flag gradually
migration: none
application_change: use new schema
risk: medium (runtime changes)
rollback: disable feature flag
- name: phase5-contract
description: Remove old columns
migration: migration_003_remove_old_columns
application_change: none
risk: low (old columns unused)
rollback: restore old columns from backup
Expected output: Phased deployment plan lists each step with migration, application change, risk level, and rollback strategy. Each phase is a separate release. The plan ensures zero downtime by avoiding any single breaking change.
Monitoring During Migration
# scripts/monitor_zero_downtime.py
"""Monitor database during online migration."""
import psutil
import time
from datetime import datetime
def monitor_migration(alert_thresholds):
"""Monitor database health during migration."""
alerts = []
while is_migration_running():
# Check connection count
connections = get_db_connections()
if connections > alert_thresholds['max_connections']:
alerts.append({
'type': 'connection_overflow',
'value': connections,
'threshold': alert_thresholds['max_connections'],
'time': datetime.now().isoformat(),
})
# Check query latency
avg_latency = get_query_latency()
if avg_latency > alert_thresholds['max_latency_ms']:
alerts.append({
'type': 'high_latency',
'value': avg_latency,
'threshold': alert_thresholds['max_latency_ms'],
'time': datetime.now().isoformat(),
})
# Check replication lag
replication_lag = get_replication_lag()
if replication_lag > alert_thresholds['max_replication_lag']:
alerts.append({
'type': 'replication_lag',
'value': replication_lag,
'threshold': alert_thresholds['max_replication_lag'],
'time': datetime.now().isoformat(),
})
# Check error rate
error_rate = get_query_error_rate()
if error_rate > alert_thresholds['max_error_rate']:
alerts.append({
'type': 'error_rate',
'value': error_rate,
'threshold': alert_thresholds['max_error_rate'],
'time': datetime.now().isoformat(),
})
if alerts:
send_alert(alerts)
alerts = []
time.sleep(10)
print("Migration completed. No issues detected.")
Expected output: Migration monitor tracks connection count, query latency, Replication lag, and error rate during the migration. Alerts are triggered if any threshold is exceeded. Monitoring ensures the migration is not impacting application performance.
Common Mistakes
1. Assuming All Migrations Are Zero-Downtime
Not all migrations can be zero-downtime. Adding a NOT NULL column without a default locks tables. Renaming a column breaks application queries. Using zero-downtime patterns adds complexity. Evaluate whether downtime is acceptable before implementing complex patterns.
2. Skipping the Backfill Phase
Adding new columns without backfilling data leaves NULL values. The application must handle NULLs. Backfill data as a separate phase. Verify data completeness before removing old columns.
3. Removing Old Columns Too Early
Removing old columns before the application is fully updated causes errors. Wait until the feature flag is 100% enabled and the old code path is removed. Monitor for any code still referencing old columns.
4. Not Monitoring During Migration
Online schema changes can impact performance. Monitor connection count, query latency, and error rate during migration. Set alerts for threshold breaches. Be prepared to pause or abort the migration if issues arise.
5. Forgetting About Read Replicas
Online schema change tools may not handle read replicas automatically. Verify the migration propagates to replicas correctly. Check replication lag after migration. Ensure read replicas have the updated schema.
6. No Rollback Plan for Online Migrations
Online migrations can fail or cause issues. Have a rollback plan: abort the migration, switch back to the original table, or use a feature flag to revert. Test the rollback before running the migration.
Practice Questions
1. What is the expand-contract pattern?
A zero-downtime migration strategy: expand (add new columns alongside old ones), backfill (copy data to new columns), contract (remove old columns). Each phase is a separate release with no breaking changes.
2. How does gh-ost achieve zero-downtime migrations?
gh-ost creates a ghost table with the new schema, copies rows in the background, applies live changes via binlog triggers, and swaps tables atomically. The original table remains accessible during the entire Process.
3. Why use feature flags with migrations?
Feature flags allow gradual rollout of schema-dependent code. The migration adds new columns. The feature flag toggles between old and new schema. If issues arise, the flag is disabled without reverting the migration.
4. What should you monitor during an online migration?
Connection count (ensure Connection Pool is not exhausted), query latency (migration should not slow queries), replication lag (replicas should stay in sync), and error rate (application queries should not fail).
Challenge
Plan and execute a zero-downtime migration for renaming a column: write the expand migration (add new column, nullable), write the backfill migration (copy data from old to new), deploy application with feature flag to read from new column, enable feature flag gradually (10%, 50%, 100%), write the contract migration (remove old column), and verify zero downtime with monitoring.
FAQ
Mini Project: Zero-Downtime Migration Framework
Build a zero-downtime migration framework that: supports expand-contract pattern with phased migrations, integrates gh-ost or pt-online-schema-change for large table changes, uses feature flags for gradual schema rollout, monitors database health during migration (connections, latency, errors), supports phased deployment with separate releases, and includes automated rollback if thresholds are breached.
What's Next
Now that you understand zero-downtime migrations, explore Migration Tools Comparison to choose the right tool for your tech stack.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro