200万行のテーブルにDDLを打つ前に知りたかったこと¶
チェック¶
- [ ] 本文を確認した
- [ ] 概要を確認した
- [ ] タグを確認した
- [ ]
inbox/直下へ移行した
概要¶
SaaSの巨大テーブルにDDLを打つときの実務上の地雷と、安全な打ち方をまとめた記事。 PostgreSQLのバージョン差、インデックス追加時のロック、ALTER TABLEのロック待ち、Advisory Lock、リバースマイグレーション不能などが扱われている。 「遅いクエリを直す」前に、修正作業そのものが障害を起こさないよう設計する、という観点が中心。
本文¶
地雷1: NOT NULL + DEFAULTカラム追加¶
PostgreSQL 11以降では、デフォルト値つきカラム追加は既存行を書き換えずに済む。
一方、PostgreSQL 10以前では全行への書き込みが発生し、ACCESS EXCLUSIVE LOCK でSELECTを含むクエリがブロックされる。
安全側に倒すなら、巨大テーブルでは次の3段階に分ける。
ALTER TABLE issue ADD COLUMN priority integer DEFAULT 0;
UPDATE issue SET priority = 0
WHERE id IN (
SELECT id FROM issue WHERE priority IS NULL LIMIT 10000
);
ALTER TABLE issue ALTER COLUMN priority SET NOT NULL;
PostgreSQL 12以降ではCHECK制約で事前にNOT NULLを保証しておくと、SET NOT NULL の全行スキャンを避けられる場合がある。
地雷2: ソフトデリートテーブルへのインデックス追加¶
通常の CREATE INDEX は書き込みをブロックする。
大きなテーブルでは、CREATE INDEX CONCURRENTLY を使う。
ソフトデリートを使うテーブルでは、多くのクエリが WHERE deleted_at IS NULL を含む。
その場合は、削除済み行を含めないパーシャルインデックスを検討する。
地雷3: ALTER TABLEのロック待ち¶
DDL自体が短くても、既存の長時間トランザクションにロック待ちすると、その後続のSELECTまで詰まることがある。
lock_timeout を短く設定し、取れないロックを待ち続けない設計にする。
地雷4: 採番テーブルとAdvisory Lock¶
採番処理がAdvisory Lockを使っていると、DDLのロック待ちと採番トランザクションの待ちが連鎖する。
pg_stat_activity で wait_event_type = 'Lock' かつ wait_event = 'advisory lock' を確認する。
安全策としては、トラフィックが少ない時間帯に実行する、lock_timeout を短くする、必要ならメンテナンスモードで計画的に止める。
地雷5: ORMのリバースマイグレーション¶
DjangoやRailsのロールバック機能があっても、本番障害時に使えるとは限らない。
reverse_sql の未定義、手動SQL、DDLの性質により、戻せない変更がある。
マイグレーション前に「ロールバック手順が本当に実行可能か」を確認する。
マイグレーション前チェック¶
- 対象テーブルの行数を確認したか
- PostgreSQLバージョンを確認したか
- ロック種別とロック時間を把握したか
lock_timeoutを設定したか- スナップショットやバックアップはあるか
- ロールバック手順は検証済みか
- 影響時間帯とメンテナンス方針を決めたか
要点¶
- 巨大テーブルのDDLは、SQLの正しさだけでなくロックと運用手順まで含めて設計する。
CONCURRENTLY、パーシャルインデックス、lock_timeoutは本番DDLの基本手札。- 「直さない」「計画的に止める」も、障害を避けるための判断になり得る。