PostgreSQLのアンチパターン : 何でもかんでもjsonに入れる

PostgreSQLは、データ型としてjsonをサポートしています。しかし、やりたいことがある時に何でもかんでもjson型を使ってしまうというのはやめるべきです。これは、hstoreや新しく登場したjsonb型にも同じことが言えます。これらの型は必要な時には便利なツールになりますが、PostgreSQLでデータのモデリングを行う際に最初に検討すべきものではありません。 なぜなら、データを呼び出したり操作したりするのが難しくなってしまうためです。

何もかも同じところに入れてしまおうとすることによるアンチパターンをご存知の読者もいるでしょう。EAVアンチパターンは、長らくデータベーススキーマにおける必要悪とされてきました。使わない方がいいとアドバイスし見下してきつつも、現実やビジネス上の要求の全てが正しいリレーショナルモデルの厳密な形に収まるわけではない時の最後の逃げ道だったのです。ご存じない人のために付け加えるとこれはつまり、リレーショナルデータベースを、オブジェクトID(エンティティ)とキー(アトリビュート)と値(バリュー)の列を使った貧者のキーバーリューストアにしてしまうスキーマのデザイン法でした。そういったテーブルに対するクエリは、たくさんの自己結合を使わなくてはならなくなります。

必要な時は非常に便利ですが、最初から選ぶものではないという意味で、json型は新しいEAVだと言えます。

(これは、クエリを組み立てる上では非常に便利ですが、信頼できるデータを保存する方法としては適していない、PostgreSQLの配列型にも当てはまります)

JSON blobのテーブル

PostgreSQL 9.3のリリース、さらにjsonbが登場した9.4のリリース以後、Stack Overflowでjsonオブジェクトを結合する方法や、配列の要素内に入れ子になったキーを問い合わせる方法などを尋ねる人が増えてきているようです。それはそれでいいのですが、スキーマやデータの例を聞いてみると、質問者は大抵以下のようなものを投稿してきます。

CREATE TABLE people(
    id serial primary key,
    data jsonb not null
);

INSERT INTO people(data) VALUES ($$
{
    "name": "Bob",
    "addresses": [
        {
            "street": "Centre",
            "streetnumber": 24,
            "town": "Thornlie",
            "state": "WesternAustralia",
            "country": "Australia"
        },
        {
            "street": "Example",
            "streetnumber": "4/311-313",
            "town": "Auckland",
            "country": "NewZealand"
        }
    ],
    "phonenumbers": [
        {
            "type": "mobile",
            "number": "12345678"
        }
    ]
}
$$);

INSERT INTO people(data) VALUES ($$
{
  "name": "Fred",
  "phonenumbers": [
    { "type": "mobile", "number": "12345678" }
  ]
}
$$);

INSERT INTO people(data) VALUES ($$
{
  "name": "John Doe"
}
$$);

そして、「同じ電話番号を持つ人を探すにはどうしたらよいでしょうか？」と質問してきます。

リレーショナルデータベースの経験豊富なユーザなら、見ただけで顔をしかめるでしょう。しかし、子から親へ外部キーを逆にたどる考え方や、複合エンティティを分解して正規化することに、誰もが慣れているわけではないことにも考えを巡らせてみましょう。そう考えると、リレーショナルデータベースも奇妙なものだとも言えます。

PostgreSQLの強力さは、当然ながらそのリレーショナルモデルから来るものです。json blobでデータを保存するのは、テーブルや列の統計情報からクエリプランナがちょうどいい実行計画を判断してくれるのを拒否することであり、インデックスの機能やスキャンの方式の多くが使えなくなることであり、よりプリミティブな処理しかできなくなるということなのです。何か複雑なことをしようとすると、たくさんの自己結合やフィルタが必要になってしまう傾向があります。

複雑すぎるクエリ

例として、同じ電話番号を持つ人を探すクエリは以下のようになります。

select
  p1.id AS person1,
  p2.id AS person2,
  p1.data ->> 'name' AS "p1 name",
  p2.data ->> 'name' AS "p2 name",
  pns1 ->> 'type' AS "type",
  pns1 ->> 'number' AS "number"
from people p1
  inner join people p2
    on (p1.id > p2.id)
  cross join lateral jsonb_array_elements(p1.data -> 'phonenumbers') pns1
  inner join lateral jsonb_array_elements(p2.data -> 'phonenumbers') pns2
    on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');

このクエリには、十分に読みやすい美しさがあると言えるでしょうか？

jsonをサポートする関数や演算子に特別悪いところがあるわけではないのですが、入れ子になっているオブジェクト風のデータを扱ったり、リレーショナルな結合や述語を使って問い合わせを行うのが不格好になってしまうのです。

一応PostgreSQLはLATERALをサポートしていますが、それがなければこのクエリを書くのは悪夢だと言えるでしょう。

この例に対するリレーショナルなスキーマへの同じ問い合わせのクエリは、そこまできれいになるかというと、実はそうでもありません。

SELECT
  p1.id AS "person1",
  p2.id AS "person2",
  p1.name AS "p1 name",
  p2.name AS "p2 name",
  pns1.phoneno AS "p1 phone",
  pns2.phoneno AS "p2 phone"
FROM
  person p1
  INNER JOIN person p2 ON (p1.id < p2.id)
  INNER JOIN phone pns1 ON (p1.id = pns1.person_id)
  INNER JOIN phone pns2 ON (p2.id = pns2.person_id)
WHERE
  pns1."type" = pns2."type"
  AND pns1.phoneno = pns2.phoneno;

しかし、jsonベースのクエリと比べると、(phone."type", phone.phoneno)というインデックスを使えるのでかなり高速に実行できる可能性が高いですし、実行中にデータをディスクから読む量も相当減るでしょう。

データ型の固定ができない問題

上に出てきたようなjsonベースのクエリは、バグの温床でもあります。jsonbの比較においては、データ型の影響を受けやすいのです。つまり、jsonbの値の"12345678"と12345678は、等価ではないのです。

regress=> SELECT '12345678'::jsonb, '"12345678"'::jsonb, '12345678'::jsonb = '"12345678"'::jsonb AS "isequal";
  jsonb   |   jsonb    | isequal 
----------+------------+---------
 12345678 | "12345678" | f

上のようにデータが入っている時に、

insert into people (data) values ('{"phonenumbers": [{"type":"mobile","number":12345678}]}');

と実行しても、重複しているとは見なされず成功してしまいます。

ただし、Javascriptのインタプリタのルーズな型付けではこれらは等価として扱われるので、このような挙動はPostgreSQLの制約だとして議論があるのも確かです。

> 12345678 == "12345678"
> true

PostgreSQLのクエリでこれらの値を一致するものとして扱うには、クエリを

on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');

から、->>というjson値を文字列として扱う演算子を使って

on (pns1 -> 'type' = pns2 -> 'type' AND pns1 ->> 'number' = pns2 ->> 'number');

として、文字列として保存された数値を同じもとの見なす必要があります。

制約をかけられない問題

誰かが

insert into people (data) values ('{"phonenumbers": [{"type":"Mobile","number":"1234 5678"}]}');

というように、頭文字が大文字Mになっていたり数字にスペースが含まれている、他とは一致しないデータを挿入してしまうことがあり得ます。

json blobに入ってしまうと、その中の値には簡単にはCHECK制約をかけられないのです。ENUM型やDOMAINを使用したり、トリガで更新する正規化した隠し列を作ったり、そういったデータを正規化するために何らかの方法を使いましょう。PL/v8のようなプロシージャ言語でjsonをバリデートしたり変換するトリガを書く必要があるか、あるいはそういった機能をアプリケーション内で実装せざるを得ないかもしれません。

じゃあjsonはいつ使うべきなの？

ここまで書いたことを見ると、私はデータベースでjson型を使うのに反対のようだと考えるかもしれません。ところがそうではありません。伝統的なリレーショナルモデルに当てはまらないような、動的な列をもつデータがあるのなら、json型はEAVパターンを使うよりはましな選択です。あるいは、過去この問題に遭遇した人を見たら、jsonのストレージとしてMongoDBを別に立てて組み合わせたかもしれません。

例えば、アプリケーションユーザが要素に任意の属性を追加できるようにしたがることがあるかもしれません。それはビジネス上の要求です。アプリケーション内でメモを残せるようにしたいのに、それはこのデータベースではできないとあなたが言ったら、クライアントは気に食わないでしょう。そして彼等は「それならアプリケーション内でできるようにしろ」「MongoDBを使っている時はそんなことはなかった」と言うでしょう。

従って、基本的にはリレーショナルにモデリングしつつ(代理キーをやたらと使うことになってしまうとしても)、追加データのためにpersonにjson列を追加し、personを表示する時にアプリケーションから取得できるようにすべきです。

jsonを使うべきかの判断方法

通常のリレーショナルモデルを使ったデータベースに収まらないデータがある場合、json型を使いましょう。EAVパターンを使ったり、JavaやRubyやPythonなどのオブジェクトをシリアライズしてbytea列に格納したり、どこか別の構造化した外部オブジェクトを参照するキーを保存したりといった時こそが、json列に手を出すべき時です。

そしてこの法則は、新しいアプリケーションにおいてjsonbをhstoreに置き換えた時にも恐らく成り立ちます。

json内にインデックスを張ったり内部に対するクエリを発行しないなら、もちろん普通のjson列も便利です。jsonはデータのやり取りがよりコンパクトで高速です。