Tobias Keller·
Expand/contract migration plan with an idempotent backfill and a parity check that must return zero
Creates detailed migration plans for database, service, or infrastructure changes with rollback strategies, data consistency verification, and phased execution.
Zero-Downtime Migration Strategy
You are a Staff Engineer specializing in large-scale system migrations without downtime. Create a comprehensive migration plan.
**Migration Type**: {{migration_type}} (database migration, service replacement, cloud provider move, framework upgrade, data center migration)
**Current State**: {{current_state}} (detailed description of what's being migrated from)
**Target State**: {{target_state}} (detailed description of what's being migrated to)
**Constraints**: {{constraints}} (maximum downtime window, data volume, cross-dependency complexity, compliance requirements)
Create the migration plan:
1. **Migration Strategy Selection** - Big bang vs phased vs strangler fig vs blue-green vs canary with recommendation and justification
2. **Pre-Migration Checklist** - Prerequisites: backups, rollback scripts tested, monitoring configured, team on standby, communication plan
3. **Step-by-Step Execution Plan** - Numbered steps with: description, estimated duration, validation command, rollback procedure for that step
4. **Data Consistency Strategy** - Dual-write pattern, CDC (Change Data Capture), snapshot + incremental sync, consistency verification queries
5. **Rollback Plan** - Trigger conditions (what metric thresholds trigger rollback), rollback steps with timing, data reconciliation after rollback
6. **Testing Strategy** - Pre-migration testing, shadow traffic validation, canary analysis criteria, post-migration verification suite
7. **Communication Plan** - Stakeholder notification schedule, status update cadence, customer communication (if visible), post-mortem scheduling
8. **Runbook** - Exact commands to run at each step (SQL commands, CLI commands, API calls), expected output for each command
9. **Monitoring & Validation** - Key metrics to watch during migration, automated validation checks, manual verification checklist
10. **Risk Register** - Risk ID, description, probability (H/M/L), impact (H/M/L), mitigation, contingency plan for each risk
11. **Timeline** - Gantt-style phase breakdown with dependencies, critical path, buffer time, go/no-go decision points
12. **Post-Migration Activities** - Cleanup of old system, performance validation, cost comparison, lessons learned documentation
Include the actual rollback script structure and validation queries. Be specific with commands, not generic guidance.
Ergebnisse
## Zero-downtime migration: `users.email` to a normalized `email_address` table
**From:** single `email` column on `users` (12M rows). **To:** separate `email_address` table supporting multiple emails per user. **Constraint:** zero downtime, no lost writes.
**Strategy: expand/contract with dual-write** — the safe default for column-to-table moves.
**Execution plan**
| # | Step | Validation | Rollback |
|---|------|-----------|----------|
| 1 | Create `email_address` table (additive, no lock) | table exists | `DROP TABLE` |
| 2 | Deploy app writing to **both** old column and new table | both populated on new signups | flag off → old path only |
| 3 | Backfill existing rows in batches of 5k | counts match | re-run, idempotent |
| 4 | Flip reads to new table behind a flag | parity check passes | flag back to old |
| 5 | Stop writing the old column | new-table-only writes confirmed | re-enable dual-write |
| 6 | Drop `users.email` (separate migration) | — | restore from backup |
**Backfill (idempotent, resumable):**
```sql
INSERT INTO email_address (user_id, address, is_primary)
SELECT id, email, true FROM users u
WHERE NOT EXISTS (SELECT 1 FROM email_address e WHERE e.user_id = u.id)
ORDER BY id LIMIT 5000;
```
**Consistency check** before each cutover — must return zero:
```sql
SELECT count(*) FROM users u
LEFT JOIN email_address e ON e.user_id = u.id AND e.is_primary
WHERE u.email IS DISTINCT FROM e.address;
```
**Rollback trigger:** any parity-check drift or error-rate spike > 1% reverts the read flag instantly — old column is still authoritative until step 5. The destructive `DROP` waits a full release cycle after step 5 so we never burn the bridge early.
Modell: Claude Opus 4
15 Likes3 SavesScore: 9
1 Kommentar
Priya Nair·
Running this system design prompt on a real ticket right now.