What Makes a Database Schema Change Backward-Compatible?
Learn what makes a database schema change backward-compatible and how to avoid breaking old code during rolling deploys.
Expected Interview Answer
A backward-compatible schema change is one that old application code, already deployed and still running, can continue to read and write correctly after the change is applied — meaning it only adds optional structure rather than removing, renaming, or re-typing anything the running code still relies on.
In practice this means preferring additive changes: new nullable columns, new tables, new indexes, and new optional fields, none of which break existing queries. Destructive or renaming changes — dropping a column, renaming a table, changing a column's type or NOT NULL constraint — are deferred until every running instance of the old code has been retired, because during a rolling deployment, old and new application versions run simultaneously against the same database. Getting this wrong causes old instances to error out or write invalid data the moment the migration runs, well before the new code even deploys.
- Old and new application versions can run against the schema simultaneously
- Rolling deployments never crash mid-rollout due to a missing column
- Migrations and code deploys can be decoupled and independently timed
- Reduces the blast radius of any single schema change
AI Mentor Explanation
A cricket board updating its official scoring app rolls the update out to stadiums one at a time, so for a period some stadiums run the old scoring format and some run the new one against the same central match database; the database change must therefore let both an old scorer expecting the original fields and a new scorer expecting the added ones keep working. Adding a new optional field for review outcomes is safe; renaming the existing runs field mid-rollout would break every old-version scorer instantly.
Step-by-Step Explanation
Step 1
Classify the change
Determine whether the change is additive (new nullable column/table) or destructive (drop, rename, retype, add NOT NULL).
Step 2
Prefer additive first
Ship additive changes that old code can safely ignore, deployed independently of any code release.
Step 3
Roll out application code
Deploy new code that uses the new structure while old instances continue running unaffected during the rollout.
Step 4
Defer destructive changes
Only drop, rename, or retype old structures after every old application instance has been fully retired.
What Interviewer Expects
- Clear distinction between additive and destructive schema changes
- Understanding that rolling deployments run old and new code simultaneously
- Concrete example of a change that would break old code (rename/drop/retype)
- Sequencing: destructive changes only after old code is fully retired
Common Mistakes
- Renaming or dropping a column in the same release that changes application code
- Adding a NOT NULL constraint without a default, breaking old inserts
- Assuming a rolling deployment is instantaneous with no mixed-version window
- Not testing the old application version against the post-migration schema
Best Answer (HR Friendly)
“I make sure a schema change only adds things — a new nullable column or table — rather than removing or renaming something the currently running code still uses. That way, during a rolling deploy where old and new app versions are both live, nothing breaks. Anything destructive, like dropping a column, I save for a later step once every old instance is gone.”
Code Example
-- Backward-compatible: additive, nullable, old app ignores it safely
ALTER TABLE Orders ADD COLUMN discount_code VARCHAR(50);
-- Breaking: old app code still selecting "status" will error immediately
-- ALTER TABLE Orders RENAME COLUMN status TO order_status;
-- Safer sequence for a rename across a rolling deploy:
-- 1) ALTER TABLE Orders ADD COLUMN order_status VARCHAR(20);
-- 2) Backfill order_status from status, deploy code reading either
-- 3) Once all old code is retired: ALTER TABLE Orders DROP COLUMN status;Follow-up Questions
- Why is renaming a column considered a breaking change during a rolling deploy?
- How would you safely add a NOT NULL constraint to an existing column?
- What is the risk of adding a foreign key constraint on a large live table?
- How do additive migrations interact with the expand-and-contract pattern?
MCQ Practice
1. Which of these is generally a backward-compatible schema change?
Adding a new nullable column is purely additive; old code that never references it continues to work unaffected.
2. Why is a mid-rollout window relevant to backward compatibility?
During a rolling deploy, both old and new code versions query the same live schema, so both must keep working.
3. When is it safe to drop a deprecated column?
Dropping too early breaks any still-running old code that reads the column, so it must wait until full rollout completes.
Flash Cards
What is a backward-compatible schema change? — A change old, still-running application code can tolerate without breaking, typically an additive one.
Give an example of a breaking change. — Renaming or dropping a column, or changing its type, while old code still references it.
Why does rolling deployment matter here? — Old and new app versions run against the same schema simultaneously during rollout, so both must keep working.
When should destructive changes happen? — Only after every instance of code depending on the old structure has been fully retired.