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

Severity tiers

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.