Expand and Contract Migration: Zero-Downtime Column Renames

Why a direct column rename breaks during a rolling deploy

A rolling deploy replaces application instances in batches, so for minutes, or longer if a rollout stalls, old and new code serve traffic against the same database. A single RENAME COLUMN has no safe moment to run. Run it before the deploy, and every old instance still querying customer_name fails. Run it after, and the new instances fail looking for full_name. Run it mid-deploy, and whichever version you haven't accounted for fails. Rollback is broken too, because reverting the code does not revert the schema.

The expand and contract migration pattern avoids this. It never removes something that running code depends on in the same step that adds its replacement. The core rule is that the schema after migration N must work with both release N-1 and release N. That rule fixes the deploy order:

• Expand migrations add the new column and any dual-write mechanism. They ship before the code that needs them.
• Contract migrations drop the old column. They ship only after no running release reads or writes it.

"Running code" covers more than your application fleet:

• Analytics pipelines select columns by name.
• CDC streams emit column names that downstream consumers parse.
• Reporting replicas run scheduled queries.
• Views and stored routines may break or keep exposing the old name, depending on the database.
• Prepared statements and cached query plans in long-lived connections can still reference the old column.

Each of these is a client with its own release cycle, so the contract phase must wait for the slowest one.

How does expand and contract work?

A rename becomes three moves instead of one: add the new name, move everything to it, then remove the old name. Each move is a separate deploy, and each one is backward compatible with the release before it.

1. Expand. Add the new column alongside the old one. Nothing reads it yet, so existing code is unaffected.
2. Migrate. Keep both columns in sync with dual writes, copy existing rows with a batched backfill, verify that the columns match, and then switch reads to the new column, ideally behind a feature flag.
3. Contract. Stop writing the old column, wait until no release or external consumer references it, and then drop it along with any sync mechanism.

At every point, the schema contains whatever both the running release and the next release need. The old and new code can therefore overlap during a rolling deploy, and you can roll back by redeploying the previous release up until the contract step. The sections below cover each phase in detail.

How expand and contract works: the six phases and their deploy order

Each phase below ships as its own deploy, and a phase starts only after the previous one has fully rolled out. The rule behind the ordering: every schema state must work with both the release currently running and the release about to replace it.

Timeline: schema state after each migration, and which releases can run against it

S0: old only. Safe: R0 ↓ R1 migration adds nullable new S1: old + empty new. Safe: R0, R1, R2 ↓ R2 starts dual writes; R3 backfills S2: old + fully populated new. Safe: R2, R3, R4, R5 ↓ R4 switches reads; R5 stops writing old ↓ R6 migration drops old S3: new only. Safe: R5, R6

The backfill in R3 has to wait until R2 is fully rolled out. Any R0 or R1 instance still serving traffic writes rows that leave new empty, and the backfill would have to chase those rows indefinitely. For the same reason, R4 can read new only once the backfill has been verified complete.

Copy                                         Releases that can safely
  Schema state                           run against this state
+--------------------------------+
| S0  columns: old               |---->  R0
+--------------------------------+
                |
                | R1  migration  add nullable new          [expand]
                v
+--------------------------------+
| S1  columns: old + new (empty) |---->  R0  R1  R2
+--------------------------------+
                |
                | R2  code       write old AND new
                | R3  data job   backfill new (after R2 fully live)
                v
+--------------------------------+
| S2  columns: old + new (full)  |---->  R2  R3  R4  R5
+--------------------------------+
                |
                | R4  code       read new (after backfill verified)
                | R5  code       stop writing old
                | R6  migration  drop old                  [contract]
                v
+--------------------------------+
| S3  columns: new               |---->  R5  R6
+--------------------------------+

 Rollback: cheap through R4; R5 needs a reverse backfill; none after R6

Releases R1 to R6 move the schema from S0 to S3; each schema state lists the releases that can safely run against it, so the running release and the next one always overlap.

Implementing dual writes: application layer versus database triggers

Phases R2 through R4 depend on one guarantee: every row written while both columns exist ends up with the same value in old and new. You can enforce that in the application or in the database. Choose one.

In the application layer, the write path sets both columns in the same statement. A repository method that previously issued UPDATE users SET old = :v WHERE id = :id now issues SET old = :v, new = :v. In an ORM, you map both attributes and assign them together in a single setter. Because both columns change in one statement, no reader can see one updated without the other.

A database trigger does the copy inside the engine. A BEFORE INSERT OR UPDATE trigger sets new from old, or the reverse, depending on which column the incoming write changed. Trigger syntax and capabilities differ between engines, so treat any generic SQL as a sketch and check it against your database.

Use one mechanism per rename. If you choose the trigger, removing it belongs in the contract phase.

Copy-- Option A: application dual write. One statement sets both columns,
-- so no reader ever sees one updated without the other.
UPDATE users SET full_name = $1, display_name = $1 WHERE id = $2;

-- Option B: trigger dual write (PostgreSQL syntax; other engines differ).
-- Use A or B for this rename, never both.
CREATE OR REPLACE FUNCTION users_sync_name() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Fill whichever column the writer left empty.
    NEW.display_name := COALESCE(NEW.display_name, NEW.full_name);
    NEW.full_name    := COALESCE(NEW.full_name, NEW.display_name);
  ELSIF NEW.display_name IS DISTINCT FROM OLD.display_name THEN
    -- Writer changed the new column: copy it back to the old one.
    -- If both changed, the new column wins.
    NEW.full_name := NEW.display_name;
  ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
    -- Writer changed the old column: copy it forward.
    NEW.display_name := NEW.full_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_sync_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_name();

-- Contract phase: remove the sync mechanism before dropping full_name.
-- DROP TRIGGER users_sync_name ON users;
-- DROP FUNCTION users_sync_name();

An application-layer UPDATE that writes both columns in one statement, and an alternative PostgreSQL BEFORE INSERT OR UPDATE trigger that copies whichever column changed, with the drop statements that belong in the contract phase.

Backfilling, verifying and switching reads safely

Once dual writes are live, every new or updated row has both columns populated. Existing rows still need copying, and a single UPDATE across the whole table would hold locks for the duration, generate one huge transaction for replicas to replay, and compete with production traffic for I/O.

Batch by primary key range

Walk the table in fixed key ranges (for example, 1,000 or 5,000 ids per batch) and commit after each one. Short transactions release row locks quickly and let replicas apply changes incrementally. Sleep between batches, and if you can read replica lag, pause when it crosses a threshold.

Restrict each batch to rows that actually need work: WHERE id BETWEEN :lo AND :hi AND new_col IS DISTINCT FROM old_col. This filter makes the job idempotent. You can rerun it after a crash, resume from the last committed range, or let it overlap with dual writes without reprocessing rows that are already correct.

Verify with a NULL-safe comparison

A plain old_col <> new_col evaluates to NULL when either side is NULL, so it misses rows where one column is NULL and the other is not. Count mismatches with IS DISTINCT FROM in PostgreSQL or NOT (old_col <=> new_col) in MySQL.

Gate the read switch

Switch reads only when two conditions hold. The mismatch count must be zero. Dual writes must also have been live longer than your slowest request, background job, or queued message, so that no old-code writer can still touch only old_col. Put the read path behind a runtime feature flag. If anything looks wrong, you can flip it back immediately without a deploy, because both columns are still being written.

Copyimport os, time
import psycopg2  # PostgreSQL; table customers, renaming full_name -> display_name

BATCH, MAX_LAG_S = 5000, 5.0
primary = psycopg2.connect("dbname=app host=db-primary user=migrator")
replica = psycopg2.connect("dbname=app host=db-replica user=migrator")
replica.autocommit = True

def replica_lag_seconds():
    # Time since the replica last replayed a commit. This also grows when the primary is idle.
    with replica.cursor() as cur:
        cur.execute("SELECT COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()), 0)")
        return float(cur.fetchone()[0])

with primary.cursor() as cur:
    cur.execute("SELECT COALESCE(MAX(id), 0) FROM customers")
    max_id = cur.fetchone()[0]  # rows inserted later are already covered by dual writes
primary.commit()

lo = int(os.environ.get("START_ID", "1"))  # resume from the last logged range after a crash
while lo <= max_id:
    hi = lo + BATCH - 1
    with primary.cursor() as cur:
        # IS DISTINCT FROM skips rows that are already correct, so reruns are idempotent
        cur.execute("""UPDATE customers SET display_name = full_name
                       WHERE id BETWEEN %s AND %s
                         AND display_name IS DISTINCT FROM full_name""", (lo, hi))
    primary.commit()  # one short transaction per key range
    print(f"committed through id {hi}", flush=True)
    lo = hi + 1
    time.sleep(0.2)  # throttle to limit I/O and lock contention on the primary
    while replica_lag_seconds() > MAX_LAG_S:
        time.sleep(5)  # back off until replicas catch up

with primary.cursor() as cur:  # NULL-safe verification; must be 0 before switching reads
    cur.execute("SELECT count(*) FROM customers WHERE full_name IS DISTINCT FROM display_name")
    print("mismatches:", cur.fetchone()[0])

def customer_name(row, flags):
    return row["display_name"] if flags.is_enabled("read_display_name") else row["full_name"]

This resumable PostgreSQL backfill updates one primary-key range per short transaction, sleeps between batches and backs off on replica lag, then runs a NULL-safe mismatch count before reads move to the new column behind a runtime feature flag.

Constraints, defaults and indexes without long locks

The DDL that creates new_col and secures it can undo all of that care. Many schema changes take an exclusive lock or rewrite the table. Even a brief exclusive lock can queue every query behind it while it waits for long-running transactions to finish, and that queue looks like an outage. Whether a given statement blocks depends on your engine and its version, so read the locking documentation for your database and test against a production-sized copy rather than assuming.

Add new_col as nullable with no default, or with a default your engine can apply as a metadata-only change. Build indexes with the engine's online or non-blocking option where one exists. These builds are slower and can fail partway, so check for and clean up invalid indexes afterwards. Add NOT NULL and unique constraints only after the backfill is verified. Where the engine supports it, add the constraint unvalidated and validate it in a separate step. Validation still scans the table, but typically without blocking writes.

The contract phase and making renames repeatable

The drop is the only irreversible step, so ship it as its own release, scheduled after every release that could still touch the old column has aged out. Until then, rolling back means redeploying old code. After the drop, rolling back means restoring data from backup.

Pre-drop checklist

• Code references are gone. Search for the old name in ORM model fields, raw SQL strings, migrations that seed data, report definitions and saved ad hoc queries. ORMs often select every mapped field, so one leftover attribute breaks every query on that table.
• No eligible release reads it. Confirm that neither the running release nor any release you would roll back to references the old column.
• External consumers have moved. Check CDC streams, analytics jobs, reporting replicas and database views. These fail quietly or later, often in another team's pipeline.

Making it repeatable

Track each rename as one ticket or checklist covering every release: expand, dual write, backfill, read switch, stop writes, drop. Gate the read switch behind a feature flag so you can reverse it without a deploy.

Add a CI check that blocks destructive DDL from shipping with related code. A simple script pattern works: scan the migrations in the diff for DROP COLUMN or RENAME COLUMN, extract the table and column names, and fail the build if application files in the same diff mention them.

The common failure is the abandoned rename. Both columns stay forever, dual writes drift, and nobody knows which column is correct. Give every checklist a named owner and a target date for the drop so the rename gets finished.

Copy#!/usr/bin/env bash
set -euo pipefail

BASE="${1:-origin/main}"          # branch the PR will merge into
MIGRATIONS_DIR="db/migrations/"   # adjust to your layout

mapfile -t changed < <(git diff --name-only --diff-filter=d "$BASE"...HEAD)

migrations=(); app_files=()
for f in "${changed[@]}"; do
  if [[ $f == "$MIGRATIONS_DIR"* ]]; then migrations+=("$f"); else app_files+=("$f"); fi
done
[[ ${#migrations[@]} -eq 0 || ${#app_files[@]} -eq 0 ]] && exit 0

mapfile -t columns < <(
  grep -hoiE '(DROP|RENAME) COLUMN( IF EXISTS)? "?[A-Za-z0-9_]+' "${migrations[@]}" \
    | awk '{print $NF}' | tr -d '"' | sort -u
)
[[ ${#columns[@]} -eq 0 ]] && exit 0

status=0
for col in "${columns[@]}"; do
  # -w: whole-word match, so "email" does not hit "email_verified".
  if hits=$(grep -lw -- "$col" "${app_files[@]}"); then
    echo "Destructive DDL on column '$col' ships with code that references it:" >&2
    sed 's/^/  /' <<< "$hits" >&2
    status=1
  fi
done

[[ $status -ne 0 ]] && echo "Move the DROP/RENAME into its own, later release." >&2
exit $status

A minimal git-and-grep script pattern that fails CI when a migration in the diff drops or renames a column that changed application files in the same diff still mention; it matches on column name only, so add table-name matching if common column names cause false positives.

Who should use expand and contract?

The pattern fits teams that meet most of these conditions:

• You deploy without downtime. Rolling, blue-green or canary deploys mean two releases share the database for some period.
• Several clients share the schema. Multiple services, background workers, analytics jobs or CDC consumers read the same tables and release on their own schedules.
• You need a fast rollback. Your incident process assumes you can redeploy the previous release, and a schema change must not break that.
• The table is large or busy. A one-shot UPDATE or table-rewriting DDL would hold locks or generate replica lag that users would notice.
• You rename or restructure columns regularly. The first rename takes the most effort. After that, the checklist and CI guard make each later rename routine.

Platform and database teams that own migration tooling get the most out of it, because they can encode the phases once and let application teams follow them. A solo developer on a small internal app with a tolerated maintenance window usually does not need it. The next section covers that trade-off.

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

Use expand and contract when old and new application code hit the same database at the same time. That happens with rolling or blue-green deploys of a monolith against a shared database, where an availability target rules out planned outages. This overlap is what breaks a direct rename, and expand and contract exists to handle it.

Avoid it for small tables, low-traffic systems and internal tools. If the rename plus deploy finishes in minutes and users accept a scheduled window, a maintenance window is cheaper than weeks of dual writes. If the name only bothers developers, an alias is often enough.

The cost is real:

• Several deploys spread over days or weeks.
• Doubled writes to the affected column while both columns exist.
• Extra code paths to test now and delete later.
• Backfill load that competes with production traffic.

The savings come from what you avoid:

• No outage windows that need coordination across teams.
• Rollback during the expand phase is a redeploy, not a restore.
• Fewer incidents caused by code and schema falling out of step.
• A reusable checklist that turns each later rename into routine work.

How expand and contract saves engineering time

The pattern adds deploys, but each one is small, routine and reversible. Several kinds of work go away:

• No maintenance-window coordination. A direct rename with downtime means scheduling a window, notifying users and other teams, staffing it, and often doing it outside working hours. Each expand and contract step ships through the normal deploy pipeline during the working day.
• Less incident time. The most expensive outcome of a bad rename is an outage mid-deploy, followed by an investigation and a restore from backup. Because every schema state works with both adjacent releases, a problem in the expand or migrate phases is fixed by redeploying the previous release or flipping the read flag back. That takes minutes rather than a recovery effort.
• Smaller, faster reviews. Each release changes one thing: a nullable column, a dual write, a read switch, a drop. Reviewers can reason about a single step instead of a coordinated schema-and-code change.
• Mistakes caught before they ship. The CI guard rejects a drop or rename that ships with code still referencing the column, so the failure happens in a pull request rather than in production.
• Decoupled teams. Downstream owners of analytics jobs, CDC consumers and reporting queries can migrate on their own schedule during the window when both columns exist, instead of releasing in lockstep with you.
• Reuse. Once the backfill script, the CI check and the checklist exist, a later rename is mostly parameter changes: table name, old column, new column.

An illustrative estimate, using assumed inputs only: suppose a team assumes a downtime rename needs a scheduled window involving three engineers for two hours, plus an hour of planning and notification, and that the team does four renames a year. That comes to about 28 engineer-hours a year spent on windows, before counting any overrun or failed rollback. With templated expand and contract tooling, assume each rename costs six small deploys at 30 minutes of attention each, or 3 engineer-hours, for about 12 engineer-hours a year. The first rename costs more while the tooling is built. Substitute your own team's numbers; the comparison depends heavily on how often you rename and how expensive your windows and incidents are.

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.