EAV asendamine JSONB-ga PostgreSQL-is

TL; DR: JSONB võib oluliselt lihtsustada andmebaasi skeemi arendamist, ilma et see kahjustaks päringute jõudlust.

Sissejuhatus

Toome klassikalise näite, mis on tõenäoliselt üks vanimaid rakendusi relatsiooniliste andmebaaside (andmebaas) maailmas: meil on üksus ja on vaja salvestada selle üksuse teatud omadused (atribuutid). Kuid mitte kõik eksemplarid ei pruugi omada sama omaduste komplekti, lisaks võib tulevikus olla vajalik uusi omadusi lisada.

Lihtsaim viis selle probleemi lahendamiseks on luua andmebaasitabelis iga omaduse väärtuse jaoks veerg ja lihtsalt täita need, mis on vajalikud konkreetse üksuse eksemplari jaoks. Suurepärane! Probleem on lahendatud… kuni teie tabelis on miljoneid kirjeid ja teil on vaja lisada uus kirje.

Kaalume EAV mustrit (Entity-Attribute-Value), mida kasutatakse üsna sageli. Üks tabel sisaldab üksusi (kirjeid), teine tabel sisaldab omaduste (atribuutide) nimesid ja kolmas tabel seob üksused nende atribuutidega ning sisaldab nende atribuutide väärtust kohaliku üksuse jaoks. See võimaldab teil omada erinevaid omaduste komplekte erinevate objektide jaoks ja lisada omadusi „reisi jooksul“, muutes andmebaasi struktuuri.

Kuid ma ei kirjutaks seda märkust, kui EAV lähenemisel ei oleks puudusi. Näiteks, et saada ühe või mitme üksuse, millel on 1 atribuut, nõuab see päringus 2 join’i: esimene – liitmine atribuutide tabeliga, teine – liitmine väärtuste tabeliga. Kui üksusel on 2 atribuuti, on juba vaja 4 join’i! Lisaks hoitakse kõiki atribuute tavaliselt stringidena, mis toob kaasa tüüpide konvertimise nii tulemusele kui ka WHERE tingimusele. Kui kirjutate palju päringuid, on see ressursikasutuse seisukohalt üsna raiskav.

Sellest hoolimata on EAV-i juba ammu kasutatud selliste probleemide lahendamiseks. Need olid vältimatud puudused ja paremat alternatiivi polnud lihtsalt olemas.
Aga siis ilmus PostgreSQL-i uus „tehnoloogia“…

Alates PostgreSQL 9.4-st on lisatud JSONB andmetüüp JSON-i binaarsete andmete salvestamiseks. Kuigi JSON-i salvestamine selles formaadis võtab tavaliselt veidi rohkem ruumi ja aega kui lihtne tekstiline JSON, toimub selle töötlemine palju kiiremini. Samuti toetab JSONB indekseerimist, mis muudab selle päringud veelgi kiiremaks.

JSONB andmetüüp võimaldab meil asendada mahuka EAV mustri, lisades vaid ühe JSONB veeru meie entiteetide tabelisse, mis lihtsustab oluliselt andmebaasi kujundamist. Kuid paljud väidavad, et see peaks tingima jõudluse languse… Just sellepärast ma selle artikli kirjutasin.

Testandmebaasi seadistamine

Selle võrdluse jaoks lõin andmebaasi uue PostgreSQL 9.5 paigalduse peal 80-dollarilisel süsteemil DigitalOcean Ubuntu 14.04. Pärast postgresql.conf-i mõningate seadete seadistamist käivitasin see skripti psql abil. EAV kujul andmete esitamiseks loodi järgmised tabelid:

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

Allpool on tabel, kus salvestatakse samad andmed, kuid atribuutide veerg on JSONB tüüpi – properties.

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

Näeb kordades lihtsam välja, eks? Seejärel lisati entiteetide tabelitesse (entity & entity_jsonb) 10 miljonit kirjet ning vastavalt täideti samade andmetega tabelid, kus kasutatakse EAV mustrit ja JSONB veeru lähenemist – entity_jsonb.properties. Nii saime erinevaid andmetüüpe kogu atribuudi hulgast. Andmete näide:

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

Nüüd on meil samad andmed kahe variandi jaoks. Alustame nende rakenduste võrdlemist!

Kujunduse lihtsustamine

Olen juba varem maininud, et andmebaasi disain on oluliselt lihtsustunud: üks tabel, tänu JSONB veeru kasutamisele omaduste jaoks, selle asemel et kasutada kolme tabelit EAV jaoks. Kuidas see aga päringutes kajastub? Entiteedi ühe omaduse värskendamine näeb välja järgmine:

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

Nagu me näeme, ei tundu viimane päring lihtsam. Et värskendada omaduse väärtust JSONB objektis, peame kasutama funktsiooni jsonb_set(), ja peame edastama meie uue väärtuse JSONB objektina. Siiski ei pea me eelnevalt mingit identifikaatorit teadma. EAV näite põhjal peame teadma nii entity_id kui entity_attribute_id, et teostada värskendust. Kui soovite värskendada omadust JSONB veerus objekti nime põhjal, – siis toimub see kõik ühe lihtsa lausega.

Nüüd valime selle entiteedi, mida just värskendasime, tema uue värvi tingimuse alusel:

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

Mina arvan, et me saame nõustuda, et teine on lühem (ilma joinita!), ja seega lugeda kergemini. Siin on JSONB võit! Kasutame JSON operaatorit ->>, et saada värv tekstiväärtusena JSONB objektist. Samuti on olemas teine võimalus sama tulemuse saavutamiseks JSONB mudelis, kasutades operaatorit @>:

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

See on veidi keerulisem: me kontrollime, kas JSON objekt veerus properties sisaldab objekti, mis on paremal pool operaatorit @>. Vähem loetav, kuid tootlikum (vt hiljem).

Lihtsustame JSONB kasutamist veelgi, kui peate valima korraga mitu omadust. Just siin sobib JSONB lähenemine tõeliselt hästi: me valime lihtsalt omadused lisakolumnitena meie tulemuste kogumis ilma vajaduseta ühinemiste järele:

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

EAV korral vajate igaks omaduseks, mida soovite küsida, 2 ühinemist. Minu arvates näitavad eeltoodud päringud suurt lihtsustamist andmebaasi disainis. Vaadake rohkem näiteid JSONB päringute kirjutamisest ka selles postitusest.
Nüüd on aeg rääkida tootlikkusest.

Tõhusus

Tootlikkuse võrdlemiseks kasutasin EXPLAIN ANALYZE päringutes, et kokku lugeda täitmise aega. Iga päring viidi läbi vähemalt kolm korda, kuna esmakordne päring nõudis päringute planeerijalt rohkem aega. Alguses tegin päringud ilma mingite indeksiteta. Selgelt oli see JSONB eelise aspekt, kuna EAV jaoks vajalikud ühendused ei saanud kasutada indekseid (välistähtede välja ei indeksitud). Seejärel lõin indeksi kahe EAV väärtuste tabeli välistähtede veeru jaoks ja ka indeksi GIN JSONB veeru jaoks.

Andmete värskendamine näitas järgmisi tulemusi aja osas (ms). Pange tähele, et skaala on logaritmiline:

Näeme, et JSONB on palju (> 50000-kordselt) kiirem kui EAV, kui indekseid ei kasutata, põhjuse tõttu, nagu eespool mainitud. Kui indekseme veergu peavõtmete järgi, siis vahe peaaegu kaob, kuid JSONB on siiski 1,3 korda kiirem kui EAV. Tähelepanu, et siin ei mõjuta JSONB veeru indeks midagi, kuna me ei kasuta omaduste veergu hindamiskriteeriumides.

Omaduse väärtuse põhjal andmete valimiseks saame järgmised tulemused (tavaline skaala):

Võime märgata, et JSONB töötab jälle kiiremini kui EAV ilma indeksiteta, kuid kui EAV-l on indeksid – töötab see siiski kiiremini kui JSONB. Kuid pärast nägin, et JSONB päringute aeg oli sama, see juhtis mind järeldusele, et GIN-indeks ei tööta. Tundub, et kui kasutate GIN-indeksit omadustest täidetud veeru jaoks, kehtib see ainult @> operaatoreid kasutades. Kasutasin seda uues testis, mis mõjutas aega tohutult: ainult 0,153 ms! See on 15000 korda kiiremini kui EAV ja 25000 korda kiiremini kui ->> operaator.

Arvan, et see oli piisavalt kiire!

Andmebaasi tabelite suurused

Võrdleme tabelite suurusi mõlema lähenemise korral. psql-is saame kõikide tabelite ja indeksite suurused ära näidata käsuga dti+

EAV lähenemise korral on tabelite suurused umbes 3068 MB ja indeksite suurused kuni 3427 MB, kokku 6,43 GB. JSONB lähenemise korral kasutatakse 1817 MB tabeli jaoks ja 318 MB indeksite jaoks, kokku 2,08 GB. See on kolm korda vähem! See fakt üllatas mind veidi, kuna salvestame omaduste nimed igas JSONB objektis.

Kuid numbrid räägivad enda eest: EAV-s salvestame 2 täisarvulist välist võtit atribuutväärtusele, mille tulemusena saame 8 baiti täiendavaid andmeid. Lisaks salvestatakse EAV-s kõik omaduste väärtused tekstina, samas kui JSONB kasutab numbrilisi ja loogilisi väärtusi seal, kus see on võimalik, mis toob kaasa väiksema mahu.

Summary

Üldiselt arvan, et isendite omaduste salvestamine JSONB formaadis võib oluliselt lihtsustada teie andmebaasi kavandamist ja hooldamist. Kui teete palju päringuid, siis kõik, mis on salvestatud ühte tabelisse isendiga, töötab tõeliselt tõhusamalt. Ja tõsiasi, et see lihtsustab andmete vahelist suhtlemist, on juba pluss, kuid ka tulemuseks olev andmebaas on kolm korda väiksema mahuga.

Samuti, tehtud testide põhjal, võib kokkuvõtteks öelda, et jõudluskaod on väga väikesed. Mõnes olukorras töötab JSONB isegi kiiremini kui EAV, mis teeb selle veelgi paremaks. Siiski ei kata see standardtest kindlasti kõiki aspekte (näiteks isendid, millel on väga palju omadusi, olulised suurendused olemasolevate andmete omaduste arvus,...), seega, kui teil on ettepanekuid nende parandamiseks, siis ärge kartke jätta kommentaare!

Allikas: habr.com