database-migration-plan

数据库迁移计划技能

为架构变更制定一份完整、安全的数据库迁移计划。迁移计划不只是 SQL，而是一系列协调执行的步骤，确保应用持续可用、数据保持一致，并且每一步都可以独立回滚。

默认采用扩展/收缩模式：扩展架构以同时支持新旧状态，迁移应用，然后收缩架构以移除旧状态。绝不要在部署期间运行的单个迁移中同时执行架构变更和数据回填。

必需输入

如果尚未提供，请询问以下信息：

• 当前架构状态 — 当前表的 DDL 或描述
• 目标架构状态 — 迁移后表应达到的 DDL 或描述
• 迁移原因 — 进行此变更的原因（新功能、性能修复、规范化、合规性）
• 数据库引擎 — PostgreSQL、MySQL、SQLite、CockroachDB 等
• 预计数据量 — 受影响表的大致行数
• 部署限制 — 是否允许停机？迁移期间的预期流量是多少？是否有多个应用实例在运行？
• 回滚窗口 — 部署后团队可以回滚多长时间，之后迁移将无法逆转？

输出格式

---

数据库迁移计划：[迁移名称]

服务： [名称] | 团队： [团队名称] 作者： [姓名] | 审核人： [姓名 / DBA] 日期： [日期] | 目标部署日期： [日期] 数据库引擎： [PostgreSQL X.X / MySQL X.X] 工单： [JIRA-XXX]

---

1. 迁移概览

变更内容： [1–2 句话：具体的架构变更 — 例如，“向 users 表添加一个不可为空的 organisation_id 列，并从 accounts 表回填数据。”]

原因： [1–2 句话：促成本次变更的业务或技术原因。]

迁移类型： [仅添加 / 添加 + 回填 / 列重命名 / 列类型变更 / 表结构调整 / 索引变更]

零停机： [是 — 采用扩展/收缩模式 / 否 — 需要维护窗口 — 注明时长]

预计迁移时长：

• 扩展阶段：[约 X 分钟]
• 数据回填：[约 X 分钟/小时 — 基于 X 行、每秒 Y 行计算]
• 收缩阶段：[部署应用版本后约 X 分钟]

---

2. 向后兼容性分析

在编写任何 SQL 之前，先评估每项变更是否与当前部署的应用代码向后兼容。

变更	向后兼容？	风险	备注
[例如：添加可为空的列 org_id]	是	低	旧应用会忽略新列
[例如：回填 org_id]	是	中	不影响旧应用；新应用读取已回填的值
[例如：为 org_id 添加 NOT NULL 约束]	否	高	插入数据时不提供 org_id 的旧应用会失败
[例如：删除旧列 account_id]	否	高	读取 account_id 的旧应用会失败
[例如：为 org_id 添加索引]	是	低	属于增量变更；不会造成破坏性变更
[例如：重命名列]	否	高	绝不要一步完成重命名；应采用扩展/收缩模式

总结： [例如：“此迁移需要分 3 个部署阶段采用扩展/收缩模式，因为第 3 和第 4 步不向后兼容。”]

---

3. 扩展/收缩阶段

阶段概览

Phase 1 — EXPAND
  Deploy migration: add new column (nullable), create new indexes
  Old app: continues to work (ignores new column)
  New app: not yet deployed
  Duration: [~X min] | Rollback: trivial — drop new column

       │
       ▼

Phase 2 — BACKFILL + DUAL-WRITE
  Deploy app update: writes to both old and new columns
  Run backfill: populate new column for existing rows
  Validate: confirm 100% of rows have non-null new column
  Duration: [~X hours depending on data volume]
  Rollback: deploy previous app version; new column is still nullable

       │
       ▼

Phase 3 — ENFORCE + SWITCH
  Deploy migration: add NOT NULL constraint, drop old column/index
  Deploy app update: reads only from new column
  Duration: [~X min] | Rollback: requires forward-fix (constraint must be dropped first)

       │
       ▼

Phase 4 — CONTRACT (optional cleanup)
  Deploy migration: drop deprecated columns, rename if needed
  Final state matches target schema
  Rollback: not recommended — contract changes are destructive

---

阶段 1 — 扩展架构

目标： 添加新列和结构，同时不破坏现有应用。 部署顺序： 先运行迁移，然后（可选）部署应用。 应用状态： 旧应用运行中；暂时不需要更改应用。

-- Migration: 001_add_org_id_to_users.sql
BEGIN;

-- Add nullable column (safe — old app ignores it)
ALTER TABLE users
    ADD COLUMN org_id UUID NULL
        REFERENCES organisations(id) ON DELETE RESTRICT;

-- Add index NOW, not in Phase 3 — building index on large table during Phase 3 is risky
CREATE INDEX CONCURRENTLY users_org_id_idx ON users (org_id);

-- Note: CONCURRENTLY does not lock the table; safe on live traffic
-- Note: Cannot run CONCURRENTLY inside a transaction block; run separately if needed

COMMIT;

阶段 1 后的验证：

-- Confirm column exists and is nullable
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'org_id';
-- Expected: is_nullable = 'YES'

-- Confirm index exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users' AND indexname = 'users_org_id_idx';

回滚（仅限阶段 1）：

BEGIN;
DROP INDEX CONCURRENTLY IF EXISTS users_org_id_idx;
ALTER TABLE users DROP COLUMN IF EXISTS org_id;
COMMIT;

---

阶段 2 — 回填现有数据

目标： 在强制执行 NOT NULL 之前，为所有现有行填充新列。 运行时机： 阶段 1 上线且运行稳定后。可以作为后台任务运行，也可以作为一次性脚本运行。 应用状态： 部署同时写入旧列和新列的应用版本。

所需的应用代码变更：

// All INSERT and UPDATE operations must now set BOTH old_column and new_column
// until Phase 3 is complete. This ensures new rows are populated during the backfill window.

回填脚本 — 批处理：

-- Run in batches to avoid locking. Adjust batch size based on table size and DB load.
-- Target: no single batch takes more than 5 seconds.

DO $$
DECLARE
    batch_size  INT := 1000;
    affected    INT;
BEGIN
    LOOP
        UPDATE users
        SET    org_id = accounts.organisation_id
        FROM   accounts
        WHERE  users.account_id = accounts.id
          AND  users.org_id IS NULL
        LIMIT  batch_size;

        GET DIAGNOSTICS affected = ROW_COUNT;
        EXIT WHEN affected = 0;

        -- Pause between batches to avoid saturating I/O
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

回填期间的监控：

-- Check progress — run periodically during backfill
SELECT
    COUNT(*) FILTER (WHERE org_id IS NOT NULL) AS backfilled,
    COUNT(*) FILTER (WHERE org_id IS NULL)     AS remaining,
    COUNT(*)                                   AS total,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE org_id IS NOT NULL) / COUNT(*), 2
    ) AS pct_complete
FROM users;

回填完成验证：

-- Must return 0 before proceeding to Phase 3
SELECT COUNT(*) AS unbackfilled_rows
FROM users
WHERE org_id IS NULL;

-- Confirm no new rows written without org_id (dual-write working)
SELECT COUNT(*) AS recent_missing
FROM users
WHERE org_id IS NULL
  AND created_at > now() - INTERVAL '1 hour';

回滚（阶段 2 — 仅应用）：

• 部署之前的应用版本（仅写入旧列）
• org_id 列仍可为空；不会丢失数据
• 已回填的值会保留；没有影响

---

阶段 3 — 强制执行约束

目标： 添加 NOT NULL 约束，并移除对旧列的依赖。 前提条件： 阶段 2 的回填必须 100% 完成（org_id IS NULL 的行数为零）。 部署顺序： 运行迁移，然后部署仅从 org_id 读取数据的应用版本。

PostgreSQL — 对大型表使用 NOT VALID + VALIDATE：

-- Step 1: Add constraint as NOT VALID (no full table scan — instant)
ALTER TABLE users
    ADD CONSTRAINT users_org_id_not_null
    CHECK (org_id IS NOT NULL) NOT VALID;

-- Step 2: VALIDATE CONSTRAINT (takes a SHARE UPDATE EXCLUSIVE lock — allows reads and writes)
-- Run this separately, as it can take minutes on large tables
ALTER TABLE users
    VALIDATE CONSTRAINT users_org_id_not_null;

-- Step 3: Once validated, convert to actual NOT NULL
-- (PostgreSQL trusts the validated check constraint — this is instant)
ALTER TABLE users
    ALTER COLUMN org_id SET NOT NULL;

-- Step 4: Drop the now-redundant check constraint
ALTER TABLE users
    DROP CONSTRAINT users_org_id_not_null;

阶段 3 之后的验证：

-- Confirm NOT NULL is enforced
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'org_id';
-- Expected: is_nullable = 'NO'

-- Test that insert without org_id fails (run in a transaction and roll back)
BEGIN;
INSERT INTO users (email) VALUES ('[email protected]');
-- Expected: ERROR: null value in column "org_id" violates not-null constraint
ROLLBACK;

回滚（阶段 3）：

-- Drop the NOT NULL constraint (restores nullable state)
ALTER TABLE users ALTER COLUMN org_id DROP NOT NULL;
-- Then deploy previous app version (dual-write)
-- Note: Once app code reading the new column is live, rolling back the constraint
-- without rolling back the app will cause issues — plan this carefully.

---

阶段 4 — 收尾（移除旧列）

目标： 在应用不再引用旧列后将其移除。 前提条件： 阶段 3 已完整部署，并且至少稳定运行 [X 天/小时回滚窗口]。 警告： 此阶段具有破坏性 — 旧列中的数据将被永久删除。

BEGIN;

-- Drop the old column
ALTER TABLE users DROP COLUMN account_id;

-- Drop any indexes that referenced the old column
DROP INDEX IF EXISTS users_account_id_idx;

COMMIT;

删除前验证：

-- Confirm no application queries still reference the old column
-- (Check this in code review and via a search of the codebase before running)
-- grep -r "account_id" app/

-- Confirm the column is safe to drop
SELECT COUNT(*) FROM users WHERE account_id IS NOT NULL;
-- Should be 0 (or irrelevant once new column is canonical)

回滚： 无法轻易回滚 — 被删除列中的数据无法恢复。只有在回滚窗口结束且确认变更稳定后，才能继续执行阶段 4。

---

4. 数据验证计划

在完整迁移前后运行以下查询，以确认数据完整性。

迁移前基线：

-- Record these values before any migration step
SELECT COUNT(*)   AS total_users FROM users;
SELECT COUNT(*)   AS total_orgs  FROM organisations;
SELECT MIN(created_at), MAX(created_at) FROM users;

-- Check for any anomalies in the source data before backfill
SELECT COUNT(*) AS users_without_account
FROM users WHERE account_id IS NULL;

回填后完整性检查：

-- All users have an org that exists
SELECT COUNT(*) AS orphaned_org_refs
FROM users u
WHERE u.org_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM organisations o WHERE o.id = u.org_id
  );
-- Expected: 0

-- org_id matches expected value from source column
SELECT COUNT(*) AS mismatched_backfill
FROM users u
JOIN accounts a ON u.account_id = a.id
WHERE u.org_id != a.organisation_id;
-- Expected: 0

-- Row count unchanged (no rows created or deleted by migration)
SELECT COUNT(*) AS total_users_after FROM users;
-- Must match pre-migration baseline

收尾后的最终检查：

-- Old column is gone
SELECT COUNT(*) FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'account_id';
-- Expected: 0

-- New column is NOT NULL
SELECT is_nullable FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'org_id';
-- Expected: NO

---

5. 性能影响评估

步骤	锁类型	锁定时长	流量影响
添加可为空的列	ACCESS EXCLUSIVE	毫秒级	可忽略
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	数分钟（与表大小成正比）	读写可继续
批量回填	仅行级锁	每批 <5s	批次较小时影响较低
ADD CONSTRAINT NOT VALID	ACCESS EXCLUSIVE	毫秒级	可忽略
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	数分钟	读写可继续
ALTER COLUMN SET NOT NULL	ACCESS EXCLUSIVE	毫秒级（如果检查约束已验证）	可忽略
DROP COLUMN	ACCESS EXCLUSIVE	毫秒级	可忽略

回填期间的预期负载增幅：

• DB CPU：[批量写入期间预计增加的百分比]
• DB I/O：[预计增幅]
• 暂停回填的监控阈值：[例如 DB CPU > 80% 且持续 >2 分钟]

回填速率估算：

• 表大小：[X 百万行]
• 批次大小：[1000 行]
• 批次间暂停时间：[100ms]
• 预计总时长：[按每秒 Y 行计算为 X 小时]

---

6. 部署操作手册

请在迁移当天按照此清单操作。每完成一步后再继续下一步。

迁移前（前一天）：

• DBA / 技术负责人已审核迁移计划
• 已评估性能影响；监控仪表板已就绪
• 已在包含生产规模数据的预发布 DB 上测试回填脚本
• 已在预发布环境中测试回滚流程
• 已向值班工程师说明情况；已设置 Slack 频道 [#db-migrations] 以便协调
• 已安排维护窗口（如有需要）

阶段 1 — 扩展（T+0）：

• 手动创建 DB 快照 / 确认自动备份是近期的
• 在生产环境运行 001_expand_add_org_id.sql
• 运行阶段 1 验证查询 — 确认通过
• 部署启用双写的应用版本
• 监控错误率 [10 分钟]

阶段 2 — 回填（T+[X hours]）：

• 确认阶段 1 已稳定运行 [X hours]
• 在 screen/tmux 会话中启动回填脚本
• 每隔 [5 minutes] 通过回填进度查询监控进度
• 监控 DB CPU 和 I/O — 超出阈值时暂停
• 运行完成情况验证 — 确认未回填的行数为 0
• 运行完整性检查 — 确认孤立引用数为 0、不匹配数为 0

阶段 3 — 强制约束（T+[X days]）：

• 确认回填已 100% 完成，且稳定运行 [X hours]
• 添加 NOT VALID 约束
• 运行 VALIDATE CONSTRAINT（监控耗时和锁等待）
• 将列改为 NOT NULL
• 运行阶段 3 验证查询
• 部署只从新列读取数据的应用版本
• 监控错误率 [30 分钟]

阶段 4 — 收缩（回滚窗口结束后的 T+[X days]）：

• 确认回滚窗口已结束 — 没有事故，无需回滚
• 搜索代码库中对旧列的引用 — 确认数量为零
• 运行 DROP COLUMN 迁移
• 运行最终完整性检查
• 关闭迁移工单；更新架构文档

---

质量检查

• 每个迁移阶段都有独立的回滚流程 — 不假定下一阶段已经执行
• 批量回填脚本在每批之间设有暂停，以避免 I/O 饱和
• 对超过 100k 行的表添加 NOT NULL 约束时，使用 NOT VALID + VALIDATE 模式
• 明确定义应用双写时段 — 直到部署阶段 3 前，都不会停止向旧列写入
• 数据验证查询包含行数检查，以确认没有数据丢失
• 已明确每条 DDL 语句使用的锁类型 — 不依赖“应该没问题”之类的假设
• 部署操作手册明确写出每一步由谁执行，而不只是写出要运行什么
• 阶段 4（收缩）明确以回滚窗口结束为前提 — 不与阶段 3 在同一天执行

反模式

• 不要将扩展和收缩阶段合并到一次部署中 — 两者必须由一个部署周期隔开
• 不要在生产规模的数据克隆上测试之前就运行 DDL 变更
• 不要对大型表上的约束添加跳过 NOT VALID + VALIDATE 模式 — 这会导致整张表被锁定
• 不要将“从备份恢复”定义为回滚方案 — 每个阶段都必须有明确且快速的回滚流程
• 不要在过渡期间省略双写逻辑 — 在所有写入方更新之前删除旧列会导致数据丢失

示例触发语

• “规划数据库迁移。”
• “设计零停机架构变更。”
• “编写扩展/收缩迁移文档。”
• “为数据库变更制定回滚流程。”
• “协调数据库架构更新与部署。”