EAV mat JSONB an PostgreSQL ersetzen

TL; DR: JSONB kann d'Datebankschema Entwécklung immens vereinfachen ouni d'Queryleistung ofzeschafen.

Aféierung

Loosst eis e klassescht Beispill vu méiglecherweis ee vun den eelste Benotzungsfäll an der Welt vun enger relationaler Datebank (Datebank): mir hunn eng Entitéit, a mir musse bestëmmte Eegeschafte (Attributer) vun dëser Entitéit späicheren. Awer net all Instanzen kënnen deeselwechte Set vun Eegeschaften hunn, a méi Eegeschafte kënnen an Zukunft bäigefüügt ginn.

Deen einfachste Wee fir dëse Problem ze léisen ass eng Kolonn an der Datebanktabelle fir all Immobiliewäert ze kreéieren an einfach déi auszefëllen, déi fir eng spezifesch Entitéitsinstanz gebraucht ginn. Super! Problem geléist ... bis Ären Dësch Millioune Rekorder enthält an Dir musst en neie Rekord derbäisetzen.

Bedenkt d'EAV Muster (Entitéit-Attributer-Wäert), geschitt et zimlech dacks. Eng Tabell enthält Entitéiten (Rekord), eng aner Tabell enthält Eegeschafte Nimm (Attributer), an eng drëtt Tabell assoziéiert Entitéite mat hiren Attributer an enthält de Wäert vun dësen Attributer fir déi aktuell Entitéit. Dëst gëtt Iech d'Méiglechkeet verschidde Sätze vun Eegeschafte fir verschidden Objeten ze hunn, an och Properties op der Flucht ze addéieren ouni d'Datebankstruktur z'änneren.

Wéi och ëmmer, ech géif dëse Post net schreiwen wann et net e puer Nodeeler vun der EVA Approche wier. Also, zum Beispill, fir eng oder méi Entitéiten ze kréien, déi all 1 Attribut hunn, sinn 2 Bäiträg an der Ufro erfuerderlech: déi éischt ass e Bäitrëtt mat der Attributtabell, déi zweet ass e Bäitrëtt mat der Wäertertabelle. Wann eng Entitéit 2 Attributer huet, da sinn 4 Joint néideg! Zousätzlech ginn all Attributer typesch als Saiten gespäichert, wat zu Typcasting fir d'Resultat an d'WHERE Klausel resultéiert. Wann Dir vill Ufroen schreift, dann ass dëst zimmlech verschwendend wat d'Ressourceverbrauch ugeet.

Trotz dësen offensichtleche Mängel ass EAV scho laang benotzt fir dës Zorte vu Probleemer ze léisen. Dëst waren inévitabel Mängel, an et gouf einfach keng besser Alternativ.
Awer dunn ass eng nei "Technologie" am PostgreSQL opgetaucht ...

Ugefaange mat PostgreSQL 9.4, gouf den JSONB Datentyp bäigefüügt fir JSON binär Daten ze späicheren. Och wann d'Späichere vun JSON an dësem Format typesch e bësse méi Plaz an Zäit brauch wéi Einfachen Text JSON, ass d'Operatiounen dorop vill méi séier. JSONB ënnerstëtzt och Indexéierung, wat Ufroen nach méi séier mécht.

D'JSONB Datentyp erlaabt eis dat ëmständlech EAV Muster ze ersetzen andeems Dir just eng JSONB Kolonn un eis Entity Tabell bäidréit, wat d'Datebankdesign staark vereinfacht. Awer vill streiden datt dëst vun enger Ofsenkung vun der Produktivitéit begleet soll ginn ... Dofir hunn ech dësen Artikel geschriwwen.

Ariichten vun enger Test Datebank

Fir dëse Verglach hunn ech d'Datebank op enger frëscher Installatioun vu PostgreSQL 9.5 op der $80 Build erstallt DigitalOcean Ubuntu 14.04 Nodeems ech e puer Parameteren an postgresql.conf konfiguréiert hat, hunn ech dat Skript mat psql. Déi folgend Tabelle goufen erstallt fir d'Donnéeën an EAV Form ze presentéieren:

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

Drënner ass eng Tabell wou déiselwecht Daten gespäichert ginn, awer mat Attributer an enger JSONB Typ Kolonn - Eegeschafte.

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

Gesäit vill méi einfach aus, oder? Duerno gouf et an d'Entitéitstabellen bäigefüügt (Entity- & entity_jsonb) 10 Milliounen Opzeechnungen, an deementspriechend war den Dësch mat de selwechten Donnéeën mat dem EAV Muster an der Approche mat enger JSONB Kolonn gefëllt - entity_jsonb.properties. Also hu mir verschidde verschidden Datentypen ënner der ganzer Set vun Eegeschafte kritt. Beispill Daten:

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

Also elo hu mir déiselwecht Daten fir béid Optiounen. Loosst eis ufänken Implementatiounen op der Aarbecht ze vergläichen!

Vereinfacht Ären Design

Et gouf virdru gesot datt den Datebankdesign immens vereinfacht gouf: eng Tabell, andeems Dir eng JSONB Kolonn fir Eegeschafte benotzt, anstatt dräi Dëscher fir EAV ze benotzen. Awer wéi spigelt sech dat an Ufroen aus? D'Aktualiséierung vun enger Entitéitsimmobilie gesäit esou 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;

Wéi Dir gesitt, gesäit déi lescht Ufro net méi einfach aus. Fir de Wäert vun enger Immobilie an engem JSONB Objet ze aktualiséieren, musse mir d'Funktioun benotzen jsonb_set(), a soll eisen neie Wäert als JSONB Objet passéieren. Mir brauchen awer keen Identifizéierer am Viraus ze kennen. Wann Dir d'EAV Beispill kuckt, musse mir souwuel d'Entity_id wéi och d'Entity_attribute_id wëssen fir den Update auszeféieren. Wann Dir e Besëtz an enger JSONB Kolonn aktualiséieren wëllt op Basis vum Objektnumm, da gëtt alles an enger einfacher Zeil gemaach.

Loosst eis elo d'Entitéit auswielen, déi mir just op senger neier Faarf aktualiséiert hunn:

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

Ech mengen, mir kënnen d'accord sinn, datt deen zweete méi kuerz ass (kee join!), an dofir méi liesbar ass. JSONB gewënnt hei! Mir benotzen den JSON ->> Bedreiwer fir d'Faarf als Textwäert vum JSONB Objet ze kréien. Et gëtt och en zweete Wee fir datselwecht Resultat am JSONB Modell z'erreechen mam @> Bedreiwer:

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

Dëst ass e bësse méi komplizéiert: mir kucken ob den JSON Objet a senger Propertieskolonne en Objet enthält deen riets vum @> Bedreiwer ass. Manner liesbar, méi produktiv (kuckt hei ënnen).

Loosst eis d'Benotzung vun JSONB nach méi einfach maachen wann Dir e puer Eegeschafte gläichzäiteg wielt. Dëst ass wou d'JSONB Approche wierklech erakënnt: mir wielt einfach Eegeschafte als zousätzlech Kolonnen an eisem Resultatset ouni de Besoin fir Join:

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

Mat EAV braucht Dir 2 Join fir all Immobilie déi Dir wëllt ufroen. Menger Meenung no weisen déi uewe genannte Ufroen eng grouss Vereinfachung am Datebankdesign. Kuckt méi Beispiller vu wéi een JSONB Ufroen schreift, och an dat posten.
Elo ass et Zäit iwwer Leeschtung ze schwätzen.

Produktivitéit

Ze vergläichen Leeschtung ech benotzt Analyse erklären bei Ufroen, fir d'Ausféierungszäit ze berechnen. All Ufro gouf op d'mannst dräimol ausgefouert well de Ufroplaner déi éischte Kéier méi laang dauert. Als éischt hunn ech d'Ufroen ouni Index gemaach. Natierlech war dëst e Virdeel vu JSONB, well d'Joins erfuerderlech fir EAV konnten keng Indexen benotzen (auslännesch Schlësselfelder goufen net indexéiert). Duerno hunn ech en Index op den 2 auslännesche Schlësselkolonnen vun der EAV Wäerttabelle erstallt, souwéi en Index gin fir eng JSONB Kolonn.

D'Datenaktualiséierung huet déi folgend Resultater a punkto Zäit gewisen (a ms). Notéiert datt d'Skala logarithmesch ass:

Mir gesinn datt JSONB vill (> 50000-x) méi séier ass wéi EAV wann Dir keng Indexen benotzt, aus dem uewe genannte Grond. Wa mir Spalten mat primäre Schlësselen indexéieren, verschwënnt den Ënnerscheed bal, awer JSONB ass ëmmer nach 1,3 Mol méi séier wéi EAV. Notéiert datt den Index op der JSONB Kolonn keen Effekt hei huet well mir d'Eegeschaftskolonn net an den Evaluatiounskriterien benotzen.

Fir d'Auswiel vun Donnéeën baséiert op Immobiliewäert, kréie mir déi folgend Resultater (normal Skala):

Dir kënnt bemierken datt JSONB erëm méi séier funktionnéiert wéi EAV ouni Indexen, awer wann EAV mat Indizes funktionnéiert et ëmmer méi séier wéi JSONB. Awer dunn hunn ech gesinn datt d'Zäite fir JSONB Ufroen d'selwecht waren, dëst huet mech zur Tatsaach gefrot datt GIN Indexen net funktionnéieren. Anscheinend wann Dir e GIN Index op enger Kolonn mat populéierten Eegeschafte benotzt, wierkt et nëmme wann Dir den Inkludéierende Bedreiwer @> benotzt. Ech hunn dat an engem neien Test benotzt an et huet e groussen Impakt op d'Zäit: nëmmen 0,153ms! Dëst ass 15000 Mol méi séier wéi EAV an 25000 Mol méi séier wéi den ->> Bedreiwer.

Ech mengen et war séier genuch!

Datebank Dësch Gréisst

Loosst eis d'Tabellgréissten fir béid Approche vergläichen. An psql kënne mir d'Gréisst vun all Dëscher an Indexen mat dem Kommando weisen dti+

Fir den EAV Approche sinn d'Tabelle Gréissten ongeféier 3068 MB an Indizes bis zu 3427 MB fir insgesamt 6,43 GB. D'JSONB Approche benotzt 1817 MB fir den Dësch an 318 MB fir d'Indexen, dat ass 2,08 GB. Et stellt sech 3 Mol manner eraus! Dës Tatsaach iwwerrascht mech e bëssen well mir Immobiliennimm an all JSONB Objet späicheren.

Awer trotzdem schwätzen d'Zuelen fir sech selwer: an EAV späichere mir 2 ganz Zuel auslännesch Schlësselen pro Attributwäert, wat zu 8 Bytes vun zousätzlech Donnéeën resultéiert. Zousätzlech späichert EAV all Immobiliewäerter als Text, wärend JSONB numeresch a boolesch Wäerter intern benotzt wa méiglech, wat zu engem méi klenge Foussofdrock resultéiert.

Resultater

Insgesamt denken ech datt d'Entitéitseigenschaften am JSONB Format späicheren kann den Design an d'Erhalen vun Ärer Datebank vill méi einfach maachen. Wann Dir vill Ufroen laaft, da hält alles an der selwechter Tabell wéi d'Entitéit tatsächlech méi effizient. An d'Tatsaach, datt dëst d'Interaktioun tëscht Daten vereinfacht ass schonn e Plus, awer déi resultéierend Datebank ass 3 Mol méi kleng am Volume.

Och op Basis vun den Tester déi gemaach goufen, kënne mir schléissen datt d'Leeschtungsverloschter ganz onbedeitend sinn. A verschiddene Fäll ass JSONB nach méi séier wéi EAV, wat et nach besser mécht. Wéi och ëmmer, dëse Benchmark deckt natierlech net all Aspekter (z.B. Entitéite mat enger ganz grousser Zuel vun Eegeschaften, eng bedeitend Erhéijung vun der Unzuel vun Eegeschafte vun existéierenden Donnéeën, ...), also wann Dir Suggestioune hutt fir se ze verbesseren , weg fillen gratis an de Kommentaren ze verloossen!

Source: will.com