Schema Migrations
Changing a schema under live traffic without taking the site down.
35 minDifficulty 3/5database · opsAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
A schema change needs to add a `NOT NULL` column to a table with ten million existing rows, in a system that can't afford downtime. Running `ALTER TABLE users ADD COLUMN role TEXT NOT NULL` directly fails immediately — there's no value for `role` on any existing row, and even if it didn't fail, some databases would lock the entire table for the duration of rewriting ten million rows, which could be minutes of total unavailability.
The mental model
A migration is a versioned, ordered, incremental change to a database schema — checked into source control alongside the code that depends on it, applied in a known sequence, and (ideally) reversible. Think of it as the schema's own commit history: each migration is one small, deliberate step, and the current schema is simply the result of applying every migration in order.
How it works
A migration adding a NOT NULL column to a populated table needs a default or a multi-step approach
Adding `role TEXT NOT NULL DEFAULT 'user'` works for existing rows (they all get the default value) but can still lock the table on some databases if it has to physically rewrite every row to add the value. A common safe pattern: add the column as NULLABLE first, backfill existing rows in batches, THEN add the NOT NULL constraint once every row has a value — three small migrations instead of one risky one.
Migrations must never be edited after they've been applied anywhere
Once a migration has run against ANY environment (a teammate's machine, staging, production), editing its contents means different environments now disagree about what that migration ACTUALLY did — some ran the old version, some would run the new one. The fix for a mistake in an already-applied migration is always a NEW migration that corrects it, never editing history that's already partially executed elsewhere.
A rollback (down migration) is the schema's own 'undo', and it isn't always possible
A migration that ADDS a column can have a clean rollback (drop the column). A migration that DROPS a column and its data cannot have a truly clean rollback — the data is genuinely gone, and 're-adding the column' produces an empty column, not the original data. Destructive migrations should be treated with extra caution specifically because their rollback path is one-directional.
Migrations that change behaviour, not just structure, need to be sequenced with the code deploy
Renaming a column that application code currently reads by its OLD name will break that code the instant the migration runs, if the code deploy hasn't happened yet — a genuinely safe rename typically requires: add the new column, deploy code that writes to BOTH, backfill, deploy code that reads from the new one, THEN drop the old one — several small, sequenced steps rather than one migration assumed to land atomically with a code deploy.
The mechanism
Migration tooling tracks which migrations have already been applied to a given database, typically in a dedicated tracking table. Running the migration command applies any not-yet-applied migrations, in order, updating the tracking table as each succeeds. This lets any environment — a fresh developer machine, a staging server, production — reach an identical, known schema state simply by applying the same ordered sequence of migrations.
What people get wrong
- Editing an already-applied migration file to fix a mistake is fine as long as you re-run it.
- Any environment that already ran the OLD version of the migration won't automatically re-run the edited version — migration tools track what's already been applied by name/id, not by content, so editing history creates silent disagreement between environments about what actually happened. This produces a genuinely confusing class of bug: 'it works on my machine' because the local database happens to have the edited version's effects, while a colleague's database (or production) still reflects the original, un-edited version.
- A schema change and the corresponding code change can always be deployed together, atomically, with no ordering concerns.
- In most real deployments, the migration runs and the new code deploys as SEPARATE steps, often with old code briefly running against the new schema (or vice versa) during the transition — a migration that isn't backward-compatible with the currently-running old code can cause a real outage during that window. This is why renames and other breaking schema changes need multi-step migration strategies (add, dual-write, backfill, cut over, remove) rather than a single migration assumed to be perfectly synchronized with a code deploy.
- A migration adding a NOT NULL column will simply fail cleanly if there are existing rows, so it's a safe operation to attempt.
- Depending on the database and how it's written, this can range from a clean failure to a long table lock that blocks other queries for the duration — 'it will just fail' isn't a safe assumption to operate under for a large, actively-used production table. The actual behaviour depends on specifics (database engine, table size, whether a default is provided) that are easy to get wrong when assuming a migration will fail safely rather than actually testing its impact.
When not to use it
- The table is small, or the application can tolerate brief downtime for this specific change.
- A simpler, single-step migration is often fine here — the multi-step, zero-downtime approach is extra complexity that's only necessary when the table is large and downtime is genuinely unacceptable.
- A migration mistake has already been applied to a shared environment (staging, production).
- A NEW migration that corrects the mistake, never editing the already-applied one — this preserves an accurate, trustworthy history of what each environment actually ran.
Terms
- Migration
- — A versioned, incremental change to a database schema, tracked and applied in a known order to keep environments' schemas in sync.
- Rollback / down migration
- — The reverse operation undoing a migration's change, not always fully possible for destructive changes that discard data.
- Zero-downtime migration
- — A schema change strategy broken into multiple small, backward-compatible steps so the application keeps working correctly throughout the transition.
- Backfill
- — Populating a newly added column with values for existing rows, typically done in batches to avoid locking a large table for an extended period.
In an interview
You need to rename a column that's actively read by running application code, on a large production table, with zero downtime. What's your approach?
- a single rename migration would break the currently-running old code the instant it's applied, before the new code deploys
- a safe approach: add the new column, deploy code that writes to both old and new, backfill existing rows, deploy code that reads from the new column, then drop the old column in a later migration
- each step is independently safe regardless of exact deploy timing relative to the migration
Can you recall it?
Why must a mistake in an already-applied migration be fixed with a new migration, rather than editing the original migration file?