Zero-Downtime Migrations skill for Claude Code

A migration is safe when every intermediate state works with both the old and the new application code, no statement holds a blocking lock for more than a few seconds, large data changes run in throttled batches outside the DDL, and there is a written plan for going back. “It ran fine on staging” is not evidence; staging has no traffic and no long-running transactions.

Ground rules

• Old code and new code run side by side during every deploy (rolling deploys, workers, cron jobs, mobile clients). Each migration must be compatible with both.
• Schema changes and data backfills are separate steps. Never backfill millions of rows inside the DDL migration’s transaction.
• Every DDL statement sets lock_timeout so a blocked ALTER fails fast instead of queueing and blocking all traffic behind it.
• Destructive steps (DROP COLUMN, DROP TABLE, type narrowing) ship in a later deploy than the code that stops using them, and only after the user confirms.

Workflow

- [ ] 1. Classify each change: additive, destructive, or rewriting
- [ ] 2. Check table size, traffic, and long-running transactions
- [ ] 3. Split into expand -> migrate data -> contract phases
- [ ] 4. Write each statement in its lock-safe form (references/lock-matrix.md)
- [ ] 5. Order deploys: which ships first, code or schema
- [ ] 6. Write the rollback / forward-fix plan per phase
- [ ] 7. Lint, rehearse on a production-sized copy, then run with monitoring

1-2. Classify and measure

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS size,
       (SELECT reltuples::bigint FROM pg_class WHERE oid = 'orders'::regclass) AS est_rows;

-- Long transactions that would block DDL lock acquisition
SELECT pid, now() - xact_start AS xact_age, state, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '1 minute'
ORDER BY xact_age DESC;

Change	Category	Risk
Add nullable column, add column with constant default (PG11+)	Additive, metadata-only	Low (brief ACCESS EXCLUSIVE)
Create index	Additive	High unless CONCURRENTLY
Add NOT NULL / CHECK / FK on existing table	Additive constraint	High unless NOT VALID + VALIDATE
Rename column/table	Breaking for old code	Expand/contract required
Change column type	Usually rewrites table	High; new column + backfill
Drop column/table	Destructive	Needs code removed first
Add column with volatile default (gen_random_uuid(), clock_timestamp())	Rewrite	High; add nullable, backfill, then set default

3. Expand / contract

Every breaking change becomes a sequence of individually safe deploys. Example: rename users.fullname to users.display_name.

1. Expand (migration): add display_name nullable.
2. Dual write (code deploy): app writes both columns, still reads fullname.
3. Backfill (job): copy fullname into display_name in batches.
4. Switch reads (code deploy): read display_name, keep dual writes.
5. Constrain (migration): add NOT NULL safely (below) if required.
6. Stop writing old (code deploy): remove fullname from code and ORM model. With Rails/Django/Prisma/Drizzle, the model must stop referencing the column before it is dropped, or inserts fail with “column does not exist”.
7. Contract (migration, after user confirmation): drop fullname.

A trigger can replace step 2 when many writers exist that you cannot change at once:

CREATE OR REPLACE FUNCTION users_sync_display_name() RETURNS trigger AS $$
BEGIN
  NEW.display_name := COALESCE(NEW.display_name, NEW.fullname);
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER users_sync_display_name BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_display_name();

4. Lock-safe statement forms (Postgres)

Always start DDL sessions with:

SET lock_timeout = '5s';
SET statement_timeout = '15min';   -- for VALIDATE/CONCURRENTLY steps; shorter for metadata-only

Retry on lock timeout with backoff (most migration runners can be wrapped in a retry loop; otherwise rerun the step).

Add NOT NULL to a large table

ALTER TABLE users ADD CONSTRAINT users_display_name_not_null
  CHECK (display_name IS NOT NULL) NOT VALID;           -- instant
ALTER TABLE users VALIDATE CONSTRAINT users_display_name_not_null;  -- scans, allows writes
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;           -- PG12+ uses the valid CHECK, no scan
ALTER TABLE users DROP CONSTRAINT users_display_name_not_null;

PG18 also allows ADD CONSTRAINT ... NOT NULL ... NOT VALID directly; the CHECK route works on all supported versions.

Add a foreign key

ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

Index orders.customer_id concurrently first.

Add a unique constraint

CREATE UNIQUE INDEX CONCURRENTLY users_email_key_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_key_idx;

Change a column type: avoid in place for big tables (most type changes rewrite the table under ACCESS EXCLUSIVE). Safe exceptions with no rewrite: varchar(n) to larger varchar(m) or to text; dropping a varchar length limit. For int to bigint: add a new bigint column, dual-write via trigger, backfill, swap in a short transaction, then contract.

Index operations: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX ... CONCURRENTLY. These cannot run inside a transaction block; mark that migration non-transactional in the tool.

The full operation-to-lock table and safe alternatives are in references/lock-matrix.md. Read it for any operation not shown above.

Backfills

-- Run from a job/script, not the migration. Repeat until 0 rows updated.
UPDATE users
SET display_name = fullname
WHERE id IN (
  SELECT id FROM users
  WHERE display_name IS NULL AND fullname IS NOT NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
);

• Batch size: aim for each batch under ~1 s; 1k-10k rows is typical. Commit each batch.
• Sleep between batches (100-500 ms) and watch replica lag; pause if lag exceeds a few seconds.
• Make it idempotent and resumable (the WHERE display_name IS NULL predicate does this).
• For very large tables, walk the PK range (WHERE id BETWEEN $start AND $start + 10000) so each batch is an index range scan, rather than repeatedly searching for NULLs.
• Run ANALYZE on the table afterwards.

5. Deploy ordering

Change	Order
Add table/column/index	Migration first, then code that uses it
Remove column/table	Code that stops using it first, then migration (later deploy)
Rename	Full expand/contract across at least three deploys
Add NOT NULL / constraint	Code that always writes the value first, backfill, then constraint
Tighten enum/CHECK values	Code stops writing old values, backfill, then constraint

6. Rollback plan

• Prefer forward fixes. A down migration that drops a column added an hour ago destroys data written in that hour.
• Expand steps are trivially reversible (drop the unused new object). Contract steps are not; take a backup or keep the data (rename to _deprecated_<date> and drop later) if the user wants a way back.
• Write per phase: “If X fails, run Y; data impact Z.”

7. Lint and rehearse

• Lint SQL migrations with squawk if available (squawk path/to/migration.sql), or Rails strong_migrations, Django django-pg-zero-downtime-migrations. Treat findings as blocking unless justified in the PR.
• Inspect ORM-generated SQL before running it: prisma migrate dev --create-only, drizzle-kit generate, python manage.py sqlmigrate app 0042, rails db:migrate with structure.sql diff. ORMs happily emit table rewrites and non-concurrent indexes.
• Rehearse on a restored production-sized copy and time each step.
• During execution watch: lock waits (pg_locks with NOT granted), replica lag, p95 latency, error rate.

MySQL notes (8.0+)

• Specify the algorithm so MySQL errors instead of silently copying: ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; or ALGORITHM=INPLACE, LOCK=NONE.
• INSTANT covers adding/dropping columns (8.0.29+ any position) and some default changes; type changes generally require a copy.
• For large-table changes that need a copy, use gh-ost or pt-online-schema-change.
• Metadata locks: an ALTER waits for open transactions on the table and blocks new queries behind it. Set lock_wait_timeout low (e.g. 5) for the DDL session.

Gotchas

• A metadata-only ALTER still needs ACCESS EXCLUSIVE briefly. Behind one long-running query, it queues, and every subsequent query on the table queues behind it. That is why lock_timeout is mandatory.
• ADD COLUMN ... DEFAULT now() is fine (now() is stable within the statement), but DEFAULT clock_timestamp() or gen_random_uuid() rewrites the table.
• Running migrations automatically on app start with multiple replicas causes races; run them once from a release step or use the tool’s advisory locking.
• Dropping a column used by a view, function, or replication publication fails or cascades; check dependencies with \d+ and pg_depend first.
• Enum ALTER TYPE ... ADD VALUE cannot be used in the same transaction that added it (before PG12 it could not run in a transaction at all).
• Logical replication / CDC (Debezium) consumers may break on renames and type changes; coordinate with their owners.

Output: migration plan

## Migration plan: users.fullname -> users.display_name (users: 38M rows, 21 GB)

| Phase | Deploy | Statement / change | Lock | Rollback |
|---|---|---|---|---|
| 1 Expand | M1 | ADD COLUMN display_name text | brief ACCESS EXCLUSIVE, lock_timeout 5s | DROP COLUMN (no data yet) |
| 2 Dual write | C1 | write both, read fullname | - | revert C1 |
| 3 Backfill | job | 5k-row batches, 200 ms sleep, est. 2h | row locks only | stop job |
| 4 Read switch | C2 | read display_name | - | revert C2 |
| 5 Constrain | M2 | CHECK NOT VALID -> VALIDATE -> SET NOT NULL | SHARE UPDATE EXCLUSIVE for validate | DROP CONSTRAINT |
| 6 Stop old writes | C3 | remove fullname from model | - | revert C3 |
| 7 Contract | M3 (needs approval) | DROP COLUMN fullname | brief ACCESS EXCLUSIVE | restore from backup only |

Rehearsed on restored snapshot 2026-10-12: M1 40 ms, M2 validate 6 min, backfill 1h52m.