EAV durch JSONB in PostgreSQL ersetzen

TL; DR: JSONB kann die Entwicklung von Datenbankschemata erheblich erleichtern, ohne die Abfrageleistung zu beeinträchtigen.

Einführung

Betrachten wir ein klassisches Beispiel, wahrscheinlich eine der ältesten Anwendungen in der Welt der relationalen Datenbanken: Wir haben eine Entität und müssen bestimmte Eigenschaften (Attribute) dieser Entität speichern. Doch nicht alle Instanzen können über denselben Satz von Eigenschaften verfügen, und zukünftig könnte es notwendig sein, weitere Eigenschaften hinzuzufügen.

Der einfachste Weg, dieses Problem zu lösen, besteht darin, für jede Eigenschaft einen Spalten in der Datenbanktabelle zu erstellen und nur die für eine bestimmte Instanz der Entität benötigten Werte auszufüllen. Das klingt gut! Problem gelöst… bis Ihre Tabelle Millionen von Datensätzen enthält und Sie eine neue Datensatz hinzufügen müssen.

Betrachten wir das EAV-Muster (Entity-Attribute-Value), es kommt ziemlich häufig vor. Eine Tabelle enthält Entitäten (Datensätze), eine andere Tabelle enthält die Namen der Eigenschaften (Attribute), und eine dritte Tabelle verbindet die Entitäten mit ihren Attributen und enthält die Werte dieser Attribute für die aktuelle Entität. Dies ermöglicht es Ihnen, verschiedene Eigenschaften-Sets für unterschiedliche Objekte zu haben und Eigenschaften „on the fly“ hinzuzufügen, ohne die Datenbankstrukturen zu ändern.

Dennoch würde ich diese Anmerkung nicht schreiben, wenn es keine Nachteile beim Ansatz mit EVA gäbe. Um beispielsweise eine oder mehrere Entitäten zu erhalten, die jeweils 1 Attribut haben, sind 2 Joins im Abfragesyntax erforderlich: der erste – der Join mit der Attributtabelle, der zweite – der Join mit der Wertetabelle. Wenn die Entität 2 Attribute hat, sind bereits 4 Joins nötig! Außerdem werden alle Attribute normalerweise als Strings gespeichert, was zu Typumwandlungen sowohl für das Ergebnis als auch für die WHERE-Bedingung führt. Wenn Sie viele Abfragen schreiben, ist das aus Sicht der Ressourcennutzung ziemlich verschwenderisch.

Trotz dieser offensichtlichen Nachteile wird EAV bereits seit langem zur Lösung solcher Probleme eingesetzt. Diese Nachteile waren unvermeidlich, und es gab einfach keine bessere Alternative.
Doch dann tauchte in PostgreSQL eine neue 'Technologie' auf...

Mit PostgreSQL 9.4 wurde der Datentyp JSONB zum Speichern von binären JSON-Daten eingeführt. Obwohl das Speichern von JSON in diesem Format normalerweise etwas mehr Platz und Zeit benötigt als einfaches Text-JSON, erfolgen die Operationen damit deutlich schneller. Außerdem unterstützt JSONB die Indizierung, was die Abfragen noch schneller macht.

Der Datentyp JSONB ermöglicht es uns, das umfangreiche EAV-Muster zu ersetzen, indem wir lediglich eine JSONB-Spalte zu unserer Entitätstabelle hinzufügen, was das Datenbankdesign erheblich vereinfacht. Viele behaupten jedoch, dass dies mit einem Leistungseinbruch einhergehen sollte... Aus diesem Grund entstand dieser Artikel.

Einrichtung der Testdatenbank

Für diesen Vergleich habe ich eine Datenbank auf einer frischen PostgreSQL 9.5 Installation auf einem 80-Dollar-Setup erstellt. DigitalOcean Ubuntu 14.04. Nach der Anpassung einiger Parameter in der postgresql.conf habe ich sie gestartet. diese Ein Skript mit psql. Um die Daten im EAV-Format darzustellen, wurden folgende Tabellen erstellt:

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
);

Unten ist eine Tabelle dargestellt, in der dieselben Daten gespeichert werden, jedoch mit Attributen in einer JSONB-Spalte – properties.

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

Sieht viel einfacher aus, oder? Dann wurden in die Entitätstabellen (entity & entity_jsonb) 10 Millionen Datensätze eingefügt, und entsprechend wurden dieselben Daten in den Tabellen, die das EAV-Muster und den JSONB-Spaltenansatz verwenden, vervollständigt – entity_jsonb.properties. Somit haben wir verschiedene Datentypen unter der gesamten Eigenschaftensammlung erhalten. Beispielhafte Daten:

{
  id:          1
  name:        "Entity1"
  description: "Testentität Nr. 1"
  properties:  {
    color:        "rot"
    length:       120
    width:        3.1882420
    hasSomething: true
    country:      "Belgien"
  } 
}

So, jetzt haben wir dieselben Daten für zwei Varianten. Lassen Sie uns die Implementierungen im Betrieb vergleichen!

Vereinfachung des Designs

Bereits erwähnt wurde, dass das Design der Datenbank erheblich vereinfacht wurde: eine Tabelle, die durch die Verwendung einer JSONB-Spalte für Eigenschaften ersetzt wird, anstatt drei Tabellen für EAV zu nutzen. Aber wie spiegelt sich das in den Abfragen wider?

-- 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;

Wie wir sehen, sieht die letzte Abfrage nicht einfacher aus. Um den Wert einer Eigenschaft im JSONB-Objekt zu aktualisieren, müssen wir die Funktion jsonb_set(), verwenden und unser neues Wert als JSONB-Objekt übergeben. Dennoch müssen wir keinen Identifikator im Voraus kennen. Im Beispiel mit EAV mussten wir sowohl entity_id als auch entity_attribute_id wissen, um das Update durchzuführen. Wenn Sie eine Eigenschaft in der JSONB-Spalte basierend auf dem Objektname aktualisieren möchten, geschieht das alles mit einer einfachen Zeile.

Lassen Sie uns nun die Entität auswählen, die wir gerade mit der Bedingung ihrer neuen Farbe aktualisiert haben:

-- 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';

Ich denke, wir können uns darauf einigen, dass die zweite Option kürzer ist (ohne Join!) und somit lesbarer. Hier gewinnt JSONB! Wir verwenden den JSON-Operator ->>, um die Farbe als Textwert aus dem JSONB-Objekt abzurufen. Es gibt auch eine zweite Methode, um dasselbe Ergebnis im JSONB-Modell mit dem Operator @> zu erreichen:

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

Es ist etwas komplizierter: Wir überprüfen, ob das JSON-Objekt in der Spalte properties das Objekt rechts vom @> Operator enthält. Weniger lesbar, aber leistungsfähiger (siehe weiter unten).

Lassen Sie uns die Verwendung von JSONB weiter vereinfachen, wenn Sie mehrere Eigenschaften gleichzeitig auswählen müssen. Hier spielt der JSONB-Ansatz wirklich seine Stärken aus: Wir wählen einfach die Eigenschaften als zusätzliche Spalten in unserem Ergebnis-Set aus, ohne dass Joins erforderlich sind:

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

Mit EAV benötigen Sie 2 Joins für jede Eigenschaft, die Sie abfragen möchten. Meiner Meinung nach zeigen die oben aufgeführten Abfragen eine erhebliche Vereinfachung im Datenbankdesign. Weitere Beispiele, wie man Abfragen für JSONB schreibt, finden Sie auch in diesem dem Beitrag.
Nun ist es an der Zeit, über die Leistung zu sprechen.

Leistung

Um die Leistung zu vergleichen, habe ich EXPLAIN ANALYZE in den Abfragen verwendet, um die Ausführungszeit zu messen. Jede Abfrage wurde mindestens drei Mal ausgeführt, da der Abfrageplaner beim ersten Mal mehr Zeit benötigt. Zunächst führte ich die Abfragen ohne Indizes aus. Offensichtlich war dies ein Vorteil für JSONB, da die Joins, die für EAV erforderlich sind, keine Indizes nutzen konnten (die Felder der Fremdschlüssel waren nicht indiziert). Danach erstellte ich einen Index für die 2 Spalten der Fremdschlüssel in der EAV-Wertetabelle sowie einen Index GIN für die JSONB-Spalte.

Datenaktualisierungen zeigten folgende Ergebnisse in Bezug auf die Zeit (in ms). Beachten Sie, dass die Skala logarithmisch ist:

Wir sehen, dass JSONB deutlich schneller ist (> 50.000 Mal) als EAV, wenn keine Indizes verwendet werden, aus dem oben genannten Grund. Wenn wir die Spalten mit Primärschlüsseln indizieren, verschwindet der Unterschied nahezu, aber JSONB ist immer noch 1,3 Mal schneller als EAV. Beachten Sie, dass der Index in der JSONB-Spalte hier keinen Einfluss hat, da wir die Eigenschaftsspalte nicht in den Bewertungsbedingungen verwenden.

Für die Auswahl von Daten basierend auf dem Wert einer Eigenschaft erhalten wir folgende Ergebnisse (üblicher Maßstab):

Es ist zu bemerken, dass JSONB erneut schneller funktioniert als EAV ohne Indizes. Wenn EAV jedoch Indizes hat, arbeitet es immer noch schneller als JSONB. Später bemerkte ich, dass die Zeit für JSONB-Abfragen gleich blieb, was mich zu dem Schluss führte, dass der GIN-Index nicht ausgelöst wurde. Anscheinend wirkt der GIN-Index bei einer Spalte mit ausgefüllten Eigenschaften nur, wenn der Einschlussoperator @> verwendet wird. Ich habe dies in einem neuen Test verwendet, der enorme Auswirkungen auf die Zeit hatte: nur 0,153 ms! Das ist 15.000 Mal schneller als EAV und 25.000 Mal schneller als der Operator ->>.

Ich denke, das war schnell genug!

Größe der DB-Tabellen

Lassen Sie uns die Tabellengrößen bei beiden Ansätzen vergleichen. In psql können wir die Größe aller Tabellen und Indizes mit dem Befehl anzeigen dti+

Für den EAV-Ansatz betragen die Tabellengrößen etwa 3068 MB und die Indizes bis zu 3427 MB, was insgesamt 6,43 GB ergibt. Bei Verwendung des JSONB-Ansatzes werden 1817 MB für die Tabelle und 318 MB für die Indizes verwendet, was 2,08 GB entspricht. Das ist dreimal weniger! Diese Tatsache hat mich etwas überrascht, da wir die Eigenschaftsnamen in jedem JSONB-Objekt speichern.

Aber die Zahlen sprechen für sich: Im EAV speichern wir 2 ganzzahlige Fremdschlüssel für den Attributwert, was zu 8 Byte zusätzlichen Daten führt. Außerdem werden im EAV alle Werte als Text gespeichert, während JSONB wo möglich numerische und boolesche Werte verwendet, was zu einem geringeren Volumen führt.

Ergebnisse

Insgesamt denke ich, dass die Beibehaltung der Eigenschaften von Entitäten im JSONB-Format das Design und die Wartung Ihrer Datenbank erheblich vereinfachen kann. Wenn Sie viele Abfragen durchführen, funktioniert alles, was in einer Tabelle mit der Entität gespeichert ist, tatsächlich effizienter. Darüber hinaus vereinfacht es die Interaktion zwischen den Daten, was bereits einen Vorteil darstellt, und die resultierende Datenbank ist um das Drei-fache kleiner.

Auch aus den durchgeführten Tests lässt sich zusammenfassen, dass die Leistungseinbußen äußerst gering sind. In einigen Fällen arbeitet JSONB sogar schneller als EAV, was es noch vorteilhafter macht. Dieser Benchmark-Test deckt jedoch natürlich nicht alle Aspekte ab (z.B. Entitäten mit einer sehr großen Anzahl von Eigenschaften, signifikante Zunahme der Anzahl der Eigenschaften bestehender Daten,...), daher, wenn Sie Vorschläge zur Verbesserung haben, zögern Sie bitte nicht, diese in den Kommentaren zu hinterlassen!

Quelle: habr.com