Changing a database schema safely means treating it as a release with its own rollout and rollback plan, not a script you run once. The safest pattern is expand, migrate, contract: add the new shape alongside the old one, move data and traffic over gradually, then remove the old shape only after every consumer has verifiably switched.
Quick answer: Never rename, split, or drop a column in one step. Add the new column(s) as nullable, dual-write to old and new, backfill history in batches, cut reads over behind a flag, then drop the old column only after a bake period. This gives you a rollback point at every stage.
Why a Schema Change Is a Product Release, Not a Script
A schema migration is a release because it changes the contract every downstream consumer depends on — application code, background jobs, analytics pipelines, third-party integrations, and sometimes customers' own API calls. Treat it like any other release: staged rollout, monitoring, and a rollback path, not a one-way door run at 2 a.m.
The instinct to treat migrations as "just infrastructure" is the root cause of most schema-change incidents. A git diff on a model file looks tiny. But that one-line change to a users table can touch a reporting job that's read full_name for three years, a mobile app version you can't force-update, and a data warehouse sync that assumes the old shape. None of those consumers show up in your pull request.
Martin Fowler and Pramod Sadalage named this problem directly in Refactoring Databases: Evolutionary Database Design, one of the few widely cited texts on the subject. Their core argument: database changes need the same discipline as code refactoring — small, reversible, verifiable steps — because a database, unlike a function, can't simply be redeployed from source. It carries state.
The Three Failure Modes of "Just Run the Migration"
Most schema incidents trace back to one of three habits:
- Renaming or dropping in place. The old column disappears in the same deploy that ships new code reading the new one — any consumer still on the old contract (a queued job, a cached client, a BI tool) breaks immediately.
- No backfill plan for existing rows. New fields work for new records but silently return null or default values for the 100,000 rows created before the migration, corrupting reports and segment logic.
- No rollback path. The team can deploy the schema change forward but has no tested way to reverse it if the migrated data turns out wrong, so a discovered bug becomes a multi-day incident instead of a five-minute revert.
Each of these is a release-management failure, not a SQL failure. The fix is a release-management pattern.
The Expand-Migrate-Contract Pattern
The expand-migrate-contract pattern breaks a schema change into three separate, independently deployable stages so the old and new shapes of the data coexist safely for as long as you need. Each stage ships, bakes, and gets verified before the next one starts — exactly like a staged feature rollout.
| Phase | What happens | Reversible? | Typical duration |
|---|---|---|---|
| Expand | Add new nullable column(s)/table(s) alongside the old ones; no reads or writes change yet | Fully — just drop the new, unused column | Minutes to deploy |
| Migrate | Dual-write to old + new; backfill historical rows in batches; cut reads to new behind a flag | Yes — flip the flag back, old column still authoritative | Days to weeks |
| Contract | Stop writing to the old column; verify nothing reads it; drop it | No — this is the only destructive step | After a bake period with monitoring |
The key insight is that only the contract phase is destructive, and it's the one step you can defer indefinitely. Everything in expand and migrate can be undone by a flag flip or a dropped (unused) column, which is exactly the property you want before you touch production data at scale.
This is also why the pattern maps cleanly onto product thinking. If you've ever drawn an ERD before writing a spec — the discipline covered in drawing the ERD first because the data model is the product — you already have the "before" and "after" shapes. Expand-migrate-contract is simply the release plan for the delta between them.
Worked Example: Splitting full_name Into first_name and last_name
Splitting a single free-text full_name field into first_name and last_name is one of the most common — and most deceptively risky — schema changes, because "just split on the space" fails for a meaningful percentage of real names. The safe version runs the split as three deploys, not one.
Say your users table looks like this today:
users (id, email, full_name, created_at)
And the target shape, driven by a support and CRM need to sort and address users by first name, is:
users (id, email, full_name, first_name, last_name, created_at)
Notice full_name isn't dropped yet — that's deliberate, and it's also a reminder that this exercise is really an attribute-modeling decision. Whether "name" is one attribute or two comes down to what actually counts as an attribute versus an entity in your model: the moment two parts of a field need to be queried, sorted, or validated independently, they're separate attributes, not string formatting.
Step 1 — Expand: Add the Nullable Columns
ALTER TABLE users ADD COLUMN first_name TEXT NULL;
ALTER TABLE users ADD COLUMN last_name TEXT NULL;
On Postgres 11 and later, adding a nullable column (or one with a constant default) is a metadata-only change — it doesn't rewrite the table, so it's safe to run on a large users table without a maintenance window. That's a real, documented behavior change in the Postgres engine, and it's worth confirming your database version actually has it before you assume the operation is cheap.
Deploy this alone. Nothing reads or writes the new columns yet. This is your first rollback point: if anything looks wrong, drop two empty columns and you're back to baseline.
Step 2 — Migrate: Dual-Write Going Forward
Ship an application change that writes first_name and last_name on every create or update of a user, in addition to full_name. Use your best split logic (a name-parsing library, not a naive .split(" "), since compound and multi-part names are common) and log low-confidence splits for manual review rather than guessing silently.
def save_user(data):
full_name = data["full_name"]
first, last = split_name(full_name) # logs low-confidence splits
db.update(user_id, full_name=full_name, first_name=first, last_name=last)
At this point, new and updated rows have both shapes populated. Old rows still only have full_name. That gap is what backfill closes.
Step 3 — Backfill the Historical Rows
Backfill in small batches, off peak hours, with a throttle, and never as one giant UPDATE users SET ... statement against a live table:
UPDATE users
SET first_name = split_first(full_name),
last_name = split_last(full_name)
WHERE first_name IS NULL
AND id BETWEEN :batch_start AND :batch_end;
For tables large enough that even batched updates cause replication lag or lock contention, this is exactly the problem online schema-change tools exist to solve. GitHub built and open-sourced gh-ost for this reason, and Percona's pt-online-schema-change predates it — both work by copying data in the background and cutting over, rather than locking the live table.
Step 4 — Cut Reads Over Behind a Flag
Once backfill coverage hits 100% (track this as a metric, not a guess), switch read paths to first_name/last_name behind a feature flag, one consumer at a time: the CRM export first, then the support tool, then customer-facing profile pages last. Each consumer you migrate is a checkpoint — if the sort order breaks in the CRM, you flip its flag back without touching anyone else.
This staged cutover matters most wherever users directly experience the field — profile displays, email greetings, invoices. Map those touchpoints the same way you'd map any customer journey: every place full_name renders today is a place that can regress silently if the split logic gets an edge case wrong (suffixes, single-word names, non-Latin scripts).
Step 5 — Contract: Stop Writing and Drop full_name
Only after every consumer reads from the new columns, and after a bake period with no errors, do you stop dual-writing and drop full_name:
ALTER TABLE users DROP COLUMN full_name;
This is the irreversible step, so it deserves the same scrutiny you'd give a hard delete. The same reasoning behind choosing soft delete over hard delete when modeling removal applies here: before you drop, ask whether you actually need full_name recoverable for a while (compliance export, an integration you forgot about) or whether an audit-log snapshot is enough. Many teams rename to full_name_deprecated and drop it a release later instead of dropping immediately.
Backfilling Without Locking the Table or Corrupting Reports
Backfilling is the highest-risk step in the whole migration because it touches every existing row while the system stays live, and the two failure modes — locking the table and silently mis-populating data — look identical from a dashboard until they don't. Batch size, throttle, and validation are what separate a boring backfill from an incident.
Three practices reduce backfill risk substantially:
- Batch by primary key range, not by table scan. Iterate
WHERE id BETWEEN x AND yin chunks of a few thousand rows, with a short sleep between batches, so you never hold a long-running lock or saturate write I/O. - Make the backfill idempotent and resumable. Guard every batch with
WHERE first_name IS NULLso a restarted job never reprocesses rows, and you can safely pause it during a traffic spike. - Validate before you trust it. Run a reconciliation query — row counts, null counts, a sample diff against source data — before you flip any read path. A backfill that "completed" without errors can still have silently mis-split a meaningful share of names.
| Backfill approach | Best for | Risk if done wrong |
|---|---|---|
Single UPDATE statement | Small tables (thousands of rows) | Long lock, blocked writes, timeout |
Batched UPDATE in application code | Medium tables, needs custom split logic | Runs for hours; must be resumable |
Online schema-change tool (gh-ost, pt-online-schema-change) | Large MySQL tables (tens of millions+ rows) | Requires binlog access and careful cutover |
| Background job queue (one job per row/batch) | Needs external API calls or heavy logic per row | Queue backlog if throughput isn't tuned |
Writing the Rollback Plan Before You Ship the Migration
A rollback plan for a schema migration should specify, before you deploy, exactly which signal triggers a rollback, which phase you're rolling back from, and what "rolled back" means at that phase — because the answer is different in expand, migrate, and contract, and only one of those phases can't be undone.
Write this down in the PRD or launch doc, not in your head. At minimum, define:
- The trigger. What specific signal — error rate on writes, a spike in null
first_nameafter cutover, a support ticket volume threshold — causes you to stop and roll back rather than push forward? - The mechanism per phase. In expand, rollback is dropping an unused column. In migrate, it's flipping the read flag back to the old column (data keeps flowing to both, so nothing is lost). In contract, there is no rollback — only restore from backup — which is exactly why contract should be the last, smallest, most-delayed step.
- The owner. Who has the authority and the access to execute the rollback at 11 p.m. without a meeting?
- The bake period. How long does the new shape run in production, dual-written and monitored, before you're comfortable calling contract safe? A week is a common floor for anything customer-facing.
| Phase | Rollback action | Data loss risk | Decision owner |
|---|---|---|---|
| Expand | Drop the new, empty column | None | Engineer on call |
| Migrate (dual-write) | Flip read flag back to old column | None — old column still current | PM + eng lead |
| Migrate (backfill) | Pause job; old column unaffected | None | Eng lead |
| Contract | Restore from backup only | Possible, if backup predates other changes | Eng lead + PM sign-off |
Stripe's public documentation of its API versioning system is a useful adjacent model here: instead of forcing every integration onto a new shape at once, Stripe pins each account to the API version active when it started, and migrates accounts individually with an explicit upgrade path. The lesson transfers directly — you don't need every consumer to move at the same time, and you shouldn't force them to.
Scoping the Migration Before You Write the PRD
The scoping work — which columns change, which consumers are affected, and how big the backfill is — belongs before the PRD, not inside it, because the backfill size and consumer count are what determine whether this is a one-sprint change or a three-phase program. Model the target schema first, then diff it against what exists today.
This is precisely the gap a Data Modelling tool is built to close: model the target entities and attributes, generate the SQL DDL for the new shape, and diff it against the current schema before you commit anything to a roadmap slide. In Prodinja's Data Modelling tool, you can lay out the first_name/last_name split alongside the existing full_name field and see the shape of the change — new nullable columns, no removed columns yet — before a single migration file exists. That diff is what lets you scope the backfill honestly (row counts, affected consumers, estimated batches) in the PRD instead of discovering the real size of the job mid-sprint.
It's the same instinct behind treating the data model as the product itself: the schema is not an implementation detail hiding behind the PRD, it's a decision the PRD should be built around. If you're new to the fundamentals of entities, attributes, and relationships before attempting a migration like this, our complete guide to data modeling is the right starting point.
It's also worth asking, before scoping any split like this, what job the change is actually serving. A full_name split driven by "the CRM needs to sort by first name" is a different scope than one driven by "compliance needs legal first/last name separately" — the kind of distinction Jobs to Be Done thinking is designed to surface before you write a single line of DDL.
Key Takeaways
- Treat every schema change as a staged release, with the same rollout discipline, monitoring, and named rollback owner as a feature launch — not a one-time script.
- Use expand-migrate-contract: add nullable new structure, dual-write and backfill in batches, cut reads over per-consumer behind a flag, then drop the old shape last and only after a bake period.
- Backfills fail quietly, not loudly — batch by primary key, make jobs idempotent and resumable, and validate row counts before trusting the job finished correctly.
- Only the contract phase is irreversible — defer it as long as useful, and require explicit sign-off (not just a passing test suite) before you run it.
- Write the rollback trigger and owner into the plan before you ship, not after something breaks — "we'll figure it out if it goes wrong" is not a rollback plan.
- Model the target schema and diff it against today's shape before writing the PRD — it turns "add first_name and last_name" from a vague ticket into a scoped, estimable migration plan.
Frequently Asked Questions
How do you migrate a database schema without downtime?
Use the expand-migrate-contract pattern: add new nullable columns first (no downtime, metadata-only on modern Postgres/MySQL), dual-write and backfill in throttled batches while the app keeps running, then cut reads over per-consumer behind a flag. Downtime typically comes from skipping straight to a rename or drop, which locks or breaks reads immediately.
What's the safest way to rename a column in production?
Never rename in place. Add the new column, dual-write to both old and new names, backfill history, migrate every reader to the new name behind a flag, confirm nothing reads the old name, then drop it. A true in-place RENAME COLUMN breaks any consumer still deployed against the old name the instant it runs.
How long should you dual-write before cutting over reads?
Long enough to cover your full backfill plus a bake period with real production traffic and monitoring — commonly a few days to a couple of weeks for customer-facing fields, shorter for internal-only tables. The right duration is set by your slowest consumer to migrate and your tolerance for the (rollback-able) risk of running two writes per record.
Do you need a PRD for a schema migration?
Yes, if the migration is user-visible, touches a large backfill, or affects more than one consuming system — the PRD is where you document the trigger, rollback owner, and bake period, not just the target schema. Purely internal, single-consumer schema tweaks with no backfill can usually skip the full PRD and just need a written rollback note in the PR.
What's the difference between a schema migration and a data migration?
A schema migration changes the structure — new columns, tables, or constraints — while a data migration moves or transforms the values that live inside that structure, such as backfilling first_name from full_name. In practice they're usually paired: the expand phase is a schema migration, and the backfill inside migrate is a data migration riding on top of it.