DB Migrations
Production schema changes fail when old and new code disagree during rollout. Prefer expand/contract: add compatible changes first, remove old shapes later.
When to Offer This Workflow
Trigger conditions:
- ALTER TABLE in production; large table rewrites
- Blue/green deploys coupled to schema state
- Need zero-downtime or low-downtime migrations
Initial offer:
Use six stages: (1) classify change, (2) expand phase, (3) backfill & dual-write, (4) flip reads/writes, (5) contract phase, (6) verify & rollback). Confirm database engine (PostgreSQL, MySQL, etc.).
Stage 1: Classify Change
Goal: Additive vs destructive; lock risk (full table rewrite vs instant metadata change).
Exit condition: Migration labeled as expand or contract with risk notes.
Stage 2: Expand Phase
Goal: Add nullable columns or new tables without breaking currently deployed code.
Practices
- Avoid DEFAULT clauses that lock large tables badly on some engines (use phased backfill instead)
Stage 3: Backfill & Dual-Write
Goal: Throttled batch backfill; dual-write old and new representations during transition when needed.
Stage 4: Flip Reads/Writes
Goal: Deploy code that reads new columns only after backfill completes; use feature flags for staged rollout.
Stage 5: Contract Phase
Goal: Drop old columns only after no code references them (search repo, logs, feature usage).
Stage 6: Verify & Rollback
Goal: Monitor errors, slow queries, replication lag; rollback = redeploy previous app version + avoid destructive steps until stable.
Final Review Checklist
- Change classified; expand/contract path clear
- Additive migrations before dependent code
- Backfill throttled and verified
- Read/write cutover sequenced with flags
- Contract only after references gone
- Monitoring and rollback tested
Tips for Effective Guidance
- Long transactions on migrations can cause outages—chunk work.
- Use online schema tools (pt-online-schema-change, etc.) when appropriate.
Handling Deviations
- SQLite/embedded engines have different locking—validate per engine.