PostgreSQL で EAV を JSONB に置き換える

TL; DR: JSONB は、クエリのパフォーマンスを犠牲にすることなく、データベース スキーマの開発を大幅に簡素化できます。

導入

おそらくリレーショナル データベース (データベース) の世界で最も古い使用例の XNUMX つである古典的な例を示しましょう。エンティティがあり、このエンティティの特定のプロパティ (属性) を保存する必要があります。 ただし、すべてのインスタンスが同じプロパティのセットを持つわけではなく、将来さらに多くのプロパティが追加される可能性があります。

この問題を解決する最も簡単な方法は、データベース テーブルにプロパティ値ごとに列を作成し、特定のエンティティ インスタンスに必要な列を単純に入力することです。 素晴らしい！ 問題は解決しました...テーブルに数百万のレコードが含まれており、新しいレコードを追加する必要があるまでは。

EAV パターンを考えてみましょう (エンティティの属性値）、それは非常に頻繁に発生します。 XNUMX つのテーブルにはエンティティ (レコード) が含まれ、別のテーブルにはプロパティ名 (属性) が含まれ、XNUMX 番目のテーブルにはエンティティとその属性が関連付けられ、現在のエンティティのそれらの属性の値が含まれます。 これにより、オブジェクトごとに異なるプロパティのセットを用意したり、データベース構造を変更せずにその場でプロパティを追加したりできるようになります。

ただし、EVA のアプローチにいくつかの欠点がなければ、私はこの記事を書いていなかったでしょう。 したがって、たとえば、それぞれ 1 つの属性を持つ 2 つ以上のエンティティを取得するには、クエリで 2 つの結合が必要です。4 つ目は属性テーブルとの結合、XNUMX つ目は値テーブルとの結合です。 エンティティに XNUMX つの属性がある場合、XNUMX つの結合が必要です。 さらに、通常、すべての属性は文字列として保存されるため、結果と WHERE 句の両方で型キャストが行われます。 大量のクエリを作成すると、リソースの使用という点で非常に無駄になります。

これらの明らかな欠点にもかかわらず、EAV はこの種の問題を解決するために長い間使用されてきました。 これらは避けられない欠点であり、これより優れた代替手段はありませんでした。
しかし、PostgreSQL に新しい「テクノロジー」が登場しました...

PostgreSQL 9.4 以降、JSON バイナリ データを保存するために JSONB データ型が追加されました。 この形式で JSON を保存すると、通常、プレーン テキストの JSON よりも多少多くのスペースと時間がかかりますが、操作の実行ははるかに高速になります。 JSONB はインデックス作成もサポートしているため、クエリがさらに高速になります。

JSONB データ型を使用すると、エンティティ テーブルに JSONB 列を XNUMX つ追加するだけで面倒な EAV パターンを置き換えることができるため、データベース設計が大幅に簡素化されます。 しかし、これには生産性の低下が伴うはずだと多くの人が主張しています...それで私はこの記事を書きました。

テストデータベースのセットアップ

この比較のために、$9.5 ビルドの PostgreSQL 80 の新規インストール上にデータベースを作成しました。 DigitalOcean Ubuntu 14.04。 postgresql.confでいくつかのパラメータを設定した後、実行しました この psqlを使用したスクリプト。 次の表は、データを EAV 形式で表示するために作成されました。

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

以下は、同じデータが保存されるテーブルですが、JSONB タイプの列に属性が含まれています。 プロパティ.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

かなりシンプルに見えますね。 次に、エンティティ テーブルに追加されました (エンティティ & エンティティ_jsonb) 10 万レコード、それに応じて、EAV パターンと JSONB 列を使用したアプローチを使用して、テーブルに同じデータが入力されました - エンティティ_jsonb.properties。 したがって、プロパティのセット全体の中からいくつかの異なるデータ型を受け取りました。 データ例:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

これで、両方のオプションに対して同じデータが得られました。 職場での実装の比較を始めましょう!

設計を簡素化する

データベース設計が大幅に簡素化されたことは前述しました。EAV に XNUMX つのテーブルを使用する代わりに、プロパティに JSONB 列を使用することで XNUMX つのテーブルになりました。 しかし、これはリクエストにどのように反映されるのでしょうか? XNUMX つのエンティティ プロパティを更新すると、次のようになります。

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

ご覧のとおり、最後のリクエストはそれほど単純ではありません。 JSONB オブジェクトのプロパティの値を更新するには、関数を使用する必要があります。 jsonb_set()、新しい値を JSONB オブジェクトとして渡す必要があります。 ただし、事前に識別子を知る必要はありません。 EAV の例を見ると、更新を実行するには、entity_id とentity_attribute_id の両方を知る必要があります。 オブジェクト名に基づいて JSONB 列のプロパティを更新する場合、すべて XNUMX 行で完了します。

次に、新しい色に基づいて更新したばかりのエンティティを選択しましょう。

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

XNUMX 番目の方が短い (結合がない) ため、より読みやすいことに同意できると思います。 ここでJSONBが勝ちます！ JSON ->> 演算子を使用して、JSONB オブジェクトからテキスト値として色を取得します。 @> 演算子を使用して JSONB モデルで同じ結果を達成する XNUMX 番目の方法もあります。

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

これは少し複雑です。プロパティ列の JSON オブジェクトに @> 演算子の右側にオブジェクトが含まれているかどうかを確認します。 読みやすさは低下しますが、生産性は向上します (下記を参照)。

複数のプロパティを一度に選択する必要がある場合に、JSONB をさらに簡単に使用できるようにしましょう。 ここで JSONB アプローチが本当に役に立ちます。結合を必要とせずに、結果セット内の追加列としてプロパティを選択するだけです。

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

EAV では、クエリするプロパティごとに 2 つの結合が必要になります。 私の意見では、上記のクエリはデータベース設計を大幅に簡素化していることを示しています。 JSONB クエリの記述方法のその他の例も参照してください。 これ 役職。
今度はパフォーマンスについて話します。

Производительность

私が使用したパフォーマンスを比較するには 分析する クエリで実行時間を計算します。 クエリ プランナーの初回実行には時間がかかるため、各クエリは少なくとも 2 回実行されました。 まず、インデックスを使用せずにクエリを実行しました。 EAV に必要な結合ではインデックスを使用できない (外部キー フィールドにはインデックスが作成されない) ため、これは明らかに JSONB の利点でした。 この後、EAV 値テーブルの XNUMX つの外部キー列にインデックスを作成し、インデックスも作成しました。 GIN JSONB列の場合。

データ更新の時間（ミリ秒）では以下の結果が得られました。 スケールが対数であることに注意してください。

前述の理由により、インデックスを使用しない場合、JSONB は EAV よりもはるかに (> 50000-x) 高速であることがわかります。 主キーを使用して列にインデックスを付けると、差はほとんどなくなりますが、それでも JSONB は EAV より 1,3 倍高速です。 評価基準でプロパティ列を使用していないため、JSONB 列のインデックスはここでは効果がないことに注意してください。

プロパティ値に基づいてデータを選択すると、次の結果が得られます (通常のスケール)。

JSONB はインデックスなしの EAV よりも高速に動作しますが、インデックスありの EAV の場合でも、JSONB よりも高速に動作することがわかります。 しかし、JSONB クエリの時間が同じであることがわかり、GIN インデックスが機能しないという事実に気づきました。 どうやら、プロパティが設定された列で GIN インデックスを使用する場合、それは include 演算子 @> を使用した場合にのみ有効になります。 これを新しいテストで使用しましたが、時間に大きな影響を与えました: わずか 0,153 ミリ秒でした。 これは、EAV より 15000 倍、->> 演算子より 25000 倍高速です。

十分速かったと思います！

データベーステーブルのサイズ

両方のアプローチのテーブル サイズを比較してみましょう。 psql では、次のコマンドを使用してすべてのテーブルとインデックスのサイズを表示できます。 dti+

EAV アプローチの場合、テーブル サイズは約 3068 MB、インデックスは最大 3427 MB で、合計 6,43 GB になります。 JSONB アプローチでは、テーブルに 1817 MB、インデックスに 318 MB、つまり 2,08 GB が使用されます。 なんと3倍も少ないことが判明！ すべての JSONB オブジェクトにプロパティ名を格納しているため、この事実には少し驚きました。

しかし、それでも数字がすべてを物語っています。EAV では属性値ごとに 2 つの整数外部キーを保存するため、8 バイトの追加データが生じます。 さらに、EAV はすべてのプロパティ値をテキストとして保存しますが、JSONB は可能な限り内部で数値とブール値を使用するため、フットプリントが小さくなります。

結果

全体として、エンティティ プロパティを JSONB 形式で保存すると、データベースの設計と保守がはるかに簡単になると思います。 多数のクエリを実行している場合は、すべてをエンティティと同じテーブルに保持すると、実際にはより効率的に動作します。 これによりデータ間のやり取りが簡素化されるという事実はすでにプラスですが、結果として得られるデータベースの容量は 3 分の XNUMX になります。

また、実行されたテストに基づいて、パフォーマンスの低下は非常にわずかであると結論付けることができます。 場合によっては、JSONB は EAV よりも高速であり、さらに優れています。 ただし、このベンチマークはすべての側面をカバーしているわけではありません (例: 非常に多数のプロパティを持つエンティティ、既存データのプロパティ数の大幅な増加など)。そのため、それらを改善する方法についての提案があれば、 、お気軽にコメントを残してください！

出所： habr.com