Zero-downtime schema migration on a hot table

WHAT THIS TESTS The interviewer wants to see if you understand that a deploy and a migration are separate events, and that every intermediate state of schema and code must coexist so traffic never breaks and rollback stays possible.

A GOOD ANSWER COVERS The expand-contract or parallel-change pattern in distinct steps. First expand: add nullable street, city, and state columns with an online DDL tool so no long lock is taken. Second, deploy application code that writes both the old address column and the new columns on every insert and update, while still reading the old one. Third, backfill existing rows in small batches with throttling so you do not saturate IO or replication. Fourth, deploy code that reads from the new columns and verify parity. Fifth, in a later release stop writing the old column. Sixth, contract by dropping it once nothing references it.

COMMON WRONG ANSWERS Running a single ALTER that rewrites the whole table and blocks writes, backfilling in one giant transaction that bloats the log, or coupling the column drop to the same deploy that adds new columns so an old replica or rolled-back instance hits a missing column.

LIKELY FOLLOW-UPS How do you backfill safely, how do online DDL tools like gh-ost or pt-online-schema-change work, how do you handle replication lag, and how would you roll back from each phase.

ONE CONCRETE EXAMPLE For splitting address you add three columns, deploy a service version that parses and writes both representations, run a chunked job updating ten thousand rows per pass with a sleep between batches, flip reads after a parity check, then drop the legacy column a week later once dashboards confirm no callers remain.