AKARI Tech Blog

燈株式会社のエンジニア・開発メンバーによる技術ブログです

トップ > DB > データベースの長期的な安全性を確保する外部キー管理

データベースの長期的な安全性を確保する外部キー管理

DB Digital Billder

皆さんこんにちは！

今週のAKARI Tech Blogは、AI SaaS 事業本部 AI-ERP 事業部の小松が担当します。 私たちが開発・提供している Digital Billder は、建設業界における「請求・発注・見積・経費精算・原価管理」といった一連の業務をクラウドで一元管理できる Web アプリケーションです。

www.lp.digitalbillder.com

はじめに

Digital Billderでは、ユーザー・請求書・発注書・経費精算といった多くのエンティティがPostgreSQLの外部キー制約で関連付けられています。ある日、不要なデータをDELETEしたところ、たった1行の削除に1,400msもかかる事象が発生しました。

調査の結果、原因は外部キー列へのインデックス貼り漏れでした。これを機にFK制約まわりを深掘りしたところ、インデックス不足による遅延だけでなく、ロックの波及によるデッドロック、CASCADEによる意図しない大量削除、マイグレーション時のサービス停止など、実務で問題になりやすいポイントが複数あることが分かりました。

本記事では、この実際の改善事例を出発点として、PostgreSQLにおける外部キー制約の内部動作とパフォーマンス問題の原因・対策を整理します。

なぜ外部キー制約の理解が重要なのか

そもそもリレーショナルデータベースは「集合と関係」を扱うデータモデルです。外部キー制約はデータベースの参照整合性を維持するための重要な機能です。

外部キー制約がなければ、親テーブルに存在しない値を子テーブルに登録できてしまったり、子テーブルが参照している値を親テーブルから削除できてしまったりします。例えば、請求書が参照しているユーザーIDが親テーブルに存在しなければ、その請求書の詳細画面を開いたときに「発行者：不明」になったり、URLで直接アクセスした先のデータが存在しないといったバグが起こりえます。「アプリケーション側で確認するから大丈夫」という判断をすることもできますが、ふとした考慮漏れからこうしたデータ不整合を引き起こしてしまうことも考えられます。

外部キー制約は、適切に扱うことで誤ったデータ操作や存在しないデータを参照するなどという問題を防いでくれます。 しかし、一方で本記事で示すようにパフォーマンスに関わる問題を引き起こす場合もあります。

そのため重要になるのは外部キーについて正しく理解して運用するということだと考えられます。

1. PostgreSQLにおける外部キーの内部動作

まずはじめに、PostgreSQLの内部動作について調べていきたいと思います。

PostgreSQLでは、外部キー制約は内部トリガー（RI Trigger）として実装されています。RIとは Referential Integrity（参照整合性）の略で、外部キーを定義すると、これらのトリガーが内部的にSQLを実行して参照整合性を確認します。具体的には以下のチェックが自動的に追加されます。

  • 子テーブルへのINSERT / UPDATE時 → 親テーブルに対応する行が存在するか確認
  • 親テーブルのDELETE / UPDATE時 → FKのactionがNO ACTIONの場合、子テーブルに参照している行が存在しないか確認

親テーブル、子テーブルは次のSQLのようになります。

CREATE TABLE users (
  id UUID PRIMARY KEY
);

CREATE TABLE invoices (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id)
);

ここで重要なのは、親テーブルのDELETE時、PostgreSQLは子テーブルに対して参照行の存在確認を必ず実行するという点です。この確認にはインデックスがあればIndex Scanが使われますが、なければSeq Scan（全表走査）になります。DELETEのコストは「親テーブルのサイズ」ではなく「子テーブルの検索効率」に依存し、usersのような中心エンティティほど影響は大きくなります。

PostgreSQLは子側カラムにインデックスを自動生成しない

前のセクションで見た通り、親テーブルのDELETE時にはPostgreSQLが子テーブルをスキャンして参照行の存在を確認します。このスキャンのパフォーマンスを左右するのが、子テーブル側の外部キー列に対するインデックスの有無です。

外部キーを定義しても、子テーブル側の外部キー列にインデックスは自動生成されません。これはPostgreSQLの設計思想によるもので、すべての外部キーが検索に使われるとは限らないこと、インデックスは書き込みコストを増やすことが理由です。

PostgreSQLの公式ドキュメントにも、被参照テーブルからの行のDELETEや更新時にはスキャンが必要となるため参照列にインデックスを付けることが推奨されている旨の記載がありますが、自動生成はしないと明記されています。

余談ですが、MySQL（InnoDB）は外部キー列にインデックスがなければ自動で生成します。一方、Oracle、SQL Server、PostgreSQLは自動生成しません。MySQL経験者がPostgreSQLに移行した際に見落としやすい違いです。

インデックスがない場合に何が起きるか

次のようなSQLでデータを削除する場合を考えてみます。

DELETE FROM users WHERE id = '...';

子テーブルの外部キー列にインデックスがないと、Seq Scan（全表走査）が発生します。これは単に遅いだけでなく、ロック保持時間の増大、他トランザクションの待機、API遅延へと連鎖します。

原則として外部キー列にはインデックスを作成する。

特に親テーブルに対してDELETEやUPDATEが発生するケースでは、インデックスがないと子テーブルの全表走査が発生し、深刻なパフォーマンス問題につながります。レコード数が少ないマスタテーブルやenum的なテーブルなど、例外的に不要なケースもありますが、迷ったら作成しておく方が安全です。

2. 実際の改善事例

冒頭で触れた通り、不要データを削除するクエリでパフォーマンス問題が発生しました。

今回はusersテーブルのデータを削除する場合を想定して解説します。

DELETE FROM users u
WHERE company_id <> 'xxx'
  AND split_part(email, '@', 2) = 'example.com';

削除対象はたった1行。にもかかわらず、実行時間は 1,406ms でした。

EXPLAIN ANALYZEを使い実行計画を確認したところ、外部キー制約の子テーブルの参照に時間がかかっていることが判明しました。

usersは中心エンティティであり、非常に多くのテーブルから外部キーで参照されていました。DELETE時にすべてのFKトリガーが発火し、特に重かった上位5制約だけで 858ms（全体の61%） を占めていました。

結果、多くの子テーブルでFK列にインデックスが存在しなかったことが判明しました。

速度を改善するためにインデックスを追加します。追加は次のSQLで実行します。

CREATE INDEX CONCURRENTLY idx_child_table_fk_column
  ON child_table (fk_column);
-- 他4テーブルも同様

改善結果

項目 改善前 改善後
総実行時間 1,406ms 523ms
上位5制約合計 858ms 1.6ms

インデックスを5つ追加しただけで63%の改善。残りのFK列にもインデックスを追加すれば200ms台まで改善可能な見込みです。

3. ロックの観点

外部キーで最も実務影響が大きいのはロック挙動です。クエリが「遅い」こと自体よりも、ロック待ちでトランザクションが進めなくなってしまうことがあります。 外部キー制約のチェック中はロックが保持されるため、その間に同じ行を更新・削除しようとする別のトランザクションが待たされ、それがAPI全体の応答遅延や接続プールの枯渇に波及します。

例えば、以下のように親テーブルから1行削除するケースを考えます。

PostgreSQLは内部的に、子テーブルに参照行が存在しないかを確認するため、子テーブルに対してFOR KEY SHAREロックを取得します。

FOR KEY SHAREは子テーブルの該当行に共有ロックを取るもので、他トランザクションはその行のキー列の更新と削除ができなくなります。ただし、キー列以外のUPDATE（FOR NO KEY UPDATE）は許可されます。

内部的には以下のようなSQLが実行されています。

SELECT 1
FROM invoices
WHERE user_id = '...'
FOR KEY SHARE;

インデックスがない場合の連鎖的影響

インデックスがないと子テーブルの全行走査が発生し、スキャン時間がそのままロック保持時間になります。その間に別トランザクションが同テーブルのキー列を更新・削除しようとすると、FOR KEY SHAREと競合してロック待ちの状態になります。

FOR KEY SHAREは、行の削除やキー列の更新と競合しますが、キー列以外のUPDATE（FOR NO KEY UPDATE）とは競合しません。

また、以下のような状況ではデッドロックが発生する可能性があります。

トランザクションAがusers行を削除しようとし、同時にトランザクションBがinvoicesの行のキー列を更新しようとする場合を考えます。

  1. Aがusers行をロック
  2. Bがinvoices行をロック
  3. AがinvoicesをFOR KEY SHAREしようとするが、Bが保持中で待機
  4. Bがusersを参照しようとするが、Aが保持中で待機

このように相互待機が発生し、PostgreSQLがデッドロックを検出して、どちらかが強制ROLLBACKされます。中心エンティティへの操作が多い環境、更新頻度が高い子テーブル、長時間トランザクション、インデックス不足といった条件が重なるとこのパターンに遭遇しやすくなります。

4. CASCADEと参照アクションの設計判断

PostgreSQLでは、親テーブルの値が変更されたときのアクションを以下から選択できます。

アクション 挙動
RESTRICT 子が存在する限り、親の削除・更新を拒否（即時チェック）
NO ACTION デフォルト。子が存在すれば拒否。遅延制約時はCOMMIT時にチェック
CASCADE 親の削除・更新に連動して子も削除・更新
SET NULL 親が削除されると子のFK列をNULLに設定
SET DEFAULT 親が削除されると子のFK列をDEFAULT値に設定

RESTRICTとNO ACTIONの2つは似ているようで、遅延制約（DEFERRABLE）を使う場合に決定的な違いがあります。

遅延制約は外部キー制約のチェックタイミングをCOMMIT時に遅延させる仕組みです。

RESTRICTは遅延制約が有効でも即時チェックされ、トランザクション中にエラーになります。一方、NO ACTIONは遅延制約が有効なら通常はCOMMIT時にチェックされるため、トランザクション中は一時的に不整合を許容できます。 遅延制約を活用したい場合はNO ACTIONとセットで使うことになります。

また、CASCADEのトリガーは遅延制約を有効にしても遅延しません。PostgreSQLはCASCADEで実行された操作に対してもTrigger Eventを発行します。

気をつけるべきアクションとして ON DELETE CASCADEは便利ですが慎重な設計判断が必要です。

users → invoices → logsのようなチェーンがある場合、usersを1件削除するだけで数万行が連鎖的に削除されます。特に会計データ、承認履歴、監査ログ、法的証跡のような「消してはいけないデータ」にCASCADEを設定すると、WHERE条件のミスやデータ操作を行うコードのtypo、管理画面の誤操作などで重要なデータを削除や更新してしまう事故につながります。

そのためデータの性質に応じて削除ポリシーを分類すると、設計判断が明確になります。削除ポリシーは以下のように考えられます。

消してよいデータ → CASCADE

セッション、一時トークン、キャッシュ、通知の既読状態など、親が存在しなければ意味を持たないデータが該当します。判断基準は「親が消えた後にこのデータだけ残っていたら困るか？」です。残っていても誰も参照しないのであれば、CASCADEで親と一緒に消すのが自然です。

消してはいけないデータ → RESTRICT

会計データ、承認履歴、監査ログ、法的証跡など、ビジネス上または法律上の理由で保持義務があるデータが該当します。RESTRICTにしておけば、子が存在する限り親のDELETEがエラーになるため、「うっかり消してしまった」という事故を防げます。Digital Billderのような業務システムでは、請求書や発注書に紐づく明細データはこちらに分類されます。

消さないデータ → 論理削除

ユーザー、企業、請求書本体など、アプリケーション上は「削除」したいがデータとしては残しておく必要があるものが該当します。物理削除すると外部キーで繋がっている子テーブルすべてに影響が及ぶため、レコード自体は残したまま「削除済み」という状態を持たせ、アプリケーション側のクエリで除外するのが論理削除の考え方です。実装方法としては、deleted_atのようなタイムスタンプカラムを持たせる方法や、is_deletedのようなフラグカラムを使う方法、statusカラムにdeletedなどの値を持たせる方法があります。ただし、論理削除にはUNIQUE制約との相性の悪さ（削除済みレコードと新規レコードでキーが衝突する）や、フィルタ条件の付け忘れによる削除済みデータの露出など、運用上の落とし穴があります。

5. マイグレーション時の注意点

巨大テーブルに外部キー制約を追加する場合、素朴にALTER TABLEを実行すると大きな問題が生じます。

ALTER TABLE invoices ADD CONSTRAINT fk_invoices_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

この操作は既存データの全行チェックを行い、その間ShareRowExclusiveLockロックを取得します。ShareRowExclusiveLockはSELECTは許可しますが、INSERT / UPDATE / DELETEをブロックします。そのため、数百万行規模のテーブルでは全行チェックが長時間実行され、その間アプリケーションからの書き込みが停止する可能性があります。

まずステップ1として、NOT VALIDオプション付きで制約を追加します。これは「制約の定義だけをDBに登録して、既存データのチェックはスキップする」という意味です。

-- ステップ1: 制約を追加（既存データのチェックはスキップ）
ALTER TABLE invoices
  ADD CONSTRAINT fk_invoices_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

このステップでも取得されるロックはShareRowExclusiveLockです。

ただし NOT VALIDを付けた場合は既存データの全行チェックを行わないため、ロックの保持時間は非常に短くなります。この時点から、新しく実行されるINSERTやUPDATEに対しては制約が有効になります。

次にステップ2として、VALIDATE CONSTRAINTで既存データの整合性を検証します。

-- ステップ2: 既存データが制約を満たしているか検証
ALTER TABLE invoices
  VALIDATE CONSTRAINT fk_invoices_user_id;

このステップで取得されるのはShareUpdateExclusiveLockで、ステップ1よりもさらに弱いロックです。SELECT、INSERT、UPDATE、DELETEのいずれもブロックしません。全行チェックは行いますが、その間もアプリケーションは通常通り動作を継続できます。

この2段階に分けることで、強いロックが保持される時間を実質ゼロに近づけつつ、最終的には全データの整合性が検証された状態にできます。本番環境でFK制約を追加する場合は、この方法を使うべきです。

最後に、外部キー列にインデックスが存在しないケースを検出するSQLを紹介します。

SELECT
  c.conname AS constraint_name,
  t.relname AS table_name,
  a.attname AS column_name
FROM pg_constraint c
JOIN pg_class t ON c.conrelid = t.oid
JOIN pg_attribute a ON a.attrelid = t.oid
  AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = t.oid
      AND a.attnum = ANY(i.indkey)
  );

これは簡易版で単一列FK向けになりますが、実行すると以下のようにインデックスが未作成のFK列が一覧で出力されます。

 constraint_name          | table_name     | column_name
--------------------------+----------------+------------
 fk_invoices_user_id      | invoices       | user_id
 fk_expenses_user_id      | expenses       | user_id
 fk_order_items_order_id  | order_items    | order_id

We’re Hiring!

燈では、プロダクトの新機能開発はもちろん、SaaSとしての基盤を活かした UX改善・パフォーマンス改善・運用コスト削減 など、“プロダクトを良くし続ける” 取り組みに興味があるエンジニアを募集しています。

興味がある方は、ぜひカジュアル面談でお話しましょう！

akariinc.co.jp