A senior backend interview question I like: "You need to rename the users.name column to full_name on a table with 400 million rows, and the app can't go down. Walk me through it." Most candidates say "run ALTER TABLE at night". The interviewer wants the expand-and-contract pattern.
full_name as a nullable column. Adding a nullable column without a default is a metadata-only change in modern Postgres and MySQL.name and full_name but still reads name.full_name. Verify with a checksum or sampled comparison that both columns match. Stop writing name in a later deploy.name (check query logs, not just the codebase), drop it.Every step is independently deployable and reversible until the final drop. That is the property to emphasise.
CREATE INDEX CONCURRENTLY in Postgres; a plain create blocks writes.NOT VALID check constraints first, validate separately, to avoid a long lock.Summarise the trade-off: expand-and-contract costs several deploys and temporary storage, but each step is safe and observable. Then mention how you would monitor it: replication lag, error rates, and a mismatch counter between the two columns during the dual-write phase.
Disclosure: I help build real-time interview help from Linkjob AI; this is from our backend system-design practice notes.