Plan a Schema Migration Rollback
Design a database migration that can be rolled back without data loss, rehearse it on a copy, measure lock impact, and keep old and new application versions compatible.
Objective
Apply a schema change with a tested rollback path, so a bad release does not require restoring the entire database.
Environment
- A migration tool with versioned, forward and reverse steps
- A staging database with production-like data volume
- A backup or snapshot taken immediately before the change
- A deploy process that can run old and new application versions concurrently
Steps
1. Classify the change
Mark the migration as additive, destructive, or data-transforming. Additive changes are easiest to roll back; destructive changes require a backup and a compatibility plan.
Expected state: The risk class and the rollback strategy are documented before writing SQL.
2. Design expand-contract
Add new columns or tables, deploy code that writes both shapes, migrate reads, and remove the old shape in a later release.
Expected state: The previous application version still works during the migration window.
3. Rehearse on a copy
Run the migration and rollback on a staging copy with production-like volume, measure duration, locks, and replication lag.
Expected state: The migration fits the maintenance or online-change budget.
4. Apply with a backup
Take a snapshot, run the migration in a controlled window, and monitor errors, locks, and replication.
Expected state: The change completes with no unexpected blocking and the snapshot is available.
5. Roll back if needed
Run the reverse migration, redeploy the previous application version, and verify reads and writes. If the reverse is unsafe, restore from the snapshot.
Expected state: The system returns to the previous schema and behavior without losing committed data.
Decision points
Is a destructive change ever acceptable in one step?
- Only with a tested snapshot and a maintenance window
- Yes, if the data is backed up
Prefer expand-contract. A one-step destructive change needs a rehearsed restore, not only a backup file.
Should the rollback drop the new column?
- Yes, to restore the exact previous schema
- No, leave it unused until the next release
Leave additive columns in place when they are harmless; dropping them adds risk without improving compatibility.
Expected outcome
The migration is applied or rolled back within the window, with old and new code compatible during the transition and no unverified data loss.
Verification
Run the application test suite against both schema versions, compare row counts and checksums for transformed data, and time the rollback on staging. Confirm the reverse migration is idempotent or guarded.
Cleanup
Remove temporary dual-write code after the contract step, archive the migration and rollback logs, and update the runbook.
Rollback is part of the migration
A migration without a reverse path turns a bad deploy into a data-restore event. The reverse may be a SQL down migration, a feature flag, or a snapshot restore, but it must be designed and tested before the change. “We can restore the backup” is not a plan unless the restore has been timed on production-like data.
-- Expand: additive, safe for old and new code
ALTER TABLE orders ADD COLUMN shipping_zone text;
-- Backfill in batches, then contract in a later release
UPDATE orders SET shipping_zone = 'standard' WHERE shipping_zone IS NULL;Keep both versions compatible
During a rolling deploy, old and new application instances run at the same time. A migration that drops a column the old version reads causes immediate errors. Expand-contract keeps the schema compatible: add the new shape, write both, switch reads, then remove the old shape after the old version is gone.
Measure locks and duration
Some ALTER TABLE operations take an exclusive lock and rewrite the table. On a large table, that can block writes for minutes. Rehearse on a copy with production-like volume and measure. Use online DDL or a shadow table when the engine supports it, and schedule the change outside peak traffic.
Test the rollback
Run the down migration and the previous application version against the migrated schema. Verify that reads and writes work and that transformed data is intact. If the down migration cannot restore the data, document that the only rollback is a snapshot restore and measure it. A rollback path that has never been executed is a hope, not a control.