How Do You Design a Rollback Strategy for Schema Migrations?
Learn how to design a safe rollback strategy for database schema migrations using the expand-contract pattern and staged cleanup.
Expected Interview Answer
A safe rollback strategy pairs every forward migration with a tested, reversible 'down' script or an expand-contract sequence, so a failed deploy can restore the previous schema and application version without losing or corrupting data.
In practice this means writing an explicit down migration for every up migration, avoiding destructive operations (dropping columns, renaming tables) until a later cleanup migration once the new code path is proven, and using the expand-contract pattern: first add new structures alongside old ones (expand), deploy code that writes to both, then remove the old structures only after verification (contract). Irreversible changes such as data backfills or type conversions need a backup or shadow-column approach so rollback restores prior state rather than merely reversing DDL. Rollback plans must also be rehearsed against a staging copy of production-scale data, because a migration that reverses cleanly on an empty table can time out or deadlock on a multi-million-row table.
- Failed deploys can be undone without data loss
- Expand-contract avoids breaking old code during rollout
- Destructive changes are deferred until the new path is proven
- Rehearsed rollbacks reduce production incident duration
AI Mentor Explanation
Think of a groundskeeper relaying the outfield to a new grass type. They do not rip out the old turf first; they lay the new grass in strips alongside the old, let a few matches run on the mixed surface to confirm drainage and bounce are safe, and only then remove the remaining old turf. If the new grass turns out to drain badly, the crew can simply stop removing old sections and keep playing on what remains. A schema rollback strategy follows the same expand-then-contract order, so a bad change never leaves the field unplayable.
Step-by-Step Explanation
Step 1
Write the down migration first
Author a tested reverse script alongside every forward migration before it is merged.
Step 2
Expand before contracting
Add new columns or tables alongside old ones and dual-write, rather than altering structures in place.
Step 3
Verify on production-scale data
Rehearse both the up and down migration against a staging copy sized like production to catch locking or timeout issues.
Step 4
Defer destructive cleanup
Drop or rename old structures only in a later migration, once the new path has run safely in production.
What Interviewer Expects
- Mentions the expand-contract pattern by name or concept
- Distinguishes reversible DDL changes from irreversible data changes
- Explains why destructive drops should be deferred to a later migration
- Raises the need to test rollback against realistic data volume
Common Mistakes
- Assuming every migration can simply be reversed automatically
- Dropping columns or tables in the same migration that adds their replacement
- Never testing the down migration, only the up migration
- Ignoring lock and timeout behavior on large production tables
Best Answer (HR Friendly)
“I always pair a forward migration with a tested rollback script, and for risky changes I use the expand-contract pattern: add the new structure first, run both old and new in parallel, then remove the old one only once I have confirmed the new path works in production. That way if something breaks, I can revert safely without losing data.”
Code Example
-- Migration 001_up.sql (expand): add new column, keep old one
ALTER TABLE Users ADD COLUMN email_normalized VARCHAR(255);
UPDATE Users SET email_normalized = LOWER(TRIM(email));
-- Migration 001_down.sql (rollback the expand step)
ALTER TABLE Users DROP COLUMN email_normalized;
-- Migration 002_up.sql (contract): only after app reads/writes
-- email_normalized successfully in production for a full cycle
ALTER TABLE Users DROP COLUMN email;
ALTER TABLE Users RENAME COLUMN email_normalized TO email;Follow-up Questions
- How would you migrate a column type on a table with 500 million rows without downtime?
- What is the expand-contract pattern and why is it safer than an in-place ALTER?
- How do you handle a migration that partially applied before a crash?
- What tooling would you use to automate migration testing in CI?
MCQ Practice
1. What is the main purpose of the expand-contract migration pattern?
Expand-contract adds new structures alongside old ones so both old and new code can run safely before the old structure is removed.
2. When should a destructive DROP COLUMN typically run in a rollback-safe migration plan?
Deferring the drop to a later migration keeps a safe rollback path available while the new structure is being validated.
3. Why should rollback scripts be tested against production-scale data, not just a small dev database?
A migration that reverses instantly on an empty table can lock or time out on a large production table, so realistic-scale testing catches issues dev data misses.
Flash Cards
What is expand-contract migration? — Adding new schema structures alongside old ones, dual-running, then removing the old structures only after verification.
When should you drop an old column after a migration? — In a later, separate migration, only after the new column has proven correct in production.
Why test rollback on production-scale data? — Because locking and timeout behavior differs drastically between small dev tables and large production tables.
What should every up migration have? — A tested, corresponding down migration that can reverse it safely.