Database Migration Strategies: Zero-Downtime Approaches

In This Guide

• 1. Types of Database Migrations
• 2. Schema Migrations — The Expand-Contract Pattern
• 3. Engine Migrations — Changing Databases Entirely
• 4. The Dual-Write Pattern
• 5. Shadow Database Strategy
• 6. Large-Scale Data Migration
• 7. Rollback Strategies
• 8. Migration Checklist
• 9. Frequently Asked Questions

Example: Renaming a Column (the safe way)

-- WRONG: This breaks all existing queries instantly
ALTER TABLE users RENAME COLUMN name TO full_name;  -- 💥 Downtime

-- RIGHT: Expand-Contract (3 deployments, 0 downtime)

-- Deploy 1: EXPAND — add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Backfill (batched to avoid locks):
UPDATE users SET full_name = name WHERE id BETWEEN 1 AND 10000;
UPDATE users SET full_name = name WHERE id BETWEEN 10001 AND 20000;
-- ...continue in batches

-- Deploy 2: MIGRATE — app writes to both, reads from new
-- Application code:
-- INSERT INTO users (name, full_name, ...) VALUES ($1, $1, ...);
-- SELECT full_name FROM users WHERE ...;

-- Deploy 3: CONTRACT — remove old column (after verifying all reads use new)
ALTER TABLE users DROP COLUMN name;

The Four Phases

Phase 1: PREPARE (2-4 weeks)
├── Audit all queries — identify incompatible syntax
├── Build abstraction layer (Repository pattern)
├── Set up new database with equivalent schema
├── Build data validation scripts (row counts, checksums)
└── Create rollback plan

Phase 2: DUAL-WRITE (1-2 weeks)
├── Write to BOTH old and new databases
├── Read from OLD database only
├── Verify new database has consistent data
└── Fix any sync discrepancies

Phase 3: SHADOW-READ (1-2 weeks)
├── Read from BOTH databases
├── Compare results, log mismatches
├── Primary reads still from old database
└── Fix all mismatches before proceeding

Phase 4: CUTOVER (1 day)
├── Switch reads to new database
├── Keep dual-writes for rollback window (24-48 hours)
├── Monitor error rates, latency, data consistency
└── Disable old database writes after rollback window

Dual-Write Implementation

class OrderRepository {
    constructor(
        private oldDb: MySQLConnection,
        private newDb: PostgreSQLConnection,
        private migrationPhase: 'dual-write' | 'shadow-read' | 'cutover'
    ) {}

    async createOrder(order: Order): Promise<Order> {
        // Always write to old (source of truth during migration)
        const result = await this.oldDb.insert('orders', order);

        // Write to new database — async, don't block on failure
        try {
            await this.newDb.insert('orders', this.transformForNewDb(order));
        } catch (error) {
            // Log for reconciliation, DON'T fail the request
            logger.error('Dual-write to new DB failed', { orderId: order.id, error });
            await this.reconciliationQueue.add({ table: 'orders', id: order.id });
        }

        return result;
    }

    async getOrder(id: string): Promise<Order> {
        if (this.migrationPhase === 'cutover') {
            return this.newDb.query('SELECT * FROM orders WHERE id = $1', [id]);
        }

        const oldResult = await this.oldDb.query('SELECT * FROM orders WHERE id = ?', [id]);

        if (this.migrationPhase === 'shadow-read') {
            // Read from new DB too, compare results, log mismatches
            const newResult = await this.newDb.query('SELECT * FROM orders WHERE id = $1', [id]);
            if (!this.deepEqual(oldResult, newResult)) {
                logger.warn('Shadow read mismatch', { id, old: oldResult, new: newResult });
            }
        }

        return oldResult;  // Always return from old during migration
    }
}

• Ordering issues — if write to old succeeds but new fails, then retries, you can get out-of-order writes. Use idempotent writes.
• Increased latency — writing to two databases doubles write time. Make the secondary write async.
• Transaction consistency — you can't have ACID transactions across two databases. Accept eventual consistency during migration.

Application ──→ Old Database (MySQL)
                    │
                    │  CDC (Debezium / DMS)
                    ▼
              Shadow Database (PostgreSQL)
                    │
                    │  Validation job (compares row counts, checksums)
                    ▼
              Dashboard: "99.97% consistent, 12 rows divergent"

When consistency reaches 100% and stays there for 48 hours → safe to cut over

Batched Data Backfill

#!/bin/bash
# Batched migration — 1000 rows at a time with throttling

BATCH_SIZE=1000
LAST_ID=0
TOTAL=0

while true; do
    # Migrate next batch
    COUNT=$(psql -t -A -c "
        INSERT INTO new_orders (id, customer_id, amount, status, created_at)
        SELECT id, customer_id, amount, status, created_at
        FROM old_orders
        WHERE id > $LAST_ID
        ORDER BY id
        LIMIT $BATCH_SIZE
        ON CONFLICT (id) DO NOTHING
        RETURNING 1
    " | wc -l)

    # Update cursor
    LAST_ID=$(psql -t -A -c "
        SELECT COALESCE(MAX(id), $LAST_ID) FROM new_orders WHERE id > $LAST_ID
    ")

    TOTAL=$((TOTAL + COUNT))
    echo "Migrated $TOTAL rows (last_id: $LAST_ID)"

    # No more rows — done
    if [ "$COUNT" -lt "$BATCH_SIZE" ]; then
        echo "Migration complete: $TOTAL rows"
        break
    fi

    # Throttle — sleep between batches to avoid overwhelming the database
    sleep 0.1
done

Pre-Migration

• Document rollback plan — specific commands, not "revert if needed"
• Test migration on staging with production-size data
• Measure migration duration on staging (multiply by 1.5x for production)
• Notify on-call team and stakeholders of migration window
• Take a backup (and verify you can restore from it)
• Set up monitoring dashboards for error rates and latency

During Migration

• Monitor database CPU, connections, replication lag
• Watch application error rates in real-time
• Run data validation queries continuously
• Have rollback command ready to execute

Post-Migration

• Verify row counts match (old vs new)
• Run checksums on critical tables
• Test all critical application flows end-to-end
• Keep old database running for rollback window (minimum 48 hours)
• Schedule cleanup of old resources after rollback window

Related Articles