Interview › Databases & Caching
How do you make a schema change backward compatible during a rolling deploy?
Databases & Caching · Intermediate level
Answer
For rolling deploys, I make schema changes backward compatible with expand-and-contract: add new structures first, deploy code that supports old and new, backfill, switch reads, then remove old structures later.
Technical explanation
Old and new app versions may run at the same time, so schema must support both during rollout.
Large table rewrites, blocking locks, foreign-key validation, and index builds can cause outages if done naively.
Always rehearse on production-sized data and set lock_timeout and statement_timeout.
Hands-on example
Expand-contract example:
ALTER TABLE users ADD COLUMN full_name text;
Deploy app that dual-writes name and full_name and reads COALESCE(full_name, name).
Backfill in batches.
After all old app versions are gone, add constraints and later drop the old column.
Foreign key safer pattern:
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?