migration-risk-analyzer

迁移风险分析器

系统评估数据库迁移风险：解析 DDL/DML 操作，分类锁类型和持续时间，估算停机时间，制定回滚策略，识别不可逆更改，并通过迁移前后验证查询提供部署建议。

参考文件

文件	内容	加载时机
references/lock-matrix.md	PostgreSQL、MySQL 的操作与锁类型映射	始终
references/safe-patterns.md	在线 DDL 模式、零停机迁移技术	需要降低风险时
references/rollback-templates.md	常见 DDL 操作的回滚脚本	请求回滚策略时
references/validation-queries.md	迁移前后验证 SQL 模板	始终

前置条件

• 迁移 SQL 或迁移文件（Alembic、Django、Flyway 等）
• 目标数据库引擎（PostgreSQL、MySQL）
• 受影响表的大致大小（用于估算持续时间）

工作流

阶段 1：解析迁移

从迁移脚本中提取所有操作：

1. DDL 操作 — CREATE TABLE、ALTER TABLE（ADD/DROP/MODIFY COLUMN、ADD/DROP INDEX）、DROP TABLE、RENAME TABLE
2. DML 操作 — 对现有数据执行 UPDATE、INSERT、DELETE
3. 索引操作 — CREATE INDEX、DROP INDEX、REINDEX
4. 约束操作 — ADD/DROP FOREIGN KEY、ADD/DROP CHECK、ADD/DROP NOT NULL

阶段 2：评估锁风险

确定每项操作的锁类型和影响：

锁级别	影响	示例
无锁	无影响	CREATE TABLE、CREATE INDEX CONCURRENTLY (PG)
共享锁	阻止写入，允许读取	CREATE INDEX（非并发）
排他锁	阻止所有访问	ALTER TABLE ADD COLUMN（MySQL < 8.0）、DROP TABLE
行级锁	仅阻止受影响的行	带 WHERE 子句的 UPDATE

请考虑：

• 表大小（锁定 10 行的表影响很小；锁定 1 亿行的表则影响重大）
• 并发查询模式（高写入率的 OLTP 与批量查询的 OLAP）
• 锁超时设置

阶段 3：估算持续时间

根据操作类型和表大小进行估算：

操作	小型表（<100K）	中型表（100K-10M）	大型表（>10M）
ADD COLUMN（可为空）	< 1s	< 1s	< 1s（PG）/ 数分钟（MySQL）
ADD COLUMN（带默认值）	< 1s	数秒	数分钟（重写表）
CREATE INDEX	< 1s	数秒	数分钟至数小时
ADD NOT NULL	数秒	数分钟	数小时（全表扫描）
回填 UPDATE	数秒	数分钟	数小时

阶段 4：设计回滚

针对每项操作，确定其可逆性：

操作	可逆	回滚
ADD COLUMN	是	DROP COLUMN
DROP COLUMN	否	数据会丢失
ADD INDEX	是	DROP INDEX
DROP TABLE	否	数据会丢失
RENAME COLUMN	是	改回原名
ALTER TYPE	有时	可能会丢失精度
UPDATE data	有时	仅当保留旧值时才可回滚

对于不可逆操作，建议采用备份策略。

阶段 5：生成报告

生成风险评估并提出部署建议。

输出格式

## 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
```sql
-- Rollback for operation 1: {description}
{rollback SQL}

不可逆操作

• {operation} — 不可逆。缓解措施：

  -- Backup before migration
  {backup SQL}
  

迁移前检查清单

• 已完成数据库备份
• 已在预发布环境中测试回滚脚本
• 已确认降低流量（如有需要）
• 已配置监控和告警
• 已通知相关人员
• 已根据锁等待调整连接池大小

迁移后验证

-- Verify structural changes
{validation queries}

-- Verify data integrity
{integrity checks}

部署建议

策略： {Online | Low-Traffic Window | Maintenance Window} 预计停机时间： {time or "None with proper execution"} 回滚时间： {time} 风险缓解： {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.
2. **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.
3. **Irreversible means irreversible.** DROP COLUMN destroys data. No amount of
   rollback scripting recovers it. Flag every irreversible operation prominently.
4. **Test the rollback.** Rollback scripts must be tested in staging before the
   migration runs in production. Untested rollback is no rollback.
5. **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

| Problem | Resolution |
|---------|------------|
| Database engine not specified | Ask. Lock behavior differs significantly between engines. |
| Table sizes unknown | Analyze without duration estimates. Flag that estimates require row counts. |
| ORM migration format (not raw SQL) | Parse the ORM migration file. Translate operations to SQL equivalents for analysis. |
| Migration has data-dependent logic | Flag conditional operations. Risk depends on data state at migration time. |
| Multiple migrations in sequence | Analyze each independently and as a group. Cross-migration lock accumulation is a risk. |

## 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

## Rationalizations

| Rationalization | Reality |
|---|---|
| "It's backwards compatible" | Backwards compatible at the schema level doesn't mean backwards compatible at the application level — query plans, ORM mappings, and application code all interact |
| "We can roll back" | Rollback is not free — data written after migration may not survive rollback; DROP COLUMN has no rollback without backup |
| "It's a small table" | Table size is one factor — lock duration, concurrent write rate, and replication lag matter more than row count |
| "We've run this migration type before" | Past success doesn't predict future success — different data distribution, different load, different constraints |
| "Downtime window is long enough" | Estimate based on dev data, not production — migration on 10k rows takes seconds; on 50M rows with indexes, it takes hours |
| "The ORM handles it" | ORMs generate SQL, they don't guarantee safety — `ALTER TABLE` locking behavior is engine-specific and ORM-opaque |

## Red Flags

- No estimate of migration duration based on production data volume
- No rollback plan or rollback plan that doesn't account for data written post-migration
- Analyzing migration SQL without checking the current table size and write rate
- No consideration of replication lag in multi-replica setups
- Assuming zero downtime without verifying lock behavior for the specific DDL operation
- Skipping index analysis — adding an index on a large table can lock writes for minutes to hours

## Verification

- [ ] Production table sizes and row counts documented for all affected tables
- [ ] Lock behavior identified for each DDL statement (exclusive lock, no lock, etc.)
- [ ] Migration duration estimated using production-scale data, not dev fixtures
- [ ] Rollback plan documented with specific steps and data preservation guarantees
- [ ] Concurrent write impact assessed — what happens to in-flight transactions during migration
- [ ] Replication lag impact assessed for multi-replica configurations