Data Migration Strategies Without Downtime

The Migration Challenge

Migrating data while serving production traffic is risky. Stop the application, migrate, restart. But minutes or hours of downtime damages customer trust. Modern migrations require zero or minimal downtime.

The Dual-Write Pattern

Most successful migrations use dual-writes:

Phase 1: Deploy dual-write Writes go to both old system (source of truth) and new system (being migrated). Reads still from old system.

async function createUser(user: User) {
  // Write to old system (source of truth)
  const oldResult = await oldDb.insert('users', user);

  try {
    // Write to new system
    await newDb.insert('users', user);
  } catch (error) {
    // New system write failed, but old system succeeded
    // Log for manual reconciliation
    logger.error('New system write failed', { user, error });
  }

  return oldResult;
}

Phase 2: Backfill existing data Copy all existing data from old to new system. Run in background without affecting live traffic.

async function backfillData() {
  let lastId = 0;
  const batchSize = 1000;

  while (true) {
    const rows = await oldDb.query(
      'SELECT * FROM users WHERE id > ? LIMIT ?',
      [lastId, batchSize]
    );

    if (rows.length === 0) break;

    await newDb.insertMany('users', rows);
    lastId = rows[rows.length - 1].id;

    // Pause between batches to avoid overwhelming
    await sleep(1000);
  }
}

Phase 3: Validation Compare data between systems. Checksums, counts, spot checks catch discrepancies.

async function validateMigration() {
  const oldCount = await oldDb.query('SELECT COUNT(*) as count FROM users');
  const newCount = await newDb.query('SELECT COUNT(*) as count FROM users');

  if (oldCount[0].count !== newCount[0].count) {
    throw new Error('Row count mismatch');
  }

  // Spot check random records
  const randomUsers = await oldDb.query(
    'SELECT * FROM users ORDER BY RANDOM() LIMIT 100'
  );

  for (const user of randomUsers) {
    const newUser = await newDb.query(
      'SELECT * FROM users WHERE id = ?',
      [user.id]
    );

    if (!deepEqual(user, newUser[0])) {
      logger.warn('Data mismatch', { oldUser: user, newUser: newUser[0] });
    }
  }
}

Phase 4: Switch reads Gradually switch read traffic to new system. Start with 1% of reads, monitor, expand.

function getDataSource(readPercentage: number) {
  const random = Math.random() * 100;
  return random < readPercentage ? newDb : oldDb;
}

async function getUser(id: string) {
  const db = getDataSource(readPercentage);
  return db.query('SELECT * FROM users WHERE id = ?', [id]);
}

// Gradually increase readPercentage: 1%, 5%, 25%, 50%, 100%

Phase 5: Cleanup Once new system fully handles reads and writes, remove dual-writes and old system.

Validation Techniques

Checksum validation:

function checksumTable(db: Database, table: string) {
  return db.query(
    `SELECT MD5(GROUP_CONCAT(MD5(CONCAT_WS(',', *)))) as checksum
     FROM ${table}`
  );
}

Row-by-row validation: Sample 0.1-1% of data, compare fields manually. Catches schema mismatches.

Logical validation: Run business logic: all paid customers have valid payment methods, all invoices reference existing customers.

Handling Edge Cases

Concurrent writes during migration: With dual-writes, this is handled. New system eventually catches up.

Deletes in old system: Mark deletes instead of removing rows. Flag for soft-delete in new system.

Schema changes: Migrations often include schema changes. Map old fields to new schema:

async function transformRow(oldRow: any, schema: FieldMap) {
  const newRow: any = {};

  for (const [oldField, newField] of Object.entries(schema)) {
    if (oldField === 'email_address') {
      newRow['email'] = oldRow[oldField];
    } else if (oldField === 'created_date') {
      newRow['created_at'] = new Date(oldRow[oldField]);
    } else {
      newRow[newField] = oldRow[oldField];
    }
  }

  return newRow;
}

Rollback Planning

If migration fails after switching reads, rollback:

async function rollback() {
  // Switch reads back to old system
  readPercentage = 0;

  // Stop dual-writes
  stopDualWrites();

  // Investigate issues before retrying
}

Rollback should be instant and automated, requiring no downtime.