Data Migration: Zero-Downtime Strategies That Work

Overview

A bad deploy rolls back in minutes. A bad data migration doesn’t — once you’ve transformed or deleted rows, the only way back is another migration, or a restore that loses everything written since. That’s why the teams that get this right treat migration as a weeks-long process, not a script you run on Friday night.

This guide walks through the pattern that survives production: expand the schema, dual-write, backfill in resumable batches, validate like you don’t trust your own code, shadow-read, then cut over behind a feature flag. Along the way: real Python and SQL for the parts everyone gets wrong.

When to Use

Use these patterns when:

• Moving between databases (MySQL → PostgreSQL, on-prem → cloud)
• Restructuring tables or normalizing/denormalizing data
• Introducing a new datastore (Elasticsearch, Redis, a warehouse)
• Sharding an existing database
• Migrating a legacy system to a modern platform
• Splitting or merging services that own their own datastores

For the operational side — checklists, comms plan, sign-off — the Data Migration Runbook Template turns this guide into a fill-in document.

Core Concepts

Concept	Description
Dual Write	Writing to both old and new systems simultaneously
Backfill	Populating a new datastore with historical data
Shadow Read	Reading from the new system and comparing with the old
Cutover	Switching reads and writes from old to new system
Rollback Window	The time during which you can revert without data loss
Idempotency	Running the same migration twice produces the same result

Step-by-Step Zero-Downtime Migration

1. Plan the Migration

Document every step before touching production data:

## Migration Plan: Users Table Normalization

**Goal:** Split `users` table into `users` + `user_profiles`
**Timeline:** 3 weeks
**Rollback window:** 48 hours after cutover

### Phase 1: Schema Changes (Week 1)
- [ ] Add `user_profiles` table
- [ ] Add foreign key `users.profile_id`
- [ ] Deploy application code that dual-writes to both tables
- [ ] Verify writes are succeeding to both tables

### Phase 2: Backfill (Week 1-2)
- [ ] Run backfill script in batches (1000 rows/batch)
- [ ] Monitor script progress and error rate
- [ ] Verify backfill completeness with row counts and checksums

### Phase 3: Shadow Reads (Week 2)
- [ ] Enable reading from `user_profiles` in parallel
- [ ] Compare results: old vs new (log mismatches)
- [ ] Fix any data discrepancies

### Phase 4: Cutover (Week 3)
- [ ] Switch reads to `user_profiles`
- [ ] Monitor error rates for 24 hours
- [ ] Remove dual-write code
- [ ] Drop old columns (after rollback window)

### Validation Checklist
- [ ] Row count matches: `SELECT COUNT(*) FROM users` == `SELECT COUNT(*) FROM user_profiles`
- [ ] Sample data comparison: 100 random users compared field-by-field
- [ ] Application integration tests pass
- [ ] Performance tests pass (new queries are fast enough)

### Rollback Plan
- [ ] If issues within 48h: revert read path to old schema
- [ ] If issues after 48h: write forward-fix migration (no rollback possible)

2. Implement Dual Writes

Write to both systems during the transition — the old one stays the source of truth until cutover:

# Dual-write during migration
import logging

logger = logging.getLogger(__name__)

class UserRepository:
    def __init__(self, old_db, new_db):
        self.old_db = old_db
        self.new_db = new_db

    def create_user(self, user_data):
        # Old system is still the source of truth
        user_id = self.old_db.users.insert(user_data)

        # New system: best effort — a failure here must not fail the request
        try:
            self.new_db.user_profiles.insert({
                'user_id': user_id,
                'display_name': user_data['name'],
                'bio': user_data.get('bio', ''),
                'created_at': user_data['created_at']
            })
        except Exception as e:
            logger.error("Dual write failed", extra={
                'user_id': user_id,
                'error': str(e)
            })

        return user_id

    def get_user(self, user_id):
        # Shadow-read phase: try the new system, fall back to the old
        try:
            profile = self.new_db.user_profiles.find_by_user_id(user_id)
            if profile:
                return self._convert_profile_to_user(profile)
        except Exception:
            pass

        return self.old_db.users.find_by_id(user_id)

# Backfill script: batched, resumable, throttled
import time

class BackfillUsers:
    def __init__(self, old_db, new_db):
        self.old_db = old_db
        self.new_db = new_db
        self.batch_size = 1000

    def run(self):
        last_id = self._get_checkpoint()

        while True:
            batch = self.old_db.users.find_after_id(last_id, limit=self.batch_size)
            if not batch:
                break

            for user in batch:
                self._migrate_user(user)

            last_id = batch[-1]['id']
            self._save_checkpoint(last_id)

            # Throttle so the backfill doesn't starve production queries
            time.sleep(0.1)

    def _migrate_user(self, user):
        """Upsert makes re-runs idempotent."""
        self.new_db.user_profiles.upsert(
            {'user_id': user['id']},
            {
                'display_name': user['name'],
                'bio': user.get('bio', ''),
                'created_at': user['created_at']
            }
        )

    def _get_checkpoint(self):
        row = self.old_db.execute(
            "SELECT last_id FROM migration_checkpoints WHERE migration = %s",
            ('users_to_profiles',)
        )
        return row['last_id'] if row else 0

    def _save_checkpoint(self, last_id):
        self.old_db.execute("""
            INSERT INTO migration_checkpoints (migration, last_id)
            VALUES ('users_to_profiles', %s)
            ON CONFLICT (migration) DO UPDATE SET last_id = EXCLUDED.last_id
        """, (last_id,))

The checkpoint table is what makes the script safe to kill: crash at row 500,000, restart, and it picks up where it left off instead of duplicating half a million rows.

Companion code: the data-migration-guide companion repo has a runnable sqlite demo of this whole flow — dual-write, resumable backfill, validation — plus the expand-contract SQL and a fill-in migration plan.

3. Validate Data Integrity

Never trust that a migration worked because it finished. Verify at three levels:

class MigrationValidator:
    def __init__(self, old_db, new_db):
        self.old_db = old_db
        self.new_db = new_db

    def validate_counts(self):
        """Row counts must match exactly."""
        old_count = self.old_db.execute("SELECT COUNT(*) AS c FROM users")['c']
        new_count = self.new_db.execute("SELECT COUNT(*) AS c FROM user_profiles")['c']
        assert old_count == new_count, f"Count mismatch: {old_count} != {new_count}"

    def validate_sample(self, sample_size=1000):
        """Field-by-field comparison on a random sample."""
        users = self.old_db.execute(
            "SELECT * FROM users ORDER BY RANDOM() LIMIT %s", (sample_size,)
        )
        mismatches = 0
        for user in users:
            profile = self.new_db.user_profiles.find_by_user_id(user['id'])
            if not profile or user['name'] != profile['display_name']:
                mismatches += 1
        assert mismatches == 0, f"{mismatches} mismatches in sample"

    def validate_checksums(self):
        """Aggregate checksums catch what samples miss."""
        old = self.old_db.execute("""
            SELECT MD5(string_agg(name || bio, ',' ORDER BY id)) AS checksum FROM users
        """)
        new = self.new_db.execute("""
            SELECT MD5(string_agg(display_name || bio, ',' ORDER BY user_id)) AS checksum
            FROM user_profiles
        """)
        assert old == new, "Checksum mismatch"

4. Execute the Cutover

## Cutover Checklist

### Before Cutover
- [ ] Backfill is 100% complete
- [ ] Validation passed (counts, samples, checksums)
- [ ] Shadow reads show <0.1% mismatch rate
- [ ] New system performance is acceptable under load
- [ ] Rollback procedure is documented and tested
- [ ] Team is on standby during cutover window

### During Cutover
1. Pause non-critical writes (optional, reduces risk)
2. Enable feature flag to route reads to new system
3. Monitor error rates for 15 minutes
4. If errors spike: disable feature flag (instant rollback)
5. If stable: proceed to write cutover
6. Enable writes to new system
7. Monitor for 1 hour

### After Cutover
- [ ] Error rates within normal range for 24 hours
- [ ] New system handling 100% of traffic
- [ ] Old system still receiving dual writes (for safety)
- [ ] Rollback window countdown started (48 hours)

# Feature flag-based cutover — read the flag per call, not at boot
class UserService:
    def __init__(self, config):
        self.config = config

    def get_user(self, user_id):
        # Config read must be dynamic so flipping the flag takes effect
        # without a redeploy — that's what makes rollback instant.
        if self.config.get('use_new_user_schema'):
            return self._get_from_new_schema(user_id)
        return self._get_from_old_schema(user_id)

    def create_user(self, user_data):
        # Keep dual-writing during the migration window
        old_id = self._create_in_old(user_data)
        self._create_in_new(user_data, old_id)
        return old_id

Schema Evolution Patterns

1. Expand-Contract for Columns

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

-- Step 2: Backfill new column
UPDATE users SET email_normalized = LOWER(email) WHERE email_normalized IS NULL;

-- Step 3: Deploy code that writes to both columns

-- Step 4: Make new column non-nullable
ALTER TABLE users ALTER COLUMN email_normalized SET NOT NULL;

-- Step 5: Deploy code that reads from the new column

-- Step 6: Drop the old column (after the rollback window)
ALTER TABLE users DROP COLUMN email;

2. Table Splitting

-- Step 1: Create the new table
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    preferences JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Step 2: Dual-write trigger
CREATE OR REPLACE FUNCTION sync_user_profile() RETURNS trigger AS $$
BEGIN
    INSERT INTO user_profiles (user_id, bio, preferences, created_at)
    VALUES (NEW.id, NEW.bio, NEW.preferences, NEW.created_at)
    ON CONFLICT (user_id) DO UPDATE SET
        bio = EXCLUDED.bio,
        preferences = EXCLUDED.preferences;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_profile_sync
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_user_profile();

-- Step 3: Backfill
INSERT INTO user_profiles (user_id, bio, preferences, created_at)
SELECT id, bio, preferences, created_at FROM users
ON CONFLICT (user_id) DO NOTHING;

-- Step 4: Cut reads over to user_profiles
-- Step 5: Drop the migrated columns from users (after the rollback window)

What Works

• Test on a copy of production data. Staging rarely matches production volume or edge cases, and a 2-hour staging migration can take 20 hours live.
• Make every script idempotent. If it crashes mid-run, restarting shouldn’t create duplicates — upserts and checkpoints, always.
• Throttle the backfill. Running at max speed starves production queries; time.sleep between batches is cheap insurance.
• Validate at three levels. Counts alone won’t catch a corrupted bio field — add samples and checksums.
• Keep the rollback window open. 24-72 hours minimum before dropping old data. Blue-green deployments use the same instinct for code; apply it to data too.
• Tell everyone. Migrations affect every team that touches the database — analytics included.

Common Mistakes

• No rollback plan. Once old columns are dropped, “rolling back” is a brand-new migration under incident pressure.
• Running at peak hours. Backfills belong in low-traffic windows.
• Forgetting foreign keys. Migrating a parent table without updating child references breaks constraints — or worse, orphans rows silently.
• Trusting a clean exit code. A script can finish without errors and still produce wrong data. That’s why step 3 exists.
• Deleting too early. Expand-contract exists because you will sometimes need to roll back.
• Underestimating duration. Extrapolate from a real slice, not from a toy dataset.

Variants

• Online schema change tools: pt-online-schema-change and gh-ost for MySQL; pg_repack for PostgreSQL — they do the trigger dance for you
• CDC-based migration: Debezium streams changes to the new system in real time, no application dual-write needed
• Dump and restore: pg_dump/pg_restore for smaller databases that can afford a maintenance window
• Managed services: AWS DMS, Azure Database Migration Service, Google DMS for cross-cloud moves

See Also

• Data Migration Runbook Template — the operational checklist version
• ETL Pipeline Guide — when the “new system” is a warehouse
• Database Sharding Guide — migrations at the sharding layer
• Disaster Recovery Guide — the rollback discipline at a larger scale

Further Reading

• Evolutionary Database Design (Fowler/Sadalage) — where expand-contract comes from
• Debezium documentation — CDC-based migration in practice
• PostgreSQL triggers — the dual-write mechanism used above

Conclusion

Data migration isn’t an event, it’s a process: plan, expand, dual-write, backfill, validate, shadow, cut over, contract. Skip any of those and you’re gambling with the one asset you can’t redeploy.