Expand–contract: changing a schema while several app versions run at once Intermediate

The problem

The orders table has a status column, and the new app version wants to rename it to payment_status. Run RENAME COLUMN before the deploy finishes: old instances query status and fail immediately. Deploy the new app first: it queries payment_status before the schema has it.

A rolling deploy always has a window where two app versions run at once against one database, and a rollback brings the old version back.

Expand–contract solves this with several small steps, each compatible with both the old and the new app:

• Expand: add the new capability without breaking the old app.
• Migrate: move writes, data, then the read path.
• Contract: remove the old part once you are sure nobody uses it.

This is not a “zero downtime” magic spell: DDL still takes locks, backfills still cost resources, and the wrong order still causes errors. Its value is making the safety conditions of each step explicit.

Map of the 5 phases

Phase	Schema	Old app	New app	Safe to roll back?
1. Expand	add payment_status (nullable)	writes status	not deployed yet	Yes: an extra column is harmless
2. Dual-write	same as above	still running, writes status	writes both columns, reads status	Yes, but rows the old app writes meanwhile will lack payment_status → backfill again
3. Backfill	same as above	must be fully gone	same as phase 2	Yes
4. Switch reads	same as above	no	writes both, reads payment_status	Yes, if still dual-writing and mismatches are checked before moving forward again
5. Contract	stop writing status, then DROP COLUMN	no	uses only the new column	No: the column and data must be restored

Three decisive milestones, in order: no old writers left → zero mismatches → rollback window over. Don’t move on until the previous milestone is met.

Five phases of a column rename, matching the table above

Enlarge diagram

Old app → ordersstatus

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

Phase 1 — Expand: add a nullable payment_status. The old app does not know about the new column, so it keeps running normally.

How each phase works

Starting schema:

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  status text NOT NULL
);

Phase 1 — Expand

ALTER TABLE orders ADD COLUMN payment_status text;

Adding a nullable column does not rewrite the table. But ALTER TABLE takes an ACCESS EXCLUSIVE lock by default (the docs: “unless explicitly noted”), and if it has to wait for a long transaction, every query behind it queues up waiting for it. So always set lock_timeout and retry:

for i in 1 2 3 4 5; do
  psql -v ON_ERROR_STOP=1 \
    -c "SET lock_timeout = '3s'; ALTER TABLE orders ADD COLUMN payment_status text;" \
    && break
  sleep $((i * 2))   # back off gradually, let the blocker finish
done

lock_timeout only counts time spent waiting for the lock, unlike statement_timeout. Don’t set it in postgresql.conf because it affects every session; set it per migration session. If all retries fail: stop, find the blocker in pg_stat_activity/pg_locks, and don’t keep raising the timeout.

Phase 2 — Dual-write

The new app writes both columns in the same statement and still reads status:

UPDATE orders SET status = $1, payment_status = $1 WHERE id = $2;

Keep the old read path because the old app is still running and only writes status. If the new app reads payment_status too early, it sees stale data.

Forgetting a writer (worker, cron, admin tool) is the most common source of drift. There are two ways to keep the columns in sync:

Approach	Pros	Cons
Dual-write in the app	Explicit in code, easy to test	You must inventory every writer
DB trigger (BEFORE INSERT OR UPDATE setting NEW.payment_status := NEW.status)	Catches old writers too, even after a rollback	Logic hidden in the DB, extra cost per write, must remember to drop it in phase 5

If the system has many writers that are hard to inventory, a trigger is safer; phase 3 then does not need to wait for old writers to be gone.

Phase 3 — Backfill

Only run it when there are no old writers left (or a trigger is in place). Batch by primary key to avoid long transactions, large WAL and replication lag:

UPDATE orders
SET payment_status = status
WHERE id >= 1000 AND id < 2000
  AND payment_status IS NULL;

Each UPDATE creates a new row version, so the table grows; see VACUUM and bloat and throttle the rate by replication lag. Finally, check:

SELECT count(*) FROM orders WHERE payment_status IS DISTINCT FROM status;

The result must be 0. IS DISTINCT FROM handles NULL correctly; <> misses NULL rows.

Phase 4 — Switch reads

Deploy the version that reads payment_status, still writing both columns. Watch the mismatch metric, query errors, latency and replication lag; with read replicas, account for replica lag before concluding that every reader sees the new data.

Phase 5 — Contract

1. Release a version that stops writing status (and drop the trigger if any). After this step, rolling back to the old app is no longer safe.
2. Inventory readers outside the main app: BI reports, CDC connectors, ops scripts, scheduled jobs, downstreams reading replicas.
3. One release before the drop, make the ORM ignore the column (Rails’s ignored_columns, or drop it from the entity/explicit SELECT). ORMs often cache the column list; SELECT * and cached prepared statements can fail (with errors like “cached plan must not change result type”) when the column disappears.
4. DROP COLUMN in its own migration, still under lock_timeout. It is fast because it only hides the column, but it does not free space immediately; the space is reclaimed gradually as rows are rewritten.

NOT NULL and indexes without locking the table

If the contract requires payment_status NOT NULL, don’t run SET NOT NULL directly on a large table: by default it scans the whole table while holding a strong lock. The safe path:

-- 1. Add the constraint WITHOUT a scan (new rows are still checked)
ALTER TABLE orders
  ADD CONSTRAINT orders_ps_not_null CHECK (payment_status IS NOT NULL) NOT VALID;

-- 2. Scans the table but only needs SHARE UPDATE EXCLUSIVE, does not block reads/writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_ps_not_null;

-- 3. A valid CHECK proves there are no NULLs, so PostgreSQL skips the scan
ALTER TABLE orders ALTER COLUMN payment_status SET NOT NULL;

-- 4. Clean up the temporary constraint
ALTER TABLE orders DROP CONSTRAINT orders_ps_not_null;

Step 1 still needs a brief lock, so set lock_timeout as in phase 1. Skipping the scan when a valid CHECK exists is documented under ALTER TABLE; this optimization appeared in PostgreSQL 12.

Similarly, if the new column needs an index, use CREATE INDEX CONCURRENTLY so writes are not blocked:

CREATE INDEX CONCURRENTLY orders_payment_status_idx ON orders (payment_status);

Three caveats from the docs:

• It cannot run inside a transaction block.
• The build takes longer because of two scans and waiting for old transactions.
• If it fails (deadlock, unique violation), it leaves an INVALID index that still costs on writes and must be DROPped and rebuilt. Check with \d orders.

Rollback: watch out for drift

After phase 2, rolling back to the old app brings back a writer that only writes status. The rows it writes have an old or NULL payment_status, so the new column drifts.

• Before redeploying the new version (or switching reads), rerun the mismatch check and backfill the drifted rows.
• If you have to roll back during phase 4 (reading the new column), the old version reads status, which was dual-written, so it stays correct; drift only appears for rows written after the rollback.
• A sync trigger eliminates this problem almost entirely.

When to use / not to use

Use expand–contract when	Not needed when
Rolling deploys or canaries: several app versions run at once	Small tables, a short deliberate maintenance window is acceptable
Renaming/retyping columns, splitting tables, adding constraints on large tables	Only adding a new nullable column the old app doesn’t need to know about (one expand phase is enough)
You need to roll back the app without rolling back the schema	Single-instance environments, stop–start deploys

Real-world example

Separating order status from payment status

The payments team uses orders.status for two meanings at once. The table has about ten million rows; the API, refund worker and support tool all write to it.

1. Inventory the 3 writers, add payment_status under lock_timeout = 3s.
2. Release a dual-write version that still reads status, confirm old processes have left the cluster.
3. Backfill by id range at 10,000 rows/batch, pausing when replication lag rises; check mismatches = 0.
4. Release a version that reads payment_status, still writing both columns.
5. After the rollback window, stop writing status, drop the column from the ORM in the next release, and DROP COLUMN only in the final release.

The care lies in verifying each milestone, not in the number of migration files. (The numbers in this scenario are illustrative.)

Simulate the state of one row (an in-memory model that does not touch PostgreSQL, so it proves nothing about DDL or locks):

npx --prefix examples ts-node examples/src/expand-contract/rename-column-model.ts

Try adding an oldWriter that only modifies status after dual-write: if the new reader reads paymentStatus too early, it sees the old value. That is exactly the race the phase order must avoid.

Trade-offs

Benefits

• Keeps old and new apps compatible during rolling deploys or rollbacks.
• Splits a large migration into verifiable milestones.
• Backfills have bounded load and observable progress.

Costs

• Dual-write adds code paths and a drift risk if a writer is missed.
• Temporarily costs extra storage, WAL, IO and operational time (several releases instead of one).
• Does not remove DDL locks, races with writers, or the cost of rolling back after the contract.

Common mistakes

Common pitfall

The new app reads the new column while old writers still exist. Old writers only write the old column, so the new column is stale. With mixed versions, read from the old source; switch only when old writers are gone and mismatches are zero.

Common pitfall

Backfilling the whole table in one transaction. Rows stay locked for a long time, WAL and replication lag grow, and rollback is expensive. Run in batches with checkpoints.

Common pitfall

DDL without lock_timeout. One long open transaction makes ALTER TABLE wait, and every query after it queues behind it: a whole-table incident caused by a “lightweight” migration.

Common pitfall

Dropping the column right after deploying the new version. ORM column caches, old workers, CDC jobs or a rollback may still need it. The contract is a separate release, well after phase 4.

🎮 Try it

• Zero-downtime column rename: put the 6 expand–contract steps in order.
• Online index creation: CREATE INDEX CONCURRENTLY on an 80-million-row table.

Production checklist

1. Every DDL statement runs with SET lock_timeout in the migration session and retries with backoff.
2. Every writer and reader (including BI, CDC, cron) has been inventoried, or a sync trigger is used.
3. Backfills run in batches, throttled by replication lag, with a progress metric.
4. Mismatches are zero before switching reads; rerun the check after every rollback.
5. NOT NULL via NOT VALID → VALIDATE → SET NOT NULL; indexes via CONCURRENTLY.
6. The ORM has ignored the old column for at least one release before DROP COLUMN.
7. The contract is a separate release, after the rollback window ends.
8. Rehearse on a copy with realistic size and long transactions; also see graceful shutdown so rolling deploys don’t cut requests midway.

Summary

Key takeaways

• Expand adds compatible schema; migrate moves writers, data and readers; contract removes the old parts last.
• Mixed versions: the new app dual-writes but still reads the old column; switch reads only after old writers are gone and mismatches are zero.
• “No downtime” does not mean “no locks”: always use lock_timeout, NOT VALID+VALIDATE, CONCURRENTLY.
• Rolling back to the old app makes the new column drift; check and backfill again before moving forward.

Self-check:

1. Why not read payment_status as soon as the column appears?
2. Why can an ALTER TABLE waiting for a lock block even queries unrelated to it?
3. After rolling back to the old app, what must you do before switching reads again?
4. Why drop the column from the ORM one release before DROP COLUMN?

References

• ALTER TABLE — PostgreSQL 17
• CREATE INDEX — PostgreSQL 17
• lock_timeout — PostgreSQL 17
• Avoiding downtime in migrations — GitLab