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.