Migrating a live database while people are using it is a matter of sequencing. The pattern is well established: make changes additive, run old and new together, and only remove the old once nothing depends on it.
Expand, migrate, contract
Add the new structure without touching the old. Write to both while backfilling historical data. Move reads to the new structure once it is complete and verified. Only then remove the old columns.
Each phase is independently reversible, which is what makes the approach safe.
Backfill carefully
- Work in batches sized to avoid long locks
- Make the job resumable and idempotent
- Throttle against replication lag, not just CPU
- Record progress so a restart does not begin again
Verify before switching
Compare row counts and checksums between old and new. Run the new read path in shadow mode and log differences without serving them. Switch only when the difference count is zero and stays there.
Plan the rollback first
Write the rollback procedure before starting and rehearse it on a copy. A migration you cannot reverse is a bet on everything going right the first time.
Never deploy code that requires a schema change in the same release as the change itself. Ship the schema first, the code second, and keep them independently reversible.
Where to start
Rehearse the whole sequence against a production-sized copy. The problems that matter only appear at real data volumes.
