You need to rename a column in a large production table that the live app uses. How do you do it without downtime?
What the interviewer wants to hear
- Expand and contract (parallel change) approach
- Add new column, write to both, backfill in batches
- Switch reads to new column, then stop writing old one
- Drop old column in a later deploy
- Avoid long locks; test and have a rollback plan
Model answer
A direct rename breaks old app instances still running during a rolling deploy, so I use the expand and contract pattern. Step one: add the new column as nullable, which is a fast metadata change in PostgreSQL. Step two: deploy code that writes to both the old and new columns but still reads the old one. Step three: backfill existing rows in small batches so I do not hold long locks or create huge transactions. Step four: deploy code that reads from the new column, and verify the data matches. Step five: stop writing the old column. Finally, in a later release, drop the old column. Every step is backward compatible, so I can roll back the app at any point. I also check lock timeouts and run the migration first on a staging copy of production data.
Can you answer it under time pressure?
Take a timed medium round and get scored on every answer.
More medium Backend Developer questions
- An API endpoint is slow because it runs a heavy database query. How would you add caching, and how do you keep the cache correct?
- How do you change a public API without breaking existing clients, such as old mobile app versions?
- Users say one API endpoint has become slow in production. How do you find and fix the cause?
- Sending emails and generating PDF invoices makes your checkout API slow. How would you move this work to the background?
- How would you add rate limiting to a public API that runs on many servers?
- You are asked to review the security of a REST API before launch. What would you check?
- Your service receives webhooks from a payment provider. How do you make handling them secure and reliable?
- When would you choose Kafka and when RabbitMQ for messaging between services?
- How would you design an API that lets users upload large files, such as videos of several gigabytes?