Chapter 75: Zero-Downtime Migrations

Schema Migrations

Zero-downtime migrations allow you to evolve your database schema and application code without service interruption. The key is making changes backward compatible.

Expand/Contract Pattern

The expand/contract (or expand/migrate/contract) pattern is fundamental to zero-downtime migrations:

graph LR A[Old Schema
Old Code] --> B[Expanded Schema
Old Code] B --> C[Expanded Schema
New Code] C --> D[Contracted Schema
New Code]

Three-Phase Migration

Phase 1 - Expand: Add new schema elements (columns, tables) without removing old ones. Deploy code that writes to both old and new schema but only reads from old.

-- Phase 1: Expand - Add new column
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- Application code (Phase 1)
public void UpdateUser(User user) {
    // Write to both old and new
    db.Execute(@"
        UPDATE users
        SET email_confirmed = @confirmed,
            email_verified = @confirmed  -- New column
        WHERE id = @id",
        new { id = user.Id, confirmed = user.EmailConfirmed });
}

public User GetUser(int id) {
    // Read from old column only
    return db.Query<User>(
        "SELECT *, email_confirmed as EmailConfirmed FROM users WHERE id = @id",
        new { id }
    ).First();
}

Phase 2 - Migrate: Copy data from old schema to new. Deploy code that writes to both but reads from new.

-- Phase 2: Migrate data
UPDATE users SET email_verified = email_confirmed WHERE email_verified IS NULL;

-- Application code (Phase 2)
public User GetUser(int id) {
    // Now read from new column
    return db.Query<User>(
        "SELECT *, email_verified as EmailConfirmed FROM users WHERE id = @id",
        new { id }
    ).First();
}

Phase 3 - Contract: Remove old schema elements. Deploy code that only uses new schema.

-- Phase 3: Contract - Remove old column
ALTER TABLE users DROP COLUMN email_confirmed;

-- Application code (Phase 3)
public void UpdateUser(User user) {
    // Write only to new column
    db.Execute(@"
        UPDATE users
        SET email_verified = @confirmed
        WHERE id = @id",
        new { id = user.Id, confirmed = user.EmailConfirmed });
}

Backward Compatible Changes

Safe changes that don't break existing code:

Adding nullable columns
Adding columns with defaults
Adding new tables
Adding indexes (with online algorithms)
Relaxing constraints (e.g., removing NOT NULL)

Unsafe changes requiring expand/contract:

Renaming columns or tables
Dropping columns or tables
Changing column types
Adding NOT NULL constraints
Splitting or merging tables

Interview Question: How would you rename a column with zero downtime?

Answer: Phase 1: Add new column, deploy code that writes to both columns but reads from old. Phase 2: Backfill new column with old column's data, deploy code that reads from new column. Phase 3: Drop old column. Each phase is a separate deployment with validation between phases.

Online Schema Changes

Traditional ALTER TABLE statements lock tables, causing downtime. Online schema change tools allow modifications without blocking reads or writes.

pt-online-schema-change (Percona)

Tool for MySQL that performs schema changes without locking the table:

# Add index without blocking
pt-online-schema-change \
  --alter "ADD INDEX idx_email (email)" \
  --execute \
  h=localhost,D=mydb,t=users \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=50

# How it works:
# 1. Create new table with desired schema
# 2. Copy data in chunks
# 3. Create triggers to keep tables in sync
# 4. Swap tables atomically
# 5. Drop old table

gh-ost (GitHub)

GitHub's triggerless online schema change tool for MySQL:

# Add column with gh-ost
gh-ost \
  --user=root \
  --host=localhost \
  --database=mydb \
  --table=users \
  --alter="ADD COLUMN last_login TIMESTAMP" \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=50 \
  --throttle-query="SELECT HOUR(NOW()) BETWEEN 9 AND 17" \
  --execute

# Advantages over pt-online-schema-change:
# - No triggers (uses binlog instead)
# - Pauseable and resumable
# - Testable (--test-on-replica)
# - Better throttling control

pg_repack (PostgreSQL)

PostgreSQL extension for online table reorganization:

-- Install extension
CREATE EXTENSION pg_repack;

-- Repack table (reorganize, reclaim space, rebuild indexes)
pg_repack -t users -k

-- Add column (PostgreSQL 11+ does this online natively)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- For complex changes, use pg_repack to rebuild table
-- while keeping it online via log table

Native Online DDL

Modern databases support some online operations natively:

-- MySQL 8.0+ online DDL
ALTER TABLE users
  ADD COLUMN preferences JSON,
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- PostgreSQL 11+ fast default values
ALTER TABLE users
  ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Fast: metadata-only change

-- PostgreSQL concurrent index creation
CREATE INDEX CONCURRENTLY idx_email ON users(email);
-- Allows reads/writes during creation

Interview Question: What are the risks of online schema change tools?

Answer: Risks include: replication lag (large table copies generate significant binlog), storage requirements (need space for duplicate table), triggers add overhead (pt-osc), long-running transactions can block the final swap, bugs in tooling could corrupt data. Mitigations: test on staging/replica first, monitor replication lag, perform during low-traffic periods, use throttling, have rollback plan, validate data after migration.

Dual-Write Pattern

When migrating from one data store to another, write to both systems during transition period while reading from old. Then switch reads to new system.

sequenceDiagram participant App participant OldDB participant NewDB Note over App,NewDB: Phase 1: Dual Write App->>OldDB: Write App->>NewDB: Write App->>OldDB: Read Note over App,NewDB: Phase 2: Switch Reads App->>OldDB: Write App->>NewDB: Write App->>NewDB: Read Note over App,NewDB: Phase 3: Drop Old App->>NewDB: Write App->>NewDB: Read

Implementation

public class UserRepository {
    private readonly IDatabase oldDb;
    private readonly IDatabase newDb;
    private readonly IFeatureFlags flags;

    public async Task<User> GetUser(int id) {
        // Phase 2: Read from new, fallback to old
        if (flags.IsEnabled("read-from-new-db")) {
            var user = await newDb.GetUser(id);
            if (user != null) return user;

            // Fallback to old
            return await oldDb.GetUser(id);
        }

        // Phase 1: Read from old
        return await oldDb.GetUser(id);
    }

    public async Task UpdateUser(User user) {
        // Always write to old (safety)
        await oldDb.UpdateUser(user);

        // Dual write to new
        if (flags.IsEnabled("dual-write")) {
            try {
                await newDb.UpdateUser(user);
            } catch (Exception ex) {
                // Don't fail request if new db fails
                logger.LogError(ex, "Failed to write to new database");
                metrics.IncrementCounter("dual_write_failures");
            }
        }
    }
}

Consistency Challenges

Dual writes are not atomic. Problems that can occur:

Partial Failure: Write succeeds in old, fails in new
Ordering: Concurrent writes may arrive in different order
Data Drift: Databases get out of sync over time

Verification and Reconciliation

// Background job to verify consistency
public class ConsistencyChecker {
    public async Task VerifyUser(int userId) {
        var oldUser = await oldDb.GetUser(userId);
        var newUser = await newDb.GetUser(userId);

        if (!UsersAreEqual(oldUser, newUser)) {
            logger.LogWarning(
                "Data inconsistency for user {UserId}: old={OldData} new={NewData}",
                userId, oldUser, newUser
            );

            // Reconcile: old is source of truth
            await newDb.UpdateUser(oldUser);
            metrics.IncrementCounter("reconciliation_fixes");
        }
    }

    // Run for random sample of users continuously
    public async Task ContinuousVerification() {
        while (migrationInProgress) {
            var userIds = await GetRandomUserSample(1000);
            foreach (var id in userIds) {
                await VerifyUser(id);
            }
            await Task.Delay(TimeSpan.FromMinutes(5));
        }
    }
}

Interview Question: How would you handle dual-write failures?

Answer: Log failures with details, increment metrics/alerts, use dead-letter queue to capture failed writes for later retry, run background reconciliation job to fix inconsistencies, consider making old database authoritative (new DB failures don't break requests), potentially use outbox pattern for guaranteed delivery, and have monitoring dashboard showing dual-write success rate.

Shadow Traffic

Mirror production traffic to new system for testing without affecting users. Compare results between old and new systems.

graph TB A[Load Balancer] --> B[Primary System] A -.->|Mirror| C[Shadow System] B --> D[Return Response] C --> E[Log Results
No Response] E --> F[Comparison Tool] B -.-> F

Implementation

// Nginx configuration for traffic mirroring
location / {
    # Primary backend
    proxy_pass http://primary_backend;

    # Mirror to shadow backend (async, no response to client)
    mirror /mirror;
    mirror_request_body on;
}

location /mirror {
    internal;
    proxy_pass http://shadow_backend;
    proxy_set_header X-Shadow-Request "true";
}

// Application: detect shadow requests
public class ShadowTrafficMiddleware {
    public async Task InvokeAsync(HttpContext context, RequestDelegate next) {
        bool isShadow = context.Request.Headers["X-Shadow-Request"] == "true";

        if (isShadow) {
            // Don't write to database, send emails, charge cards, etc.
            context.Items["ReadOnly"] = true;

            // Log everything for comparison
            var sw = Stopwatch.StartNew();
            await next(context);
            sw.Stop();

            await LogShadowResult(context, sw.ElapsedMilliseconds);
        } else {
            await next(context);
        }
    }
}

Comparing Results

public class ShadowComparison {
    public async Task CompareResults(string requestId) {
        var primary = await GetResponse("primary", requestId);
        var shadow = await GetResponse("shadow", requestId);

        var comparison = new {
            RequestId = requestId,
            StatusMatch = primary.Status == shadow.Status,
            BodyMatch = CompareJson(primary.Body, shadow.Body),
            PrimaryLatency = primary.Latency,
            ShadowLatency = shadow.Latency,
            LatencyDelta = shadow.Latency - primary.Latency,
            Differences = FindDifferences(primary.Body, shadow.Body)
        };

        await SaveComparison(comparison);

        if (!comparison.StatusMatch || !comparison.BodyMatch) {
            logger.LogWarning(
                "Shadow mismatch for request {RequestId}: {Differences}",
                requestId, comparison.Differences
            );
        }
    }

    private List<string> FindDifferences(string json1, string json2) {
        var obj1 = JsonSerializer.Deserialize<JsonElement>(json1);
        var obj2 = JsonSerializer.Deserialize<JsonElement>(json2);
        return JsonDiff.Compare(obj1, obj2);
    }
}

Use Cases

Testing Rewrites: Verify new implementation matches old
Performance Testing: Compare latency under real load
Canary Analysis: Test new version with production patterns
Load Testing: 2x traffic to shadow for capacity planning

Considerations

Prevent side effects in shadow system (no writes, external calls)
Handle non-deterministic responses (timestamps, IDs)
Storage costs for shadow system and logs
Network overhead of mirroring

Interview Question: How do you prevent shadow requests from causing side effects?

Answer: Tag shadow requests with header, use middleware to set read-only mode, mock external services (payment gateways, email), use separate database (read replica), disable write operations at ORM level, use transactions with rollback for shadow requests, or run shadow system in isolated environment with stubs.

Dark Launches

Deploy new features to production but don't expose them to users. Use feature flags to gradually enable for testing, then roll out to users.

graph TD A[Code Deployed] --> B[Feature Flag Off] B --> C[Internal Testing] C --> D[Beta Users 1%] D --> E[Gradual Rollout 10%] E --> F[Gradual Rollout 50%] F --> G[Full Launch 100%] G -.->|Issue Found| H[Rollback to 0%]

Feature Flag Implementation

public class FeatureFlagService {
    public bool IsEnabled(string featureName, User user = null) {
        var flag = GetFlag(featureName);

        // Not yet launched
        if (!flag.Enabled) return false;

        // Internal users always get it
        if (user?.IsInternal == true) return true;

        // Gradual rollout by user ID
        if (flag.RolloutPercentage < 100) {
            var bucket = HashUserId(user?.Id) % 100;
            if (bucket >= flag.RolloutPercentage) return false;
        }

        // User in beta list
        if (flag.BetaUsers.Contains(user?.Id)) return true;

        return flag.Enabled;
    }
}

// Usage in code
public async Task<IActionResult> GetRecommendations() {
    if (featureFlags.IsEnabled("new-recommendation-engine", currentUser)) {
        return await newRecommendationService.GetRecommendations();
    } else {
        return await oldRecommendationService.GetRecommendations();
    }
}

Phased Rollout Strategy

Phase 1 - Dark Launch (0%): Code deployed but feature off for all users. Internal testing only.

Phase 2 - Dogfooding (Internal): Enable for employees. Find obvious issues.

Phase 3 - Beta (1-5%): Enable for small percentage or beta user list. Monitor metrics closely.

Phase 4 - Gradual Rollout (10%, 25%, 50%): Increase percentage in stages. Watch for issues.

Phase 5 - Full Launch (100%): Enable for everyone. Can still roll back instantly.

Monitoring and Rollback

public class FeatureMetrics {
    public async Task TrackFeatureUsage(string feature, bool enabled, User user) {
        var variant = enabled ? "new" : "old";

        metrics.Increment($"feature.{feature}.{variant}.requests");

        // Track success/error rates by variant
        try {
            await PerformAction();
            metrics.Increment($"feature.{feature}.{variant}.success");
        } catch (Exception ex) {
            metrics.Increment($"feature.{feature}.{variant}.error");
            throw;
        }
    }
}

// Automated rollback on high error rate
public class FeatureHealthMonitor {
    public async Task MonitorFeature(string featureName) {
        var newErrorRate = await metrics.GetErrorRate($"feature.{featureName}.new");
        var oldErrorRate = await metrics.GetErrorRate($"feature.{featureName}.old");

        if (newErrorRate > oldErrorRate * 2 || newErrorRate > 0.05) {
            logger.LogError(
                "Feature {Feature} error rate too high: {Rate}. Rolling back.",
                featureName, newErrorRate
            );

            await featureFlags.Disable(featureName);
            await alerts.Send($"Auto-rollback: {featureName}");
        }
    }
}

A/B Testing

Dark launches enable A/B testing to measure feature impact:

// Assign users to variant
public string GetVariant(string experimentName, User user) {
    var experiment = experiments.Get(experimentName);
    if (!experiment.Running) return "control";

    var bucket = HashUserId(user.Id, experiment.Salt) % 100;

    if (bucket < 50) return "control";
    else return "treatment";
}

// Track conversion
public async Task TrackConversion(string experiment, User user, string event) {
    var variant = GetVariant(experiment, user);

    await analytics.Track(new {
        Experiment = experiment,
        Variant = variant,
        UserId = user.Id,
        Event = event,
        Timestamp = DateTime.UtcNow
    });
}

Interview Question: What's the difference between feature flags and branching?

Answer: Feature flags allow runtime toggling without deployment, enable gradual rollouts, support A/B testing, and provide instant rollback. Branching requires redeployment to change, is all-or-nothing, and rollback means reverting code. Feature flags are superior for production rollouts but add code complexity. Use flags for user-facing features, branches for refactoring/infrastructure.

Blue-Green Deployments for Data

Blue-green deployment pattern adapted for database migrations: maintain two databases, replicate between them, then switch traffic.

Migration Process

-- Step 1: Set up replication from blue to green
-- Blue is primary, green is replica
CHANGE MASTER TO
  MASTER_HOST='blue-db.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='...',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
START SLAVE;

-- Step 2: Apply schema changes to green (will replicate)
-- Green can have different schema (expanded)
ALTER TABLE users ADD COLUMN new_column VARCHAR(100);

-- Step 3: Verify replication lag is minimal
SHOW SLAVE STATUS\G
-- Check Seconds_Behind_Master

-- Step 4: Switch application to green (change connection string)
-- Can do gradually with connection pooling

-- Step 5: Stop replication, promote green to primary
STOP SLAVE;
RESET SLAVE ALL;

-- Step 6: Optionally reverse replication (green to blue)
-- This allows quick rollback by switching connection back

Application Connection Switching

public class DatabaseConnectionManager {
    private readonly IConfiguration config;

    public string GetConnectionString() {
        var activeDatabaseColor = config["ActiveDatabaseColor"]; // "blue" or "green"

        return activeDatabaseColor switch {
            "blue" => config["BlueDbConnectionString"],
            "green" => config["GreenDbConnectionString"],
            _ => throw new InvalidOperationException("Unknown database color")
        };
    }
}

// Gradual switch using connection pool
public class GradualConnectionSwitch {
    private int blueConnections = 100;
    private int greenConnections = 0;

    public IDbConnection GetConnection() {
        var total = blueConnections + greenConnections;
        var random = Random.Shared.Next(total);

        if (random < blueConnections) {
            return new SqlConnection(blueConnectionString);
        } else {
            return new SqlConnection(greenConnectionString);
        }
    }

    public void ShiftToGreen(int percentage) {
        blueConnections = 100 - percentage;
        greenConnections = percentage;
    }
}

Rollback Strategy

Keep blue database in sync with green after switch to allow quick rollback:

After switching to green, set up replication from green back to blue
Keep blue database active but not serving traffic
Can switch back to blue instantly if issues found
After validation period, decommission blue

Interview Question: What are the limitations of blue-green for databases?

Answer: Limitations: requires 2x storage and compute, replication lag can cause inconsistency during switch, schema must remain backward-compatible during transition, cannot easily rollback after schema contraction, data generated on green after switch can't easily move back to blue if incompatible schema. Best for: read-heavy workloads, when you can afford duplicate infrastructure, when schema changes are additive.

Data Migration Strategies

Backfill Migration

Migrate all historical data before switching reads to new system:

public class BackfillMigration {
    public async Task MigrateUsers() {
        var batchSize = 1000;
        var lastId = 0;

        while (true) {
            var users = await oldDb.Query<User>(@"
                SELECT * FROM users
                WHERE id > @lastId
                ORDER BY id
                LIMIT @batchSize",
                new { lastId, batchSize }
            );

            if (!users.Any()) break;

            foreach (var user in users) {
                var transformed = TransformUser(user);
                await newDb.InsertUser(transformed);
            }

            lastId = users.Max(u => u.Id);
            logger.LogInformation("Migrated up to user ID {LastId}", lastId);

            // Rate limiting
            await Task.Delay(100);
        }

        logger.LogInformation("Backfill complete");
    }
}

Lazy Migration

Migrate data on-demand as it's accessed. Faster initial migration, gradual data transfer:

public class LazyMigrationRepository {
    public async Task<User> GetUser(int id) {
        // Try new database first
        var user = await newDb.GetUser(id);
        if (user != null) return user;

        // Not migrated yet, get from old
        user = await oldDb.GetUser(id);
        if (user == null) return null;

        // Migrate on read
        var transformed = TransformUser(user);
        await newDb.InsertUser(transformed);

        logger.LogInformation("Lazy-migrated user {UserId}", id);
        return transformed;
    }

    public async Task UpdateUser(User user) {
        // Always write to new database
        await newDb.UpdateUser(user);

        // Also update old if still exists there
        if (await oldDb.Exists(user.Id)) {
            await oldDb.UpdateUser(user);
        }
    }
}

Hybrid Migration

Combine backfill and lazy migration: backfill bulk of data, lazy migrate remaining:

public class HybridMigration {
    public async Task Execute() {
        // Phase 1: Backfill active users (last 90 days)
        await BackfillActiveUsers(days: 90);

        // Phase 2: Backfill recent users (last year) at lower priority
        await BackfillRecentUsers(days: 365);

        // Phase 3: Old users migrated on-demand (lazy)
        await EnableLazyMigration();

        // Phase 4: After 30 days, backfill remaining users slowly
        await Task.Delay(TimeSpan.FromDays(30));
        await BackfillRemainingUsers();
    }
}

Chunked Migration with Checkpoints

public class CheckpointMigration {
    private readonly ICheckpointStore checkpoints;

    public async Task MigrateWithCheckpoints(string migrationName) {
        var checkpoint = await checkpoints.GetLastCheckpoint(migrationName) ?? 0;
        var batchSize = 1000;

        while (true) {
            using var transaction = await db.BeginTransactionAsync();

            var records = await oldDb.GetRecords(checkpoint, batchSize);
            if (!records.Any()) break;

            foreach (var record in records) {
                await newDb.Insert(Transform(record));
            }

            checkpoint = records.Max(r => r.Id);
            await checkpoints.SaveCheckpoint(migrationName, checkpoint);
            await transaction.CommitAsync();

            logger.LogInformation(
                "Migration {Name} checkpoint: {Checkpoint}",
                migrationName, checkpoint
            );
        }
    }
}

Interview Question: How do you handle schema differences during migration?

Answer: Use transformation layer to map old schema to new, handle missing fields with defaults, split/merge columns as needed, use expand/contract for gradual schema evolution. For complex transformations, consider intermediate staging area, validate transformed data before writing, maintain bidirectional transforms if rollback needed, and version transformations for different source data versions.

Rollback Planning

Every migration needs a rollback plan. Ability to quickly revert is critical for zero-downtime migrations.

Always Have a Rollback Path

Design migrations to be reversible at every step:

// Migration script with rollback
public class Migration_20260512_AddEmailVerified : IMigration {
    public async Task Up() {
        await db.ExecuteAsync(@"
            ALTER TABLE users
            ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
        ");
    }

    public async Task Down() {
        await db.ExecuteAsync(@"
            ALTER TABLE users
            DROP COLUMN email_verified;
        ");
    }
}

// Application rollback via feature flag
public User GetUser(int id) {
    if (featureFlags.IsEnabled("use-new-schema")) {
        // New code path
        return newRepository.GetUser(id);
    } else {
        // Rollback: old code path
        return oldRepository.GetUser(id);
    }
}

Version Everything

Maintain version numbers for schema, data, and application code:

CREATE TABLE schema_versions (
    version INT PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    rollback_script TEXT
);

public class SchemaVersion {
    public async Task<int> GetCurrentVersion() {
        return await db.ExecuteScalarAsync<int>(
            "SELECT COALESCE(MAX(version), 0) FROM schema_versions"
        );
    }

    public async Task ApplyMigration(int version, string script, string rollbackScript) {
        using var transaction = await db.BeginTransactionAsync();

        // Apply migration
        await db.ExecuteAsync(script);

        // Record version
        await db.ExecuteAsync(@"
            INSERT INTO schema_versions (version, description, rollback_script)
            VALUES (@version, @description, @rollbackScript)",
            new { version, description = $"Migration {version}", rollbackScript }
        );

        await transaction.CommitAsync();
    }

    public async Task Rollback(int toVersion) {
        var currentVersion = await GetCurrentVersion();

        for (int v = currentVersion; v > toVersion; v--) {
            var rollbackScript = await db.ExecuteScalarAsync<string>(
                "SELECT rollback_script FROM schema_versions WHERE version = @v",
                new { v }
            );

            await db.ExecuteAsync(rollbackScript);
            await db.ExecuteAsync(
                "DELETE FROM schema_versions WHERE version = @v",
                new { v }
            );

            logger.LogInformation("Rolled back to version {Version}", v - 1);
        }
    }
}

Rollback Testing

Test rollback procedures before production migration:

public class MigrationTest {
    [Test]
    public async Task TestMigrationRollback() {
        // Arrange: apply migration
        await migration.Up();
        var versionAfterMigration = await schema.GetCurrentVersion();

        // Act: rollback
        await migration.Down();
        var versionAfterRollback = await schema.GetCurrentVersion();

        // Assert: back to original state
        Assert.Equal(versionAfterMigration - 1, versionAfterRollback);

        // Verify data integrity
        var users = await db.Query("SELECT * FROM users");
        Assert.All(users, u => Assert.NotNull(u.Email));
    }

    [Test]
    public async Task TestApplicationRollback() {
        // Deploy new version
        await DeployVersion("v2.0");

        // Verify it works
        var result = await app.GetUser(123);
        Assert.NotNull(result);

        // Rollback
        await featureFlags.Disable("use-new-code");

        // Verify old code path still works
        result = await app.GetUser(123);
        Assert.NotNull(result);
    }
}

Rollback Decision Matrix

Scenario	Rollback Strategy	Speed
Application bug	Redeploy previous version	5-10 min
Feature causing issues	Disable feature flag	Instant
Schema migration issue	Run rollback script	1-5 min
Data migration corruption	Restore from backup + replay	Hours
Database switch issue	Change connection string back	Instant

Monitoring for Rollback Triggers

public class MigrationHealthMonitor {
    public async Task MonitorMigration() {
        var metrics = await GetMetrics();

        // Check error rate
        if (metrics.ErrorRate > THRESHOLD_ERROR_RATE) {
            await TriggerRollback("High error rate");
        }

        // Check latency
        if (metrics.P99Latency > THRESHOLD_LATENCY) {
            await TriggerRollback("High latency");
        }

        // Check data consistency
        var inconsistencies = await CheckDataConsistency();
        if (inconsistencies > THRESHOLD_INCONSISTENCY) {
            await TriggerRollback("Data inconsistency detected");
        }
    }

    private async Task TriggerRollback(string reason) {
        logger.LogError("Triggering rollback: {Reason}", reason);
        await alerts.SendCritical($"Auto-rollback triggered: {reason}");

        // Disable feature flag first (instant)
        await featureFlags.Disable("new-schema");

        // Then rollback schema if needed
        await schema.Rollback(PREVIOUS_VERSION);

        await alerts.SendCritical("Rollback complete");
    }
}

Interview Question: What makes a migration non-reversible, and how do you handle it?

Answer: Non-reversible migrations: data loss operations (dropping columns, deleting records), data transformations that lose information (truncating strings, rounding numbers), schema changes that aren't backward-compatible. Handle by: maintaining old and new columns during transition, keeping dropped data in archive table, versioning data with transformation metadata, using blue-green with separate databases, extensive testing before production, and accepting that some migrations require maintenance window.

Summary

Zero-downtime migrations require careful planning and execution. Key principles:

Use expand/contract pattern for schema changes: add new, migrate data, remove old
Online schema change tools prevent table locks: pt-osc, gh-ost, pg_repack
Dual-write pattern migrates between data stores: write to both, verify, switch reads
Shadow traffic tests new systems with production load without user impact
Dark launches and feature flags enable gradual rollout and instant rollback
Blue-green deployments for data: maintain two databases, replicate, switch
Choose migration strategy: backfill (bulk migrate), lazy (on-demand), hybrid
Always have rollback plan: version everything, test rollback, monitor for triggers

The common thread: make changes incrementally and reversibly, never require downtime for a single atomic operation, and always maintain ability to quickly revert if issues arise.