MigrationGENERALSCALE-SPECIFICCONTESTED

Data Migration

Schema, data and application code are three things that must change in a safe order. Getting the order wrong is the difference between a routine deploy and a restore from backup.

Constraints & invariants Who owns what What the next change costs

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind survives until the requirement changes.

The question

The schema has to change, the existing rows have to change, and the code has to change. What order does that happen in, and what is live in between?

The requirement

Orders currently store a single address text blob. They must store structured shipping and billing addresses, because tax now depends on the billing country.

The obvious build

Write one migration: add the new columns, run an UPDATE that parses the blob into them, drop the old column, and deploy the new code that reads the new columns. One script, one deploy.

Why it breaks

The UPDATE over forty million rows takes hours and holds locks; writes queue behind it and the service is down for the duration (Locks and Deadlocks).

How it breaks as requirements change

The UPDATE over forty million rows takes hours and holds locks; writes queue behind it and the service is down for the duration (Locks and Deadlocks).
The parse fails on the 2019 rows, which used a different address format. The script has already committed nine million rows and there is no record of which ones were guessed.
The old column is dropped in the same migration, so rolling back the deploy leaves new code reverted and the data it needed gone. The rollback is now a restore.
Between the migration running and the new code being live — or the reverse, depending on deploy order — one of the two is looking for columns the other has not produced (Designing the Migration).
The nightly warehouse ingest runs mid-backfill and loads a table in a state that is neither old nor new, which is discovered a week later as a discrepancy in a tax report (The Pipeline Succeeded. The Data Is Wrong. in Data Engineering).

Requirement→Constraints→Invariants→Responsibilities→Boundaries→Interfaces→State→Dependencies→Failure→Implementation→Tests→Feedback→Evolution

What limits the solution, and what must never stop being true

This domain leads with these two. A design that ignores its constraints is not a design, and an invariant nobody named is one nothing is protecting.

Constraints

Forty million order rows, seven years of history, and the oldest rows have address formats no current code has ever parsed.
The table is written to continuously; a long lock is an outage.
A reporting warehouse ingests this table nightly and will notice any shape change (Data Engineering and Backend Engineering in Data Engineering is the boundary being crossed).
Regulatory retention means old orders cannot be discarded or approximated; whatever the migration does to them has to be correct.

Invariants

No order may be unreadable at any point. Not during the backfill, not mid-deploy, not during a rollback.
The parse of a historical address is either correct or explicitly marked unparsed. A silently wrong billing country is a tax error, which is a worse outcome than a null.
The migration must be resumable and idempotent: it will be interrupted, and re-running it must not corrupt rows it already processed (Idempotency by Design).

Who owns what, and where the seams fall

Responsibilities decide boundaries; boundaries decide what an interface has to say.

Responsibilities

The schema change owns shape only, and must be safe to apply while old code runs. Adding a nullable column is safe; adding a NOT NULL column with a default may rewrite the table (How Is Database Data Physically Stored?).
The backfill owns transforming existing rows, in batches, resumably, with a record of which rows were transformed and how confidently.
The application code owns tolerating both shapes for the entire overlap, which is longer than the backfill because of the slowest consumer.
Someone owns the unparseable rows as a product decision. "What do we do with the 40,000 addresses we cannot parse?" is not an engineering question and it will not answer itself (The Requirements Nobody States).

Boundaries

The three changes must be in separate deploys. Schema, then code that tolerates both, then backfill, then code that requires the new shape, then contraction. Bundling any two removes a rollback point (Expand and Contract).
The batch boundary is where resumability lives: each batch commits independently and records its own progress, so an interrupted migration resumes rather than restarts.
The consumer boundary — the warehouse, the reports, the exports — is outside your deploy and sets how long the old shape must remain populated.

The order, and what is live at each step

The sequence is not ceremony. Each step exists so that the one after it is revertible, and the ordering constraint is specific: readers must tolerate a shape before writers produce it, and data must exist before readers depend on it.

The step that is easiest to get wrong is the last one, because by then the migration feels finished and dropping a column looks like tidying up.

Address blob to structured shipping and billing addresses

1
Expand the schema
Add nullable shipping_* and billing_* columns. No data change, no code change.
fails by A NOT NULL column with a default that rewrites forty million rows and locks the table (Why Migrations Are the Dangerous Change in DevOps).
2
Tolerate both, write both
Deploy code that reads new-falling-back-to-old and writes both shapes for every new and updated order.
fails by Shipping the writer before the reader, so an instance that has not been updated meets rows it cannot interpret.
3
Backfill in batches
Bounded, rate-limited, resumable batches; record per-row outcome including "could not parse".
fails by One UPDATE statement, which is an outage, and which loses all progress if killed.
4
Validate
Counts match, sampled rows re-derive the original blob, unparseable set quantified and escalated as a product decision.
fails by Trusting the job's exit code, which reports that rows were written and nothing about whether they are right (Validating a Backfill Before You Publish in Data Engineering).
5
Switch reads
New shape becomes authoritative. Dual-write continues so a revert needs no data repair.
fails by Stopping dual-write at the same time, which turns the rollback from a config change into a restore.
6
Contract, in two steps
Stop writing the blob; wait; verify no reader remains; drop the column in its own deploy.
fails by Dropping while the nightly warehouse ingest still selects it — the failure arrives at 2am, a week later (Destructive Migrations in DevOps).

Six steps, at least five deploys, and the system is releasable and revertible after every one of them. The elapsed time is dominated by step three and by the rollback window before step six (Expand and Contract).

The backfill is a job, not a statement

The single most common data-migration incident is a one-statement update over a large table. It is not slow because the database is bad; it is slow because it is doing forty million row transformations inside one transaction, holding locks and generating a write-ahead log the size of the table (Write-Ahead Logging).

Written as a job, the same work is boring: it can be paused, resumed, throttled when replica lag rises, and audited afterwards. The extra code is perhaps thirty lines and it is the difference between a deploy and an incident.

The statement that causes the outage, and the loop that does not

1-- the outage: one transaction, 40M rows, table locked, hours of WAL
2UPDATE orders
3   SET billing_country = parse_country(address)
4 WHERE billing_country IS NULL;
5 
6-- the job: bounded batches, resumable, throttleable, auditable
7UPDATE orders o
8   SET billing_country = p.country,
9       address_parse   = p.confidence      -- 'exact' | 'guessed' | 'failed'
10  FROM (SELECT id, (parse_country(address)).*
11          FROM orders
12         WHERE address_parse IS NULL
13         ORDER BY id
14         LIMIT 5000) p
15 WHERE o.id = p.id;
16-- driver: repeat until 0 rows; sleep between batches;
17-- pause when replica lag > 5s; `address_parse` IS the progress marker.

Two design decisions are doing the work. The batch is bounded by primary key order so it is resumable without a separate progress table. And address_parse records *how* each row was derived, which is what makes the unparseable rows a reportable number instead of a silent guess — without it, a wrong billing country is indistinguishable from a right one forever (Stable Identifiers).

Where data migrations actually go wrong

Almost none of these are failures of SQL. They are failures to treat the intermediate state as a real state of the system that consumers will observe, and to treat historical data as a source of requirements nobody wrote down.

The row worth reading twice is the silent guess. Every other failure here announces itself; that one does not, and it is the reason validation is a step rather than a habit.

Data migration failures, cause and response

Trigger	Symptom	Cause	Response
Single-statement backfill on a large table	Writes queue, latency spikes, the service is effectively down	One transaction holding locks across millions of rows	Batch, order by primary key, throttle on replica lag, make the progress marker part of the row
Parse fails on 2019-format addresses	40,000 rows with a plausible but wrong country	The transform guessed instead of recording that it could not tell	Record confidence per row; escalate the unparseable count as a product decision before switching reads
Job killed at 60%	Restart double-applies the transform to already-migrated rows	Not idempotent; progress inferred rather than recorded	Make the transform idempotent and the progress durable — ideally the same column (Idempotency by Design)
Old column dropped after the switch	Nightly warehouse ingest fails; rollback requires a restore	Contraction executed against the application's readers, not the database's	Verify readers from database telemetry, and give the drop its own deploy and approval
Warehouse ingest runs mid-backfill	A tax report is wrong; discovered a week later	The intermediate state was never communicated to consumers outside the deploy	Treat the intermediate state as an interface: tell consumers it exists, or gate them until validation passes (Data Contracts in Data Engineering)

How to build it

Most important first.

Add the new columns as nullable, in their own migration, with no data change. This is the only step that touches the schema while old code is live, and it must be provably instant on your database (Why Migrations Are the Dangerous Change in DevOps).
Deploy code that writes both shapes and reads the new shape falling back to the old. Now every new and updated row is correct, and the population of the backlog is a separate problem.
Backfill in bounded batches with a rate limit, recording progress and outcome per row. Never one statement over forty million rows; always a loop you can stop.
Validate before switching: compare counts, sample rows against the old blob, and quantify the unparseable set. Report the number to whoever owns the product decision.
Move reads to the new shape only after validation, and keep writing the old shape until the rollback window closes.
Contract last, and separately: stop dual-writing, then drop the old column, in two deploys with a gap. The drop is irreversible without a restore, so it gets its own change with its own approval (Destructive Migrations in DevOps).

What the next change costs

The field this whole domain exists for. A structure is only better if it makes the change after this one cheaper — and it is worth saying which changes it does not help.

Cost of the next change

This migration: five deploys, a batched backfill job that runs for days, and a validation step. Weeks of elapsed time and a few days of engineering, versus an hour for the naive version.
After: a change to address handling costs one edit against a structured model rather than a parse of a text blob. That is the return, and it is large — the whole point of the migration was that the next requirement, tax by billing country, is cheap.
What stays expensive: the unparseable rows. They will need a decision and possibly manual work, and that cost persists after the migration ends.
The cost of the shortcut is not lower, it is differently shaped: an outage plus a data-quality problem that is still being discovered in six months (The Cost of Change).

What the recommended approach costs

Five deploys and a week of dual-writing is genuinely more work and more moving parts than one script, and for a small table with a maintenance window it is the wrong choice.
Dual-writing means a period where the same fact exists twice and can disagree, which is a class of bug the naive version does not have.
Keeping the old column through the rollback window delays the benefit: the code cannot be simplified until contraction, which is weeks after the migration "finished".

What can go wrong

Failure modes

A single-statement backfill locks the table and takes the service down; the obvious mitigation, killing it, rolls back hours of work.
The backfill silently guesses on unparseable rows, producing plausible wrong data that is indistinguishable from correct data afterwards. This is the worst outcome in the lesson, because it is undetectable later.
The old column is dropped before every consumer has moved, and the warehouse ingest fails at 2am with no path forward but a restore.
The migration is not idempotent, is interrupted at 60%, and re-running it double-applies a transformation to rows it already processed.
The mitigation fails on its own terms: a resumable batched migration is written, but its progress table is not durable, so a restart loses the position and re-processes everything.

Dependencies, and their direction

The migration depends on the database's DDL semantics, which differ enormously: an ALTER TABLE that is instant on one engine rewrites the whole table on another, and this is where "it worked in staging" comes from (Should I Add an Index? and the storage internals it links to).
It depends on every reader of the table, including ones that bypass your application entirely — warehouse ingests, ad-hoc analyst queries, a partner's read replica (Schema Leakage in Backend).
The backfill depends on production capacity: it competes with live traffic for I/O, and an unthrottled one is a self-inflicted incident.

Misreads

"The migration is the SQL script." The script is one of five steps and the least risky. The sequence, the tolerance code and the validation are the migration.
"We tested it in staging." Staging has a thousand rows, all created by the current code, none of them from 2019. Migration bugs live in old data and in scale, and staging has neither (Production Data in Lower Environments in DevOps).
"Dropping the column at the end is cleanup." It is the irreversible step. Give it its own deploy, its own approval, and a verification that nothing still reads it.
"Nullable columns are a bad model, so make it NOT NULL from the start." During a migration, nullable is the mechanism that lets old and new coexist. Tighten the constraint after the backfill completes, as its own step (Optional Values and Absence).

Testing it, and how it ages

What to test, and at which boundary

Rehearse on a full-size restored copy of production, not a sample. The rows that break the parse are old, rare and absent from any sample someone chose (Restore Drills in DevOps).
Test resumability by killing the job at 40% and restarting it, then verifying that no row was transformed twice.
Validate with count comparisons and row-level sampling against the original blob, and treat the unparseable count as a first-class result rather than an error rate.
Test the rollback: with the new columns populated and the old column still present, does the previous release still work? Run its suite (Backward Compatibility as a Constraint).
Test the nightly consumer against the intermediate state, because it will run in it whether or not you planned for that.

How this design ages

The parse logic for historical formats does not go away when the migration ends — it becomes the definition of what those old rows meant, and it should live somewhere findable rather than in a deleted script (Docs Close to Code).
Each migration teaches you the real throughput of your backfill infrastructure, and that number should be reused rather than re-guessed.
Systems that migrate data often converge on a house tool: batched, resumable, rate-limited, with progress and validation built in. Building it on the second migration rather than the fifth is usually correct (The Rule of Three).

Where this applies

This domain's advice is contested more than most. These labels say what each claim is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view rather than a caricature.

GENERALThat schema, data and code are three separately-deployable changes with a required order follows from the system staying live, and holds for relational databases, document stores and event logs alike.
SCALE-SPECIFICAt ten thousand rows the backfill is a single statement that completes in under a second and the whole sequence collapses into one deploy. At forty million with continuous writes, the backfill is a job with its own reliability requirements. The threshold is roughly wherever your database's lock duration becomes user-visible, which is a number you should know for your engine.
CONTESTEDThe strongest opposing view: online multi-step migrations carry weeks of dual-write complexity and a real risk of being abandoned half-finished, whereas a rehearsed offline migration in a maintenance window is one atomic step with a trivially correct rollback — restore the backup. Teams operating on regional systems with predictable idle hours choose this deliberately and have fewer data incidents to show for it. It stops being available with global traffic or an external SLA, and it fails badly when the rehearsed forty minutes turns out to be four hours with users already locked out.

Where the depth lives

This domain teaches the codebase-level structure and hands the rest off.

Backendbackend-migrations dual-write-problem

Databasehow-data-is-stored transactions-and-acid locks-and-deadlocks mvcc write-ahead-logging should-i-add-an-index replication requirements-to-schema

DevOpsdatabase-migration-safety zero-downtime-migrations destructive-migrations backfill-operations restore-drills production-data-in-lower-environments

Domains that do not exist yet

— System Design — backfill throughput, replica lag and the capacity headroom a migration consumes are sizing questions that decide whether a six-hour job is safe to run at all.