(PostgreSQL Advent Calendar 2015 10日目)

こんちは！最近、「多対多なら双方にリストを持たせれば関連テーブルが消えてテーブル数が減らせるよ！」という記事を見つけて、頭を抱えてしまいました！これもO/Rマッパーの弊害かと思いましたが、どちらかというと難しいプログラミング言語をやってる人はその勉強に時間をとられて、テーブル設計その他を勉強する時間がないんでしょうね。

さて、「理論から学ぶデータベース実践入門」のイベントで発表したライトニングトークを文字起こししてみました。

• (slideshare) Nippondanji氏に怒られても仕方ない、配列型とJSON型の使い方

配列型や JSON 型の多いなる可能性を感じていただけたらと思います。

PostgreSQL には「配列型」があります。これは、複数の値を持てるデータ型です。集合と違って、順番を保持できるのがポイントです。
たとえば、緯度と経度をひとつのデータとして扱いたい場合、配列型を使えば¹簡単に緯度と経度の組を表現できます。集合とは違って順番を保持できるので、緯度と経度が逆に取り出されるようなことはありません。

「JSON 型」とは、その名の通り JSON データを保持するためのデータ型です。キーと値の組や、どんな構造かが事前には分かってないデータを保存するのに向いています。たとえば HTML フォームのスキーマ定義を格納するときなどに大変重宝します。

しかし配列型やJSON型を安易に使うと、怒られてしまいます。会場の皆さんは全員「理論から学ぶデータベース実践入門」を読んでるはずですので理由はわかると思いますが、配列型や JSON 型はひとつのカラムに複数の値を保持します。つまり第一正規化を完全に無視してるんですね。現場ではパフォーマンス上の理由から第三正規化を外すこともちょくちょくありますが、第一正規化を外すことは通常はしません。それをやってしまうのが配列型や JSON 型ですので、これは怒られても仕方ありません。

話は変わって、こちらのサンプルデータをご覧ください。product テーブルに商品が 2 つ、coloring テーブルに商品ごとのカラーバリエーションが登録されています。coloring テーブルの Product ID カラムは product テーブルの ID カラムを参照しますので、参照関係にある値を赤と青で色分けしています。

この 2 つのテーブルを join すると、このような結果が得られます。

• 商品 ID が 101 の T シャツ は 2 つのカラーバリエーションがあり、
• 商品 ID が 102 のセーターは 3 つのカラーバリエーションがあります。

このような join は見慣れたものであり、特に珍しくありません。

しかしアプリケーション開発時に欲しいのは、先のようなタプルの羅列ではなく、1 : N 関係を保ったままのデータ構造です。

通常の join だと、商品「ID=101 T-Shirt」が 2 回、商品「ID=102 Sweater」が 3 回、登場していました。アプリ開発時に欲しいのはそれではなくて、どちらの商品も 1 回しか登場せず、それらに複数のカラーバリエーションがぶら下がっているようなデータです。つまり 1 : N 構造を保った、ツリーのような構造ですね。そういうのが欲しいのに、join ではそれらが作れません。

こちらが実際の SQL 実行例です。「101 T-Shirt」が 2 回、「102 Sweater」が 3 回登場しています。こういうのをやめたい、というわけです。

ここで、PostgreSQL の array_agg() という集約関数を紹介します。通常の集約関数は、たとえば複数の値を合計するとか、複数の値の平均値を計算する、といったことができますよね。同じように、array_agg() は複数の値から配列を作ることができます。

これを使うと、複数の値を配列型としてまとめることができます。そのため、1 : N 関係を保ったままクエリ結果を取得できます。

「行コンストラクタ」という機能を使うと、値の組を複数取り出せます。たとえば今回の例なら、「カラーコード」と「カラー名」の組をまとめて取り出せます。ただし、実際は「行の配列」ではなく「文字列の配列」になってしまうので、取り出したあとにパースする必要があります。ちょっと使いにくいですね。

また集約関数 json_agg() を使うと、複数の値を集約して JSON データを作れます。これもデータを 1 : N のまま取り出すことに使え、また array_json() と違ってパースする必要がありません (または簡単にパースできます)。

一見よさそうに見えますが、実は json_agg() ではキー名を指定できなくて、デフォルトでは画面のように JSON のキーが f1、f2、…になってしまいます。json_agg()、残念な子です。

ところで PostgreSQL では、他の言語でいうところの構造体やレコード型に相当するものを定義できます。これは複合型と呼ばれています。複合型をうまく定義してあげて、それにキャストしてあげると、json_agg() でキー名が設定されます。

しかしこの方法だと、事実上、1 : N で取り出したい SQL ごとに複合型を定義する必要があります。これは使いづらい。Java の無名クラスのように、PostgreSQL でも無名複合型が定義できればいいんですが、そんな気の利いたものはありません。json_agg()、やはり残念な子です。

さらには、相関サブクエリを使う方法もあります。この方法だと、複合型を定義しなくても JSON データのキー名を指定できます。

しかし SQL をみれば分かるように、select 文が三重の入れ子になるんですよね。これは可読性が悪いし、相関なのでパフォーマンスも懸念が残ります。json_agg()、つくづく残念な子です。

このように、PostgreSQL の配列型や JSON 型を使うと、1 : N を 1 : N のまま取り出せます。

しかしこれまで見たように、使いやすいとはとてもいえません。SQL ごとに複合型を定義するわけにもいかないし、かといって相関サブクエリは複雑すぎます。

また各言語ごとの DB ドライバが、配列型や JSON 型に未対応なことがほとんどです。自分が調べた限りでは、Python 用以外はほぼ全滅でした。なので、いったん text 型として取り出してから自分でパースする必要があります。

配列型や JSON 型を嫌う人は結構いらっしゃると思います。なにせ第一正規化を平然と無視するような機能ですから、気持ちは分かります。

しかし、配列型や JSON 型をクエリ結果に使うのは、別にいいのではないでしょうか。大事なのはテーブルが正規化されていることであり、クエリ結果まで正規化される必要はない、と主張しておきます。

アプリケーション開発側にとっては、1 : N のまま取り出せるのは便利だし、とても自然なことだと感じます。これこそが本来あるべき姿であり、なぜ今まで出来なかったのか？と思ってしまうほどです。

そもそも、我々が欲しいのは Relational なのでしょうか？我々にとって欲しいのは、速くて便利でトランザクションが使える DB であり、必ずしも Relational である必要はないのではないでしょうか。正直言って、タプルの集合より、木構造のほうが便利です。

…なんてことを言ってるから、怒られても仕方ないでしょう。

まとめですが、PostgreSQL の配列型と JSON 型の集約関数 (array_agg() と json_agg()) を使うと、1 : N を 1 : N のまま取り出すことができます。
しかし、現状では使いやすいとはとてもいえず、さまざまな問題があります。また配列型や JSON 型に対する DB ドライバの対応状況もひどいありさまです。なんとかしたいですね。

というわけで、ポスグレに興味がわいたら、ポスグレのカンファレンスに参加してみてはどうでしょうか (注: すでに終了しています)。公式バナーなのに PostgreSQL のスペルミスがあるという、前代未聞のイベントですのでぜひ参加してみてください。

また、ポスグレの内部構造はこちらの本が参考になります。DB の仕組みを知るにはうってつけなので、興味のある方は読んでみてください。

発表は以上です。ありがとうございました。