大規模テーブルのオンラインスキーマ変更 — gh-ost、pt-online-schema-change、ネイティブ Online DDL

+-------------------------------------------------------------+
|  直接 ALTER TABLE が障害に至る経路                            |
|                                                             |
|   ALTER 開始                                                 |
|      |                                                      |
|      v                                                      |
|   [メタデータ/書き込みロック取得]                              |
|      |                                                      |
|      +--> 書き込みトランザクションが待ち行列に蓄積             |
|      |        |                                             |
|      |        v                                             |
|      |     コネクションプール枯渇 --> 読み取りもタイムアウト    |
|      |                                                      |
|      +--> レプリカに同じ DDL を直列適用 --> 遅延が膨らむ        |
|      |                                                      |
|      +--> 大量 I/O/CPU --> 同一サーバーの他クエリが低速化       |
|                                                             |
|   => 一つのテーブルの変更が DB 全体の障害に拡散                |
+-------------------------------------------------------------+

-- INSTANT: 8.0+、カラム追加が即座に終わる（メタデータのみ変更）
ALTER TABLE orders
  ADD COLUMN coupon_code VARCHAR(32) NULL,
  ALGORITHM=INSTANT;

-- INPLACE + LOCK=NONE: テーブルコピーなしで、読み書きを許可しつつインデックス作成
ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

+----------------------------------------------------------+
|  MySQL ネイティブ Online DDL のアルゴリズム選択             |
|                                                          |
|   変更の種類                推奨アルゴリズム   テーブルコピー |
|   ----------------------    --------------    ---------- |
|   カラム追加（末尾）         INSTANT           なし        |
|   カラムのデフォルト変更      INSTANT           なし        |
|   セカンダリインデックス追加   INPLACE           なし        |
|   カラム型の変更             COPY              あり        |
|   PRIMARY KEY の変更        COPY              あり        |
|   文字セットの変更           COPY              あり        |
+----------------------------------------------------------+

+--------------------------------------------------------------+
|  pt-online-schema-change の動作（トリガーベース）             |
|                                                              |
|   1) 新テーブル _orders_new 作成 + 目的の ALTER 適用          |
|                                                              |
|   2) 元の orders にトリガー設置                               |
|        INSERT/UPDATE/DELETE --> _orders_new へ伝播           |
|                                                              |
|       アプリケーションの書き込み                               |
|            |                                                 |
|            v                                                 |
|         [ orders ] --(トリガー)--> [ _orders_new ]           |
|            ^                                                 |
|            |  3) 既存行をチャンク単位でコピー                  |
|            +--- バックフィル: 1000行ずつ INSERT ... SELECT    |
|                                                              |
|   4) 原子的 RENAME: orders <-> _orders_new を入れ替え         |
|                                                              |
|   5) トリガー削除 + 旧テーブル _orders_old 削除               |
+--------------------------------------------------------------+

pt-online-schema-change \
  --alter "ADD COLUMN coupon_code VARCHAR(32) NULL" \
  --host=db-primary.internal \
  --user=migrator \
  --ask-pass \
  --chunk-size=1000 \
  --max-load "Threads_running=50" \
  --critical-load "Threads_running=120" \
  --max-lag=2 \
  --check-slave-lag=db-replica.internal \
  D=shop,t=orders \
  --execute

+--------------------------------------------------------------+
|  gh-ost の動作（バイナリログベース、トリガーなし）            |
|                                                              |
|   アプリケーションの書き込み                                  |
|        |                                                     |
|        v                                                     |
|    [ orders ] ---> MySQL binlog                              |
|        ^                |                                    |
|        |                v                                    |
|        |          gh-ost（レプリカのふりで binlog 購読）       |
|        |                |                                    |
|        |        +-------+-------+                            |
|        |        |               |                            |
|        |   binlog イベント適用   既存行バックフィル（チャンク） |
|        |        |               |                            |
|        |        v               v                            |
|        |     [ _orders_gho ゴーストテーブル ]                 |
|        |                                                     |
|        +--- 4) カットオーバー: orders <-> _orders_gho 入れ替え |
+--------------------------------------------------------------+

gh-ost \
  --host=db-primary.internal \
  --user=migrator \
  --password=secret \
  --database=shop \
  --table=orders \
  --alter="ADD COLUMN coupon_code VARCHAR(32) NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=120" \
  --max-lag-millis=1500 \
  --throttle-control-replicas="db-replica.internal" \
  --allow-on-master \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

# 実行中にスロットルを強制（バックフィル一時停止）
echo "throttle" | nc -U /tmp/gh-ost.shop.orders.sock

# スロットル解除
echo "no-throttle" | nc -U /tmp/gh-ost.shop.orders.sock

# チャンクサイズをリアルタイム変更
echo "chunk-size=200" | nc -U /tmp/gh-ost.shop.orders.sock

-- 書き込みを止めずにインデックス作成（トランザクションブロックの外で実行）
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

-- 失敗して INVALID 状態で残ったインデックスがないか確認
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

-- INVALID インデックスも同じく CONCURRENTLY で削除
DROP INDEX CONCURRENTLY idx_orders_customer_created;

-- ロックを3秒以内に得られなければこの DDL を失敗させる
SET lock_timeout = '3s';

-- 11+ では NOT NULL なしのカラム追加は基本的に即座（テーブル再作成なし）
ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(32);

SET lock_timeout = '3s';
-- DEFAULT 付きのカラム追加も 11+ ではメタデータのみ変更で速い
ALTER TABLE orders ADD COLUMN status SMALLINT NOT NULL DEFAULT 0;

-- ステップ1: 既存行の検証をスキップして制約追加（短いロック）
ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  NOT VALID;

-- ステップ2: 空いているときに既存行を検証（書き込みを止めない弱いロック）
ALTER TABLE order_items
  VALIDATE CONSTRAINT fk_order;

-- 悪い例: 数億行を一トランザクションで（絶対禁止）
-- UPDATE orders SET coupon_code = 'NONE' WHERE coupon_code IS NULL;

-- 良い例: PK 範囲でチャンクを区切って繰り返す
-- （アプリ/スクリプトのループで batch_start を1000ずつ増やす）
UPDATE orders
SET coupon_code = 'NONE'
WHERE id >= 1000000 AND id < 1001000
  AND coupon_code IS NULL;

// 疑似コードレベルのチャンクバックフィルループ（レプリケーション遅延監視つき）
func backfill(db *sql.DB, maxID, batch int) error {
    for start := 0; start < maxID; start += batch {
        for replicationLagSeconds(db) > 2 {
            time.Sleep(500 * time.Millisecond) // 遅延が回復するまで待機
        }
        _, err := db.Exec(
            "UPDATE orders SET coupon_code = 'NONE' "+
                "WHERE id >= ? AND id < ? AND coupon_code IS NULL",
            start, start+batch,
        )
        if err != nil {
            return err
        }
        time.Sleep(50 * time.Millisecond) // チャンク間の短い休止
    }
    return nil
}

+--------------------------------------------------------------+
|  カットオーバーのタイムライン（理想的な場合）                 |
|                                                              |
|   バックフィル進行 =========================>  (昼間、スロットル) |
|                                            |                 |
|                                  [カットオーバー待機、フラグファイル] |
|                                            |                 |
|   トラフィック少ない時間 ------------------> [カットオーバー実行] |
|                                            |  短いロック       |
|                                            v                 |
|                                  orders <-> ghost 入れ替え    |
|                                            |                 |
|                                  旧テーブル片付け             |
+--------------------------------------------------------------+

+--------------------------------------------------------------+
|  拡張-収縮（expand-contract）パターン                         |
|                                                              |
|   1) 拡張: 新カラム追加（旧カラムはそのまま維持）             |
|   2) 二重書き込み: アプリが旧+新カラム両方に記録              |
|   3) バックフィル: 既存行の新カラムをチャンクで埋める          |
|   4) 読み取り切替: アプリが新カラムを読むようにデプロイ        |
|   5) 収縮: 安定後に旧カラムを削除                            |
|                                                              |
|   => 各段階が独立デプロイ + 段階ごとにロールバック可能         |
+--------------------------------------------------------------+

[ ] 変更を軽い変更（INSTANT/即座）と重い変更（再作成）に分類したか
[ ] 軽い変更はネイティブ DDL/マイグレーションツールで直接処理できるか
[ ] 重い変更に gh-ost か pt-osc のどちらを使うか決めたか
[ ] 元のサイズと同じだけの追加ディスク余裕を確保したか
[ ] 外部キー/トリガーがかかったテーブルか確認しオプションを用意したか
[ ] スロットル閾値（負荷/レプリケーション遅延）を保守的に設定したか
[ ] レプリカ遅延の監視対象をツールに指定したか
[ ] カットオーバーをトラフィックの少ない時間帯に計画したか（延期フラグ活用）
[ ] カットオーバー直前に長い/未コミットのトランザクションがないと確認したか
[ ] ロールバック手順（カットオーバー前/後）を文書化したか
[ ] 同規模のステージングでリハーサルを終えたか
[ ] PostgreSQL なら lock_timeout 設定と INVALID インデックス点検の計画があるか
[ ] 拡張-収縮でアプリのデプロイとスキーマ変更を分離したか