Concept questionHard~9 minConsistency / 4 of 7

Interview question

How would you add a required column or split a column in production without breaking the app?

Plan a safe schema migration without downtime

Explains expand-and-contract migrations, backwards-compatible schema changes, backfills, rollout order, and rollback thinking.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I would avoid making a breaking schema change and code change at the same instant. For a required column, I would first add it nullable or with a safe default, deploy code that writes it, backfill existing rows, then add NOT NULL after the data is complete. This expand-and-contract approach keeps old and new application versions compatible during deployment.

Full answer below

Practice focus

Schema migration safety, expand-and-contract, backfills, compatibility, deployment order, locks, and rollback.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Schema migrations are where database design meets deployment reality. This question checks whether a candidate can change data safely while the app is live.
I would avoid making a breaking schema change and code change at the same instant. For a required column, I would first add it nullable or with a safe default, deploy code that writes it, backfill existing rows, then add NOT NULL after the data is complete. This expand-and-contract approach keeps old and new application versions compatible during deployment.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
For a column split or rename, I would use a similar staged approach. Add new columns, dual-write or derive new values, backfill, read from the new shape after validation, then remove the old column in a later deployment. I would consider lock duration, table size, indexes, foreign keys, and whether the migration can run online. Rollback plans matter because code rollback may need the old schema to remain available.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
For production I would treat migrations as release artifacts with monitoring and ownership. Large backfills need batching, progress tracking, throttling, and a retry path. I would run preflight queries to estimate affected rows and detect bad data. After release, I would validate row counts, null counts, constraints, and application errors. Data, application versions, and deployment order need to stay compatible throughout the change.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Adding a NOT NULL column without default or backfill on a large live table.
Renaming or dropping a column in the same deployment that changes code.
Running an unbounded backfill during peak traffic.
Having no rollback plan for code and schema compatibility.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain expand-and-contract migration.
Ignores existing data.
No batching or monitoring for backfills.
Treats migrations as harmless because they work locally.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would you backfill a large table safely?
What makes a migration rollback-friendly?
When would you split migration and code deployments?

Continue the section

Next in Consistency: Blocking vs Deadlock

Step 5 of 7

Diagnose blocking versus deadlocks

Opening drill

Concept questionHard~9 minConsistency / 4 of 7

Interview question

How would you add a required column or split a column in production without breaking the app?

Plan a safe schema migration without downtime

Explains expand-and-contract migrations, backwards-compatible schema changes, backfills, rollout order, and rollback thinking.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

Full answer below

Practice focus

Schema migration safety, expand-and-contract, backfills, compatibility, deployment order, locks, and rollback.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Schema migrations are where database design meets deployment reality. This question checks whether a candidate can change data safely while the app is live.
I would avoid making a breaking schema change and code change at the same instant. For a required column, I would first add it nullable or with a safe default, deploy code that writes it, backfill existing rows, then add NOT NULL after the data is complete. This expand-and-contract approach keeps old and new application versions compatible during deployment.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
For a column split or rename, I would use a similar staged approach. Add new columns, dual-write or derive new values, backfill, read from the new shape after validation, then remove the old column in a later deployment. I would consider lock duration, table size, indexes, foreign keys, and whether the migration can run online. Rollback plans matter because code rollback may need the old schema to remain available.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
For production I would treat migrations as release artifacts with monitoring and ownership. Large backfills need batching, progress tracking, throttling, and a retry path. I would run preflight queries to estimate affected rows and detect bad data. After release, I would validate row counts, null counts, constraints, and application errors. Data, application versions, and deployment order need to stay compatible throughout the change.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Adding a NOT NULL column without default or backfill on a large live table.
Renaming or dropping a column in the same deployment that changes code.
Running an unbounded backfill during peak traffic.
Having no rollback plan for code and schema compatibility.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain expand-and-contract migration.
Ignores existing data.
No batching or monitoring for backfills.
Treats migrations as harmless because they work locally.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would you backfill a large table safely?
What makes a migration rollback-friendly?
When would you split migration and code deployments?

Continue the section

Next in Consistency: Blocking vs Deadlock

Step 5 of 7

Diagnose blocking versus deadlocks

How would you add a required column or split a column in production without breaking the app?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue the section

How would you add a required column or split a column in production without breaking the app?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue the section