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
先兼容 → 再切换 → 最后清理
-
Back up first, then change, verify last Any irreversible operation must be broken into stages
-
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
-
Decouple application code and schema releases Schema changes should be deployable independently of code
-
Large table = database ≥ 1GB or ≥ 10 million rows Large-table migration must account for table locking / replication lag / disk
-
Canary before full rollout For critical changes: 5% → 50% → 100%
-
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
- Large table NOT NULL DEFAULT locks the table——Older PG versions rewrite the entire table (fixed in PG 11+)
- Drop the column before releasing the code——Old version services crash
- Backfill script cannot be run repeatedly——Cannot recover after failure
- Did not check replication lag——Primary-replica lag reaches minutes
- Did not monitor disk——Disk full halfway through backfill
- Rollback plan only says "roll back code"——No data recovery path
- CREATE INDEX without CONCURRENTLY——PG locks writes
- MySQL runs gh-ost without leaving buffer——Disk full
- Canary skipped——Go directly to full rollout
- Irreversible operations not backed up——Cannot recover after dropping the wrong column
- Contract without an observation period——Dropping the old column right after switching makes rollback difficult
- Trigger copies data without rate limiting——Primary-replica lag explodes
- Application code strongly depends on the new schema——Application hangs when schema fails
- 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