What is an expand contract migration, and why does a one-step rename break during a rolling deploy?
An expand contract migration splits one breaking schema change into several small changes that are each backward compatible. First you expand: add the new column, table, or index next to the old one. Then you move the application and the data over. Finally you contract: drop the old structure once nothing reads or writes it.
A one-step change fails because of how deploys roll out. In a rolling or multi-instance deploy, some instances run version N while others run N+1, and both use the same database. This overlap can last minutes. If a deploy stalls or is rolled back, it lasts longer. Background workers and cron jobs often keep running old code even after the web tier has moved on.
Take a rename of users.fullname to users.display_name in a single migration. When the migration commits, every N instance still running starts failing on queries that reference fullname. If you run the migration after the deploy instead, the N+1 instances fail on display_name until it does. A type change has the same problem. One version gets values it cannot parse, or writes values the new column type rejects.
The core rule: every schema state must work with both N and N+1. If a state cannot meet that rule, add an intermediate step.
This is for backend and platform engineers working on monoliths with busy tables and rolling deploys. They need zero downtime migrations because a maintenance window is either unavailable or too expensive.
How does an expand contract migration work, phase by phase?
The example splits users.full_name into first_name and last_name. Each phase ships as its own deploy, and the next one starts only after the previous one has fully rolled out and looked healthy. During a rolling deploy, version N and N+1 serve traffic together, so each step only has to be compatible with its immediate neighbour. That rule is what keeps every step reversible until the last one.
| Phase | Versions live | Reads | Writes | Rollback |
|---|---|---|---|---|
| 1. Expand schema | v1 | full_name |
full_name |
Drop the new nullable columns |
| 2. Dual-write | v1, v2 | full_name |
all three | Redeploy v1. The new columns go stale, which is harmless |
| 3. Backfill | v2 | full_name |
all three, plus a batch job | Stop the job. It is idempotent, so rerun it later |
| 4. Switch reads | v2, v3 | new columns (v3) | all three | Redeploy v2, because full_name is still current |
| 5. Stop old writes | v3, v4 | new columns | new columns only | You can go back to v3. Going back to v2 means re-deriving full_name first |
| 6. Contract schema | v4 | new columns | new columns | Not reversible without restoring data |
Phase 5 needs one precondition: full_name must be nullable or have a default before v4 stops writing it. Otherwise v4's inserts fail.
Phase 4 is where you compare the data. Before switching reads, sample rows and check that the derived values match what the old column implies. After phase 5, full_name drifts out of date with every write, so the rollback window narrows with each phase.
Between phases you wait for deploys to finish, backfills to complete, and old app versions or long-running workers to drain. Because of that, the whole sequence often spans days or weeks, not a single afternoon.
users.full_name -> first_name + last_name (each box = one separate deploy)
+---------------------+ +---------------------+ +---------------------+
| 1. EXPAND SCHEMA |--->| 2. DUAL-WRITE |--->| 3. BACKFILL |
| live: v1 | | live: v1 + v2 | | live: v2 |
| R: full_name | | R: full_name | | R: full_name |
| W: full_name | | W: all three cols | | W: all three cols |
| DDL: add first_name,| | new cols stale if | | + batch job, small |
| last_name (NULL) | | rolled back: ok | | idempotent chunks |
| undo: drop new cols | | undo: redeploy v1 | | undo: stop, rerun |
| | | | | |
+---------------------+ +---------------------+ +---------------------+
|
+-----------------------------------------------------+
v
+---------------------+ +---------------------+ +---------------------+
| 4. SWITCH READS |--->| 5. STOP OLD WRITES |--->| 6. CONTRACT SCHEMA |
| live: v2 + v3 | | live: v3 + v4 | | live: v4 |
| R: first/last (v3) | | R: first/last | | R: first/last |
| W: all three cols | | W: first/last only | | W: first/last |
| check: sample rows | | pre: full_name is | | DDL: drop full_name |
| match full_name | | nullable/default | | |
| undo: redeploy v2 | | undo: v3 ok, v2 only| | undo: NONE without |
| | | after re-derive | | data restore |
+---------------------+ +---------------------+ +---------------------+
R = reads, W = writes. Each ---> waits for rollout, backfill and drain.
The six expand-contract deploys for splitting users.full_name, showing which app versions are live, which columns each phase reads and writes, and how the rollback options narrow until the contract step, which cannot be undone without restoring data.
Why does Postgres lock behavior matter more than DDL speed in an online schema change?
Each phase looks cheap on paper, but the risk sits in the lock each statement needs, not in how long it runs. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECTs. Postgres grants locks in queue order, so when your DDL waits behind a long-running transaction, every query that arrives after it waits too. A column add that would finish in milliseconds can stall all traffic to the table for as long as that one transaction stays open. The usual culprits are a slow report, a batch job, or a session left idle in transaction.
Fail fast, then retry
Set lock_timeout in the migration session to a short value, such as a few seconds, so a blocked DDL gives up instead of building a queue. Set statement_timeout as a backstop. Wrap the statement in a retry loop with backoff, so a failed attempt costs a brief blip rather than an outage. Leave the database defaults alone, because these limits should apply only to the migration.
Postgres add column specifics
Adding a nullable column with no default is a metadata-only change. Recent versions can also add a column with a constant, non-volatile default without rewriting the table. A volatile default such as random() or clock_timestamp() still forces a full rewrite under the exclusive lock. Behavior differs across releases, so check the documentation for your version before assuming either case.
If long transactions are always present and retries never succeed, more retries will not fix it. Shorten the application's transactions, or schedule the change for a low-traffic window.
#!/usr/bin/env bash
# Add a column under a short lock_timeout, retrying with backoff.
# Only lock-timeout failures are retried. Any other error aborts.
set -uo pipefail
# Connection comes from the usual PG* env vars (PGHOST, PGDATABASE, ...).
# PGOPTIONS applies these limits to this session only; database defaults stay unchanged.
export PGOPTIONS='-c lock_timeout=3s -c statement_timeout=30s'
# Nullable with no default: a metadata-only change, so it only needs the lock briefly.
DDL='ALTER TABLE orders ADD COLUMN shipping_note text;'
max_attempts=10
delay=2
for attempt in $(seq 1 "$max_attempts"); do
if err=$(psql -X -v ON_ERROR_STOP=1 -c "$DDL" 2>&1 >/dev/null); then
echo "applied on attempt $attempt"
exit 0
fi
# A lock timeout means a long transaction held the table. Back off and retry.
if grep -q 'lock timeout' <<<"$err"; then
echo "attempt $attempt: lock not acquired, retrying in ${delay}s" >&2
sleep $((delay + RANDOM % 3)) # jitter avoids retrying in lockstep
delay=$((delay * 2 > 60 ? 60 : delay * 2))
continue
fi
echo "non-retryable error: $err" >&2
exit 1
done
# Repeated failures point to long-running app transactions.
# Shorten them, or run the change in a low-traffic window.
echo "gave up after $max_attempts attempts" >&2
exit 1
A bash wrapper sets lock_timeout and statement_timeout for the migration session only and retries the column add with capped, jittered backoff, so a blocked ALTER TABLE gives up quickly instead of queuing every query behind it.
How do you add indexes and constraints to a large table without blocking writes?
The fix for index and constraint changes is to split one blocking statement into a cheap catalog change and a slow verification step that runs under a weaker lock.
CREATE INDEX CONCURRENTLY builds the index while inserts, updates and deletes continue. It scans the table twice and waits for in-flight transactions between phases. It cannot run inside a transaction block, so your migration tool must not wrap it in BEGIN/COMMIT. Most tools need a per-migration opt-out for this. If the build fails, for example on a uniqueness violation or a cancelled statement, Postgres leaves an INVALID index behind. Queries do not use it, but writes still maintain it. Drop it with DROP INDEX CONCURRENTLY and retry. Do not assume a rerun of IF NOT EXISTS will repair it.
For constraints, add the CHECK or foreign key with NOT VALID. This only checks new and changed rows, so it is a brief catalog change. Then run VALIDATE CONSTRAINT as a separate migration. Validation scans existing rows under a lock that permits writes.
To make a column NOT NULL, add and validate CHECK (col IS NOT NULL) first. Postgres can then use that proof and skip the full scan that SET NOT NULL would otherwise run under an exclusive lock. After that, drop the redundant CHECK.
| Blocking form | Online equivalent | Lock during the slow part | Total work | Failure mode |
|---|---|---|---|---|
CREATE INDEX |
CREATE INDEX CONCURRENTLY |
Writes allowed | Two scans plus waits | INVALID index; drop and retry |
ADD CONSTRAINT (CHECK/FK) |
NOT VALID, then VALIDATE CONSTRAINT |
Writes allowed | Scan deferred, same cost | Stays NOT VALID; fix data, rerun |
SET NOT NULL |
Validated CHECK, then SET NOT NULL |
Brief exclusive, no scan | Extra steps | Same as CHECK validation |
Each online form takes longer and does more total work than its blocking counterpart. You accept that cost so that writes are never blocked.
-- Each numbered step is its own migration, run without a wrapping BEGIN/COMMIT.
-- 1. Build the index online. Fails if run inside a transaction block.
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
-- If the build failed, find the INVALID leftover, drop it, then retry step 1.
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;
-- 2. Add constraints as NOT VALID: a brief catalog change, existing rows not scanned.
-- A short lock_timeout keeps the brief lock from queueing behind long transactions.
SET lock_timeout = '5s';
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders
ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;
-- 3. Validate separately. This scans existing rows, but writes to orders continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
-- 4. SET NOT NULL can use the validated CHECK as proof (PostgreSQL 12+),
-- so the exclusive lock is held only briefly and no full scan runs.
SET lock_timeout = '5s';
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
-- 5. The CHECK is now redundant.
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;
A sequence of separate migrations that builds an index concurrently, cleans up an INVALID index after a failed build, adds constraints as NOT VALID and validates them later, and uses a validated CHECK so SET NOT NULL skips its full-table scan.
How can you implement a backfill and dual writes without hurting production?
The backfill copies existing values into the new column while live traffic keeps writing. Run it as a loop over primary key ranges: update rows whose id falls between a lower and upper bound and whose new column is still NULL, commit, persist the upper bound, sleep, then advance. Short transactions release row locks quickly and let vacuum reclaim old row versions. The IS NULL filter makes reruns harmless, and the stored bound lets you resume after a crash or a deliberate kill.
While it runs, watch replication lag, dead tuples on the table, autovacuum activity, and WAL volume. Every updated row creates a new tuple version and WAL. If lag climbs or dead tuples accumulate faster than autovacuum clears them, shrink the batch or lengthen the pause. Throttling lowers impact but stretches the window, and the dual-write period stays open until the backfill finishes.
Something must keep old and new columns in sync during that period:
| Approach | Covers | Costs |
|---|---|---|
| Application dual writes | Code paths in the monolith | Misses manual SQL, other services, bulk jobs |
| Database trigger | Every write, from any client | Per-row overhead on a hot table; logic hidden from application code |
Before switching reads, verify that the columns agree. For each primary key range, count rows where old_col IS DISTINCT FROM new_col. IS DISTINCT FROM treats two NULLs as equal, so missing values are compared correctly. Scan every chunk, or a random sample of ranges if a full pass is too expensive. Switch reads only after the count is zero.
-- Progress row lets the backfill resume after a crash or deliberate kill.
CREATE TABLE IF NOT EXISTS backfill_progress (job text PRIMARY KEY, last_id bigint NOT NULL);
INSERT INTO backfill_progress VALUES ('orders_new_col', 0) ON CONFLICT DO NOTHING;
-- Run in autocommit mode (not inside BEGIN) so COMMIT in a DO block is allowed (PG 11+).
DO $$
DECLARE
batch constant bigint := 5000; -- shrink if replication lag or dead tuples climb
lo bigint;
max_id bigint;
BEGIN
SELECT last_id INTO lo FROM backfill_progress WHERE job = 'orders_new_col';
-- Rows inserted after this point are covered by dual writes, not the backfill.
SELECT max(id) INTO max_id FROM orders;
WHILE lo < max_id LOOP
-- IS NULL filter makes reruns harmless and skips rows dual writes already set.
UPDATE orders SET new_col = old_col
WHERE id > lo AND id <= lo + batch AND new_col IS NULL;
UPDATE backfill_progress SET last_id = lo + batch WHERE job = 'orders_new_col';
COMMIT; -- short transaction: releases row locks, lets vacuum keep up
lo := lo + batch;
PERFORM pg_sleep(0.2); -- throttle; longer pauses lower impact but stretch the window
END LOOP;
END $$;
-- Verify one chunk; repeat per range. IS DISTINCT FROM treats NULL = NULL as equal.
SELECT count(*) AS mismatches
FROM orders
WHERE id > 0 AND id <= 100000
AND old_col IS DISTINCT FROM new_col;
-- Cheaper sampled check: roughly 1% of table pages.
SELECT count(*) AS mismatches
FROM orders TABLESAMPLE SYSTEM (1)
WHERE old_col IS DISTINCT FROM new_col;
A resumable, idempotent backfill that commits and sleeps between primary-key batches, followed by chunked and sampled queries that count rows where the old and new columns disagree.
How do you make the contract phase safe, and how do you roll back each phase?
Old code must stop referencing the column before you drop it. Grep is a starting point, not proof. Check pg_stat_statements or your query logs for statements that still name the old column over a full business cycle, including month-end jobs. Then check the places that never show up in a code search:
- ORMs that select every column. Many ORMs cache the column list at boot and generate
SELECTandINSERTstatements from it. A process started before the drop will fail on its next query. Tell the ORM to ignore the column (in Rails,ignored_columns), deploy that, and confirm every process has restarted before the drop. - Background jobs and long-running workers that may still run an older build.
- Reporting, analytics, and ad hoc dashboards that query the table directly.
- Replica consumers, such as logical replication subscribers, CDC pipelines, and ETL jobs, whose schemas break when a column disappears.
Run the drop with SET lock_timeout set to a few seconds, then ALTER TABLE ... DROP COLUMN. The drop only changes catalog metadata, but it needs an ACCESS EXCLUSIVE lock. If a long transaction is holding the table, the drop waits, and every query that arrives behind it waits too. With a timeout, the drop fails quickly and you retry it later.
Rollback by phase
- Expand: drop the new column or index.
- Dual-write: disable it with a flag. The old column is still authoritative.
- Backfill: stop it. Partial data in the new column does no harm.
- Read switch: point reads back at the old column. This works because dual writes kept it current.
- Contract: effectively irreversible. Once writes to the old column stop, it falls behind, and restoring it means another full backfill.
When should you avoid expand and contract, and what are the alternatives?
Expand and contract has a real cost. One logical change, such as renaming a column, becomes three or four deploys spread over days. Compatibility code like dual writes, fallback reads, and sync triggers has to be tracked and later deleted, or it hardens into permanent cruft. Every phase also needs its own review, and reviewers must reason about which code versions run against which schema at the same time.
| Situation | Table size | Write rate | Deploy model | Downtime tolerance | Reasonable choice |
|---|---|---|---|---|---|
| Internal tool, scheduled outages accepted | Any | Any | Any | Minutes acceptable | Short maintenance window |
| Small lookup or config table | Small | Low | Rolling | Brief lock acceptable | Single blocking migration |
| New optional field, nothing reads it yet | Any | Any | Any | None | Nullable column add only |
| Core table, continuous traffic | Large | High | Rolling, many contributors | None | Full expand and contract |
The nullable-column case often needs no ceremony. Old code ignores the column and new code tolerates nulls, so the add is already backward compatible. Lock behavior, defaults, and which operations rewrite the table differ across Postgres versions and across databases. Check your engine's documentation before you decide a step is safe.
On busy tables, the extra deploys usually cost less engineering time than the alternative. A single blocking migration that queues writes can turn into an incident that needs a rollback under pressure and a postmortem. A big-bang release forces several teams to coordinate one timed change. Expand and contract replaces that risk with small, routine deploys, each of which can be reverted on its own.
Checklist for running expand contract migrations in a monolith with many contributors
Those small deploys only stay routine if the team treats them as a process rather than a skill a few people carry in their heads. Use this list in review templates and migration tooling.
- One schema change per migration file. If a file adds a column and builds an index, a failure halfway leaves the schema in a state nobody wrote down. Single-purpose files make retries and rollbacks predictable.
- Decouple migrations from app deploys where possible. Running migrations as a separate pipeline step means a slow lock wait doesn't stall a release, and a bad release doesn't tempt anyone to roll back the schema with it.
- Set
lock_timeoutby default in migration tooling. A statement waiting on a lock queues every query behind it. A short timeout turns a potential outage into a failed migration you can retry. Engineers should have to opt out explicitly, not remember to opt in. - Flag statements that must run outside a transaction.
CREATE INDEX CONCURRENTLYfails inside a transaction block. Have tooling detect these statements and require an explicit marker, so nobody wraps them by accident. - Track each in-flight migration's phase. Record the phase, an owner, and a cleanup date for dual-write code. Without a cleanup date, expand-only migrations pile up and the contract phase never happens.
- Write a rollback plan per phase. Rolling back the expand phase is cheap. Rolling back after contract may not be possible at all. Reviewers should see that difference spelled out.
- Require pre-contract verification in review. Before anything is dropped, show evidence that no code path reads the old column and that the backfill is complete.
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.