Renaming a Postgres Column Without Downtime

ALTER TABLE users RENAME COLUMN full_name TO display_name; runs in milliseconds and takes the site down, because every currently-running instance of the application is still selecting full_name. This is the three-release sequence that achieves the same end state with no downtime and a rollback path open throughout.

When to use this pattern

  • A column name is wrong or misleading and the fix has been deferred because “it needs downtime”.
  • Several instances of the application serve traffic simultaneously, so no instant switch exists.
  • You use progressive delivery, where two versions deliberately run at once.

Prerequisites

The sequence, and what is deployed at each point

Three releases, each independently deployable and revertible. The property that holds throughout: any two consecutive versions can run against the schema at the same time.

Three Releases, One Rename Release one adds display_name and deploys dual-writing code that still reads full_name. A backfill runs between releases one and two. Release two switches reads to display_name while continuing to write both. Release three drops full_name. The old column exists and is populated from release one until release three. SCHEMA APP WRITES APP READS REVERSIBLE release 1 backfill release 2 release 3 full_name — present and populated throughout display_name — added in release 1, authoritative from release 2 both columns both columns both columns display_name full_name — not yet trusted display_name rollback to any earlier version still works one-way Release 3 ships alone, with no application changes, once the rollback window for release 2 has closed.

Complete working example

-- migrations/20260731_01_add_display_name.sql   (release 1)
-- Nullable with no default: metadata-only on Postgres 12+, brief ACCESS EXCLUSIVE lock,
-- no table rewrite. A NOT NULL DEFAULT here would rewrite 40M rows and lock for minutes.
ALTER TABLE users ADD COLUMN display_name text;

-- Index concurrently if the new column will be queried. Cannot run in a transaction,
-- so most migration tools need an explicit "no transaction" directive on this file.
CREATE INDEX CONCURRENTLY IF NOT EXISTS users_display_name_idx ON users (display_name);
// src/repositories/user.ts   (release 1) — dual-write, read the old column.
export async function upsertUser(id: string, name: string) {
  return db.user.update({
    where: { id },
    data: {
      full_name:    name,   // still authoritative
      display_name: name,   // populated, not yet trusted
    },
  });
}

// Reads deliberately stay on the old column. Do NOT write
// `display_name ?? full_name` here — the fallback would mask an incomplete
// backfill indefinitely and remove the one signal that says it is safe to proceed.
export const nameOf = (u: User) => u.full_name;
#!/usr/bin/env bash
# scripts/backfill-display-name.sh — between releases 1 and 2. Re-runnable.
set -euo pipefail
BATCH=${BATCH:-5000}

while :; do
  n=$(psql -qtAX -c "
    WITH batch AS (
      SELECT id FROM users
      WHERE display_name IS NULL
      ORDER BY id
      LIMIT ${BATCH}
      FOR UPDATE SKIP LOCKED     -- step over rows a live transaction holds
    )
    UPDATE users u SET display_name = u.full_name
    FROM batch WHERE u.id = batch.id
    RETURNING 1;" | grep -c 1 || true)

  [ "$n" -eq 0 ] && { echo "backfill complete"; break; }
  echo "copied ${n} rows"
  sleep 1                        # deliberate pacing; the backfill is never urgent
done

# The gate for release 2: zero divergence, checked not assumed.
psql -qtAX -c "SELECT count(*) FROM users WHERE display_name IS DISTINCT FROM full_name;"
// src/repositories/user.ts   (release 2) — read the new column, keep writing both.
export const nameOf = (u: User) => u.display_name;
// upsertUser is unchanged: full_name is still written, which is what keeps
// a rollback to release 1 working for the whole rollback window.
-- migrations/20260814_01_drop_full_name.sql   (release 3, ships alone)
-- Only after the rollback window for release 2 has closed.
ALTER TABLE users DROP COLUMN full_name;

Step-by-step walkthrough

Release 1 is invisible to the running application. Adding a nullable column changes nothing for code that does not select it, so the migration can be applied minutes or hours before the deploy without coordination. That decoupling is what makes the whole sequence low-risk.

The dual-write must cover every write path. One repository function is the easy case. If writes happen from a background job, an admin tool, or a second service, each needs the same change — and missing one is exactly what the divergence check at the end of the backfill detects.

Every Write Path Must Dual-Write, Including the Ones You Forgot The web application, the admin interface and a background job all write through the shared repository, which dual-writes correctly. A nightly bulk importer writes directly with raw SQL and sets only the old column, so its rows show up as divergence after the backfill. web application admin interface background job nightly bulk import shared repository dual-writes correctly raw SQL — bypasses the repository users table rows with both columns set ✓ rows with display_name NULL ✗ The divergence count after the backfill is precisely how the dashed path gets found — it is the reason the check exists.

FOR UPDATE SKIP LOCKED is what makes the backfill safe under load. Without it the batch blocks on any row a user transaction currently holds, and a long user transaction stalls the backfill while the backfill holds locks of its own. Skipping contested rows and revisiting them on a later pass keeps both sides moving.

Zero divergence is the gate, not a formality. Run the count, get zero, and only then merge release 2. A non-zero result is not a reason to run the backfill again — it means a write path is still missing, and re-running would hide that for another day.

Release 3 contains nothing else. A drop bundled with feature work means a revert of the feature is impossible without also reverting the drop, which is not possible. Shipping it alone keeps the irreversible change isolated and obvious in the release history — and lets the migration-safety gate described in database migrations and backward-compatible deploys require an explicit label for it.

Verification

# 1. Release 1's migration did not rewrite the table.
psql -c "\timing on" -c "ALTER TABLE users ADD COLUMN probe_col text;" \
     -c "ALTER TABLE users DROP COLUMN probe_col;"      # both should be single-digit ms
# 2. The backfill converged.
psql -qtAX -c "SELECT count(*) FROM users WHERE display_name IS DISTINCT FROM full_name;"

Expected:

0
# 3. A rollback to release 1 still works after release 2 has deployed.
kubectl -n production set image deploy/shop shop=ghcr.io/acme/shop:release-1
sleep 20
curl -sf https://shop.example.com/api/me | jq -r .name     # a real name, not null

The third check is the one that justifies the whole exercise. If it returns null, full_name stopped being written somewhere in release 2 — which means the rollback path you believed you had does not exist, and release 3 must not ship until it does.

When the sequence can be shortened, and when it must not be

Three releases is the safe default, not a law. Two conditions genuinely allow a shorter path.

The first is a table small enough that the backfill completes inside the migration itself — a few thousand rows, updated in one statement in well under a second. There the backfill stops being a separate phase, and releases 1 and 2 can merge into one, leaving a two-release sequence. The second is a column that is written but never read by the currently deployed version: a rename there cannot break a reader that does not exist, though it still breaks writers, so the dual-write is still required.

Everything else needs all three. In particular, a single-instance deployment is not an exemption, because a rolling restart still runs two versions briefly, and neither is a low-traffic service, where the window is smaller but the failure is identical. The cost of the full sequence is two extra pull requests and a fortnight of calendar time, during which nothing is at risk; the cost of guessing wrong is an outage whose fix is a restore. The asymmetry is what makes the default worth keeping even when it feels like ceremony.

When Fewer Than Three Releases Is Safe Two conditions permit a shorter sequence: a table small enough to backfill inside the migration, and a column no deployed version reads. Three commonly assumed exemptions — a single instance, low traffic, and a maintenance window at night — are not exemptions, because a rolling restart still runs two versions. genuinely shortens the sequence table small enough to backfill in the migration → two releases: add+backfill, then switch and drop later column written but never read by the live version → dual-write still required; the read switch is a no-op not exemptions, though often assumed "we only run one instance" — rolling restart overlaps "traffic is low" — smaller window, same failure "we deploy at 3am" — the rollback still cannot work The question is never how likely a failure is during the window — it is whether a rollback would succeed if one happened.

Common pitfalls

Reading with a fallback during the dual-write phase. display_name ?? full_name makes the application work immediately and removes every signal that the backfill is incomplete. The migration then never finishes, and the drop is unsafe forever.

Bundling the drop with the read switch. Then a revert of the read switch has nowhere to read from. The drop must be its own release, after the window.

Forgetting dependent objects. Views, materialised views, generated columns, and check constraints referencing full_name all block the drop or silently break. SELECT * FROM pg_depend against the column’s attnum before release 3 is a two-minute check that prevents a failed migration in production.

← Back to Database Migrations & Backward-Compatible Deploys