Zero-Downtime DB Migration — Expand Contract Checklist

Agent Checklist

Before proposing or running a migration, verify:

1. The first schema change is additive: new nullable column, new table, new index, or new enum shadow table.
2. Old code can still run after the new schema exists.
3. New code can run before the backfill is complete.
4. Backfill runs in bounded batches and can resume safely.
5. There is a rollback path that does not require restoring the whole database.
6. Monitoring includes error rate, lock wait, replication lag, row count, and backfill progress.
7. The contract step is delayed until logs show no reads or writes to the old path.

Batch Backfill Shape

Use small batches with an id cursor:

UPDATE tb_example
SET new_status = old_status
WHERE id > ?
  AND id <= ?
  AND new_status IS NULL;

For MySQL, keep transactions short and watch lock waits. For PostgreSQL, avoid long table rewrites and validate constraints separately when possible. For both, make the job idempotent.

Red Flags

• DROP COLUMN, RENAME COLUMN, or non-null constraint in the first release.
• A migration that assumes every app server restarts at the same second.
• Backfill that scans the whole table in one transaction.
• Rollback plan that says "restore backup" for an ordinary deploy.
• No verification query for migrated row counts.