Data Migration

Move bulk data between systems (DB→DB, format→format) with verification and zero loss.

Why

A data migration is a one-way operation on your most valuable asset — if rows are dropped or corrupted, backups only get you back to before the migration, losing all post-migration writes. The disciplined approach is extract-transform-load in idempotent batches, with a row-level checksum verification at the end, and a dual-write window so new writes go to both old and new during the migration. This makes the migration resumable (a crash at 80% doesn't restart from 0), verifiable (a checksum diff catches silent row loss), and lossless (dual-write captures writes that happen during the migration). Skipping any of these is how teams lose user data and don't notice for a week.

When

When moving to a new DB, changing data format, or merging systems. Plan a dual-write window for any migration >1GB.

How

1. Set up dual-write: new writes go to both old and new (so the migration captures writes during the copy)
2. Extract from old in idempotent batches: SELECT ... WHERE id > :cursor ORDER BY id LIMIT 1000
3. Transform each batch (map old schema -> new schema); validate each row before insert
4. Load to new with upsert (ON CONFLICT DO UPDATE) so re-running is safe (idempotent)
5. Track a cursor/checkpoint so a crash resumes from the last batch, not from 0
6. After the bulk copy, verify: COUNT(*) old == new, and a row-level checksum sample matches
7. Switch reads to the new; keep dual-write for a monitoring window; then stop writing old
8. Keep the old DB read-only for 30d as a forensic fallback before decommissioning

Example

# Idempotent batched migration (Node + Postgres)
async function migrate() {
  let cursor = await loadCheckpoint();  // resume from last batch
  while (true) {
    const rows = await oldDb.query(
      'SELECT * FROM users WHERE id > $1 ORDER BY id LIMIT 1000', [cursor]);
    if (rows.length === 0) break;
    const transformed = rows.map(toNewSchema);
    await newDb.query(
      'INSERT INTO users_new (id, email, name) VALUES ($1,$2,$3) ' +
      'ON CONFLICT (id) DO UPDATE SET email=EXCLUDED.email, name=EXCLUDED.name',
      transformed);
    cursor = rows[rows.length - 1].id;
    await saveCheckpoint(cursor);
    await sleep(50);  // throttle
  }
}

# Verification (run after bulk copy)
const oldCount = await oldDb.query('SELECT count(*) FROM users');
const newCount = await newDb.query('SELECT count(*) FROM users_new');
assert(oldCount === newCount, 'row count mismatch!');

# Row-level checksum sample (catch silent corruption)
const sample = await oldDb.query('SELECT id, md5(row_to_json(t)::text) FROM users TABLESAMPLE SYSTEM(1) t');
for (const { id, md5 } of sample) {
  const newRow = await newDb.query('SELECT row_to_json(*) FROM users_new WHERE id=$1', [id]);
  assert(md5 === md5(JSON.stringify(newRow)), `row ${id} corrupted`);
}

Common Problems

⚠No dual-write — rows written to the old DB during the 4-hour copy are lost, because the migration started before them.
⚠A non-resumable migration — a crash at 80% restarts from 0, doubling the window and the risk.
⚠No verification — the migration "completes" but silently dropped 200 rows; you find out from a user complaint a week later.

Best Practices

✓Dual-write during the migration window so new writes land in both — this is the only way to not lose writes during a multi-hour copy.
✓Make the migration idempotent (upsert) and resumable (checkpoint) — a crash resumes from the last batch, not from zero.
✓Verify with a row count + a sampled row-level checksum — count alone misses corruption; checksum catches silent data damage.