Database Concurrency & Scaling interview question · Most asked
How do you rename a column with zero downtime?
Summary
Expand/contract: add the new column, deploy code that writes both and backfill in batches, switch reads to the new column, and drop the old one only after no running code uses it.
Longer Answer
During rolling deploys old and new code run against the same schema, so every step must work with both. Use online DDL (CREATE INDEX CONCURRENTLY, NOT VALID constraints then VALIDATE, ALGORITHM=INSTANT, gh-ost for large MySQL tables) and set lock timeouts on migrations.
Key Points
- Old and new code coexist
- Expand → migrate → contract
- Batched backfill
- Online DDL + lock timeouts
Deep dive: Zero-Downtime Schema Changes
More most-asked Database Concurrency & Scaling questions
- How do you prevent overselling in a flash sale with 50,000 buyers in the first minute?
- After a deploy, logs show hundreds of 'deadlock detected' errors per hour on checkout. Walk through how you'd investigate.
- Two customers buy the last item at the same moment and both orders are confirmed. What happened, and how do you prevent it?
- Your service throws 'connection pool exhausted' errors but database CPU is only 20%. What's going on?
- Why shouldn't you call a payment API inside a database transaction?
- What's the difference between Read Committed and Repeatable Read in practice?
- Database CPU is at 85%. How do you decide what to optimise before buying a bigger server?
- A user saves their profile and the next page shows the old data. Reads go to replicas. How do you fix it?