PostgreSQLのNull許容外部キーの使い所について
はじめに
こんにちは、経営管理クラウドを開発するログラスのエンジニアの@Yuiiitotoです。
会社全体で色々なアドベントカレンダーにお邪魔させていただいております。
今回は、PostgreSQLのAdvent Calendar 2021の1つの記事として出しています。
PostgreSQLのNull許容外部キーの使い所について
はじめにいっておくと、弊社ログラスではNull許容外部キーについてはよく使っています。
使い所は基本的に 0..1対0..N の関係性のときです。
猫は野良猫がいるので飼い主がない場合があります。その場合、猫は飼い主IDを持っていません。
飼い主は猫を複数飼うことができます。また猫を飼っていない飼い主もいます。
このときcatsテーブルのowener_id(飼い主ID)をnullbleにすることで飼い主のいない猫（野良猫)を表現することが可能です。
この場合のテーブルはNull許容外部キーを使わずに中間テーブルを使うことでも実現可能です。
これはNULLをなくした正規化を行った教科書チックなパターンであるといえますね。
Null許容外部キーのIS NULL検索や IS NOT NULL検索
Null許容外部キーパターンは、素朴な疑問として IS NULL検索や IS NOT NULL検索などにインデックスを用いたクエリができるのでしょうか？
答えはYESです。
デフォルトでは、B-treeインデックスは項目を昇順で格納し、NULLを最後に格納します。
ということなのでB-treeインデックス内の最後にNULLは格納され、とのインデックスを使用して IS NULL検索や、IS NOT NULL検索などができます。
なので今回の例でいうと、Null許容外部キーパターンでも飼い主のいる猫や飼い主のいない猫を参照する場合もインデックスを利用することが可能です。
IS NULL 検索に関しては、 nullの値が少ない場合には部分インデックスをつけると高速化することが可能です。
「SQL高速化 in PostgreSQL」という本の「2.1 削除フラグがついたデータを高速に除外する」章では、nullの値が圧倒的に少ないor多い場合に値がnullではない場合だけを条件にした部分インデックスを追加することが奨励されています。
create index cats_owner_id_idx
on cats(owner_id)
where owner_id is not null;
なぜこれで速くなるのかというと、インデックスサイズがnullの値分減り、メモリに載りやすくなるためです。
一方で、中間テーブルパターンで飼い主のいない猫や飼い主のいる猫を検索する場合は中間テーブルをジョインする場合があり、その分コストがかかります。
結局どっちがいいの？
SQLでは大抵のケースでテーブルの結合は重い処理であり、そのため多くのケースではNull許容外部キーパターンのほうがパフォーマンスが良いのではないかと筆者は考えています。（もちろん前述の通りケースバイケースではあるが）
また、中間テーブルをはさむと多かれ少なかれSQLやORマッパーのコードが複雑になるためおすすめしません。
NULLを入れるのはよくないと思っている方はいると思うのですが、これを気にNullを許容した外部キーを使ってみるのはどうでしょうか？
Discussion
Null可な外部キーという発想がなかったので、この記事を読んでいろいろと考えました。
データとデータが関連するのには2パターンあります。
前者の例は、記事テーブルとコメントテーブルがあるときのコメント生成です。この場合はコメントテーブルに記事ID があり、それがnullになることは将来にわたってあり得ません。(ただし、記事削除のアクションで、記事IDをnullに設定するということはありえる)。
後者の場合は、時系列で関係性が変わりうるということで、そしてありがちなのが、過去のある時点での関係性を知りたいという要求が発生するということです。
例えば、新入社員は一定期間新人研修があり、その後ある部署に配属され、将来的に人事異動がありうるなどのケースです。この場合、今現在要求がないにしても「今の所属部署IDを従業員テーブルの属性とする」という設計より、所属を表す別テーブルにしておいた方が無難だと思う人が多いのではないかと思います。
Null可な外部キーというのは、内在的に時系列で関連が変化しうることを示唆しているので、個人的にはそのような外部キー項目を作るのには本能的な危険を感じます。私の場合は、少なくとも関係性が発生したタイムスタンプを持った連関テーブルで実装すると思います。
ありがとうございます。時系列という観点のもと非常に的を得ているご意見だなと思います。
ただパフォーマンスという観点はNull可な外部キーを貼るほうが良くなるケースもあるのでそこは正規化をどこまで崩すかのトレードオフと理解しています。
とても参考になるご意見ありがとうございます。
自分のコメントまで引用になっていますね。失礼しました。
正しくは
これに関してはNull可でなくとも関連先が変わるということもあるので正しくは「初めはNullだった可能性が捨てきれない」&「これからNullになる可能性が捨てきれない」、という話ですね。とても理解できます。
はい、パフォーマンスを追求したくない場合はこのケースのほうがいいかもしれませんね（記事中ではJOINが無いほうが実装的にシンプルじゃない？と話していますが、話をきいて基本は連関テーブルを作っていくほうがいいのではと思いました）
ですね。失礼しました。