Db Migrate

Deep database migration workflow—expand/contract, backward-compatible deploys, backfills, locking risks, and verification. Use when changing production schemas safely with zero or low downtime.

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.