migration-risk-analyzer（迁移风险分析器）

Migration Risk Analyzer

Systematic risk assessment for database migrations: parse DDL/DML operations, classify lock types and durations, estimate downtime, design rollback strategies, identify irreversible changes, and produce deployment recommendations with pre/post validation queries.

Reference Files

Prerequisites

• The migration SQL or migration file (Alembic, Django, Flyway, etc.)
• Target database engine (PostgreSQL, MySQL)
• Approximate table sizes for affected tables (for duration estimation)

Workflow

Phase 1: Parse Migration

Extract all operations from the migration script:

1. DDL operations — CREATE TABLE, ALTER TABLE (ADD/DROP/MODIFY COLUMN, ADD/DROP INDEX), DROP TABLE, RENAME TABLE
2. DML operations — UPDATE, INSERT, DELETE on existing data
3. Index operations — CREATE INDEX, DROP INDEX, REINDEX
4. Constraint operations — ADD/DROP FOREIGN KEY, ADD/DROP CHECK, ADD/DROP NOT NULL

Phase 2: Assess Lock Risk

For each operation, determine the lock type and impact:

Consider:

• Table size (locks on 10-row tables are negligible; locks on 100M-row tables are critical)
• Concurrent query patterns (OLTP with high write rates vs. OLAP with batch queries)
• Lock timeout settings

Phase 3: Estimate Duration

Estimate based on operation type and table size:

Phase 4: Design Rollback

For each operation, determine reversibility:

For irreversible operations, recommend backup strategies.

Phase 5: Generate Report

Produce a risk assessment with deployment recommendation.

Output Format

## Migration Risk Analysis

### Summary
- **Operations:** {N} DDL, {M} DML
- **Tables affected:** {list with row counts}
- **Overall risk:** {High | Medium | Low}
- **Estimated duration:** {range}
- **Requires downtime:** {Yes | No}

### Operation Risk Table

| # | Operation | Risk | Lock Type | Est. Duration | Reversible |
|---|-----------|------|-----------|---------------|------------|
| 1 | {SQL operation} | {High/Med/Low} | {lock type} | {time} | {Yes/No} |

### Lock Analysis
- **Exclusive locks:** {list of operations that block all access}
- **Maximum lock duration:** {estimated time}
- **Affected queries:** {types of queries that will be blocked}

### Rollback Strategy

#### Reversible Operations

-- Rollback for operation 1: {description} {rollback SQL}

#### Irreversible Operations

- **{operation}** — IRREVERSIBLE. Mitigation: `-- Backup before migration {backup SQL}`

### Pre-Migration Checklist

- Database backup completed
- Rollback scripts tested in staging
- Traffic reduction confirmed (if needed)
- Monitoring and alerting configured
- Stakeholders notified
- Connection pool sized for lock wait

### Post-Migration Validation

-- Verify structural changes {validation queries}

-- Verify data integrity {integrity checks}

### Deployment Recommendation

**Strategy:** {Online | Low-Traffic Window | Maintenance Window} **Estimated downtime:** {time or "None with proper execution"} **Rollback time:** {time} **Risk mitigation:** {specific recommendations}

Calibration Rules

1. Assume large tables. If table size is unknown, assume it's large enough for

locks to matter. Overestimating risk is safer than underestimating.

1. Engine-specific analysis. PostgreSQL and MySQL handle DDL very differently.

PostgreSQL can add nullable columns without table rewrite; MySQL often cannot. Always target the specific engine.

1. Irreversible means irreversible. DROP COLUMN destroys data. No amount of

rollback scripting recovers it. Flag every irreversible operation prominently.

1. Test the rollback. Rollback scripts must be tested in staging before the

migration runs in production. Untested rollback is no rollback.

1. Sequence matters. The order of operations affects lock duration. Adding a

column then backfilling then adding NOT NULL is safer than adding a NOT NULL column with a default.

Error Handling

When NOT to Analyze

Push back if:

• The migration is for a development/staging database — risk analysis is for production
• The migration only creates new tables (no ALTER, no existing data) — low risk by definition
• The user wants migration execution, not analysis — this skill assesses risk, it doesn't run migrations