Expand/Contract Pattern — Additive Schema Changes Without Downtime
Expand/contract keeps old and new code running on one schema: add the new shape, migrate traffic and data while both exist, and drop the old shape only after every reader and writer is gone. A rename is a new column plus dual-write, not one RENAME in the same deploy.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
Expand/contract, also called parallel change, is the default zero-downtime schema discipline.
- Expand the schema so old and new code can both run.
- Migrate traffic and data while both shapes exist.
- Contract by removing the old shape only after every reader and writer is gone.
You never rename or drop in the same deploy as the code that still needs the old columns. Versioned app releases pair with schema phases so a rolling deploy stays dual-compatible.
This is the application and release process. Online DDL is how you apply a heavy index build or table copy without holding an exclusive lock for the whole job. You usually need both. How the engine stores pages is the storage engines cluster, not this one.
Flow
- 1
1. Expand schema: nullable add only
- next2. Deploy code that knows both shapes
- 2
2. Deploy code that knows both shapes
- next3. Backfill nulls while dual-writing
- 3
3. Backfill nulls while dual-writing
- next4. Flag readers onto the new shape
- 4
4. Flag readers onto the new shape
- next5. Stop writing the old columns
- 5
5. Stop writing the old columns
- next6. Drop old shape only after soak
- 6
6. Drop old shape only after soak
Lesson map
Expand/Contract Pattern — Additive Schema Changes Without Downtime
Expand/contract keeps old and new code running on one schema: add the new shape, migrate traffic and data while both exist, and drop the old shape only after every reader and writer is gone. A rename is a new column plus dual-write, not one RENAME in the same deploy.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB dev["Pipeline"] db["Primary"] old["Old pods"] new["New pods"] dev -->|ADD COLUMN| db dev -->|Roll v2 writers| old dev -->|Writers set both| new dev -->|Chunked UPDATE| db new -->|Prefer new| db dev -->|DROP old column| db
Five phases, two binaries
Schema expand and code expand are different deploys. Contract is a later release.
- 1
Expand the schema
Additive only: ADD COLUMN, a new table, a new index built online. Prefer nullable columns, or a default the engine can store as metadata. - 2
Expand the code
Deploy writers and readers that understand both shapes. Old pods and new pods overlap. - 3
Migrate
Backfill nulls and dual-write new fields. Watch mismatch and lag, not just job success. - 4
Switch
Readers prefer the new shape behind a feature flag. Then stop writing the old fields. - 5
Contract
Drop the old column, table, or index in a later release, after a soak and a usage check.
What the deploy pipeline actually does
Sequence
- 1
Pipeline
Phase 1 expand schema
- 2
Pipeline → Primary
ADD COLUMN nullable
- 3
Old pods
Phase 2 both shapes
- 4
Pipeline → Old pods
Roll v2 writers
- 5
Pipeline → New pods
Writers set both
- 6
Pipeline
Phase 3 backfill
- 7
Pipeline → Primary
Chunked UPDATE
- 8
New pods
Phase 4 switch reads
- 9
New pods → Primary
Prefer new column
- 10
Pipeline
Phase 5 contract
- 11
Pipeline → Primary
DROP old column
The worked example is users.email_verified (new boolean) replacing legacy_verified_at (old timestamp). Phase 1 adds the boolean as nullable. Phase 2 ships code that reads with a fallback and writes both. Phase 3 backfills. Phase 4 prefers the boolean. Phase 5 drops the timestamp only after nothing still selects it.
Nullable columns, defaults, and NOT NULL
- A nullable expand is the safest add. Old code ignores the column. New code writes it.
- A constant DEFAULT on
ADD COLUMNin modern Postgres is often metadata-only. Still verify the version, and whether a non-constant default expression forces a rewrite. NOT NULLwithout a default on a populated table is a rewrite or a failure. Expand nullable, backfill, validate, then addNOT NULLin a later phase. You can also enforce the rule in the app, or with aCHECK, before the constraint is physical.- ORM surprises:
SELECT *and schema caches break when columns appear or disappear mid-rollout. Prefer an explicit column list. Shopify's sharded migrations put columns that are mid-add or mid-drop on an ignore list so boot does not emit SQL that names them before every shard has the same shape.
Why this beats freeze-and-cutover: a rolling deploy already means two binary versions run together. Expand/contract makes that overlap explicit. A maintenance window only works if the migration finishes before traffic returns.
Dual-compatible read and write
Prefer the new column when it is present. Fall back so rows that are not backfilled yet still answer. Writes set both columns in one statement until contract.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
The new column wins even when the legacy timestamp is still set. That is the read switch in miniature: after backfill, false must not be overwritten by a leftover timestamp.
Versus a freeze
| Expand / contract | Freeze and cut over | |
|---|---|---|
| Wins when | Always-on SLO, rolling deploys, large tables, rollback via a code flag | Tiny database, one approved window, a short irreversible transform |
| Cost | More pull requests, more calendar time, the discipline to actually contract | Revenue and SLO hit, heroics if the migration runs long, no gradual validation |
| Failure | Orphaned columns if nobody tickets the drop | A window that does not finish before traffic returns |
Moving the row to another store is heavier than a column add. That is dual-write, not this page.
Interview Q&A
How do you add a NOT NULL column without downtime?
Answer
ADD the column nullable, deploy writers, backfill, optionally enforce the rule in the app, then ADD NOT NULL or a CHECK once no nulls remain. Never add NOT NULL on a hot, large table as one shot.
Can you rename a column in one migration?
Answer
The engine rename may be metadata-fast. The rolling deploy still breaks, because old pods use the old name and new pods use the new name. Treat a rename as expand (add the new name), dual-write, then contract (drop the old name).
Who owns contract?
Answer
The same team that expanded, on a scheduled soak. Ticket the DROP. Check column usage — pg_stat_user_columns or query logs — before you drop. Contract is not the same pull request as the read switch.
What breaks if you expand the schema and skip dual-compatible code?
Answer
During the rolling deploy, half the fleet errors on missing or extra assumptions. A blue-green app deploy reduces overlap. Jobs and cron that lag the web fleet still need the expand.
How does this relate to online DDL?
Answer
Expand/contract is the product process. Online DDL is how you apply heavy schema operations — indexes, table copies — without a long exclusive lock. A nullable ADD COLUMN can be the cheap expand. A new index on a hot table is the online-DDL problem on the next page.
Why is a nullable column safer than a default plus NOT NULL?
Answer
Old code does not have to know the column exists. A NOT NULL add on a populated table rewrites or fails. A constant default is often metadata-only on modern Postgres, but a volatile default expression is not. Verify the version instead of trusting a blog from three majors ago.
What do ORMs get wrong mid-rollout?
Answer
SELECT * returns a new column to code that unpacks a fixed tuple. Schema caches keep the old catalog until restart. Dropping a column that a still-running process selects fails the query. Explicit column lists, and an ignore list for columns in transition, keep the fleet boring.
When is a freeze the right call?
Answer
The database is tiny, one person owns the window, downtime is approved, and the change is one short irreversible transform. Always-on product tables are the expand/contract case.
Pitfalls
- Shipping the reader of
new_colbefore the column exists. - Dropping the old column while any pod, report, or replica still names it.
- Treating a fast
RENAMEas application-compatible. - Adding
NOT NULLbefore the backfill has cleared nulls. - Forgetting to contract, then discovering three generations of columns nobody can drop safely.
- Using
SELECT *across a rollout that adds or removes columns.
accounts.status_code (integer) must become accounts.status (text) on a table that takes writes all day. Write the five deploys. Say which deploy is allowed to DROP status_code, and what a reader returns for a row the backfill has not reached.
Go deeper
Martin Fowler's parallel-change note is the short definition. Postgres ALTER TABLE and the MySQL online DDL page are where you check whether a particular add is metadata-only or a rewrite. Lock behavior for the rewrite is the next lesson: Online DDL.
If old and new code cannot run against the same schema at once, you do not have a zero-downtime migration. You have a race.