SuperAPI AI Directory

migration-rollout

General & other

View repository

Updated 10-16 09:31

About this skill

Migration & Rollout

References: Martin Fowler's Refactoring Databases Expand/Contract pattern, Sam Newman's Building Microservices, GitHub gh-ost, Percona pt-online-schema-change, Stripe / Shopify large-table migration practices.

Applicable Scenarios

  • Adding, modifying, deleting tables and fields
  • Large-table backfill, data repair, historical data cleansing
  • Dual-write of old and new fields, canary cutover, compatibility window
  • Adding or dropping indexes, constraints, foreign keys
  • Cross-database data migration (database splitting / merging / cross-datacenter)
  • Output rollout plan, rollback plan, and verification checklist

Core Principles

1. 生产迁移不是单条 DDL
   先兼容 → 再切换 → 最后清理
  1. Back up first, then change, verify last Any irreversible operation must be broken into stages

  2. Expand/Contract pattern (core idea of database refactoring) Expand: add new fields, old and new coexist Cutover: dual-write, backfill, dual-read Contract: drop old fields

  3. Decouple application code and schema releases Schema changes should be deployable independently of code

  4. Large table = database ≥ 1GB or ≥ 10 million rows Large-table migration must account for table locking / replication lag / disk

  5. Canary before full rollout For critical changes: 5% → 50% → 100%

  6. Rollback must always be executable Irreversible operations must have data backups


## Expand / Contract 模式(重命名列示例)

```text
目标:把 user.username 重命名为 user.display_name

阶段 1:Expand(兼容变更)
  ALTER TABLE users ADD COLUMN display_name varchar(100);
  -- 老代码继续读写 username
  -- 新代码同时写两列

阶段 2:双写
  -- 部署新代码
  -- 触发器或应用层同步:username → display_name
  CREATE TRIGGER sync_username_display_name
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_names();

阶段 3:回填
  -- 分批回填历史数据
  UPDATE users SET display_name = username
  WHERE display_name IS NULL AND id BETWEEN ? AND ?;

阶段 4:切换读
  -- 应用代码改读 display_name
  -- 持续观察一段时间(一周)

阶段 5:Contract(清理)
  -- 移除触发器
  DROP TRIGGER sync_username_display_name ON users;
  -- 应用停写 username
  -- 删除旧列
  ALTER TABLE users DROP COLUMN username;

Standard Migration Stages

1. 现状确认
   □ 表大小(行数 + 占空间)
   □ 写入频率(QPS)
   □ 依赖服务(哪些应用读 / 写)
   □ 备份状态(最近备份时间 + 已验证恢复)
   □ 复制拓扑(主从延迟 / 半同步)
   ↓
2. 兼容变更(Expand)
   □ 新增字段 / 表 / 索引
   □ 不破坏旧代码
   □ 默认 NULL 或安全默认值
   ↓
3. 回填
   □ 分批
   □ 限速
   □ 幂等(可重试)
   □ 可观测进度
   □ 可暂停
   ↓
4. 双读 / 双写或灰度切换
   □ 验证新旧数据一致
   □ 监控错误率
   □ 灰度比例:5% / 50% / 100%
   ↓
5. 清理(Contract)
   □ 删除旧字段 / 旧索引 / 旧逻辑
   □ 至少灰度全量后等 1 周
   ↓
6. 回滚和复盘
   □ 记录风险与经验

Dangerous DDL Operations Checklist

PostgreSQL

Operation Lock Level Table Lock? Alternative
ADD COLUMN(NULL) ACCESS EXCLUSIVE Very short Safe
ADD COLUMN ... DEFAULT(PG 11+ constant) ACCESS EXCLUSIVE Very short Safe
ADD COLUMN NOT NULL DEFAULT ? ACCESS EXCLUSIVE Very short (PG 11+) Older versions require multiple steps
ALTER COLUMN TYPE ACCESS EXCLUSIVE Rewrites entire table Add new column + dual-write + drop old
ADD CONSTRAINT NOT NULL ACCESS EXCLUSIVE Full table scan NOT VALID + VALIDATE
ADD FOREIGN KEY SHARE ROW EXCLUSIVE Long NOT VALID + VALIDATE
CREATE INDEX SHARE Blocks writes CONCURRENTLY
DROP TABLE / DROP COLUMN ACCESS EXCLUSIVE Short Safe
RENAME COLUMN ACCESS EXCLUSIVE Short Expand/Contract

MySQL

Operation InnoDB Online DDL? Alternative
ADD COLUMN(5.6+) ✅ INPLACE Safe
DROP COLUMN(5.6+) ✅ INPLACE Safe
ALTER COLUMN TYPE(Compatible) ✅ INPLACE / 8.0 INSTANT Mostly OK
ALTER COLUMN TYPE(Incompatible) ❌ COPY gh-ost / pt-osc
ADD INDEX(5.6+) ✅ INPLACE Safe
RENAME INDEX(5.7+) ✅ INSTANT Safe
OPTIMIZE TABLE ❌ COPY (pre-5.6) gh-ost

Migration Plan Table

Stage Operation SQL Preconditions Verification Method Rollback Method Owner Estimated Duration
1 Compatible Change Add nullable field ALTER TABLE orders ADD ... Backup completed Schema check Drop field (when no writes) DBE 5 minutes
2 Dual-Write Deploy new code - Stage 1 completed Monitor error rate Roll back deployment Dev 10 minutes
3 Backfill Batched UPDATE See script Phase 2 stable for 24h COUNT validation No rollback (idempotent) DBE 2 hours
4 Switch reads Deploy code that reads the new column - Phase 3 complete Business monitoring Rollback deployment Dev 10 minutes
5 etc. observation - - - - - - 1 week
6 Cleanup Drop old column ALTER TABLE ... DROP COLUMN Phase 5 no issues Schema check Backup and restore DBE 5 minutes

Large table migration tool

PostgreSQL

1. CREATE INDEX CONCURRENTLY
   - 不锁表
   - 失败留 INVALID 索引

2. pg_repack
   - 在线重整表(消除膨胀)
   - 触发器 + 副本

3. 内置:ADD COLUMN ... DEFAULT 常量(PG 11+)
   - 不重写表
   - 元数据级

MySQL

1. gh-ost(GitHub 出品)
   - 无触发器
   - 复制历史写入到影子表
   - 主从一致

2. pt-online-schema-change(Percona)
   - 触发器 + 影子表
   - 较老但稳定

3. 内置 ALTER TABLE ... ALGORITHM=INPLACE
   - 5.6+ 大部分 ALTER 已在线

Backfill strategy

Batch backfill

-- 错:一次性回填,锁表
UPDATE orders SET source = 'unknown' WHERE source IS NULL;

-- 对:分批 + 限速
DO $$
DECLARE
  batch_size INT := 1000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE orders SET source = 'unknown'
    WHERE id IN (
      SELECT id FROM orders WHERE source IS NULL LIMIT batch_size
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);  -- 限速
  END LOOP;
END $$;

Observable progress

-- 单独表跟踪进度
CREATE TABLE migration_progress (
  job_name varchar PRIMARY KEY,
  total_rows bigint,
  processed_rows bigint DEFAULT 0,
  status varchar DEFAULT 'running',
  started_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

Idempotency

-- 必须可重跑
UPDATE orders SET status = 'pending'
WHERE status IS NULL  -- 幂等条件
  AND id BETWEEN ? AND ?;

Rollback plan design

Three-layer rollback

代码回滚(最快):
  应用回滚到上一版本
  耗时:5~10 分钟
  适用:代码 Bug,schema 兼容时

Schema 回滚(次之):
  执行反向 DDL(DROP COLUMN / DROP INDEX)
  耗时:5~30 分钟
  适用:schema 错误

数据回滚(最慢):
  从备份恢复(point-in-time recovery)
  耗时:1 小时 ~ 数小时
  适用:数据损坏 / 删错数据

Rollback checklist

□ 回滚触发条件清晰(错误率 > X% / P99 > Y)
□ 回滚步骤可执行
□ 回滚执行人和窗口
□ 回滚后验证方式
□ 数据恢复路径(备份 + 复制)
□ 客户沟通模板(如需)

Irreversible operations (backup required)

DROP TABLE / DROP COLUMN
DELETE / TRUNCATE 大表
DROP INDEX(重建慢)
ALTER COLUMN TYPE(精度丢失)
迁移到不兼容数据库

Canary strategy

Phase 1:金丝雀(5%)
  - 1 个节点 / 1 个租户 / 1% 用户
  - 持续 1 小时~1 天
  - 监控核心指标

Phase 2:扩大(50%)
  - 多节点 / 多租户 / 50% 用户
  - 持续 1~3 天
  - 业务方确认

Phase 3:全量(100%)
  - 全部
  - 持续 1 周观察
  - 进入清理阶段

Supporting templates

  • templates/migration-plan-template.md — Complete migration plan (background + phase table + validation + rollback + risks + to be confirmed)
  • templates/rollback-checklist-template.md — Rollback checklist (trigger conditions / steps / validation / communication)

Quality self-check

□ 区分空库迁移和生产存量迁移
□ 评估锁表、长事务、磁盘、复制延迟
□ 有备份点和恢复验证
□ 回填分批、幂等、可重试、可暂停
□ 进度可观测
□ 灰度、监控、回滚条件清晰
□ 与后端发布顺序明确
□ 标注不可逆操作
□ 回滚永远可执行
□ 大表用 CONCURRENTLY / Online DDL / gh-ost
□ Expand/Contract 模式(重命名 / 类型变更)
□ 至少 1 周观察期再 Contract

Common pitfalls

  1. Large table NOT NULL DEFAULT locks the table——Older PG versions rewrite the entire table (fixed in PG 11+)
  2. Drop the column before releasing the code——Old version services crash
  3. Backfill script cannot be run repeatedly——Cannot recover after failure
  4. Did not check replication lag——Primary-replica lag reaches minutes
  5. Did not monitor disk——Disk full halfway through backfill
  6. Rollback plan only says "roll back code"——No data recovery path
  7. CREATE INDEX without CONCURRENTLY——PG locks writes
  8. MySQL runs gh-ost without leaving buffer——Disk full
  9. Canary skipped——Go directly to full rollout
  10. Irreversible operations not backed up——Cannot recover after dropping the wrong column
  11. Contract without an observation period——Dropping the old column right after switching makes rollback difficult
  12. Trigger copies data without rate limiting——Primary-replica lag explodes
  13. Application code strongly depends on the new schema——Application hangs when schema fails
  14. Migration scripts not put under version control——Each team runs their own

Collaboration with other skills

上游:
  schema-design → DDL 来源
  index-access-pattern → 索引变更来源

下游:
  data-operations-safety → 生产操作门禁
  backend-engineer → 应用代码同步发布
  devops-engineer → 部署窗口、CI/CD
  sre-ops → 监控、告警、复制延迟、磁盘
  qa-engineer → 迁移验证用例

Related references

  • references/migration-rollout-guide.md — In-depth Expand/Contract pattern, gh-ost / pg_repack in practice, backfill rate-limiting strategies, canary release, replication lag monitoring