Ersetzen von EAV durch JSONB in ​​​​PostgreSQL

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

Einführung

Lassen Sie uns ein klassisches Beispiel für einen der wahrscheinlich ältesten Anwendungsfälle einer relationalen Datenbank (Datenbank) in der Welt geben: Wir haben eine Entität und müssen bestimmte Eigenschaften (Attribute) dieser Entität speichern. Möglicherweise verfügen jedoch nicht alle Instanzen über denselben Satz an Eigenschaften, und in Zukunft werden möglicherweise weitere Eigenschaften hinzugefügt.

Der einfachste Weg, dieses Problem zu lösen, besteht darin, für jeden Eigenschaftswert eine Spalte in der Datenbanktabelle zu erstellen und einfach diejenigen auszufüllen, die für eine bestimmte Entitätsinstanz benötigt werden. Großartig! Problem gelöst ... bis Ihre Tabelle Millionen von Datensätzen enthält und Sie einen neuen Datensatz hinzufügen müssen.

Betrachten Sie das EAV-Muster (Entitätsattributwert), kommt es recht häufig vor. Eine Tabelle enthält Entitäten (Datensätze), eine andere Tabelle enthält Eigenschaftsnamen (Attribute) und eine dritte Tabelle ordnet Entitäten ihren Attributen zu und enthält den Wert dieser Attribute für die aktuelle Entität. Dies gibt Ihnen die Möglichkeit, unterschiedliche Eigenschaftensätze für unterschiedliche Objekte zu verwenden und Eigenschaften im Handumdrehen hinzuzufügen, ohne die Datenbankstruktur zu ändern.

Allerdings würde ich diesen Beitrag nicht schreiben, wenn der EVA-Ansatz nicht einige Nachteile hätte. Um beispielsweise eine oder mehrere Entitäten mit jeweils 1 Attribut zu erhalten, sind in der Abfrage zwei Joins erforderlich: Der erste ist ein Join mit der Attributtabelle, der zweite ist ein Join mit der Wertetabelle. Wenn eine Entität 2 Attribute hat, sind 2 Joins erforderlich! Darüber hinaus werden alle Attribute normalerweise als Zeichenfolgen gespeichert, was zu einer Typumwandlung sowohl für das Ergebnis als auch für die WHERE-Klausel führt. Wenn Sie viele Abfragen schreiben, ist dies im Hinblick auf den Ressourcenverbrauch ziemlich verschwenderisch.

Trotz dieser offensichtlichen Mängel wird EAV seit langem zur Lösung dieser Art von Problemen eingesetzt. Das waren unvermeidliche Mängel und es gab einfach keine bessere Alternative.
Doch dann tauchte in PostgreSQL eine neue „Technologie“ auf ...

Ab PostgreSQL 9.4 wurde der Datentyp JSONB zum Speichern von JSON-Binärdaten hinzugefügt. Obwohl das Speichern von JSON in diesem Format normalerweise etwas mehr Platz und Zeit in Anspruch nimmt als das Speichern von reinem Text-JSON, ist die Ausführung von Vorgängen damit viel schneller. JSONB unterstützt auch die Indizierung, wodurch Abfragen noch schneller werden.

Mit dem JSONB-Datentyp können wir das umständliche EAV-Muster ersetzen, indem wir unserer Entitätstabelle nur eine JSONB-Spalte hinzufügen, was den Datenbankentwurf erheblich vereinfacht. Aber viele argumentieren, dass dies mit einem Rückgang der Produktivität einhergehen sollte... Deshalb habe ich diesen Artikel geschrieben.

Einrichten einer Testdatenbank

Für diesen Vergleich habe ich die Datenbank auf einer Neuinstallation von PostgreSQL 9.5 mit dem 80-Dollar-Build erstellt DigitalOcean Ubuntu 14.04 Nach der Konfiguration einiger Parameter in postgresql.conf habe ich Folgendes ausgeführt: diese Skript mit psql. Zur Darstellung der Daten im EAV-Format 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 finden Sie eine Tabelle, in der dieselben Daten gespeichert werden, jedoch mit Attributen in einer Spalte vom Typ JSONB – immobilien.

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

Sieht doch viel einfacher aus, oder? Dann wurde es zu den Entitätstabellen hinzugefügt (Einheit & entity_jsonb) 10 Millionen Datensätze, und dementsprechend wurde die Tabelle mit den gleichen Daten unter Verwendung des EAV-Musters und des Ansatzes mit einer JSONB-Spalte gefüllt - entity_jsonb.properties. Somit haben wir im gesamten Satz von Eigenschaften mehrere unterschiedliche Datentypen erhalten. Beispieldaten:

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

Jetzt haben wir für beide Optionen die gleichen Daten. Beginnen wir mit dem Vergleich der Implementierungen bei der Arbeit!

Vereinfachen Sie Ihr Design

Es wurde bereits erwähnt, dass das Datenbankdesign stark vereinfacht wurde: eine Tabelle, indem eine JSONB-Spalte für Eigenschaften verwendet wurde, anstatt drei Tabellen für EAV zu verwenden. Doch wie spiegelt sich das in den Anfragen wider? Das Aktualisieren einer Entitätseigenschaft sieht folgendermaßen aus:

-- 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 Sie sehen, sieht die letzte Anfrage nicht einfacher aus. Um den Wert einer Eigenschaft in einem JSONB-Objekt zu aktualisieren, müssen wir die Funktion verwenden jsonb_set()und sollte unseren neuen Wert als JSONB-Objekt übergeben. Wir müssen jedoch keine Kennung im Voraus kennen. Wenn wir uns das EAV-Beispiel ansehen, müssen wir sowohl die „Entity_ID“ als auch die „Entity_Attribute_ID“ kennen, um die Aktualisierung durchführen zu können. Wenn Sie eine Eigenschaft in einer JSONB-Spalte basierend auf dem Objektnamen aktualisieren möchten, geschieht dies alles in einer einfachen Zeile.

Wählen wir nun die gerade aktualisierte Entität anhand ihrer neuen Farbe aus:

-- 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 der zweite kürzer (kein Join!) und daher besser lesbar ist. JSONB gewinnt hier! Wir verwenden den JSON->>-Operator, um die Farbe als Textwert aus einem JSONB-Objekt abzurufen. Es gibt auch eine zweite Möglichkeit, mit dem @>-Operator das gleiche Ergebnis im JSONB-Modell zu erzielen:

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

Das ist etwas komplizierter: Wir prüfen, ob das JSON-Objekt in seiner Eigenschaftenspalte ein Objekt enthält, das sich rechts vom @>-Operator befindet. Weniger lesbar, produktiver (siehe unten).

Machen wir die Verwendung von JSONB noch einfacher, wenn Sie mehrere Eigenschaften gleichzeitig auswählen müssen. Hier kommt der JSONB-Ansatz wirklich ins Spiel: Wir wählen einfach Eigenschaften als zusätzliche Spalten in unserem Ergebnissatz aus, ohne dass Joins erforderlich sind:

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

Mit EAV benötigen Sie zwei Joins für jede Eigenschaft, die Sie abfragen möchten. Meiner Meinung nach zeigen die oben genannten Abfragen eine große Vereinfachung im Datenbankdesign. Weitere Beispiele zum Schreiben von JSONB-Abfragen finden Sie auch in Dies Post.
Jetzt ist es an der Zeit, über Leistung zu sprechen.

Leistung

Um die Leistung zu vergleichen, habe ich verwendet ANALYSE ERKLÄREN in Abfragen, um die Ausführungszeit zu berechnen. Jede Abfrage wurde mindestens dreimal ausgeführt, da der Abfrageplaner beim ersten Mal länger benötigt. Zuerst habe ich die Abfragen ohne Indizes ausgeführt. Dies war offensichtlich ein Vorteil von JSONB, da die für EAV erforderlichen Joins keine Indizes verwenden konnten (Fremdschlüsselfelder wurden nicht indiziert). Danach habe ich einen Index für die beiden Fremdschlüsselspalten der EAV-Wertetabelle sowie einen Index erstellt GIN für eine JSONB-Spalte.

Die Datenaktualisierung zeigte die folgenden zeitlichen Ergebnisse (in ms). Beachten Sie, dass die Skala logarithmisch ist:

Wir sehen, dass JSONB aus dem oben genannten Grund viel (> 50000-x) schneller als EAV ist, wenn Sie keine Indizes verwenden. Wenn wir Spalten mit Primärschlüsseln indizieren, verschwindet der Unterschied fast, aber JSONB ist immer noch 1,3-mal schneller als EAV. Beachten Sie, dass der Index der JSONB-Spalte hier keine Auswirkung hat, da wir die Eigenschaftsspalte nicht in den Bewertungskriterien verwenden.

Für die Auswahl von Daten basierend auf dem Immobilienwert erhalten wir folgende Ergebnisse (Normalmaßstab):

Sie können feststellen, dass JSONB wiederum schneller arbeitet als EAV ohne Indizes, bei EAV mit Indizes jedoch immer noch schneller als JSONB. Aber dann sah ich, dass die Zeiten für JSONB-Abfragen gleich waren, was mich zu der Tatsache führte, dass GIN-Indizes nicht funktionieren. Wenn Sie einen GIN-Index für eine Spalte mit ausgefüllten Eigenschaften verwenden, wird dieser offenbar nur wirksam, wenn Sie den Include-Operator @> verwenden. Ich habe dies in einem neuen Test verwendet und es hatte einen enormen Einfluss auf die Zeit: nur 0,153 ms! Dies ist 15000-mal schneller als EAV und 25000-mal schneller als der ->>-Operator.

Ich denke, es war schnell genug!

Größe der Datenbanktabelle

Vergleichen wir die Tabellengrößen für beide Ansätze. In psql können wir mit dem Befehl die Größe aller Tabellen und Indizes anzeigen dti+

Für den EAV-Ansatz betragen die Tabellengrößen etwa 3068 MB und die Indizes bis zu 3427 MB, also insgesamt 6,43 GB. Der JSONB-Ansatz verwendet 1817 MB für die Tabelle und 318 MB für die Indizes, also 2,08 GB. Es fällt dreimal weniger aus! Diese Tatsache hat mich ein wenig überrascht, da wir Eigenschaftsnamen in jedem JSONB-Objekt speichern.

Dennoch sprechen die Zahlen für sich: In EAV speichern wir 2 ganzzahlige Fremdschlüssel pro Attributwert, was zu 8 Bytes zusätzlicher Daten führt. Darüber hinaus speichert EAV alle Eigenschaftswerte als Text, während JSONB nach Möglichkeit intern numerische und boolesche Werte verwendet, was zu einem geringeren Platzbedarf führt.

Ergebnisse

Insgesamt denke ich, dass das Speichern von Entitätseigenschaften im JSONB-Format das Entwerfen und Verwalten Ihrer Datenbank erheblich vereinfachen kann. Wenn Sie viele Abfragen ausführen, funktioniert es tatsächlich effizienter, alles in derselben Tabelle wie die Entität zu belassen. Und die Tatsache, dass dadurch die Interaktion zwischen Daten vereinfacht wird, ist bereits ein Pluspunkt, aber die resultierende Datenbank ist dreimal kleiner.

Aufgrund der durchgeführten Tests können wir außerdem den Schluss ziehen, dass der Leistungsverlust sehr unbedeutend ist. In einigen Fällen ist JSONB sogar schneller als EAV und damit noch besser. Dieser Benchmark deckt jedoch natürlich nicht alle Aspekte ab (z. B. Entitäten mit einer sehr großen Anzahl von Eigenschaften, eine deutliche Erhöhung der Anzahl der Eigenschaften vorhandener Daten, ...), also haben Sie Vorschläge, wie Sie diese verbessern können , hinterlassen Sie es gerne in den Kommentaren!

Source: habr.com