Database Migrations & Backward-Compatible Deploys

Progressive delivery assumes you can put the previous version back. A schema change quietly removes that assumption: the container rolls back in thirty seconds, the dropped column does not come back at all, and the “stable” version you just restored starts throwing errors on every request. Every strategy in this section — blue-green, canary, automated rollback — depends on two application versions being able to run against one database at the same time.

This guide covers the sequence that preserves that property: expand the schema additively, dual-write, backfill under throttle, switch reads, and contract only after the rollback window has closed. It also covers the CI gate that stops an unsafe migration from merging in the first place, because the cheapest place to catch a DROP COLUMN is the pull request, not the incident.

Prerequisites

Why Rollback Breaks Before Anything Else Does

A deploy is two changes that appear to be one: the application version and the schema version. Application versions are trivially reversible because the old image still exists. Schema versions are not, because the data the old structure held is gone the moment the structure is dropped. The result is an asymmetry that only shows up under pressure.

Destructive Migration vs Expand-Contract: What Rollback Finds Top timeline: version one runs against the old schema, a migration renames a column, version two deploys, then a rollback to version one fails because the column it reads no longer exists. Bottom timeline: an expand step adds a new column, version two dual-writes, and a rollback to version one still succeeds because the old column is intact. DESTRUCTIVE MIGRATION v1 reads full_name RENAME → display_name v2 reads display_name rollback to v1 column full_name does not exist One schema, two incompatible versions — the revert makes the incident worse. EXPAND AND CONTRACT v1 reads full_name ADD display_name v2 writes both rollback to v1 full_name still populated — succeeds One schema, two compatible versions — the revert is a non-event. The contract step (DROP full_name) ships in a later release, after the rollback window closes.

The rule that follows is short enough to put in a pull-request template: a migration may only add; removal is a separate release. Everything else in this guide is machinery for making that rule practical — how to keep two shapes in sync while both are live, how to move historical data without locking a hot table, and how to know when it is finally safe to contract.

There is a second, subtler reason the rule matters under progressive delivery. During a canary, both versions are serving simultaneously, not sequentially. A migration that is safe for a sequential blue-green cutover — apply, then switch everything at once — can still break a canary, because the stable version keeps receiving traffic against the changed schema for the entire analysis window. Canary-safe is a stricter bar than rollback-safe, and it is the bar worth designing to.

Step-by-Step Implementation

1. Expand — add the new structure, change nothing else

-- migrations/20260731_01_add_display_name.sql
-- Additive only: v1 does not know this column exists and is unaffected.
ALTER TABLE users ADD COLUMN display_name text;

-- Nullable, no default: on PostgreSQL this is a metadata-only change that takes
-- a brief ACCESS EXCLUSIVE lock and does not rewrite the table.
-- A NOT NULL DEFAULT on a large table is the classic accidental full rewrite.

Verify the operation did not rewrite the table before running it against production:

psql -c "EXPLAIN (ANALYZE, BUFFERS) ALTER TABLE users ADD COLUMN display_name text;" \
  --set=ON_ERROR_STOP=1   # run against a production-shaped staging restore first

Adding an index in the same step needs CONCURRENTLY, which cannot run inside a transaction — most migration tools need an explicit escape hatch for that, and forgetting it is how a five-second migration becomes a fifteen-minute table lock.

2. Dual-write — one code path, two destinations

Deploy application code that writes both shapes and still reads the old one. This version is fully compatible with the one before it, so the deploy is reversible at every moment.

// src/repositories/user.ts — dual-write phase
export async function updateUser(id: string, input: UserInput) {
  return db.user.update({
    where: { id },
    data: {
      full_name: input.name,      // old shape — still authoritative for reads
      display_name: input.name,   // new shape — populated but not yet trusted
    },
  });
}

// Reads deliberately stay on the old column until the backfill has converged.
export const readName = (u: User) => u.full_name;

The temptation at this point is to read display_name ?? full_name and skip the backfill. Resist it: the fallback hides an incomplete migration indefinitely, and you lose the one signal — a divergence count of zero — that tells you the next step is safe.

3. Backfill — throttled batches, measured convergence

#!/usr/bin/env bash
# scripts/backfill-display-name.sh — bounded batches so replication lag stays flat.
set -euo pipefail
BATCH=${BATCH:-5000}
LAG_LIMIT_MS=${LAG_LIMIT_MS:-1000}

while :; do
  updated=$(psql -qtAX -c "
    WITH batch AS (
      SELECT id FROM users
      WHERE display_name IS NULL
      ORDER BY id
      LIMIT ${BATCH}
      FOR UPDATE SKIP LOCKED          -- never block live writes
    )
    UPDATE users u SET display_name = u.full_name
    FROM batch WHERE u.id = batch.id
    RETURNING 1;" | wc -l)

  [ "$updated" -eq 0 ] && { echo "backfill complete"; break; }

  # Back off whenever the read replica falls behind — the backfill is never urgent.
  lag=$(psql -qtAX -c "SELECT COALESCE(EXTRACT(MILLISECONDS FROM replay_lag),0)::int FROM pg_stat_replication LIMIT 1;")
  if [ "${lag:-0}" -gt "$LAG_LIMIT_MS" ]; then sleep 10; else sleep 1; fi
  echo "batch done: ${updated} rows, replica lag ${lag}ms"
done

Confirm convergence before moving on:

psql -qtAX -c "SELECT count(*) FROM users WHERE display_name IS DISTINCT FROM full_name;"
# → 0 means every row agrees; anything else means a write path is still missing the dual-write.

A non-zero count here almost always means one code path — a bulk importer, an admin tool, a background job — was not updated in step 2. That is exactly what the check is for.

The throttle is the part worth understanding rather than copying. An unthrottled backfill is a denial-of-service attack you wrote yourself: it saturates write throughput, replication falls behind, and every read served from a replica starts returning stale data long before anyone thinks to blame the migration.

Replica Lag During Unthrottled and Throttled Backfill A chart with elapsed minutes on the horizontal axis and replica lag on the vertical. The unthrottled run's lag rises steeply past the one-second threshold to about four and a half seconds before the backfill finishes at eight minutes. The throttled run stays below the threshold throughout and finishes at nineteen minutes. replica lag 0s 1s 3s 5s LAG_LIMIT_MS = 1000 — stale reads begin above this line unthrottled — 8 min, peak lag 4.5s finishes throttled — 19 min, lag never exceeds 0.5s finishes 0 5 10 15 20 min 40M-row table, 5,000-row batches. The backfill is never urgent — eleven extra minutes costs nothing.

4. Switch reads, then contract

Deploy a version that reads display_name. Keep writing full_name — this is what preserves rollback. Only when the rollback window has closed does the final migration drop the old column, and that drop ships as its own release with nothing else in it.

Expand-Contract Phases: Write Path, Read Path, Rollback Safety Five phases across the page — Expand, Dual-write, Backfill, Switch reads, Contract. For each, a row shows what the application writes and what it reads, and a bottom band marks rollback as safe for the first four phases and one-way after contract. WRITES READS ROLLBACK 1 Expand 2 Dual-write 3 Backfill 4 Switch reads 5 Contract full_name both columns both columns both columns display_name full_name full_name full_name display_name display_name safe — every version runs against this schema one-way release N release N+1 release N+2 release N+3 Phases 1–4 span three ordinary releases; only phase 5 is irreversible, and it ships alone.

5. Gate the unsafe cases in CI

The discipline above only holds if something checks it. A migration linter in the pull-request pipeline is a dozen lines and catches the destructive statements before review even starts.

#!/usr/bin/env bash
# scripts/migration-safety-gate.sh — blocks destructive DDL in the same release as app changes.
set -euo pipefail
CHANGED=$(git diff --name-only "origin/${GITHUB_BASE_REF:-main}"... -- 'migrations/*.sql')
[ -z "$CHANGED" ] && { echo "no migrations touched"; exit 0; }

# Destructive verbs that break a rollback if the previous version is still deployable.
if grep -inE '\b(DROP\s+(COLUMN|TABLE)|RENAME\s+(COLUMN|TO)|ALTER\s+COLUMN\s+\w+\s+TYPE)\b' $CHANGED; then
  echo "❌ Destructive DDL found. Ship it as a contract-only release, labelled 'migration:contract'."
  # An explicit label is the documented escape hatch — deliberate, visible, reviewed.
  [[ "${PR_LABELS:-}" == *"migration:contract"* ]] || exit 1
fi

# CREATE INDEX without CONCURRENTLY locks writes for the duration of the build.
grep -inE 'CREATE\s+(UNIQUE\s+)?INDEX(?!\s+CONCURRENTLY)' -P $CHANGED && exit 1 || true
echo "✅ migrations are expand-safe"

Configuration Reference

Setting Type Default Effect
statement_timeout ms 0 (none) Caps how long a migration may hold a lock before failing — set 5000 for DDL
lock_timeout ms 0 (none) Fails fast when a table lock cannot be acquired instead of queueing behind traffic
BATCH integer 5000 Rows per backfill iteration; larger batches finish sooner but lengthen each lock
LAG_LIMIT_MS integer 1000 Replica lag above which the backfill pauses
FOR UPDATE SKIP LOCKED clause off Lets the backfill step over rows a live transaction holds instead of blocking
CONCURRENTLY flag off Builds an index without blocking writes; cannot run inside a transaction
Rollback window duration undefined How long the contract step must wait; drives the dual-write duration

Integration with Adjacent Topics

Canary analysis assumes schema compatibility. The metric-gated rollouts in canary releases and progressive rollouts run both versions against one database for the whole analysis window. If the canary applied a contracting migration, the stable version starts erroring and the analysis blames the canary for damage the migration caused.

Automated rollback needs a reversible target. The triggers in automated rollback triggers and runbook integration will fire on the error-rate spike a bad deploy causes — and then fail, loudly and late, if the schema moved underneath them. Wire the migration-safety gate into the same required checks that protect the deploy branch.

Preview environments are where this gets tested. The seeded databases described in database mocking and seeding for ephemeral environments are the only place to rehearse a migration against realistic row counts before it meets production volume.

Performance and Cost Impact

Expand-contract trades calendar time for reversibility. A rename that a destructive migration completes in one release takes three, spread over one to two weeks. What you buy is a rollback path that stays open the entire time, and the numbers favour it decisively: a five-minute revert versus a restore-from-backup measured in hours, with data loss between the last snapshot and the incident.

The runtime costs are smaller than teams expect. Dual-writing a text column adds roughly 3–5% to write latency on a typical row — one extra assignment in an UPDATE that was already touching the row. Storage for the duplicated column on a 40-million-row table is a few hundred megabytes. Throttled backfill of that same table takes 30–50 minutes at 5,000 rows per second and never blocks a live write, because SKIP LOCKED steps around anything a user transaction is holding.

The cost that actually bites is attention: three releases need to be sequenced, and the contract step is easy to forget for six months. Track outstanding contractions as real work items with the release number that created them, or the schema accumulates a permanent layer of columns nothing reads — each one a small tax on every future migration, every backup, and every engineer trying to work out which column is authoritative.

Troubleshooting

ERROR: canceling statement due to statement timeout during ALTER TABLE. The DDL queued behind a long-running transaction and could not acquire its lock. Find the blocker with SELECT pid, query, state FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;, then retry during a quieter window. Do not raise the timeout — it is the safety mechanism that stopped a table-wide lock from stalling every request.

Backfill converges, then the divergence count climbs again. A write path is still missing the dual-write. The usual suspects are a background job, a bulk import, or an admin interface that writes through a different repository. Search for direct table writes that bypass the shared repository function, rather than re-running the backfill.

Rollback succeeds, but the previous version writes nulls. The old version never knew about the new column, so rows it writes during the rollback window have display_name IS NULL. This is expected — it is also why step 4 keeps reading through a code path that can be reverted, and why the backfill script must be re-runnable after any rollback.

Canary aborts with errors on the stable version, not the canary. A contracting migration shipped with the canary. Confirm with the migration diff for that release; the fix is to restore the dropped structure and re-ship the change through the expand path. This is precisely the failure the CI gate above exists to prevent.

Frequently Asked Questions

Why can a rollback fail even though the deploy succeeded?

Because code is reversible and schema is not. Reverting the container restores an application version that references structures the migration removed, so the “safe” rollback rolls forward into a crash loop. Expand-contract keeps both versions runnable against a single schema, which is what makes the revert a non-event.

How long should the dual-write window stay open?

At least the length of your rollback window plus the backfill duration — for most teams one to two weeks. The cost of leaving it open longer is a duplicated column and a few percent of write latency; the cost of closing it early is discovering mid-incident that you cannot go back.

Should migrations run in the deploy job or as a separate step?

A separate step that completes before the new version receives traffic. Running migrations from application startup means every replica races to apply them, failures surface as unhealthy pods rather than a clear migration error, and a rollback restarts the race.

Are database migrations compatible with canary releases?

Additive ones are; contracting ones are not. During a canary both versions serve simultaneously against one database, so anything that removes or repurposes a structure breaks the stable version mid-rollout. Ship expansion with the canary and contraction in a later, canary-free release.

← Back to Deployment Strategies & Progressive Delivery