# Migration Strategy

> Ship schema changes with zero downtime using the expand-contract pattern — never rename/drop in one step, backfill safely, keep old and new code coexisting during deploy. Use before any schema change on a live database. Not for designing the schema (use schema-design) or wiring migrations into CI (use cicd-pipeline).

- Skill: `jaykim88/migration-strategy` (Agent Skill)
- Install (CLI): `npx skillmds@latest add jaykim88/migration-strategy`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jaykim88/migration-strategy/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- License: MIT
- Author: JayKim88 (https://skillmd.com/u/jaykim88)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/jaykim88/migration-strategy

---


# Migration Strategy

## Purpose
Change a live database schema without downtime or data loss by never doing a destructive change in a single step — expand first, migrate, then contract — so old and new application versions coexist safely during rollout.

**Universal** — the expand-contract (parallel-change) pattern and "additive-first, destructive-later" rule apply to any relational DB and migration tool.

## Procedure

1. **Never rename, drop, or retype in one deploy**
   - The running old code expects the old schema; a one-step destructive change breaks it during the rollout window
   - Use expand → migrate → contract across multiple deploys

2. **EXPAND — additive only (deploy 1)**
   - Add the new column/table (nullable, no constraint yet)
   - Deploy app code that DUAL-WRITES (writes both old and new) and reads old
   - Old and new code both work against this schema

3. **MIGRATE — backfill (between deploys)**
   - Backfill new column from old data in CHUNKS (not one giant transaction — see `transaction-management`)
   - Throttle to avoid lock/IO storms on a live DB
   - Verify backfill completeness before proceeding

4. **Switch reads (deploy 2)**
   - Deploy app code that reads NEW, still dual-writes
   - Add NOT NULL / constraints now that data is backfilled (use `NOT VALID` then `VALIDATE CONSTRAINT` to avoid long locks)

5. **CONTRACT — remove old (deploy 3)**
   - Stop writing old; drop the old column/table
   - Only after confirming nothing reads it (grep + logs/metrics)

6. **Safe DDL on Postgres**
   - `CREATE INDEX CONCURRENTLY` (no table lock)
   - `ADD COLUMN` with no default = fast; with volatile default on large table = rewrite (avoid)
   - `ALTER ... SET NOT NULL` via `CHECK ... NOT VALID` → `VALIDATE` to avoid full-table lock

7. **Validate (validation loop)**
   - Run the migration against a production-sized copy; measure lock duration
   - If a step locks the table > acceptable window → rewrite using the concurrent/non-blocking variant and re-test
   - Verify rollback: each step must be reversible until contract

## Anti-patterns

| ❌ Anti-pattern | ✅ Correct |
|---|---|
| `ALTER TABLE RENAME COLUMN` in one deploy | Expand (add new) → backfill → contract (drop old) |
| `ADD COLUMN NOT NULL DEFAULT <volatile>` on big table | Add nullable → backfill → set NOT NULL via NOT VALID/VALIDATE |
| `CREATE INDEX` (locks table) | `CREATE INDEX CONCURRENTLY` |
| Backfill in one transaction | Chunked, throttled backfill |
| Drop old column same release as adding new | Drop only after reads migrated + verified |

## Severity tiers

| Tier | Examples | Action SLA |
|---|---|---|
| **Critical** | Destructive single-step change on a live table (rename/drop/retype in one deploy); dropping a column still being read; `ADD COLUMN NOT NULL DEFAULT <volatile>` rewriting a large table under an exclusive lock → outage or data loss | Block release; fix immediately |
| **Major** | Backfill in one giant transaction (lock/replication lag); `CREATE INDEX` without `CONCURRENTLY` on a large table; lock duration never measured on a prod-sized copy | Fix this sprint |
| **Minor** | Backfill not throttled on a small table; missing per-step rollback note; expand/contract split into more deploys than necessary | Schedule within 2 sprints |

## Stop & Ask (AI must pause for user approval)

- **Before applying any expand-contract step to a live database** — confirm lock-duration was measured on a prod-sized copy
- **Before the CONTRACT step** (dropping an old column/table) — verify reads are migrated AND no caller references the old name (grep + observability)
- **Before a backfill of > 100k rows** — confirm chunking, throttle, and replication-lag tolerance are in place
- Never auto-apply a destructive migration; explicit user approval is required

## Completion Criteria
- [ ] No destructive change in a single deploy
- [ ] Dual-write during transition
- [ ] Backfill chunked + verified complete
- [ ] DDL uses non-blocking variants (CONCURRENTLY / NOT VALID→VALIDATE)
- [ ] Lock duration measured on prod-sized copy
- [ ] Each step reversible until contract

## Output
- **Migration files**: ordered expand → backfill → switch → contract
- **Backfill script**: chunked + throttled
- **Migration plan doc**: deploy sequence + rollback per step
- **Commit format**: `feat(migration): expand <table> for <change>` / `chore(migration): contract — drop old <column>`

## Implementation

### TypeScript + Prisma + Postgres (default)
- `prisma migrate dev` for dev; `prisma migrate deploy` in CI
- Prisma doesn't auto-do expand-contract — author the steps manually as separate migrations
- Raw SQL for `CREATE INDEX CONCURRENTLY` (Prisma can't run it inside a transaction — use `--create-only` then edit)
- Backfill: a one-off script or a `background-jobs` task, chunked

### Other stacks
- **Python**: Alembic — same expand-contract discipline; `op.create_index(postgresql_concurrently=True)`
- **Go**: `golang-migrate` — author up/down per step
- **Universal**: expand-contract is tool-agnostic; the Postgres-specific locking concerns (CONCURRENTLY, NOT VALID) map to other DBs' online-DDL features (MySQL `ALGORITHM=INPLACE`)

## Related skills
- `schema-design` — the migration applies the designed schema change
- `cicd-pipeline` — migrations run as an explicit gate in the deploy pipeline
- `transaction-management` — backfills must be chunked, not one giant transaction

## Reference
- **Key insight encoded**: Never rename/remove in one step — expand first (add new, dual-write), backfill, then contract later, so old and new code coexist during deploy for zero downtime.