これは
PostgreSQLの大規模なテーブル（6000万レコード程度) にカラムを追加する必要があったので、注意するべき点について調べた内容のメモ。
ざっくり注意するべき点
テーブル全体のリライト（書き換え）が発生するかどうかと、それに伴う排他ロックの取得と保持時間
詳細
テーブルリライトの発生条件とロックの影響
ALTER TABLE ADD COLUMN がリライトを必要とするかどうかで、作業時間・影響範囲は大きく変わる。
リライトが発生しないケース（推奨）
- 非揮発性（non-volatile）なデフォルト値、または
NULLを指定する場合はリライトが不要。デフォルト値は実行時に一度だけ評価され、メタデータに保存される - 既存行のスキャン・書き換えが発生しないため、高速に完了
- 既存行には論理的にデフォルト値（またはNULL）が適用される
リライトが発生するケース（大規模テーブルでは避けるべき）
- 揮発性（volatile）なデフォルト値を指定した場合 例：
DEFAULT now() - テーブル全体＋インデックスのリライトが必要
- 一時的にディスク容量が約2倍必要になる場合がある
- MVCC上、同時トランザクションによっては「テーブルが空に見える」状況が起こり得る
ロックレベルの注意
- 多くの
ALTER TABLEは ACCESS EXCLUSIVE ロック を取得する - このロックは 読み取り・書き込みの両方を完全にブロック
- リライトがある場合、ロック保持時間が長時間化し業務影響が大きい
- 複数の変更は 1回の
ALTER TABLEにまとめてスキャン回数を削減することが推奨される
制約追加時の注意点とロックの扱い
カラム追加後に NOT NULL や CHECK、FOREIGN KEY を付与する場合にも注意が必要。
NOT NULL制約の追加
- テーブル全体をスキャンしてNULLがないか確認
- リライトは発生しないが、更新系トランザクションがブロックされる場合がある
NOT VALID を使った制約追加（推奨）
- まず
NOT VALIDで制約を追加（即時検証なし） - 後から
VALIDATE CONSTRAINTを実行し既存データを検証
VALIDATE 実行時は SHARE UPDATE EXCLUSIVE ロック のみで済む。更新を完全停止させずに制約検証を進められる。
パーティションテーブル・インデックスが多い場合の追加注意点
構造によっては、カラム追加がパフォーマンスに間接的な影響を与える。
パーティションテーブルの場合
- 親に対して行った変更は、すべてのパーティションへ伝播
-
ALTER TABLE ONLY（親のみ変更）は拒否される - パーティション数が多いほどメタデータ更新コストが増える
インデックスが多いテーブルの場合
- カラム追加自体はインデックスを作らないが、インデックス数が多いほどロックオーバーヘッド増大
- 不要なインデックスは削除し、10個以下などに合理化するのが有効（
pg_stat_user_indexesなどで監査）
まとめ
大規模テーブルへのカラム追加で最も重要なのは、
- リライトが起きるかどうか
- どのロックがどれだけ保持されるか
の2点。
- 非リライト型の追加を選ぶ
- NOT VALID を活用して制約を後から検証
- パーティション数・インデックス数にも注意
- 複数の変更は1回のALTERにまとめる
これらによって、運用環境への影響を最小限に抑えつつ、安全にスキーマ変更が行える。
参考