How do you do a zero-downtime schema change that adds a required column?
PICTURE THIS: DATABASE INDEX
Simple meaning
Add the column as nullable, deploy code that writes both old and new, backfill in batches, then add NOT NULL and drop old columns in later deploys.
WHY — SQL instead of guessing?
Why interviewers care about SQL:
question about SQL.
trade-offs, and what you would actually do on a Backend project - not buzzwords.
Name the idea, why it exists, then one short example.
End with when you use it and one common pitfall.
STEPS — What happens step by step?
Before you speak the answer, walk the interviewer through these steps:
- 1Add the column as
nullable, deploy code that writes both old and new, backfill in batches, then add NOT NULL and drop old columns in later deploys.
- 2Never expand-and-contract in one
migration that locks a huge table during peak.
- 3Online schema tools and
short locks are part of the plan.
- 4Give an example
One tiny concrete case you can say aloud.
- 5Common mistake
What juniors usually get wrong.
- 6Close
When you pick this over the alternative.
EXAMPLE — See it in action
Here's a short line you can speak, broken into clear beats:
Note: Adapt this scaffold to your own project — keep it under 60–90 seconds.
Key takeaway
Add the column as nullable, deploy code that writes both old and new, backfill in batches, then add NOT NULL and drop old columns in later deploys. Never expand-and-contract in one migration that locks a huge table during peak.