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.