Înlocuirea EAV cu JSONB în PostgreSQL

TL; DR: JSONB poate simplifica foarte mult dezvoltarea schemei bazei de date fără a sacrifica performanța interogărilor.

Introducere

Să dăm un exemplu clasic de probabil unul dintre cele mai vechi cazuri de utilizare din lume a unei baze de date relaționale (bază de date): avem o entitate și trebuie să salvăm anumite proprietăți (atribute) ale acestei entități. Dar nu toate instanțele pot avea același set de proprietăți și mai multe proprietăți pot fi adăugate în viitor.

Cel mai simplu mod de a rezolva această problemă este să creați o coloană în tabelul bazei de date pentru fiecare valoare de proprietate și să completați pur și simplu pe cele care sunt necesare pentru o anumită instanță de entitate. Grozav! Problemă rezolvată... până când tabelul conține milioane de înregistrări și trebuie să adăugați o înregistrare nouă.

Luați în considerare modelul EAV (Entitate-Atribut-Valoare), apare destul de des. Un tabel conține entități (înregistrări), un alt tabel conține nume de proprietăți (atribute), iar un al treilea tabel asociază entitățile cu atributele lor și conține valoarea acelor atribute pentru entitatea curentă. Acest lucru vă oferă posibilitatea de a avea seturi diferite de proprietăți pentru diferite obiecte și, de asemenea, să adăugați proprietăți din mers fără a modifica structura bazei de date.

Cu toate acestea, nu aș scrie această postare dacă nu ar exista unele dezavantaje ale abordării EVA. Deci, de exemplu, pentru a obține una sau mai multe entități care au câte 1 atribut, sunt necesare 2 îmbinări în interogare: prima este o îmbinare cu tabelul de atribute, a doua este o îmbinare cu tabelul de valori. Dacă o entitate are 2 atribute, atunci sunt necesare 4 îmbinări! În plus, toate atributele sunt stocate în mod obișnuit ca șiruri de caractere, ceea ce duce la turnarea tipului atât pentru rezultat, cât și pentru clauza WHERE. Dacă scrieți o mulțime de interogări, atunci acest lucru este destul de irositor în ceea ce privește utilizarea resurselor.

În ciuda acestor deficiențe evidente, EAV a fost folosit de multă vreme pentru a rezolva aceste tipuri de probleme. Acestea erau neajunsuri inevitabile și pur și simplu nu exista o alternativă mai bună.
Dar apoi a apărut o nouă „tehnologie” în PostgreSQL...

Începând cu PostgreSQL 9.4, tipul de date JSONB a fost adăugat pentru a stoca date binare JSON. Deși stocarea JSON în acest format necesită de obicei puțin mai mult spațiu și timp decât JSON text simplu, efectuarea operațiunilor pe acesta este mult mai rapidă. JSONB acceptă, de asemenea, indexarea, ceea ce face interogările și mai rapide.

Tipul de date JSONB ne permite să înlocuim modelul greoi EAV prin adăugarea unei singure coloane JSONB la tabelul nostru de entități, simplificând foarte mult proiectarea bazei de date. Dar mulți susțin că acest lucru ar trebui să fie însoțit de o scădere a productivității... De aceea am scris acest articol.

Configurarea unei baze de date de testare

Pentru această comparație, am creat baza de date pe o nouă instalare a PostgreSQL 9.5 pe versiunea de 80 USD DigitalOcean Ubuntu 14.04. După ce am setat niște parametri în postgresql.conf, am alergat acest script folosind psql. Următoarele tabele au fost create pentru a prezenta datele în formă 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
);

Mai jos este un tabel în care vor fi stocate aceleași date, dar cu atribute într-o coloană de tip JSONB - proprietăţi.

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

Pare mult mai simplu, nu-i așa? Apoi a fost adăugat la tabelele de entități (entitate & entity_jsonb) 10 milioane de înregistrări și, în consecință, tabelul a fost completat cu aceleași date folosind modelul EAV și abordarea cu o coloană JSONB - entity_jsonb.properties. Astfel, am primit mai multe tipuri de date diferite între întregul set de proprietăți. Exemple de date:

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

Deci acum avem aceleași date pentru ambele opțiuni. Să începem să comparăm implementările la locul de muncă!

Simplificați-vă designul

S-a afirmat anterior că proiectarea bazei de date a fost mult simplificată: un tabel, prin utilizarea unei coloane JSONB pentru proprietăți, în loc de a folosi trei tabele pentru EAV. Dar cum se reflectă acest lucru în cereri? Actualizarea proprietății unei entități arată astfel:

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

După cum puteți vedea, ultima solicitare nu pare mai simplă. Pentru a actualiza valoarea unei proprietăți într-un obiect JSONB trebuie să folosim funcția jsonb_set()și ar trebui să treacă noua noastră valoare ca obiect JSONB. Cu toate acestea, nu trebuie să cunoaștem niciun identificator în avans. Privind exemplul EAV, trebuie să cunoaștem atât entity_id, cât și entity_attribute_id pentru a efectua actualizarea. Dacă doriți să actualizați o proprietate într-o coloană JSONB pe baza numelui obiectului, atunci totul se face într-o singură linie simplă.

Acum să selectăm entitatea pe care tocmai am actualizat-o pe baza noii culori:

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

Cred că putem fi de acord că al doilea este mai scurt (fără join!) și, prin urmare, mai ușor de citit. JSONB câștigă aici! Folosim operatorul JSON ->> pentru a obține culoarea ca valoare text dintr-un obiect JSONB. Există, de asemenea, o a doua modalitate de a obține același rezultat în modelul JSONB folosind operatorul @>:

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

Acest lucru este puțin mai complicat: verificăm dacă obiectul JSON din coloana de proprietăți conține un obiect care se află în dreapta operatorului @>. Mai puțin lizibil, mai productiv (vezi mai jos).

Să facem utilizarea JSONB și mai ușoară atunci când trebuie să selectați mai multe proprietăți simultan. Aici intervine cu adevărat abordarea JSONB: pur și simplu selectăm proprietăți ca coloane suplimentare în setul nostru de rezultate fără a fi nevoie de îmbinări:

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

Cu EAV veți avea nevoie de 2 uniuni pentru fiecare proprietate pe care doriți să o interogați. În opinia mea, interogările de mai sus arată o mare simplificare în proiectarea bazei de date. Vedeți mai multe exemple despre cum să scrieți interogări JSONB, de asemenea, în acest post.
Acum este timpul să vorbim despre performanță.

productivitate

Pentru a compara performanța am folosit EXPLICA ANALIZA în interogări, pentru a calcula timpul de execuție. Fiecare interogare a fost executată de cel puțin trei ori, deoarece planificatorul de interogări durează mai mult prima dată. Mai întâi am rulat interogările fără niciun index. Evident, acesta a fost un avantaj al JSONB, deoarece îmbinările necesare pentru EAV nu puteau folosi indecși (câmpurile de cheie străină nu erau indexate). După aceasta am creat un index pe cele 2 coloane de cheie străină din tabelul de valori EAV, precum și un index GIN pentru o coloană JSONB.

Actualizarea datelor a arătat următoarele rezultate în termeni de timp (în ms). Rețineți că scara este logaritmică:

Vedem că JSONB este mult (> 50000-x) mai rapid decât EAV dacă nu folosiți indecși, din motivul menționat mai sus. Când indexăm coloanele cu chei primare, diferența aproape dispare, dar JSONB este încă de 1,3 ori mai rapid decât EAV. Rețineți că indexul de pe coloana JSONB nu are niciun efect aici, deoarece nu folosim coloana de proprietate în criteriile de evaluare.

Pentru selectarea datelor pe baza valorii proprietății, obținem următoarele rezultate (scara normală):

Puteți observa că JSONB funcționează din nou mai rapid decât EAV fără indici, dar atunci când EAV cu indici, funcționează în continuare mai rapid decât JSONB. Dar apoi am văzut că timpii pentru interogările JSONB erau aceleași, acest lucru m-a determinat să spun că indicii GIN nu funcționează. Aparent, atunci când utilizați un index GIN pe o coloană cu proprietăți populate, acesta are efect numai atunci când utilizați operatorul include @>. Am folosit asta într-un nou test și a avut un impact uriaș asupra timpului: doar 0,153 ms! Acesta este de 15000 de ori mai rapid decât EAV și de 25000 de ori mai rapid decât operatorul ->>.

Cred că a fost destul de rapid!

Dimensiunea tabelului bazei de date

Să comparăm dimensiunile tabelului pentru ambele abordări. În psql putem arăta dimensiunea tuturor tabelelor și indecșilor folosind comanda dti+

Pentru abordarea EAV, dimensiunile tabelelor sunt de aproximativ 3068 MB și indexează până la 3427 MB pentru un total de 6,43 GB. Abordarea JSONB folosește 1817 MB pentru tabel și 318 MB pentru indecși, adică 2,08 GB. Se dovedește de 3 ori mai puțin! Acest fapt m-a surprins puțin deoarece stocăm numele proprietăților în fiecare obiect JSONB.

Dar totuși, numerele vorbesc de la sine: în EAV stocăm 2 chei străine întregi pe valoare de atribut, rezultând 8 octeți de date suplimentare. În plus, EAV stochează toate valorile proprietăților ca text, în timp ce JSONB va folosi valori numerice și booleene în interior, acolo unde este posibil, rezultând o amprentă mai mică.

Rezultatele

În general, cred că salvarea proprietăților entității în format JSONB poate face mult mai ușoară proiectarea și întreținerea bazei de date. Dacă executați o mulțime de interogări, atunci păstrarea totul în același tabel ca entitatea va funcționa de fapt mai eficient. Și faptul că acest lucru simplifică interacțiunea dintre date este deja un plus, dar baza de date rezultată este de 3 ori mai mică ca volum.

De asemenea, pe baza testelor efectuate, putem concluziona că pierderile de performanță sunt foarte nesemnificative. În unele cazuri, JSONB este chiar mai rapid decât EAV, ceea ce îl face și mai bun. Cu toate acestea, acest benchmark, desigur, nu acoperă toate aspectele (de exemplu, entități cu un număr foarte mare de proprietăți, o creștere semnificativă a numărului de proprietăți ale datelor existente,...), așa că dacă aveți sugestii despre cum să le îmbunătățiți , nu ezitați să lăsați în comentarii!

Sursa: www.habr.com