Writing Migration-Safe Database Schemas
By Mykhailo Boichuk · Co-founder & Vice-President
In short
A schema change runs against a database that is live and serving code which expects the old shape. Safe migrations are backward compatible and applied in small steps: add new structures before using them, deploy code that tolerates both shapes, backfill data carefully, and remove the old structure only once nothing depends on it. The failures come from changes that lock large tables or break running code at the moment they apply.
The schema changes under live traffic
A database migration is not applied in isolation. It runs against a system that is serving requests, with application code that expects a particular schema. The instant a migration changes that schema, the running code either still works or it does not. A change that renames a column out from under code that reads it causes immediate errors, even if both the old code and the new code are individually correct.
This is why a migration cannot be reasoned about as a single atomic switch from old to new. It is a transition during which both the old and new states coexist, and the safe path keeps the system working at every point in that transition.
Backward-compatible, in small steps
The reliable pattern decomposes a change into steps that are each backward compatible. To replace a column, you add the new one first, deploy code that writes to both and reads the old, backfill the new column, switch reads to the new column, and only then stop writing and drop the old one. At no single step does the schema break the code currently deployed.
- Add new columns or tables before any code depends on them.
- Deploy code that tolerates both the old and new shapes during the transition.
- Drop old structures only after nothing reads or writes them.
Beware locks and long operations
Some schema operations lock a table while they run, and on a large table that lock can stall the application for the duration. Adding a column with a default, building an index, or rewriting a table can each cause this depending on the database. Knowing which operations lock, and using the non-blocking variants where the database offers them, is the difference between a migration that deploys quietly and one that causes an outage.
Make migrations reversible where you can
A migration that cannot be undone is a migration you must get right the first time under pressure. Where feasible, design each step so it can be reversed, and avoid destructive operations until the new path is confirmed working in production. The expansion-then-contraction pattern, adding the new before removing the old, naturally provides a window in which a problem can be caught and the change rolled back before anything is lost.
Key takeaways
- A migration runs against live code that expects a specific schema shape.
- Decompose changes into backward-compatible steps where old and new coexist.
- Add new structures before using them and drop old ones only when unused.
- Know which operations lock tables and use non-blocking variants where available.
- Backfill large tables in batches and keep migrations reversible where possible.
Frequently asked questions
- Why can a schema change break a running application?
- The application code is deployed against a specific schema shape, so a change that alters that shape, such as renaming a column code reads, causes immediate errors.
- How do you replace a column safely?
- Add the new column, deploy code that writes both and reads the old, backfill the new, switch reads to it, then stop writing and drop the old column.
- Why backfill large tables in batches?
- A single large backfill can hold a lock or generate huge transaction load, while batching keeps each operation small and lets the application keep serving.
About the author
Mykhailo Boichuk
Co-founder & Vice-President
Mykhailo is an engineer who builds native applications and the systems behind them. He concentrates on macOS and iOS performance, local-first data architecture, and the synchronization problems that come with offline-capable software.