外部キー: 速いかどうかを聞くのはやめよう
本当に最適化したいものは何かを問え。
私がこれまで見た中で最も高くついたデータベース最適化は、誰かがすべての外部キーを削除したことから始まった。
ボトルネックを測定したからではない。書き込みが実際に遅かったからでもない。「外部キーはスケールしない」とどこかで読んだからだ。6ヶ月後、彼らは20億件の孤立レコードを持ち、削除されたユーザーに請求するシステムを抱え、分析が40%も狂っている状態だった。
制約を元に戻そうとした時？データベースは、既に破損していた既存のデータを検証しようとして完全に停止した。
Web開発の世界には、外部キーは本質的に遅いものだという根強い考えがある。外部キーというのは「本当の」システムになれば外す練習用のwheelのようなものだ。しかしそれは、制約が何のためにあるのかという点を完全に誤解している。高速か低速かを選んでいるのではない。異なる失敗モードの間で選んでいるのだ。
こう考えてみてほしい。安全ガラス、シートベルト、エアバッグはすべて車に重さを加える。確かに車両を遅くし、燃費を悪くする。でも0-60タイムを最適化するためにそれらを引き剥がす人はいない。なぜなら、別のものを最適化しているからだ。
問題は外部キーが足を引っ張るかどうかではない。もちろんだ。問題は代わりに何を得られるかで、そしてそれが実際に必要かどうかだ。
本当にトレードオフしているもの
具体的な例を出そう。気象観測システムを作っていて、気象観測所、センサーデバイス、センサー測定値、米国の州のテーブルがある。
すべて外部キーで繋ぐべきか？実際に何が変わりうるか、後果は何かを考えてみよう。
米国の州はおそらく変わらない。ワイオミングがすぐに名前が変わることはない。参照データが静的なのが分かっている時に、挿入のたびに州コードを検証するために外部キーが必要だということはない。無駄なオーバーヘッドだ。
気象観測所は追加、移動、廃止される。だがここで問うべきなのは、過去の測定値が、誰かが誤って観測所レコードを削除した場合に「観測所を失う」ことを望むかどうかだ。観測所が消えても過去のデータはそのまま残したいのかもしれない。そうであれば、測定値を履歴スナップショットとして扱っており、外部キーすら適切ではない可能性がある。
センサー測定値は毎分何千回も挿入されている。外部キー検査のたびにルックアップが発生する。すべてのルックアップがテーブル上で競合を作る。遅い検証がキュー溢れを招き、リアルタイムデータを失うなら、それは孤児レコードがあることとは違う種類のデータ損失だ。
ここから先は分かるだろう。選択はパフォーマンス対正確性という抽象概念の話ではない。特定の失敗のうち、どちらがより容認しやすいかという話だ。あなたの実際の制約と実際の結果次第である。
誤った参照が請求データの破損や規制違反を意味するなら、パフォーマンスコストに関係なく、外部キーで守りたいはずだ。遅い検証がリアルタイムセンサーデータをキュー溢れで永遠に失うことを意味するなら、検証は間違ったトレードオフかもしれない。
高速書き込みが本当に重要な時
では、最大書き込み速度が必要だと決めたわけだ。キューが積み上がり、トランザクションがタイムアウトし、外部キー検査が実際に計測した問題を引き起こしている。理論ではなく、実際にだ。
いくつかのオプションがある。トランザクション分離レベルを SERIALIZABLE から READ COMMITTED に変えれば速くなるが、一部の整合性保証を手放すことになる。あるいは、1件ずつではなく1000行ずつトランザクションで挿入して、外部キーのオーバーヘッドを償却することもできる。あるいは、参照を検証すらしない、追記専用のログ構造に非正規化することもできる。
3番目のオプションはチートではない。単に別の設計だ。
CREATE TABLE sensor_log ( id BIGSERIAL PRIMARY KEY, recorded_at TIMESTAMPTZ NOT NULL, data JSONB NOT NULL -- { station_id, sensor_id, temp, humidity, ... });
CREATE INDEX ON sensor_log USING GIN (data);CREATE INDEX ON sensor_log (recorded_at);結合なし。外部キー検査なし。ただデータを追加して、時間範囲またはJSONB blobのGINインデックスでクエリするだけ。これは「ベストプラクティス」か？データベースの教科書が教える意味では、おそらく違う。Raspberry Piで毎分50,000行を挿入している時に動作するか？もちろん動作する。
人々が「ベストプラクティス」を道徳的命令としてではなく、一般的なシナリオでうまくいくパターンとして扱い、それがあなたのシナリオに当てはまらないかもしれないものとして扱う時に起きる食い違いがある。
正規化の罠
データベースの授業は正規化を教えるのが好きだ。重複をあらゆるコストで避けろ。第3正規形かそれか。
その結果こういうものになる：Orders → OrderItems → Products → Variants → Colors → Sizes
「去年のクリスマスに赤いシャツと青いシャツのどちらを注文した？」に答えるためだけに、6テーブルの結合が必要だ。さらに商品名を含めようものなら、カタログ階層のさらに3つ先の結合が必要になる。
しかし待ってほしい。正当化は通常「ブランドがBlueのラベルを変更したらどうする？」だ。それが起こったら、本当に過去の注文が遡及的に色変更されてほしいのか？当然ない。誰かが注文した時、彼らは「その瞬間に存在していたMサイズの青いTシャツ」を買ったのであって、後で更新されるかもしれないカタログエントリへの抽象的な参照ではない。
これは繊細なので、じっくり考える価値がある。あるデータは本質的にスナップショットであり、参照ではない。スナップショットデータをライブ参照であるかのように扱うと、書き込み時に単に非正規化すべきだったものを再構築するために、このばかげた結合の増殖に陥る。
{"color": "blue", "size": "M"} を注文に直接保存しよう。終わりだ。
スナップショットデータの認識
何かがスナップショットであるべき時をどう知るか？自分に問いかけてみて、それが point-in-time record かどうかを見る。
注文は購入時の商品詳細をキャプチャする。監査ログはアクション実行時のユーザー状態を記録する。history テーブルは更新前のレコード状態を保存する。イベントストリームは何が起きたか、いつ起きたか、どのようなデータで起きたかをキャプチャする。
答えが「はい、これは瞬間を記録している」なら、正規化を止めてください。スナップショット化し始めましょう。
非透過ブロブ
スナップショット以外にもう1つのカテゴリがある。クエリしないデータだ。ただ保存して全体を取得する。
LLMモデル設定のような {"model": "gpt-4", "temperature": 0.7, "max_tokens": 2000} は、temperature でクエリするものではない。必要時にリクエストIDで全体設定を取得する。デコード後のJWTペイロード、デバッグ用のAPIリクエスト/レスポンスログ、テーマ設定と通知フラグを含むユーザー preference オブジェクト。これらはすべて非透過ブロブだ。正規化は不要。外部キーも不要。JSONBに詰め込んで先に進もう。
6テーブル結合してどの色のシャツが注文されたか調べる？それは適切な正規化ではない。それは参照と値のどちらを保存しているかの混乱した思考だ。
（ただし注意：後でそのデータをクエリする必要がある場合、spectacularly 裏目に出る可能性がある。The JSONB Seduction 参照。このアプローチが本身 nightmare を作成するタイミングについて。）
スケールはコンテキストだ
「外部キーはスケールしない」と聞いたことがあるだろう。しかしスケールはハードウェアとアーキテクチャに対して完全に相対的だ。
microSDカードに毎分10,000件のセンサー測定値を記録するRaspberry Pi？それは確かにそのハードウェアにとっては高音量のスケールだ。プロビジョンドIOPSを持つAWS Auroraで数十億行を処理するなら？ 外部キーで通しても汗をかかない。
本当の hard limit は行数や書き込みボリュームについてではない。シャーディングについてだ。
Users テーブルがサーバーAにあり、Orders テーブルがサーバーBにある場合、外部キーは物理的に実現できない。データベースにはネットワーク境界全体で制約を強制するメカニズムがない。その時点で、あなたは既に孤児を見つけるバックグラウンドジョブを実行し、結果整合性パターンを実装している。
これはコンプライアンス理由で各テナントが分離されたデータベースを持つマルチテナントSaaS、または50,000個のエッジデバイスそれぞれがローカルでSQLiteを実行するIoT展開で起こる。一旦そこに到達したら、パフォーマンス考慮に関わらず外部キーはテーブルから外される（文字通り）。
しかしそのアーキテクチャ境界に到達するまでは、10ユーザーの内部ツールを構築しているときにNetflixの問題のために prematurely 最適化しないことだ。
実際にはどう見えるか
「外部キーを使用するべきか」ではなく、この3つのことを問いかけてみよう。
この参照が間違っていると何が壊れるか？訴訟、請求データの破損、規制違反？それとも分析ダッシュボードでnullを返すだけの欠落結合？
検証が遅いと何が壊れるか？取り返しのつかないリアルタイムデータを失うか？それともクエリがただ50ミリ秒余分にかかるだけか？
このデータはスナップショットか参照か？ある瞬間の様子を記録しているか？それとも権威ある current 値を指しているか？
そこから、パターンは自然に浮かび上がる。
金融取引、認証セッション、データ破損が法的責任を意味するものは、パフォーマンスオーバーヘッドに関係なく外部キーを求めるべきだろう。
大量のログ、追記専用の時系列データ、毎分100万イベントを書き込むものは、書き込みごとの検証オーバーヘッドを必要としないかもしれない。
注文や監査ログのような履歴スナップショット、ユーザー設定のように常に完全な blob として取得するデータ、外部APIからの webhook ペイロードのような自分で制御できないスキーマ……これらは非正規化の方がうまくいくことが多い。
しかし「おそらく」や「しばしば」と言ったことに注意してほしい。コンテキストが重要で、あなたのコンテキストは私のと違う。
最後に
外部キーはパフォーマンス問題ではない。書き込み速度とデータ整合性のトレードオフであり、そのトレードオフが意味があるかどうかは、あなたの特定のボトルネックと特定の結果に完全に依存する。
本当の問題は、人々が「Webスケール」について読んだからという理由で外部キーを削除することだ。実際に書き込みパフォーマンスの問題があるか、また何を諦めているかを考慮せずにやってしまう。greenfield プロジェクトで 1日100トランザクションを処理するものにNetflixのアーキテクチャを貨物崇拝することになる。
パフォーマンスコストがあなたのユースケースにとって価値があるかもしれない。あるいは価値がないかもしれない。しかし少なくとも、あなたが実際に最適化しているもののために決定を下して、それを最適化すべきだと思っているもののためにではない。
何を最適化していますか？