Why is adding a nullable column to a live table usually safe, while dropping or renaming an existing column is not, given that the previous version of the application is still serving traffic during a deploy?
answer
- Rolling deploy = old and new code on one database
- Old code names old columns; new nullable column is invisible to it
- Rename = drop + add at once → breaks both directions
- NOT NULL with no default breaks old inserts
- Rollback needs old code to work on new schema
basics
~20 sOld code queries the columns it was written against. A new nullable column is invisible to it and its inserts still succeed. A removed or renamed column breaks those queries the instant the migration lands, while old instances are still running.
solid answer
~60 sDuring any rolling deploy there is a window where old and new application versions run against the **same** database. So the real question is never "does the migration work" but "does it work for both versions at once". **Adding a nullable column** is additive: old code never names it in a SELECT and never supplies it in an INSERT, and because it is nullable or defaulted, those inserts still satisfy the schema. Old code simply does not see it. **Dropping or renaming** is subtractive: old code still names the column, so its statements start failing immediately — including statements already in flight. Renaming is the worst case because it is a drop and an add in one step, breaking old readers and old writers simultaneously. The same asymmetry applies to adding a NOT NULL column with no default (old inserts omit it and fail), narrowing a type, and tightening a constraint against data old code still produces. That asymmetry is exactly why the expand-contract pattern exists: do the additive part first, migrate everything onto it, and only remove the old thing once nothing can still reference it.
go deeper
Explain the overlap window in plain terms and classify changes as additive or subtractive; know that a rename is a drop plus an add.
Add the two-sided compatibility rule including rollback, and the traps: SELECT *, NOT NULL without a default, tightened constraints against live traffic.
Connect it to release engineering — how long the window really is, which changes must be split across deploys, and why the drop is deliberately deferred past the rollback horizon.
Turn it into policy: additive-only migrations by default, removals gated behind evidence that no code path references the object, and a review rule that any subtractive DDL must name its preceding expand step.
## The overlap window Zero-downtime deploys are rolling: instances are replaced a few at a time so the service never stops answering. For minutes — sometimes hours if a rollout is cautious — version N and version N+1 of the application are both alive, both connected to one database, both issuing SQL. Add a rollback and the window reopens: after rolling back, the *old* code must work against the *new* schema. So the compatibility requirement is two-sided: - new code must work against the old schema (because the migration may not have run yet, or may be run after the deploy); - old code must work against the new schema (because old instances are still up, and because rollback is a real event). A change that satisfies both is safe to ship on its own. A change that does not must be split into steps that each satisfy both. ## Why additive changes are safe SQL statements name columns explicitly. An old `INSERT INTO account (id, email) VALUES (...)` does not mention a new `phone` column; the database supplies NULL or the declared default. An old `SELECT id, email FROM account` does not read it either. The new column is simply outside the old code's vocabulary. Two caveats turn a supposedly additive change unsafe: - **`SELECT *`.** Code that reads all columns can break when the shape changes — an ORM mapping to a fixed row class, a positional result-set reader, or a downstream consumer that asserts a column count. This is one of the practical arguments against `SELECT *` in application code. - **NOT NULL without a default.** Old inserts omit the column, so they fail. The safe form is nullable now, or NOT NULL with a default; the NOT NULL tightening happens later, after every writer supplies a value. ## Why subtractive changes are not safe Dropping a column invalidates every old statement that names it, and they fail immediately, not at the next deploy. Renaming is strictly worse: the old name disappears and the new name appears at the same instant, so old readers *and* old writers break together, and there is no intermediate state where either version is fully functional. Other subtractive-in-effect changes that catch people out: - narrowing a type (`VARCHAR(255)` → `VARCHAR(50)`) — old code may still write longer values; - adding a `CHECK` or `NOT NULL` constraint that current traffic violates; - removing an enum value or a lookup row still referenced by running code; - making a previously optional foreign key mandatory. All of them fail against traffic produced by the version you have not retired yet. ## The resulting rule Order every schema change so the additive part ships first and the removal ships last, separated by a deploy in which the application stops using the old thing. Concretely, a rename becomes: add the new column → write both → backfill → read the new one → stop writing the old one → drop it. Each step leaves both versions functional, and each step is independently revertible. This is why teams describe migrations as "expand, migrate, contract" or "parallel change": the awkwardness is not the DDL, it is the requirement that no single deploy may break the version still running beside it. ## What a junior should be able to do with this Classify a proposed change as additive or subtractive before writing it, and recognise that "we will deploy the app and the migration together" is not a safety argument — rolling deploys and rollbacks both violate that assumption. When a change is subtractive, ask what the intermediate additive step is. And keep a habit that makes additive changes genuinely safe: name columns explicitly instead of `SELECT *`, and give new columns a default or allow NULL until every writer has caught up. ## Rollback as the forgotten direction Most teams remember old-code-versus-new-schema during rollout and forget it during rollback. If version N+1 drops a column and then has to be reverted, version N is now running against a schema missing something it needs. This is why the drop is deliberately delayed by at least one full deploy cycle — long enough that rolling back no longer means rolling back past the removal.
- Adding a column is additive, so why can it still break an application that uses SELECT *?SELECT * returns whatever columns exist at execution time, so the result shape changes underneath code that expected a fixed set. Positional readers pick up the wrong value, strict ORM or DTO mappers fail on an unexpected field, and downstream consumers that assert a column count reject the row. Naming columns explicitly makes additive changes genuinely invisible to old code.
- Is adding a NOT NULL column with a default value safe during a rolling deploy?From a compatibility standpoint yes: old inserts that omit the column get the default, so they keep succeeding. The remaining risk is physical rather than logical — on some engines and versions, adding a defaulted NOT NULL column rewrites the whole table under a lock, which is a downtime problem even though the SQL is compatible. Modern versions of the major engines record the default in metadata and avoid the rewrite for constant defaults.
Adding a column is putting a new item on a restaurant menu — regulars who order the usual are unaffected. Renaming a dish while people are mid-order means every ticket already in the kitchen refers to something that no longer exists.
saying these in an interview costs you the question
- Believing that deploying the migration and the application together removes the overlap window
- Renaming a column in one step because "the code change is in the same pull request"
- Adding a NOT NULL column with no default to a table that old code still inserts into
- Forgetting that rollback puts old code in front of the new schema
- Assuming SELECT * makes the application tolerant of schema change rather than fragile to it