DeploymentGENERALDATABASE-SPECIFICSCALE-SPECIFIC

Expand and Contract Migrations

Five steps that let a schema change survive a rolling deploy, because for the length of that deploy two versions of your code share one database.

What actually happens How to build it

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 has a problem.

The question

How do I change a database schema when old and new code will both be running against it?

The requirement

The name column must become first_name and last_name. The service cannot go down, and the deploy must be reversible.

The obvious build

Write a migration that renames or splits the column, run it as part of the deploy, and ship the code that uses the new shape. One migration, one release.

Why it breaks

The migration runs first: every old instance still selecting name starts throwing immediately, and stays broken until the last one is replaced. That is a full outage for the length of the rollout.

How it breaks in production

The migration runs first: every old instance still selecting name starts throwing immediately, and stays broken until the last one is replaced. That is a full outage for the length of the rollout.
The code ships first: new instances select first_name, which does not exist yet, and fail their readiness checks. The rollout stalls with a half-deployed fleet.
The rollback is impossible. Once the column is gone, the previous version cannot run at all, so the only way out of a bad deploy is forward.
The migration takes a table lock on a large table, and every write blocks behind it — an outage caused by the migration's duration rather than by its content (Schema Migrations from the Application Side).
A backfill is issued as one enormous UPDATE, which holds a long transaction, bloats the write-ahead log and blocks vacuum or replication for the duration.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

The root cause is a timing mismatch: code is replaced gradually across instances, while a schema change applies to everybody at the same instant. There is no ordering of "migration then code" or "code then migration" that avoids a window where one of them is wrong.
The fix is to remove the window by making the schema temporarily support both shapes at once. That is the expand phase: the schema grows so that both the old and the new code are correct against it.
Once every instance runs the new code and every row has been backfilled, the old shape has no readers or writers left, and can be removed. That is the contract phase, in a later release.
The five steps: expand the schema additively; deploy code that writes both and reads the old; backfill existing rows in batches; switch reads to the new shape (and stop writing the old); contract by removing the old shape in a subsequent release.
Each step is independently deployable and independently reversible. That is the property being bought — at no point does rolling back one step require undoing a data change.
The same pattern applies to anything shared across versions: API response fields, queue payloads, cache value shapes, search index mappings. The database is just the case with the sharpest failure.

Why one release cannot work

Put the two timelines side by side and the problem is arithmetic rather than judgement. Code changes over the minutes of a rollout; schema changes at a single instant. Whichever you do first, there is an interval where the running code and the live schema disagree.

Expand-contract does not schedule the two more cleverly. It makes the schema a superset, so that during the entire overlap both versions are correct — and only removes the old shape once nothing can possibly still be using it.

Splitting `name` into `first_name` / `last_name`

One release

-- migration, runs at t=0
ALTER TABLE users RENAME COLUMN name TO first_name;
ALTER TABLE users ADD COLUMN last_name text;

-- code rollout, t=0 .. t=4min
-- v2: SELECT first_name, last_name FROM users
-- v1: SELECT name FROM users  <-- 42703 undefined column
--
-- every v1 instance throws for four minutes,
-- and rollback is now impossible

Expand, then contract

-- release 1 (expand): additive only
ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name  text;

-- release 2: write both, read `name`
-- release 3: backfill in batches (a job, not a migration)
-- release 4: read first_name/last_name, keep writing both
-- release 5: stop writing `name`
-- release 6 (contract):
ALTER TABLE users DROP COLUMN name;

At every point in the right-hand sequence, both the currently-deployed version and the previous one are correct against the live schema. That is what makes each step independently reversible — and reversibility, not elegance, is what you are buying with the extra releases.

The five steps, and what each one protects

Each step exists because of a specific failure. Dual-writing exists because the backfill would otherwise race live traffic. The separate read switch exists so that rollback does not require a data change. The delay before contracting exists because dropping is the only irreversible operation in the sequence.

The last column is the one to read carefully: it says what is true if you stop here and roll back. Every row should say "safe", and that is the point of the pattern.

Step	Schema	Code writes	Code reads	If you roll back here
1. Expand	Old + new columns, new ones nullable	old	old	Safe: new columns are unused.
2. Dual-write	unchanged	old + new	old	Safe: previous version still writes and reads old.
3. Backfill	unchanged	old + new	old	Safe: backfill is idempotent and restartable.
4. Switch reads	unchanged	old + new	new	Safe: old column is still current, previous version works.
5a. Stop writing old	unchanged	new	new	Risky: rows written since this deploy have no old value.
5b. Contract	Old column dropped	new	new	Irreversible: previous version cannot run at all.

The backfill is a job, not a migration

DATABASE-SPECIFICPostgres syntax. MySQL 8 supports SKIP LOCKED similarly but has no UPDATE ... FROM; the equivalent is an UPDATE with a JOIN. The principle — bounded batches, idempotent predicate, durable progress — is engine-independent.

The most common operational injury in this pattern comes from step 3 being written as a single UPDATE inside the migration. On a large table that statement holds one transaction for its entire duration, which blocks schema changes, delays vacuum, keeps replicas behind and can exhaust write-ahead log space.

A backfill is a long-running data operation and should be built like one: bounded batches, a durable cursor, a pause between batches to leave headroom for live traffic, and safe to stop and restart at any point.

Batched, restartable backfill

1-- WRONG: one transaction over the whole table
2UPDATE users
3   SET first_name = split_part(name, ' ', 1),
4       last_name  = nullif(split_part(name, ' ', 2), '')
5 WHERE first_name IS NULL;
6 
7-- RIGHT: bounded batch, driven by a job that loops
8-- and sleeps between iterations.
9WITH batch AS (
10  SELECT id
11    FROM users
12   WHERE first_name IS NULL
13     AND name IS NOT NULL
14   ORDER BY id
15   LIMIT 5000
16   FOR UPDATE SKIP LOCKED
17)
18UPDATE users u
19   SET first_name = split_part(u.name, ' ', 1),
20       last_name  = nullif(split_part(u.name, ' ', 2), '')
21  FROM batch b
22 WHERE u.id = b.id
23RETURNING u.id;
24 
25-- progress / completion gate for step 4:
26SELECT count(*) FROM users
27 WHERE name IS NOT NULL AND first_name IS NULL;  -- must be 0

FOR UPDATE SKIP LOCKED lets several backfill workers run without contending, and WHERE first_name IS NULL makes each batch idempotent — a crashed run simply resumes. The final query is the gate: step 4 does not ship until it returns zero, and it must stay zero, which it only will if step 2 is genuinely dual-writing.

How to build it

Most important first.

Step 1 — Expand: add the new column(s) as nullable, with no constraint. Additive DDL that does not rewrite the table is safe to run while both versions serve.
Step 2 — Dual-write, read old: deploy code that writes both the old and the new representation on every write, and still reads the old one. This version is compatible with the previous one in both directions.
Step 3 — Backfill: copy existing rows into the new shape in bounded batches with a pause between them, driven by a job rather than a migration script, and made restartable (Background Jobs).
Step 4 — Switch reads: deploy code that reads the new shape. Keep dual-writing for at least this release so rollback to step 2 remains possible.
Step 5 — Contract: in a later release, stop writing the old shape; in a release after that, drop the column. Two separate steps, because dropping is irreversible.
Verify between steps rather than trusting them: a query that counts rows where the new shape is null or disagrees with the old one is the gate for step 4.
Put the migration in its own execution path — a job or a pipeline step with a lock — never in the application entrypoint where every starting instance races to run it.
Prefer a constraint added NOT VALID and validated separately where the engine supports it, so the exclusive lock is brief and validation runs without blocking writes.

What can go wrong

Failure modes

Skipping the dual-write step, so rows created during the backfill by old code have no new value and the backfill's "done" count is a lie.
A backfill that runs as a single statement, holding a transaction long enough to block replication or vacuum (Replication Lag: Reads That Are Correct and Stale in Observability & Performance).
Contracting too early — dropping the old column while a rollback target still needs it — which converts a routine revert into a restore-from-backup.
Forgetting a writer. A report generator, an admin script, an ETL job or another service writing the same table will not be dual-writing, and will produce rows missing the new shape.
Adding the NOT NULL constraint before the backfill has actually finished on the last few rows, which fails the migration and leaves the deploy in an unclear state.
Treating the five steps as one pull request merged behind a flag. The steps must be separated by *completed rollouts*, not by code paths.

What can race

During the backfill, old code is still inserting rows in the old shape. Without dual-writing first, the backfill and live traffic race, and the backfill loses.
Two instances starting at once both try to run the migration. Use an advisory lock or a single migration job so exactly one runs it (Schema Migrations from the Application Side).
A read at step 4 can hit a row written moments earlier by an instance still on step 2 code, if dual-writing was skipped or a writer was missed.

Security

A dual-write phase means personal data exists in two places at once. If the change is motivated by data minimisation or an access-control boundary, the contract step is the point at which the requirement is actually met — track it to completion.
Backfills read entire tables. Run them with a role scoped to the tables involved rather than an administrative one (Least Privilege in Security Engineering).
Dropped columns can persist in backups and in replicas for the retention window. "Deleted" at the schema level is not deleted everywhere.

Misreads

"Expand-contract is about avoiding downtime." It is about *compatibility between two running versions*. Avoiding downtime is the consequence.
"We can do it in one release if we deploy the migration and the code together." There is no together. The migration is instantaneous and the code rollout is not.
"A rename is a small change." A rename is a drop and an add from the perspective of any code that is still running.
"Backfill in the migration." A migration is a schema change that should be fast and locked; a backfill is a long data operation that should be batched and restartable. Combining them makes both worse.

Operating it

How you see it in production

Track the backfill as a job with progress, rate and a restart point, not as a script someone ran in a terminal.
Alert on rows where the new shape is null but the old is not, after dual-writing is live. Any such row means a writer was missed.
Watch lock waits and long-running transactions while the migration runs; a spike in lock wait time is the migration blocking application writes (Low CPU, High Latency: Lock Contention in Observability & Performance).
Record which migration version each instance expects at startup, so a version/schema mismatch is visible in logs instead of as a 500 (Validate at Startup, Fail Loudly).

What changes at 10x and 100x

Backfill duration grows with table size, and above some size it stops being a deploy-day activity and becomes a week-long background process that must survive restarts.
On large tables, the DDL that is "instant" on a small table may rewrite the whole table. The safety of a statement is a function of the engine, the version and the row count.
With many services sharing a database, expand-contract becomes a cross-team protocol: you cannot contract until every other writer has moved, and knowing who they are is the hard part (Microservices).

What this costs

Three to five releases instead of one, spread over days or weeks, with a dual-write period where the same fact is stored twice and can diverge.
Dual-writing costs write throughput and adds a consistency obligation: if the two writes are not in the same transaction, they can disagree.
The discipline is overkill for a table with no traffic during a maintenance window. It is not overkill for anything on a request path.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALThe five steps apply to any shared representation crossing a gradual rollout: schema, API payload, queue message, cache value, index mapping.
DATABASE-SPECIFICWhich DDL is safe differs sharply. Postgres can add a nullable column instantly and, since v11, one with a constant default; CREATE INDEX blocks writes unless CONCURRENTLY. MySQL 8 does many ALTERs online but not all, and the ones it cannot do online copy the table. Check your engine and version for the exact statement, never a general rule.
SCALE-SPECIFICOn a table of thousands of rows this is ceremony and a maintenance window would do. On a table of hundreds of millions with live writes, every step matters and the backfill is a project.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designbackward-compatibility removing-fields enum-evolution

Databasereplication transactions-and-acid isolation-levels partitioning-and-sharding

Securityleast-privilege

Performancereplication-lag lock-contention

Cloudrolling-deployment deployment-strategies