そのまま外部キー制約を追加する場合の問題
既存のテーブルに外部キー制約を追加するには、ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEYを使用する。
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
この方法は、参照元テーブルの既存行が参照先テーブルの主キーを正しく参照しているかを検証する。
検証の間、参照元・参照先の両テーブルに対してSHARE ROW EXCLUSIVEロックを保持し続ける。
SHARE ROW EXCLUSIVEはSELECTをブロックしないが、INSERT・UPDATE・DELETEが必要とするROW EXCLUSIVEとは競合するため、検証が終わるまで両テーブルへの更新がブロックされる。
実際にトランザクション内で外部キー制約を追加し、コミット前に別セッションからpg_locksを確認すると、両テーブルにShareRowExclusiveLockが付与されていることが分かる。
relation | mode | granted
----------+-----------------------+---------
users | ShareRowExclusiveLock | t
orders | ShareRowExclusiveLock | t
(2 rows)
このタイミングで参照先テーブルへのINSERTを実行すると、コミットされるまでブロックされる。
$ time psql -U postgres -c "INSERT INTO users (name) VALUES ('new_user');"
...
5.323 total
参照元テーブルの行数が多いほど検証に時間がかかり、その間は両テーブルへの更新が止まってしまう。
安全に追加する手順
外部キー制約も【PostgreSQL】NOT NULL制約を後から安全に追加する方法
で紹介したNOT NULL制約と同様に、NOT VALID機能を使って段階的に追加すると、更新をブロックする時間を最小限にできる。
1. NOT VALIDで外部キー制約を追加する
NOT VALIDを指定すると、既存行を検証せずに制約だけを定義できる。
既存行を検証しないため、ロックの保持は一瞬ですむ。
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
Time: 1.855 ms
制約はこの時点ですでに有効であり、以降に追加・更新される行には即座に適用される。
未検証なのは、NOT VALID追加より前から存在する既存行のみである。
2. VALIDATE CONSTRAINTで検証する
VALIDATE CONSTRAINTで、追加した制約に対して既存行を検証する。
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
この検証で参照元テーブルに必要なロックはSHARE UPDATE EXCLUSIVEであり、SHARE ROW EXCLUSIVEより弱い。
検証中に別セッションからpg_locksを確認すると、ロックモードの違いが分かる。
relation | mode | granted
----------+--------------------------+---------
orders | AccessShareLock | t
orders | ShareUpdateExclusiveLock | t
users | AccessShareLock | t
users | RowShareLock | t
(4 rows)
参照先テーブルに対してはROW SHAREという行を読み取るだけの弱いロックしか取得しない。ROW SHARE・SHARE UPDATE EXCLUSIVEはいずれも、INSERT・UPDATE・DELETEに必要なROW EXCLUSIVEと競合しない。そのため両テーブルへの更新をブロックしない。
$ time psql -U postgres -c "INSERT INTO orders (user_id) VALUES (1);"
INSERT 0 1
0.071 total
$ time psql -U postgres -c "INSERT INTO users (name) VALUES ('another_user');"
INSERT 0 1
0.056 total
検証が完了すると、制約は完全に有効な状態になる。
SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'orders'::regclass;
conname | convalidated
-------------+--------------
orders_pkey | t
fk_user | t
(2 rows)
まとめ
外部キー制約をそのまま追加すると、既存行の検証中SHARE ROW EXCLUSIVEロックが両テーブルに保持され続け、大規模テーブルでは長時間INSERT・UPDATE・DELETEがブロックされる。
NOT VALIDで制約を追加してからVALIDATE CONSTRAINTで検証する手順を踏むと、制約自体の追加は一瞬で完了し、既存行の検証も弱いロックで行われるため更新をブロックしない。
本番環境で稼働中の大規模テーブルに外部キー制約を追加する際は、この手順を使うとよい。
