Terry Yao's Blog

[database/11] 資料庫遷移：換 DB 不爆炸的完整流程

閱讀時間約 11 分鐘

結論先講

資料庫遷移不是技術問題，是風險管理問題。 技術上 rename 一個欄位只要一行 SQL，但在有流量的生產環境裡，這一行 SQL 可以讓整個服務掛 10 分鐘。這篇講的不是「怎麼寫 migration」，而是「怎麼讓 migration 不出事」。

核心原則：每次 migration 都要可以 rollback，每個步驟都要能獨立部署，永遠不要在一個 migration 裡做太多事。

Schema Migration 工具比較

主流工具一覽

工具語言/框架特色學習曲線
AlembicPython / SQLAlchemy自動偵測 model 變更產生 migration中
Django MigrationsPython / Django完全整合框架，自動產生低
TypeORM MigrationsNode.js / TypeORM支援自動產生，TypeScript 撰寫中
Prisma MigrateNode.js / Prisma宣告式 schema，自動 diff低
FlywayJava / 多語言SQL 為主，版本號命名低
LiquibaseJava / 多語言XML/YAML/JSON 定義，支援回滾高

Alembic 範例

# alembic/versions/001_add_phone_to_users.py
from alembic import op
import sqlalchemy as sa
 
def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))
    op.create_index('idx_users_phone', 'users', ['phone'])
 
def downgrade():
    op.drop_index('idx_users_phone', 'users')
    op.drop_column('users', 'phone')

Flyway 範例

-- V001__add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users (phone);
 
-- U001__add_phone_to_users.sql (undo)
DROP INDEX idx_users_phone;
ALTER TABLE users DROP COLUMN phone;

我的建議： 如果你用框架（Django、Rails），用框架內建的 migration。如果是多語言微服務，用 Flyway——純 SQL 最透明，任何人都看得懂。

Migration 的黃金原則

1. 永遠可逆（Always Reversible）

每個 migration 都要有 downgrade / undo。不是說你一定會用到，但它是你的安全網。

# ❌ 不可逆的 migration
def upgrade():
    op.drop_column('users', 'legacy_field')
 
def downgrade():
    raise NotImplementedError("Cannot undo")  # 資料已經沒了
 
# ✅ 可逆的做法：先標記廢棄，過一段時間再刪
def upgrade():
    op.alter_column('users', 'legacy_field', new_column_name='_deprecated_legacy_field')
 
def downgrade():
    op.alter_column('users', '_deprecated_legacy_field', new_column_name='legacy_field')

2. 永遠不破壞（Never Destructive）

在 migration 中避免這些操作：

危險操作為什麼危險安全替代方案
DROP COLUMN舊版程式碼還在讀這個欄位先部署不讀的程式碼 → 再刪欄位
RENAME COLUMN等於同時 DROP + ADD新增欄位 → 雙寫 → 切換 → 刪舊欄位
ALTER TYPE可能鎖表很久新增欄位 → 背景轉換 → 切換
ADD NOT NULL既有資料可能有 NULL先 SET DEFAULT + 背景填值 → 再加 NOT NULL

3. 小步快跑（Small Increments）

# ❌ 一個大 migration
V010__refactor_entire_order_system.sql  (300 行)

# ✅ 拆成多個小步驟
V010__add_new_orders_table.sql
V011__backfill_new_orders.sql
V012__add_triggers_for_dual_write.sql
V013__switch_reads_to_new_table.sql
V014__remove_dual_write_trigger.sql
V015__drop_old_orders_table.sql

零停機遷移模式

Expand-Contract Pattern（擴展-收縮模式）

這是最重要的模式，尤其適合 column rename 和 type change。

場景：把 users.name 拆成 first_name 和 last_name

Phase 1 - Expand（擴展）
├── Migration: 新增 first_name, last_name 欄位（nullable）
├── 部署: 程式碼同時寫入 name + first_name/last_name
└── 背景任務: 把既有資料拆分填入新欄位

Phase 2 - Migrate（遷移）
├── 驗證: 確認所有資料都已填入新欄位
├── 部署: 程式碼改為只讀新欄位（還是雙寫）
└── 測試: 觀察一段時間確認沒問題

Phase 3 - Contract（收縮）
├── 部署: 程式碼停止寫入 name
├── Migration: 加 NOT NULL constraint 到新欄位
└── Migration: 刪除 name 欄位（可以再等一個 sprint）

# Phase 1: Expand
def upgrade():
    op.add_column('users', sa.Column('first_name', sa.String(100), nullable=True))
    op.add_column('users', sa.Column('last_name', sa.String(100), nullable=True))
 
# Phase 1: 背景填充腳本
async def backfill_names():
    batch_size = 1000
    while True:
        result = await db.execute("""
            UPDATE users SET
                first_name = split_part(name, ' ', 1),
                last_name = split_part(name, ' ', 2)
            WHERE first_name IS NULL
            LIMIT 1000
        """)
        if result.rowcount == 0:
            break
        await asyncio.sleep(0.1)  # 避免打爆 DB

Dual-Write Pattern（雙寫模式）

適合整張表結構大改或跨資料庫遷移：

// Node.js 雙寫範例
async function createOrder(data) {
  // 寫入舊表
  await oldDb.orders.create(data);
 
  // 同時寫入新表
  try {
    await newDb.orders.create(transformData(data));
  } catch (err) {
    // 新表寫入失敗不影響主流程，但要記錄
    logger.error('Dual-write failed for new orders table', err);
    metrics.increment('dual_write.failure');
  }
}

MySQL → PostgreSQL 遷移

這是最常見的跨資料庫遷移場景。

工具選擇

工具適用場景特色
pgloader中小型資料庫（< 100GB）開源、一個指令搞定
AWS DMS大型、需要持續同步支援 CDC（持續資料捕捉）
手動 dump/restore小型資料庫最可控但最麻煩

pgloader 範例

-- pgloader 設定檔
LOAD DATABASE
  FROM mysql://root:pass@localhost/myapp
  INTO postgresql://postgres:pass@localhost/myapp
 
WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers
 
SET maintenance_work_mem to '512MB',
    work_mem to '48MB'
 
CAST type int with extra auto_increment to serial,
     type bigint with extra auto_increment to bigserial,
     type tinyint to boolean using tinyint-to-boolean,
     type datetime to timestamptz;

常見地雷

MySQLPostgreSQL注意事項
AUTO_INCREMENTSERIAL / GENERATED ALWAYS AS IDENTITYpgloader 自動處理
TINYINT(1)BOOLEANMySQL 沒有真 boolean
DATETIMETIMESTAMPTZ記得處理時區
ENUM('a','b','c')CREATE TYPE ... AS ENUM要先建 type
GROUP BY 可以不列所有欄位必須列所有非聚合欄位查詢要改
utf8 (3-byte)UTF8 (真的 4-byte)資料不會有問題
ON UPDATE CURRENT_TIMESTAMP需要用 trigger沒有自動更新時間戳
IFNULL()COALESCE()函數名不同
LIMIT x, yLIMIT y OFFSET x語法順序不同
-- MySQL 語法
SELECT * FROM orders GROUP BY user_id;  -- MySQL 允許（但結果不確定）
SELECT IFNULL(name, 'N/A') FROM users;
SELECT * FROM users LIMIT 10, 20;
 
-- PostgreSQL 語法
SELECT user_id, MAX(id) FROM orders GROUP BY user_id;  -- 必須明確
SELECT COALESCE(name, 'N/A') FROM users;
SELECT * FROM users LIMIT 20 OFFSET 10;

Schema Migration vs Data Migration

這是很多人搞混的概念：

Schema MigrationData Migration
改什麼表結構、索引、約束資料內容
速度通常很快（DDL）可能很慢（大量 UPDATE）
鎖表部分操作會鎖（ALTER TABLE）不鎖表但佔用 I/O
工具Alembic、Flyway自己寫腳本
可逆通常可以很難完全可逆

Data Migration 最佳實踐：

# ❌ 一次更新所有資料（鎖表 + 可能 OOM）
UPDATE users SET full_name = first_name || ' ' || last_name;
 
# ✅ 分批處理
import time
 
BATCH_SIZE = 5000
while True:
    result = db.execute("""
        UPDATE users SET full_name = first_name || ' ' || last_name
        WHERE full_name IS NULL
        AND id IN (
            SELECT id FROM users WHERE full_name IS NULL LIMIT %s
        )
    """, [BATCH_SIZE])
 
    if result.rowcount == 0:
        break
 
    db.commit()
    time.sleep(0.5)  # 給 DB 喘口氣
    print(f"Updated {result.rowcount} rows")

測試 Migration

Shadow Database

在 CI 裡對一個空的測試資料庫跑所有 migration，確保能完整執行：

# GitHub Actions
- name: Test migrations
  run: |
    # 建立測試資料庫
    createdb test_migrations
 
    # 跑所有 migration
    alembic upgrade head
 
    # 跑所有 downgrade
    alembic downgrade base
 
    # 再跑一次 upgrade 確保可重複
    alembic upgrade head

Production 前的檢查清單

- [ ] 在 staging 環境跑過
- [ ] migration 可以 rollback
- [ ] 估算 migration 執行時間（對大表）
- [ ] 確認不會鎖表超過 5 秒
- [ ] 確認舊版程式碼相容新 schema
- [ ] 準備好 rollback 腳本
- [ ] 通知 on-call 工程師

Rollback 策略

如果 migration 在 production 失敗了：

  1. 有 downgrade 腳本 → 直接跑 rollback
  2. 沒有 downgrade 腳本 → 手動修復（這就是為什麼要永遠寫 downgrade）
  3. 資料已經被改壞 → 從備份恢復（希望你有做備份）
# Alembic rollback
alembic downgrade -1  # 回退一步
 
# Flyway（需要 Teams 版本才有 undo）
flyway undo
 
# Django
python manage.py migrate myapp 0009  # 回退到指定版本

Blue-Green Database 部署：

對於風險極高的遷移，可以準備兩套資料庫：

1. Green DB（新版）：跑完 migration
2. Blue DB（舊版）：維持原樣
3. 應用程式指向 Green DB
4. 觀察 30 分鐘
5. 沒問題 → 關閉 Blue DB
6. 有問題 → 切回 Blue DB

成本高，但對關鍵系統（金流、醫療）值得。

FAQ

Q1: Migration 檔案要不要進 git？

要。Migration 檔案就是你的 schema 變更歷史，跟程式碼一樣重要。永遠不要手動修改已經跑過的 migration 檔案。

Q2: 多人開發時 migration 衝突怎麼辦？

最常見的問題。解法：（1）用 migration 工具的 merge 功能（Alembic 的 alembic merge）；（2）約定一次只有一個人在改同一張表；（3）把 migration 的產生和合併納入 code review 流程。

Q3: 大表加索引會鎖表嗎？

在 PostgreSQL 裡，CREATE INDEX 會鎖寫入。用 CREATE INDEX CONCURRENTLY 可以避免鎖表，但耗時更長且不能在 transaction 裡跑。MySQL 8.0 的 ALTER TABLE ... ADD INDEX 預設已經是線上操作（Instant DDL），影響很小。

Q4: 什麼時候該做跨資料庫遷移？

當你的需求已經超出現有資料庫的能力範圍：需要 JSONB 但在用 MySQL、需要地理查詢但沒有 PostGIS、效能瓶頸無法透過優化解決。但記住：遷移成本很高，通常要 2-6 個月，確定值得才做。

Q5: 能不能直接改 production 資料庫的 schema？

在開發環境可以。在 production 絕對不行。所有 schema 變更都要透過 migration 工具，有版本控制、可追蹤、可回滾。手動改 schema 是生產事故的第一步。

Database 系列文章

  1. 為什麼需要資料庫
  2. MySQL：為什麼越來越多人覺得它不夠好？
  3. PostgreSQL 的崛起
  4. SQL vs NoSQL：怎麼選
  5. Redis 不只是快取
  6. MongoDB：什麼時候該用文件資料庫
  7. ORM：愛恨交織的抽象層
  8. 交易與隔離等級
  9. 備份與災難恢復
  10. SQL 效能調校
  11. 資料庫遷移策略 ← 你在這裡
  12. 資料庫擴展模式

關係圖譜

反向連結