Expand and Contract Migration: Zero-Downtime Schema Changes in a Monolith

Why a naive column rename breaks a zero downtime migration

An expand and contract migration, also called parallel change, splits one breaking schema change into a series of backward-compatible steps. First you expand the schema by adding the new structure next to the old one. Then you move readers and writers over. Only after that do you contract by removing the old structure. It exists because in most production systems the database and the application code do not change at the same instant.

Consider a rolling deploy of a monolith that renames users.fullname to users.display_name. The migration runs, and new instances start up expecting display_name. Old instances keep serving requests until the rollout replaces them, and they still select and update fullname, which no longer exists. Every request they handle that touches that column fails. The failures last for the whole overlap window, whether that is minutes on a large fleet or one long-lived connection pool on a single node.

Rollback fails the same way in reverse. If you redeploy the previous build, it queries fullname against a schema that only has display_name. The usual escape hatch is now the thing that's broken.

This leads to the core rule: every deploy must work with both the schema before it and the schema after it. The schema may only move in a direction the code that is currently running can tolerate.

This guide is for teams running rolling or blue-green deploys of a monolith against a shared database. In that setup, two application versions talking to one schema is the normal state during every release, not an edge case.

Copy                         +---------------------+
                         |    Load balancer    |
                         +----------+----------+
                                    |
                      +-------------+-------------+
                      |                           |
                      v                           v
          +------------------------+  +------------------------+
          | Old instance (v1)      |  | New instance (v2)      |
          | SELECT fullname ...    |  | SELECT display_name ...|
          +------------------------+  +------------------------+
                      |                           |
  ERROR: no fullname  |                           |  OK
                      v                           v
          +----------------------------------------------------+
          |  Shared database (one schema for both versions)    |
          |  users: id | display_name | email                  |
          |  fullname was renamed; it no longer exists         |
          +----------------------------------------------------+

  Rollback to v1 sends every request down the left, failing path.

During a rolling deploy, old and new instances share one schema, so a single rename migration breaks every v1 request for the whole overlap window and again on rollback.

How it works: a column rename as six ordered deploys

Each step below is its own deploy, and each must run safely alongside the version before it. The example renames users.username to users.handle.

1. Add handle as a nullable column. Running code ignores it. Making it nullable avoids a table-wide default write and keeps old inserts valid.
2. Deploy v1, which writes both columns and reads username. v0 instances still read username, so a mixed fleet serves consistent data.
3. Backfill handle for existing rows. This is a data change only. Run it after v1 is fully rolled out, so new rows are already covered and the backfill only has to reach a fixed set of older ones.
4. Deploy v2, which reads handle and still writes both. Because v1 and v2 write the same values, either one can serve any request.
5. Deploy v3, which writes only handle. From now on username goes stale. Rolling back to v2 is fine. Rolling back to v1 or v0 serves outdated values, and those versions write rows that leave handle empty.
6. Drop username after a holding period. Wait at least as long as you would ever need to roll back past v3.

Compatibility and rollback

Copy-- PostgreSQL. Each block runs at a different point in the rollout.

-- Step 1: nullable, no default. Old inserts that omit handle stay valid.
ALTER TABLE users ADD COLUMN handle text;

-- Step 2 (v1 app code): write both columns, keep reading username.
--   INSERT INTO users (username, handle, email) VALUES ($1, $1, $2);
--   UPDATE users SET username = $1, handle = $1 WHERE id = $2;

-- Step 3: backfill in small batches after v1 is fully rolled out.
-- Repeat until it reports UPDATE 0. Short transactions keep row locks brief.
UPDATE users
SET handle = username
WHERE id IN (
  SELECT id FROM users
  WHERE handle IS NULL AND username IS NOT NULL
  LIMIT 1000
);

-- Gate before deploying v2: should return 0.
SELECT count(*) FROM users WHERE handle IS NULL AND username IS NOT NULL;

-- Step 4 (v2 app code): read handle, still write both.
--   SELECT id, handle FROM users WHERE id = $1;

-- Before step 5: if username is NOT NULL, v3 inserts would fail.
ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

-- Step 5 (v3 app code): write handle only. username now goes stale.
-- Emergency rollback past v2 needs a repair first, e.g.:
--   UPDATE users SET username = handle
--   WHERE username IS DISTINCT FROM handle;

-- Step 6: after the holding period. Irreversible.
ALTER TABLE users DROP COLUMN username;

The PostgreSQL statements behind each step of the username-to-handle rename. They include a batched backfill and a check to run before switching reads. They also cover the NOT NULL relaxation that must happen before old-column writes stop, and the repair query needed before any rollback past v2.

Implementing dual writes: application code versus database triggers

Phase 2 has two common implementations. You can write both columns from the ORM or repository layer, or you can let a database trigger copy each value from the old column to the new one. The two approaches fail in different ways.

Drift is the main risk. It happens in two ways. First, a write path gets missed: a cron job, a raw SQL fix run by hand, or a second service sharing the database updates only the old column. Second, the two writes run in separate transactions, for example when a callback saves the new column after the main commit. If the process crashes or the second write fails, one column holds the new value and the other holds the old one. No error is raised, so nothing alerts you to the mismatch.

Triggers close the bypass gap but move behavior where engineers don't look. Application writes are easier to reason about but trust that you found every caller. Whichever you choose, the backfill and verification steps later in this guide exist because neither approach guarantees zero drift.

Copy-- PostgreSQL: copy users.email into users.email_address during the expand phase.
-- A BEFORE trigger modifies the row being written, so the copy is part of the
-- same statement and transaction as the original write, including writes from
-- cron jobs, psql sessions and other services.
CREATE OR REPLACE FUNCTION users_sync_email_address()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- The old column is still the source of truth, so it always wins.
  NEW.email_address := NEW.email;
  RETURN NEW;
END;
$$;

-- Fire only when the old column can change. Name the trigger so it shows up
-- clearly in \d users during an incident.
CREATE TRIGGER users_sync_email_address
BEFORE INSERT OR UPDATE OF email ON users
FOR EACH ROW
EXECUTE FUNCTION users_sync_email_address();

-- Application-side alternative: write both columns in ONE statement, never in
-- a post-commit callback, so a crash cannot leave them out of sync.
-- UPDATE users SET email = $1, email_address = $1 WHERE id = $2;

-- Drift check for either approach. IS DISTINCT FROM treats NULLs as values,
-- so a NULL in only one column still counts as a mismatch.
SELECT id, email, email_address
FROM users
WHERE email_address IS DISTINCT FROM email
LIMIT 100;

A PostgreSQL BEFORE trigger that copies the old column into the new one in the same transaction, the equivalent single-statement application write, and a query that finds rows where the two columns have drifted.

Running backfills and DDL safely on a live table

A single UPDATE across the whole table is the tempting backfill, and on a live system it is the wrong one. It runs as one transaction, holds row locks on everything it touches until commit, and generates a burst of write-ahead or redo log that replicas must replay. That burst is what turns into replication lag and stale reads.

Backfill in batches

Walk the table in primary-key ranges and commit after each batch. Short transactions release locks quickly and spread log volume over time. Write each batch so that rerunning it changes nothing. For example, only copy rows where the new column is still null or differs from the old one. Record the last key processed so the job can resume after a crash or deploy. Between batches, check database load and replica lag, and pause when either crosses a threshold you have chosen in advance.

Batch size is a trade-off. Small batches are gentle on the database but stretch the window in which old and new columns can drift apart. Large batches finish sooner but cause more contention per commit.

DDL on a live table

Some ALTER TABLE operations rewrite the table or take strong locks. Which ones do depends on the engine, version and specific operation, so check your database's documentation and test against a production-sized copy. Set a lock timeout and a statement timeout before running DDL. A blocked ALTER otherwise queues every query behind it, and with a timeout it fails fast and can be retried. Build indexes with the engine's concurrent or online option where one exists.

For NOT NULL and foreign keys, add the constraint unvalidated, then validate it after the backfill. Validation scans the table without blocking normal writes the way a fully checked add would. Where NOT NULL cannot be added that way, use a validated check constraint as an intermediate step. On some engines it lets the later NOT NULL skip the full scan.

Copy-- PostgreSQL. Fail fast instead of queueing traffic behind a blocked ALTER.
SET lock_timeout = '3s';
SET statement_timeout = '15s';

-- One backfill batch. The driver loop supplies :last_id, saves the new
-- high-water mark after COMMIT, and pauses when load or replica lag is high.
-- IS DISTINCT FROM makes reruns a no-op for rows already copied.
BEGIN;
UPDATE orders
   SET customer_ref = legacy_customer_id
 WHERE id > :last_id AND id <= :last_id + 1000
   AND customer_ref IS DISTINCT FROM legacy_customer_id;
COMMIT;

-- Index without blocking writes. Cannot run inside a transaction block.
-- If it fails it leaves an INVALID index: drop it and retry.
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_ref_idx
    ON orders (customer_ref);

-- Foreign key: add unvalidated (brief lock, no scan), validate later.
ALTER TABLE orders ADD CONSTRAINT orders_customer_ref_fkey
    FOREIGN KEY (customer_ref) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_ref_fkey;

-- NOT NULL via a CHECK constraint as the intermediate step.
ALTER TABLE orders ADD CONSTRAINT orders_customer_ref_not_null
    CHECK (customer_ref IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_ref_not_null;

-- PostgreSQL 12+ uses the validated CHECK to skip the full-table scan here.
ALTER TABLE orders ALTER COLUMN customer_ref SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_ref_not_null;

A PostgreSQL sequence that sets lock and statement timeouts, runs one idempotent primary-key-range backfill batch, builds an index concurrently, and adds a foreign key and NOT NULL as unvalidated constraints that are validated after the backfill.

Verifying each phase before you contract

Every phase transition is a one-way door in practice, so each one needs evidence rather than a calendar reminder. Three checks cover most of the risk.

Reconciliation queries. Before switching reads to the new column, count rows where the old and new values differ, including rows where exactly one side is null. Plain equality returns unknown when one side is null, so use a null-safe comparison. The target is zero, and it should stay at zero across several runs. A one-time zero proves the backfill finished. A sustained zero proves the dual writes are still working. Run the query on a replica or in batches so the check does not load the primary.

Shadow reads. For a period, read both columns in the application, serve the old value, and log any mismatch with the row key. This catches code paths that write only one column, such as a forgotten admin endpoint or a bulk import, which reconciliation shows as unexplained drift but cannot trace to a source.

Metering old-column reads. Once reads have moved, instrument or log every remaining access to the old column. A counter that has stayed flat for a full business cycle, including month-end jobs, is the signal to contract.

Before dropping anything, look beyond the repository. Check for:

• older deployed versions still serving traffic
• cron jobs
• replica consumers
• caches holding serialized rows
• analytics pipelines
• change-data-capture consumers
• saved reporting queries

These often name the column directly and never appear in a code search. The database's query statistics or statement logs are the most reliable place to find them.

Applying the pattern beyond renames

The six-step shape stays the same for other schema changes. What varies is where old and new representations must coexist, and that decides what gets dual-written.

Type changes and splits are lossy in at least one direction, so the conversion logic must live in one shared function or trigger. If it is duplicated, the copies will drift. Moving a column is the riskiest of the four because dual writes now span two tables. A write that commits to one and fails on the other leaves a mismatch, and the backfill will not catch it later. For table renames, databases that support updatable views let a view carry the old name. That removes the dual write entirely, but you must verify that the view behaves identically for every statement the old callers issue.

When to use it, when to avoid it, and what it costs

Every variant above trades one risky deploy for several safe ones, and that trade is not always worth making.

What it costs

A single rename becomes several deploys spread over days or weeks, because each phase has to run in production long enough to prove itself. In a monolith shared by several teams, each phase also has to fit around everyone else's releases. Someone has to track which phase is live and when the next one ships.

The most expensive outcome is an abandoned contract phase. The old column stays in the schema, every write still touches both columns, and new engineers have to work out which one is authoritative. You pay the full cost of the expand phase and keep paying for it, without getting the cleanup you did the work for.

What it saves

It saves time on incident response for locked tables. It also avoids rollbacks that fail because the old code no longer matches the new schema, and it removes the off-hours maintenance windows that pull several engineers in at midnight.

Keeping it organized

• Ship one pull request and one release per phase, so each phase can be rolled back on its own.
• Track the phases in a migration checklist or ticket that names the current phase and the criteria for moving to the next one.
• Where the framework allows it, keep schema migrations separate from code changes.
• Schedule the contract step as planned work with an owner and a date, not as optional cleanup.

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 and 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.