What is an expand contract migration, and why does a direct column rename break zero downtime deploys?
An expand contract migration changes a schema in three phases instead of one. First you expand: you add the new structure alongside the old one. Next you migrate: application code and data move over to the new structure. Finally you contract: you remove the old structure once nothing reads or writes it. Renaming a column is the classic case, because a rename looks atomic but cannot be atomic for the running system.
The database applies RENAME COLUMN instantly. Your application fleet does not change instantly. During a rolling deploy, some instances still run the old release while others start the new one, and all of them share one database. If the rename runs before the rollout, old instances query a column that no longer exists. If it runs after, new instances query a column that doesn't exist yet. A rollback reopens the same gap in reverse: the previous release comes back against a schema it has never seen. Every version of the ordering fails for some fraction of requests.
The pattern follows one compatibility rule:
- Every schema state must work with both the code that is currently deployed and the code being rolled out.
- Every code version must work with the schema both before and after its paired migration.
A direct rename breaks both halves of this rule at once.
This guide is for backend and platform engineers who run monoliths with rolling deploys and no maintenance window. In that setup, mixed-version operation is a normal, repeated state of the system, so the schema has to support it.
Who should use an expand contract migration?
Use it if your team changes the schema of a database that serves live traffic, and you cannot stop every application instance before a schema change takes effect. In practice, that usually means one or more of these:
- Backend engineers who own a monolith or service with rolling, blue-green or canary deploys, where old and new releases run against the same database at the same time.
- Platform and infrastructure engineers who maintain the deploy pipeline and migration tooling, and want schema changes to follow one repeatable, reviewable process.
- Teams that share a database with background jobs, reporting queries, ETL pipelines or other services. These consumers deploy on their own schedules, so they cannot all switch to a new column name at the same moment.
- Teams that need fast, safe rollbacks. If a deploy must be reversible at any time without a schema change, every schema state has to stay compatible with the previous release.
It is less useful for small tables in internal tools, single-instance applications that restart all at once, or systems where a short scheduled outage is acceptable. The last section of this article covers those cases.
How does it work?
The core idea is to never ask the database and the application fleet to change at the same moment. Instead, you break one incompatible change into a sequence of small, compatible ones:
- Expand the schema so it supports both the old and the new shape. For a rename, that means adding the new column next to the old one. Nothing reads it yet, so existing code is unaffected.
- Make the application tolerate both shapes. Code writes to both columns while still reading from the old one, so either release can run against the database.
- Move the data. A backfill copies existing values into the new column, and a parity check confirms the two columns match.
- Move the source of truth. Reads switch to the new column, then writes to the old column stop.
- Contract the schema by dropping the old column once no code, job or rollback target depends on it.
Each step is a separate deploy. At every point, the schema is compatible with the release that is running and the one that replaces it, so a rolling deploy or a rollback never meets a schema it does not understand. The next section walks through those steps in detail.
How does an expand contract migration work step by step?
Each phase below ships as its own deploy, and you do not start the next one until the current version is fully rolled out everywhere. The rule that keeps this safe is that every running version must be compatible with the schema and with the version deployed immediately before and after it, because both run at the same time during a rollout.
| Phase | Source of truth | Compatible code versions | Rollback |
|---|---|---|---|
| 1. Add nullable new column | Old | All existing code, which ignores the column | Leave the column; drop it later |
| 2. Dual-write, read old | Old | Old-only writers and dual-writers | Redeploy previous; rerun backfill later |
| 3. Backfill old → new | Old | Dual-writers | Stop the job; it is idempotent |
| 4. Verify parity | Old | Dual-writers | Nothing to undo; fix and re-backfill |
| 5. Switch reads to new | New | Dual-writers reading either column | Redeploy read-old version; both columns are current |
| 6. Stop writing old | New | New-only writers and phase 5 | Reverse backfill or forward fix |
| 7. Drop old column | New | New-only code | Restore from backup |
The new column must be nullable in phase 1 because old instances still insert rows without it. Dual-writing starts before the backfill so that rows changed during the copy are not left behind. The backfill closes the gap left by instances that ran before phase 2.
Phase 6 is the point of no easy return. Through phase 5, both columns hold the same data, so any earlier version can run again. Once writes to the old column stop, it goes stale with every update. Older code would then read outdated values. Rolling back means copying new → old first, or shipping a forward fix instead.
Each phase = one deploy. Finish the rollout before starting the next.
WRITES READS ROLLBACK
+------------------------------+
| 1 Add nullable new column | none old leave column
+------------------------------+
| 2 Dual-write, read old | old+new old redeploy previous
+------------------------------+
| 3 Backfill old -> new | old+new old stop job (idempotent)
+------------------------------+
| 4 Verify parity | old+new old fix, re-backfill
+==============================+ <== SOURCE OF TRUTH FLIPS: OLD -> NEW
| 5 Switch reads to new | old+new new redeploy read-old
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ <== POINT OF NO EASY RETURN
| 6 Stop writing old | new new REVERSE BACKFILL
+------------------------------+ or forward fix
| 7 Drop old column | new new restore from backup
+------------------------------+
Phases 1-5: both columns current -> any earlier version can run again
Phase 6+ : old column goes stale -> copy new -> old before rolling back
The seven expand-contract phases, showing which column each phase writes and reads, where the source of truth moves from old to new, and why rollback after phase 6 requires a reverse backfill or a forward fix.
How do you implement dual writes in a monolith?
The fix for both problems is to put every read and write of the column behind one piece of code. In a monolith, that is usually the model or the data access layer. Expose the field under its new name, and let that layer decide which physical column or columns to touch. Callers stop knowing the column names at all, so changing phases becomes a change in one file.
During the dual-write phase, write both columns in the same statement or transaction. If they are written separately, a crash between the two writes leaves them out of sync, and nothing will notice. Control the phase with a config flag that has a few explicit states: write old only, write both and read old, write both and read new, then write new only. Because the switch is a flag, you can move the source of truth without a deploy and move it back just as quickly.
| Concern | Application dual writes | Database trigger |
|---|---|---|
| Catches raw SQL, admin scripts, other services | No | Yes |
| Where the logic lives | Versioned application code | Inside the schema, easy to forget |
| Debugging | Visible in app logs and traces | Hidden side effects |
| Cleanup | Delete code behind the flag | Separate migration to drop the trigger |
Drift comes from any write that skips the model layer, such as ORM bulk updates that bypass hooks, hand-written SQL in reports or jobs, and one-off console fixes. Search the codebase for the old column name and remove every direct reference before relying on dual writes.
import os
import sqlite3
from enum import Enum
class Phase(Enum):
WRITE_OLD = "write_old" # before the new column is in use
DUAL_READ_OLD = "dual_read_old" # write both, old column is the source of truth
DUAL_READ_NEW = "dual_read_new" # write both, new column is the source of truth
WRITE_NEW = "write_new" # old column no longer touched, ready to drop
def current_phase() -> Phase:
# Read on every call so a runtime config change moves the source of truth
# without a deploy. Swap in your own config or flag lookup here.
return Phase(os.environ.get("USERS_DISPLAY_NAME_PHASE", "write_old"))
class UserRepository:
"""The only code that knows the physical names full_name and display_name."""
def __init__(self, conn: sqlite3.Connection):
self.conn = conn
def get_display_name(self, user_id: int):
phase = current_phase()
reads_old = phase in (Phase.WRITE_OLD, Phase.DUAL_READ_OLD)
column = "full_name" if reads_old else "display_name"
row = self.conn.execute(
f"SELECT {column} FROM users WHERE id = ?", (user_id,)
).fetchone()
return row[0] if row else None
def set_display_name(self, user_id: int, value: str) -> None:
phase = current_phase()
if phase is Phase.WRITE_OLD:
sql, params = "UPDATE users SET full_name = ? WHERE id = ?", (value, user_id)
elif phase is Phase.WRITE_NEW:
sql, params = "UPDATE users SET display_name = ? WHERE id = ?", (value, user_id)
else:
# One statement updates both columns, so a crash cannot leave them out of sync.
sql = "UPDATE users SET full_name = ?, display_name = ? WHERE id = ?"
params = (value, value, user_id)
with self.conn: # commits on success, rolls back on exception
self.conn.execute(sql, params)
A Python repository that hides both column names behind one field, writes both columns in a single statement inside a transaction, and picks the read and write columns from a four-state phase flag.
How do you backfill and verify a renamed column safely?
Once every write path updates both columns, existing rows still need their new column filled. Avoid doing this with a single UPDATE across the whole table. One statement runs as one transaction. It holds row locks until it finishes, keeps old row versions alive, and generates a burst of log that replicas must apply in order. On a large table, that causes blocked writes, growing replication lag, and a rollback as long as the update itself if anything fails.
Backfill in small, resumable batches
Walk the table by primary key, updating a bounded range such as a few thousand ids per statement. Commit each batch separately. Pause between batches, and back off when replica lag or lock waits rise. Record the last processed id so a crashed or paused job resumes where it stopped. Make each batch idempotent so rerunning a range is harmless.
Don't overwrite newer writes
Dual writes run while the backfill does. If a batch copies a value it read earlier, it can overwrite a fresher value that the application just wrote. Restrict each update to rows where the new column is still null. If you need a transformed value, compute it in the same statement from the old column, the same source the application uses, rather than from data read earlier.
Verify parity before switching reads
Run two counts: rows where the old and new values differ (using a null-safe comparison), and rows where the new column is null but the old one is not. Switch reads to the new column only after both counts stay at zero across repeated checks while dual writes are still active. A single zero reading can hide a write path that only runs occasionally.
-- PostgreSQL. Renaming customers.email_addr (old) to customers.email (new).
-- A driver script runs one batch per transaction, commits, sleeps, and repeats.
-- Progress table so a crashed or paused job resumes where it stopped.
CREATE TABLE IF NOT EXISTS backfill_progress (
job text PRIMARY KEY,
last_id bigint NOT NULL
);
INSERT INTO backfill_progress VALUES ('customers_email', 0)
ON CONFLICT (job) DO NOTHING;
-- One batch: a bounded primary-key range, committed on its own.
BEGIN;
UPDATE customers
SET email = email_addr -- derive from the old column in this statement
WHERE id > (SELECT last_id FROM backfill_progress WHERE job = 'customers_email')
AND id <= (SELECT last_id FROM backfill_progress WHERE job = 'customers_email') + 5000
AND email IS NULL -- never overwrite a value dual writes already set
AND email_addr IS NOT NULL; -- idempotent: rerunning a range changes nothing
UPDATE backfill_progress SET last_id = last_id + 5000 WHERE job = 'customers_email';
COMMIT;
-- Driver: stop when last_id >= (SELECT max(id) FROM customers).
-- Throttle check run by the driver between batches; back off if lag grows.
SELECT max(replay_lag) FROM pg_stat_replication;
-- Parity check 1: values differ (null-safe comparison).
SELECT count(*) AS mismatched
FROM customers
WHERE email IS DISTINCT FROM email_addr;
-- Parity check 2: new column still missing where old has data.
SELECT count(*) AS unfilled
FROM customers
WHERE email IS NULL AND email_addr IS NOT NULL;
-- Switch reads only after both stay at 0 across repeated runs with dual writes on.
A PostgreSQL batched, resumable backfill that only fills nulls from the old column, followed by the two parity queries that must stay at zero before reads move to the new column.
What locking, constraint and index issues should you check on your database?
The expand step is only cheap if each DDL statement is cheap, and the cost depends on the engine and version you run. Adding a nullable column with no default is usually a metadata change. Adding a default, a NOT NULL constraint or an index can take a long exclusive lock or rewrite the whole table. Depending on the engine, that rewrite either blocks writes or holds a lock that queues every later query behind it.
| Operation | Typical risk | What to verify |
|---|---|---|
| Add nullable column, no default | Low, often metadata-only | Lock acquisition still waits for long-running transactions |
| Add column with default | May rewrite the table | Whether your version stores the default as metadata |
Add NOT NULL |
Full scan to validate existing rows | Whether you can add the constraint unvalidated and validate it later |
| Create index | Can block writes for the whole build | Whether an online or concurrent build option exists, and how it behaves on failure |
| Unique constraint or foreign key | Validation scan plus locks on referenced tables | Whether you can build the backing index first and attach the constraint to it |
Constraints and indexes do not follow a rename. You must rebuild every unique constraint, foreign key and index on the old column against the new one before you switch reads. Otherwise queries silently lose their index, or duplicates get in. Check the exact non-blocking options in your database documentation; do not assume they behave alike across engines.
Plan for the temporary cost as well. Until contract, every row stores the value twice, each duplicated index adds work on every write, and indexes grow. On large or write-heavy tables this shows up as extra disk use, replication lag and slower writes. Rehearse every statement against a production-sized copy and record lock waits and run times before you touch production.
How do you make the contract step safe and avoid abandoned migrations?
Dropping the old column is the one step you cannot undo, so ship it as a separate change and require evidence before it merges.
Find every consumer
Code search covers your application repository. Many consumers of the table live elsewhere:
- ORM schema caches that load column metadata at boot
- Raw SQL strings assembled at runtime
- Views and stored procedures
- Reporting queries
- ETL jobs and data pipelines
- BI dashboards
- Read replicas that other teams query
- Other services that share the database
Views and procedures are stored in the database catalog, not in your repository. Search the catalog's object definitions for the old column name.
Prove the column is unused
Use three independent signals:
- Search for the column name across every repository you can reach, including configuration and dashboard definitions.
- Sample query logs or statement statistics for references to the column. Sample over a window long enough to catch monthly and quarterly jobs.
- Add temporary instrumentation to the old accessor so any remaining read or write logs a warning with a stack trace.
Each signal misses things the others catch, so silence from one of them is not proof.
Wait at least one release cycle after the read switch. If you roll back to the previous build, that build still reads the old column. When no supported rollback target needs it, drop the column and add the final constraints, such as NOT NULL on the new column.
Prevent abandonment
Migrations tend to stall after the read switch because nothing visibly breaks. When the expand step ships, assign a named owner and open a tracked cleanup ticket with a due date. Link the ticket from the migration file.
-- PostgreSQL. Old column: customers.email_address; new column: customers.email.
-- Steps 1-2 are evidence for the contract PR. Step 3 is the drop, shipped separately.
-- 1. Views and functions live in the catalog, not the repo. Search their definitions.
SELECT schemaname, viewname
FROM pg_views
WHERE definition ILIKE '%email_address%';
SELECT n.nspname, p.proname
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND p.prosrc ILIKE '%email_address%';
-- 2. Statement statistics (requires the pg_stat_statements extension).
-- This covers only the window since the last stats reset. Confirm that window
-- includes month-end and quarter-end jobs before treating zero rows as evidence.
SELECT calls, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ILIKE '%email_address%'
ORDER BY calls DESC;
-- 3. Contract step, after a full release cycle with no remaining readers or writers.
-- Add NOT NULL without a long exclusive lock: create the check as NOT VALID,
-- validate it (this takes a weaker lock), then SET NOT NULL. On PostgreSQL 12+
-- SET NOT NULL uses the validated check and skips the full table scan.
SET lock_timeout = '5s';
ALTER TABLE customers ADD CONSTRAINT customers_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_email_not_null;
BEGIN;
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
ALTER TABLE customers DROP CONSTRAINT customers_email_not_null;
ALTER TABLE customers DROP COLUMN email_address; -- irreversible
COMMIT;
PostgreSQL queries that search the catalog and statement statistics for references to the old column, then a contract step that adds NOT NULL on the new column without a long table lock and drops the old column.
How can expand contract migrations save engineering time?
Expand-contract adds deploys, but it removes the work that usually costs the most: coordinating outages, firefighting failed rollouts and rebuilding a migration plan for every change. The savings come from a few specific places.
- No outage coordination. A maintenance window needs a scheduled slot, notices to users and dependent teams, someone on call during the window, and a plan for when it overruns. Expand-contract steps ship during normal working hours through the regular pipeline.
- Fewer incidents from schema mismatches. A direct rename that breaks a rolling deploy produces errors, an investigation and often an emergency rollback that hits the same mismatch in reverse. Because every phase stays compatible with the previous release, a bad deploy can be rolled back like any other code change.
- Cheap, boring rollbacks. Through phase 5, rolling back is a redeploy or a flag change. Engineers do not have to write a corrective migration under pressure.
- Small, reviewable changes. Each phase is a narrow diff: one DDL statement, one repository change, one backfill job or one drop. Reviewers can check each one quickly, and a problem points to a single step.
- Reusable tooling. The phase flag, the single data access layer, the batched backfill driver, the parity queries and the consumer-search queries work the same way for the next rename, type change or column split. After the first migration, most of the work is filling in names.
- Less after-hours work. Because no step requires the whole fleet to stop, nothing has to run at night or on weekends.
As an illustrative estimate, take these assumed inputs: a maintenance window costs 2 engineers 4 hours of planning plus 2 hours each during the window, or 12 engineer-hours; a failed direct rename on a rolling deploy costs about 8 engineer-hours of incident response and cleanup, and you assume it happens on one in four attempts, or 2 engineer-hours on average. Expand-contract with existing tooling is assumed to take 6 engineer-hours spread across its deploys and reviews. Under those assumptions, it is cheaper than the maintenance window and comparable to an unplanned direct rename once incident risk is counted. It also moves the work into normal hours. Replace these inputs with your own team's figures. On a first migration without reusable tooling, the estimate is higher, and the savings only appear on later changes.
The time savings depend on finishing the migration. A half-done migration with dual writes still in place keeps costing review and debugging time, so the contract step and its cleanup ticket are part of what makes the pattern pay off.
When should you avoid an expand contract migration, and what are the alternatives?
Expand-contract is not free. One rename becomes three or four deploys spread over days or weeks, and each carries dual-path code that someone has to review, test and later delete. For many columns that cost is larger than the risk it removes.
| Option | Fits when | Trade-off |
|---|---|---|
| Short maintenance window | Small or low-traffic table, and users tolerate a brief pause | Outage coordination, but only one deploy |
| Direct rename | Every deploy already stops all old instances before new ones start | Breaks silently if you later move to rolling deploys |
| Database view or ORM column alias | You want the new name in code and the physical column can stay | Extra mapping layer. Writes through a view depend on your database's updatable-view support |
| Keep the old name | The name is only mildly misleading | A comment or a data dictionary entry |
The pattern earns its cost in three places. You do not have to negotiate a downtime slot with other teams. Old and new code both work at every step, so a schema mismatch never forces an emergency rollback. And once the team has run it once, the same steps cover type changes, column splits and table moves. Each later change mostly reuses that work instead of designing a migration from scratch.
Use expand-contract when most of these are true:
- Old and new application versions overlap during deploys.
- The table is large or busy enough that a locking change would be noticed.
- Many modules or background jobs reference the column.
- The team expects to make similar changes again.
If none of these hold, take the simpler option.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to expand contract migration, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.