Designing Zero-Downtime Database Migrations with Claude Code: A Production Operations Guide

Implementing Expand-Contract with Claude Code

Expand-Contract splits a schema change into two phases — Expand, where the new shape is added; Contract, where the old shape is removed — and threads a deploy of both-old-and-new-coexist code in between. As a concrete example, suppose I want to rename email to email_address on the users table. Running ALTER TABLE users RENAME COLUMN email TO email_address outright would break every old replica the moment it executed. Expand-Contract turns that single risky operation into a sequence:

-- 1. Expand: add the new column (nullable for now)
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
CREATE INDEX CONCURRENTLY idx_users_email_address ON users(email_address);
 
-- 2. Backfill: copy data into the new column in batches
-- Application now dual-writes to both columns and reads from the old one
UPDATE users SET email_address = email
WHERE email_address IS NULL AND id BETWEEN 1 AND 10000;
 
-- 3. Switch: deploy a version that reads from email_address
-- but still writes to both, so we can roll back
 
-- 4. Contract: drop the old column once we're confident
ALTER TABLE users DROP COLUMN email;

When I ask Claude Code, "I want to rename email to email_address. Walk me through Expand-Contract," it produces the four migrations above as separate files. The discipline that matters is making each step deployable on its own and reversible until the very last drop.

The pattern took me a while to internalize. The mental gap between "I want to rename a column" and "this is a four-stage deploy" is wide enough that I used to talk myself out of doing it properly. With Claude Code in the loop, that cognitive cost largely disappeared. I name the files numerically — 001_add_email_address.sql, 002_backfill_email_address.sql, 003_switch_email_reads.sql, 004_drop_email.sql — and Claude Code reliably constructs the next file in the sequence with full awareness of what the previous ones did.

Adding a NOT NULL Column Safely — A Five-Stage Deploy

Adding a NOT NULL column is one of the most accident-prone migrations, even within Expand-Contract. The naive form, ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active', completes instantly on PostgreSQL 11+ via a metadata-only change, but on older versions and certain managed services it rewrites every row. On a million-row table, that's minutes of production lock.

The five-stage deploy I recommend is as follows.

In stage one, add a NULL-able column.

ALTER TABLE orders ADD COLUMN status VARCHAR(20);

In stage two, deploy an application version where new inserts always supply status, and existing reads tolerate NULL.

// orders.ts — guarantee status on insert from the application side
async function createOrder(input: OrderInput) {
  return db.insert(orders).values({
    ...input,
    status: input.status ?? 'active',  // defaulted in app code, not DB
  });
}

In stage three, run a backfill against existing rows. The non-negotiable rule is that batches stay small (1,000 to 10,000 rows) and yield with a sleep between them.

-- backfill in 10k-row batches with 100ms pauses
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE orders SET status = 'active'
    WHERE status IS NULL AND id IN (
      SELECT id FROM orders WHERE status IS NULL LIMIT batch_size
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

In stage four, add a CHECK constraint as NOT VALID first. This is the trick that unlocks zero-downtime NOT NULL: NOT VALID adds the constraint immediately without scanning existing rows, and a separate VALIDATE CONSTRAINT step then verifies the data under a much weaker lock.

-- add the constraint instantly, without scanning existing rows
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;
-- validate later, under SHARE UPDATE EXCLUSIVE (allows reads/writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

In stage five, you finally promote the column to a real NOT NULL. Because the CHECK constraint already proved every row complies, this step is instant.

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Communicating this five-stage flow to Claude Code is as simple as recording the rule in CLAUDE.md and adding "follow the NOT NULL five-stage deploy" to the initial prompt. From there, Claude Code generates the migrations in the right order without further coaxing.

The Hard Cases — Renames and Type Changes

Renames and type changes are the trickiest Expand-Contract operations because they require a window where the same logical value lives in two places. The technique I rely on most is dual-write enforced by a database trigger.

-- mirror writes from the old column into the new one
CREATE OR REPLACE FUNCTION sync_email_to_email_address()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.email IS DISTINCT FROM OLD.email OR OLD IS NULL THEN
    NEW.email_address := NEW.email;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_email_to_email_address();

Note that the trigger is one-directional: writes to the old column propagate to the new one, but not vice versa. Once application code has fully switched to the new column, the trigger is dropped — by then the old column should already be unused.

Type changes are even nastier. ALTER TABLE ... ALTER COLUMN ... TYPE rewrites the entire table, which is unacceptable in production. The solution is the same Expand-Contract pattern, applied to types instead of names.

-- 1. add the new-type column
ALTER TABLE products ADD COLUMN price_v2 BIGINT;
 
-- 2. dual-write from application code
-- async function updatePrice(id, price) {
--   await db.update(products).set({ price, price_v2: price }).where(...);
-- }
 
-- 3. backfill in batches
UPDATE products SET price_v2 = price WHERE price_v2 IS NULL AND id BETWEEN 1 AND 10000;
 
-- 4. switch reads to price_v2
 
-- 5. drop the old column and rename
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_v2 TO price;

The final RENAME COLUMN is itself fast, but it does take a brief lock. I run it during off-peak hours just to keep risk close to zero.

Backfill Strategy for Online Migrations

Backfills are usually the longest stage of a zero-downtime migration. On a hundred-million-row table, a careless backfill produces replication lag, and reads against replicas suddenly return stale data. Three strategies keep this under control in practice.

The first is tuning batch size and sleep adaptively. I update 1,000 to 10,000 rows per batch and adjust the inter-batch sleep based on observed replica lag. Asking Claude Code for "an adaptive backfill script that doubles its sleep when replica lag exceeds 100 ms" produces something like the following:

// backfill.ts — replica-lag-aware backfill
import { db } from './db';
 
async function backfillEmailAddress() {
  let sleepMs = 100;
  let lastId = 0;
  const batchSize = 5000;
 
  while (true) {
    const start = Date.now();
 
    const result = await db.execute(sql`
      UPDATE users SET email_address = email
      WHERE email_address IS NULL AND id > ${lastId}
      ORDER BY id
      LIMIT ${batchSize}
      RETURNING id
    `);
 
    if (result.rows.length === 0) break;
    lastId = Math.max(...result.rows.map(r => r.id));
 
    const lag = await getReplicaLagMs();
    if (lag > 100) {
      sleepMs = Math.min(sleepMs * 2, 5000);
      console.log(`Lag ${lag}ms detected, increasing sleep to ${sleepMs}ms`);
    } else if (lag < 50 && sleepMs > 100) {
      sleepMs = Math.max(sleepMs / 2, 100);
    }
 
    const elapsed = Date.now() - start;
    console.log(`Batch updated ${result.rows.length} rows in ${elapsed}ms, sleeping ${sleepMs}ms`);
    await new Promise(r => setTimeout(r, sleepMs));
  }
}
 
async function getReplicaLagMs(): Promise<number> {
  const result = await db.execute(sql`
    SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag_ms
  `);
  return result.rows[0]?.lag_ms ?? 0;
}
 
backfillEmailAddress().catch(console.error);

The second strategy is deferring index creation. Indexes fragment quickly during a backfill, so I use CREATE INDEX CONCURRENTLY after the backfill completes. CONCURRENTLY is slower than a regular CREATE INDEX but doesn't block writes, which is the constraint that actually matters in production.

The third strategy is making backfills idempotent. Every UPDATE carries a WHERE email_address IS NULL (or equivalent) clause so that a script can be safely killed and restarted. I learned this the hard way after a non-idempotent backfill died at an unknown row count and I had no clean way to resume.

Reverse Migrations Are Part of the Design

Rollback isn't an emergency parachute — it's a constraint that shapes how you write the original migration. Three rules I follow without exception.

First, never write a down that destroys data. If up adds a column and inserts rows, a corresponding DROP COLUMN in down deletes everything that was written in between. For at least a week after I ship a destructive change, my down migrations only flip application reads back to the old column; they don't drop anything until I'm confident.

Second, test the rollback in CI. My migration pipeline runs up, then down, then up again, and asserts the row counts and shape match. I ask Claude Code for "a Vitest test that runs up→down→up three times and verifies row preservation," and the resulting test catches subtle data-losing rollbacks before they ever reach production.

Third, always take a targeted backup before destructive operations. pg_dump --table=users -t users.email > backup.sql is sufficient for most cases. Claude Code can generate the appropriate dump command directly from the migration file.

Reviewing Migrations with a Claude Code Subagent

Migrations written alone tend to develop blind spots. The single highest-value habit I've adopted is running a Claude Code subagent as a reviewer before any production migration ships. I keep a migration-reviewer agent definition in .claude/agents/migration-reviewer.md and route every migration through it.

---
name: migration-reviewer
description: Reviews DB migration files and flags risks of production incidents
---
 
You are a senior DBA with 20 years of PostgreSQL production experience.
For each migration file you receive, evaluate against:
 
1. Lock duration: how long could this DDL hold a lock on a hot table?
2. Downtime: would the previous deploy break against this schema?
3. Rollback: does down lose real user data?
4. Performance: are batched backfills, replica lag, and index strategy sound?
5. Idempotency: can this script be safely killed and restarted?
 
Reply with PASS / WARN / FAIL per item plus a specific suggested fix.
A single FAIL means we do not ship.

A small hook wires this in front of pnpm migrate:plan so every migration gets reviewed automatically:

// .claude/hooks/pre-migrate.ts
import { spawn } from 'child_process';
 
const migrationFile = process.argv[2];
const result = await runClaudeAgent('migration-reviewer', { file: migrationFile });
 
if (result.includes('FAIL')) {
  console.error('❌ Migration review failed');
  console.error(result);
  process.exit(1);
}
 
console.log('✅ Review passed');
console.log(result);

Subjectively, having this reviewer in the loop has cut my post-deploy migration incidents by something close to a factor of five. The deeper subagent patterns are covered in the Claude Code Subagent Parallel Execution Guide, and most of those techniques apply just as well to non-migration work.

Working With Modern ORMs — Drizzle, Prisma, and Beyond

Modern ORMs introduce their own migration semantics, and each one has friction points worth understanding before you trust them with production schema changes. The patterns above translate directly, but the surface area looks different in each tool.

Drizzle ORM generates migrations from schema diffs, which is convenient until the diff misses a subtle reorder of columns or assumes that a text to varchar(255) change is harmless. I always inspect the generated SQL before running it, and I ask Claude Code to "compare this Drizzle migration against the Expand-Contract rules in CLAUDE.md and flag anything risky." That review step has caught bugs the diff tool was happy to let through.

Prisma's migrate dev workflow is friendlier in development but more dangerous in production because it tends to generate single-step migrations for renames. Setting previewFeatures = ["multiSchema"] and writing migrations by hand using prisma migrate diff --script keeps you in control. The migrations Claude Code generates here are more conservative than the auto-diff output, so this is one place where letting an AI rewrite the auto-generated SQL into Expand-Contract form pays for itself within a few migrations.

Outside the JavaScript ecosystem, Rails' strong_migrations gem and Django's RunSQL operation give you similar levers. The pattern is the same: trust the framework for the easy cases, but write or rewrite anything risky by hand using the principles in this guide.

Common Pitfalls — Mistakes That Reach Production

Even with Expand-Contract internalized, three classes of mistakes still find their way into production. Knowing them in advance is most of the defense.

The first pitfall is forgetting that a foreign key constraint creates implicit indexes and locks. Adding REFERENCES other_table(id) requires a row-level lock on the parent table for the duration of the validation. On a hot parent table, this is enough to stall production. The fix is the same NOT VALID trick used for CHECK constraints — add the foreign key as NOT VALID, then validate it later under a weaker lock.

-- avoid the long lock by deferring validation
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

The second pitfall is partial index creation that fails halfway and leaves the database in an awkward state. CREATE INDEX CONCURRENTLY does not run inside a transaction, so a failed run leaves an INVALID index that still consumes disk and confuses the query planner. The remedy is to detect and clean up these stale indexes as part of the migration runner:

-- find and drop any invalid indexes left behind by failed CONCURRENTLY runs
SELECT indexrelid::regclass AS invalid_index
FROM pg_index WHERE NOT indisvalid;
 
-- drop them explicitly, also CONCURRENTLY
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email_address;

I keep a small Claude Code skill that scans for invalid indexes after every migration run and surfaces them in the logs.

The third pitfall is ignoring how connection pools interact with schema changes. PgBouncer in transaction-pooling mode keeps prepared statements bound to the original schema; after a column is renamed, a stale prepared statement can return cryptic "column does not exist" errors even though the application code looks fine. The fix is to either flush the pool's prepared statements after each migration or to disable transaction-mode prepared statement caching during migration windows. I generally have Claude Code generate a small post-migration script that issues DEALLOCATE ALL on every active connection.

Observing the Migration in Real Time

A migration that runs blind is a migration that fails silently. The three signals I always watch live during a production run are query latency at the application edge, lock wait time at the database, and replica lag at the read fleet. Together they reveal the difference between a migration that's progressing normally and one that's about to overload the system.

The PostgreSQL view pg_stat_activity is the single most useful real-time tool. A query like the one below surfaces sessions blocked on the migration and tells you whether to keep going or abort:

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_duration,
       LEFT(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_duration DESC NULLS LAST
LIMIT 20;

I ask Claude Code to generate a small terminal dashboard that polls this view every two seconds, color-coding rows by transaction duration. During a migration window I keep that dashboard open in one pane and the migration progress log in another. If a session goes red — meaning it's been blocked for more than thirty seconds — I have a decision to make in seconds, not minutes.

For replica lag, the equivalent on PostgreSQL is pg_last_xact_replay_timestamp(). A small wrapper script that emits a metric to your monitoring stack every five seconds is enough to catch lag spikes early. Claude Code generates this kind of glue code reliably; the trick is to keep the metric ingestion path independent of the database being migrated, so a database hiccup doesn't blind the dashboard right when you need it most.

Migration Culture — Habits That Compound

Most migration disasters I've seen, including my own 2 AM incident, were not caused by lack of knowledge. The right pattern was usually documented somewhere. The disasters happened because the engineer running the migration didn't have a habit that forced them to check.

Three habits compound the most over time. The first is writing the rollback before writing the forward migration. If the rollback is hard, that's information about the forward migration — usually that it's destroying data or violating Expand-Contract. Writing it first surfaces the problem early.

The second habit is announcing the migration in advance, even for small ones. A short post in a team channel that says "I'm running migration 0042 against production at 14:00 JST, expected duration 45 seconds, rollback in pinned thread" creates accountability and gives the rest of the team a chance to flag conflicts.

The third habit is treating the migration log like an artifact, not throwaway output. After the migration completes, I have Claude Code summarize what happened, what the lock times actually were, and whether anything needed manual intervention. That summary lives in the repo alongside the migration file. Six months later when someone runs into a similar pattern, the log is the most valuable reference they could ask for.

These habits look like overhead at first. They become invisible after a few weeks, and the migrations they protect against are exactly the kind that would have ended up as an incident report.

A Pre-Production Checklist

Here is the ten-item checklist I run through every time before applying a migration to production. Including it in your prompt to Claude Code is an effective way to keep nothing slipping through the cracks.

The first check is whether you've actually measured the migration on a test database with a comparable amount of data — not ten thousand rows, but ten million. The second is whether the predicted lock time is within your SLA. The third is whether the rollback procedure is written down in language a half-asleep on-call engineer can follow.

The fourth is whether replica-lag tuning and sleep tuning for any backfill have been finalized. The fifth is whether the application deploy sequence is written as a timeline that names which version is running at each Expand-Contract stage. The sixth is whether monitoring is ready: query latency, lock waits, and replica lag must all be on a dashboard during execution.

The seventh is whether stakeholders who care about the affected surface are notified ahead of time. The eighth is whether you have an emergency stop procedure already in your terminal — pg_cancel_backend(pid) ready to paste at a moment's notice. The ninth is whether a working log is set up for the operation; I create .claude/logs/migration-YYYYMMDD.md and have Claude Code record progress as the migration runs.

The tenth is whether a post-mortem is scheduled. Every production migration gets a post-mortem regardless of outcome, and asking Claude Code to draft the first version into _documents/postmortems/ makes the writeup happen instead of getting deferred forever.

If you pair this discipline with the broader operational guidance in the Claude Code Production Error Handbook, the same culture extends beyond migrations into every other risky production change.

For your first concrete step tomorrow, take a recent migration from your project and ask Claude Code, "if we rewrote this in Expand-Contract, what would change?" The ten minutes that takes will surface more weaknesses in your usual approach than a week of theoretical reading. That's where the muscle memory starts.