database-migration-plan

データベース移行計画スキル

スキーマ変更に対する、完全かつ安全なデータベース移行計画を作成します。移行計画は単なる SQL ではありません。アプリケーションの可用性とデータの一貫性を保ち、各ステップを個別にロールバックできるようにするための、連携した一連の手順です。

デフォルトのアプローチは、拡張／縮小パターンです。古い状態と新しい状態の両方に対応できるようスキーマを拡張し、アプリケーションを移行してから、古い状態を削除するために縮小します。デプロイ中に実行される単一の移行で、スキーマ変更とデータのバックフィルを組み合わせてはいけません。

必須の入力情報

まだ提供されていない場合は、次の情報を確認してください。

• 現在のスキーマ状態 — 現在のテーブルの DDL または説明
• 移行後のスキーマ状態 — 移行後にテーブルがどのような状態になるべきかを示す DDL または説明
• 移行の理由 — この変更を行う理由（新機能、パフォーマンスの修正、正規化、コンプライアンス）
• データベースエンジン — PostgreSQL、MySQL、SQLite、CockroachDB など
• 推定データ量 — 影響を受けるテーブルのおおよその行数
• デプロイ上の制約 — ダウンタイムは許容されますか？移行中に想定されるトラフィック量はどの程度ですか？実行中のアプリケーションインスタンスは複数ありますか？
• ロールバック可能期間 — 移行が不可逆になる前に、デプロイ後どのくらいの期間ロールバックできますか？

出力形式

---

データベース移行計画：[Migration Name]

サービス： [Name] | チーム： [Team name] 作成者： [Name] | レビュー担当者： [Name / DBA] 日付： [Date] | デプロイ予定日： [Date] データベースエンジン： [PostgreSQL X.X / MySQL X.X] チケット： [JIRA-XXX]

---

1. 移行の概要

変更内容： [1～2 文で、具体的なスキーマ変更を記載します。例：「users テーブルに NULL 不可の organisation_id 列を追加し、accounts テーブルのデータでバックフィルします。」]

理由： [1～2 文で、この変更を行うビジネス上または技術上の理由を記載します。]

移行の種類： [追加のみ / 追加＋バックフィル / 列名の変更 / 列の型の変更 / テーブルの再構成 / インデックスの変更]

ダウンタイムなし： [はい — 拡張／縮小を使用 / いいえ — メンテナンス時間が必要 — 所要時間を記載]

推定移行時間：

• 拡張フェーズ：[約 X 分]
• データのバックフィル：[約 X 分／時間 — X 行を毎秒 Y 行処理する場合]
• 縮小フェーズ：[アプリケーションのバージョンをデプロイしてから約 X 分]

---

2. 下位互換性の分析

SQL を 1 行でも書く前に、各変更が現在デプロイされているアプリケーションコードと下位互換性があるか評価してください。

変更	下位互換性あり？	リスク	備考
[例：NULL 許容列 org_id を追加]	はい	低	旧バージョンのアプリケーションは新しい列を無視します
[例：org_id をバックフィル]	はい	中	旧バージョンのアプリケーションには影響しません。新しいバージョンはバックフィルされた値を読み取ります
[例：org_id に NOT NULL 制約を追加]	いいえ	高	org_id なしで挿入する旧バージョンのアプリケーションで失敗します
[例：古い列 account_id を削除]	いいえ	高	account_id を読み取る旧バージョンのアプリケーションで失敗します
[例：org_id にインデックスを追加]	はい	低	追加のみで、互換性を損なう変更はありません
[例：列名を変更]	いいえ	高	1 ステップで名前を変更してはいけません。拡張／縮小を使用してください

概要： [例：「ステップ 3 と 4 に下位互換性がないため、この移行では 3 つのデプロイフェーズにわたる拡張／縮小パターンが必要です。」]

---

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 カラムは NULL を許容したままです。データは失われません
• バックフィル済みの値は残ります。問題はありません

---

フェーズ 3 — 制約の適用

目的： NOT NULL 制約を追加し、旧カラムへの依存を取り除きます。 前提条件： フェーズ 2 のバックフィルが 100% 完了している必要があります（org_id IS NULL の行が 0 件）。 デプロイ順序： マイグレーションを実行し、その後 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 days/hours rollback window] の間、安定していること。 警告： このフェーズは破壊的です。旧カラムのデータは完全に削除されます。

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. パフォーマンス影響評価

手順	ロックの種類	ロック時間	トラフィックへの影響
NULL を許容するカラムの追加	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 が 2 分を超えて 80% 超］

バックフィル速度の見積もり：

• テーブルサイズ：［X 百万行］
• バッチサイズ：［1000 行］
• バッチ間の一時停止：［100ms］
• 推定所要時間：［Y 行／秒の場合、合計 X 時間］

---

6. デプロイ手順書

移行当日に、このチェックリストに従ってください。次の手順に進む前に、各項目に完了マークを付けてください。

移行前（前日）：

• DBA／テックリードが移行計画を確認済み
• パフォーマンスへの影響を評価済み。監視ダッシュボードを準備済み
• 本番環境相当のデータを使って、ステージング DB でバックフィルスクリプトをテスト済み
• ステージング環境でロールバック手順をテスト済み
• オンコールエンジニアに説明済み。連携用に Slack チャンネル [#db-migrations] を設定済み
• メンテナンス時間帯を設定済み（必要な場合）

フェーズ 1 — 拡張（T+0）：

• DB の手動スナップショットを取得／自動バックアップが最新であることを確認
• 本番環境で 001_expand_add_org_id.sql を実行
• フェーズ 1 の検証クエリを実行し、合格を確認
• デュアルライトを行うアプリのバージョンをデプロイ
• [10 minutes] の間、エラー率を監視

フェーズ 2 — バックフィル（T+[X hours]）：

• フェーズ 1 が [X hours] の間安定していることを確認
• screen/tmux セッションでバックフィルスクリプトを開始
• [5 minutes] ごとにバックフィルの進捗クエリで進捗を監視
• DB の CPU と I/O を監視し、しきい値を超えた場合は一時停止
• 完了検証を実行し、未バックフィルの行が 0 件であることを確認
• 整合性チェックを実行し、孤立した参照と不一致がそれぞれ 0 件であることを確認

フェーズ 3 — 制約適用（T+[X days]）：

• バックフィルが 100% 完了し、[X hours] の間安定していることを確認
• NOT VALID 制約を追加
• VALIDATE CONSTRAINT を実行（所要時間とロック待ちを監視）
• 列を NOT NULL に変更
• フェーズ 3 の検証クエリを実行
• 新しい列からのみ読み取るアプリのバージョンをデプロイ
• [30 minutes] の間、エラー率を監視

フェーズ 4 — 縮小（ロールバック期間終了後 T+[X days]）：

• ロールバック期間が終了し、インシデントもロールバックの必要もなかったことを確認
• コードベース内で古い列への参照を検索し、ゼロ件であることを確認
• DROP COLUMN マイグレーションを実行
• 最終的な整合性チェックを実行
• 移行チケットをクローズし、スキーマのドキュメントを更新

---

品質チェック

• すべての移行フェーズに独立したロールバック手順があること。どのフェーズも、次のフェーズが実行済みであることを前提としていない
• バッチバックフィルスクリプトに、I/O の飽和を避けるためのバッチ間の一時停止が含まれている
• 100k 行を超えるテーブルでは、NOT NULL 制約に NOT VALID + VALIDATE パターンを使用する
• アプリのデュアルライト期間が明確に定義されている。フェーズ 3 がデプロイされるまで、古い列への書き込みを停止しない
• データ検証クエリに行数チェックが含まれており、データ損失がないことを確認できる
• すべての DDL 文についてロックの種類が特定されている。「問題ないはず」という想定をしない
• デプロイ手順書には、実行内容だけでなく、各手順の実行担当者が記載されている
• フェーズ 4（縮小）は、ロールバック期間の終了を明確な実行条件とする。フェーズ 3 と同じ日に実行しない

アンチパターン

• 拡張フェーズと縮小フェーズを 1 回のデプロイにまとめない。両者の間にはデプロイサイクルを設ける必要がある
• 本番環境と同規模のデータクローンでテストする前に、DDL 変更を実行しない
• 大規模テーブルで制約を追加する際に NOT VALID + VALIDATE パターンを省略しない。省略するとテーブル全体がロックされる
• ロールバックを「バックアップから復元」と定義しない。各フェーズに明確かつ迅速なロールバック手順を用意する
• 移行期間中のデュアルライトのロジックを省略しない。すべての書き込み処理が更新される前に古い列を削除すると、データ損失が発生する

使用例となるトリガーフレーズ

• 「データベースの移行を計画してください。」
• 「ダウンタイムのないスキーマ変更を設計してください。」
• 「拡張／縮小型の移行を文書化してください。」
• 「データベース変更のロールバック手順を作成してください。」
• 「デプロイに合わせてデータベーススキーマの更新を調整してください。」