EAV asendamine JSONB-ga PostgreSQL-is

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

Sissejuhatus

Vaatame klassikalist näidet, mis on ilmselt üks vanimaid rakendusi rikkaandmestike maailmas: meil on entiteet, mille omadusi (atribuutide) tuleb salvestada. Kuid kõik eksemplarid ei pruugi omada samu omadusi ning tulevikus võib tekkida vajadus lisada veel uusi omadusi.

Lihtsaim lahendus sellele probleemile on luua andmebaasi tabelis iga atribuudiväärtuse jaoks veerg ja lihtsalt täita need veerud, mis on vajalikud konkreetse entiteedi eksemplari jaoks. Suurepärane! Probleem on lahendatud… kuni teie tabelis on miljoneid sissekandeid ja teil tekib vajadus lisada uus rida.

Vaatame EAV mustrit (Entiteet-Atribuut-Väärtus), see, it occurs quite often. One table contains entities (records), another table contains property names (attributes), and a third table links entities with their attributes and contains the value of these attributes for the current entity. This allows you to have different sets of properties for different objects and also to add properties 'on the fly' without changing the database structure.

However, I wouldn’t be writing this note if there weren’t any drawbacks to the EVA approach. For instance, to retrieve one or several entities that have one attribute each, two joins are required in the query: the first being a join with the attributes table, and the second a join with the values table. If an entity has two attributes, then four joins are needed! Additionally, all attributes are usually stored as strings, which leads to type conversions both for the result and for the WHERE condition. If you write many queries, it can be quite wasteful in terms of resource usage.

Hoolimata nendest ilmsestest puudustest on EAV juba pikka aega kasutusel selliste probleemide lahendamiseks. Need olid vältimatud puudused ja paremat alternatiivi lihtsalt ei olnud.
Aga siis ilmus PostgreSQL-i uus "tehnoloogia"…

Alates PostgreSQL 9.4 on lisatud JSONB andmetüübi tugi 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 päringud veelgi kiiremaks.

JSONB andmetüüp võimaldab meil asendada mahuka EAV mustri, lisades meie entsiti tabelisse vaid ühe JSONB veeru, mis lihtsustab andmebaasi kavandamist oluliselt. Kuid paljud väidavad, et see peaks kaasnema jõudluse langusega… Selle pärast see artikkel ilmuski.

Testiva andmebaasi seadistamine

Selleks võrdluseks lõin ma andmebaasi uue PostgreSQL 9.5 installatsiooni peal 80-dollarise komplekti jaoks DigitalOcean Ubuntu 14.04. Pärast mõnede seadete seadistamist postgresql.conf-is käivitasin ma seda psql-i abil integreerimine. EAV kujul andmete esitamiseks on loodud järgmised tabelid:

Loo tabel entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
Loo tabel entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
Loo tabel entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENTS entity(id), 
  entity_attribute_id INT    REFERENTS entity_attribute(id), 
  value               TEXT
);

Allpool on tabel, kus hoitakse samu andmeid, kuid atribuudid on JSONB tüüpi veerus – properties.

Loo tabel entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

Tundub palju lihtsam, eks? Siis lisati tabelitesse (entity & entity_jsonb) 10 miljonit kirjet ja vastavalt täideti sama andmetega tabel, kus kasutatakse EAV mustrit ja JSONB veeru lähenemist – entity_jsonb.properties. Seega saime mitmeid erinevaid andmetüüpe kogu omaduste kogumi hulgas. Näidetest:

{
  id:          1
  name:        "Entity1"
  description: "Test entity nr. 1"
  properties:  {
    color:        "punane"
    length:      120
    width:       3.1882420
    hassomething: true
    country:      "Belgia"
  } 
}

Nüüd on meil samad andmed kahes variandis. Alustame rakenduste võrdlemisega!

Disaini lihtsustamine

Varasemalt on juba öeldud, et andmebaasi disain on märkimisväärselt lihtsustatud: üks tabel, kasutades JSONB veergu omaduste jaoks, selle asemel et kasutada kolme tabelit EAV jaoks. Kuidas see aga päringutes kajastub? Ühe omaduse värskendamine entiteedi kohta 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 näeme, ei tundu viimane päring lihtsam. Selleks, et värskendada omaduse väärtust JSONB objekti sees, peame kasutama funktsiooni jsonb_set(), ja peame edastama meie uue väärtuse kui JSONB objekti. Siiski ei pea me eelnevalt teadma mingit ID-d. Vaatame EAV näidet, kus peame värskendamiseks teadma nii entity_id kui ka entity_attribute_id. Kui soovite omadust JSONB veerus objekti nime põhjal värskendada, siis toimub see kõik ühe lihtsa reaga.

Nüüd valime entiteedi, mida just värskendasime, tema uue värvi 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';

Ma arvan, et võime nõustuda, et teine on lühem (ilma joinita!) ja seega paremini loetav. Siin võidab JSONB! Kasutame JSON operaatorit ->>, et saada värv tekstiväärtusena JSONB objektist. Samuti on olemas teine viis sama tulemuse saavutamiseks JSONB mudelis, kasutades operaatorit @>:

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

See on veidi keerulisem: kontrollime, kas JSON objekt veerus properties sisaldab objekti, mis asub operaatori @> paremal küljel. Vähem loetav, kuid suurem tootlikkus (vt allpool).

Teeme JSONB kasutamise veelgi lihtsamaks, kui peate valima mitu omadust korraga. Just siin sobib JSONB lähenemine tõeliselt: me lihtsalt valime omadused täiendavate veergudena meie tulemuste komplektis ilma vajaduseta ühendada:

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

EAV kasutamisel on igas omaduses, mida soovite pärida, vaja 2 liitumist. Minu arvates näitavad eeltoodud päringud andmebaasi disainis suurt lihtsustamist. Vaadake rohkem näiteid, kuidas kirjutada päringuid JSONB-le, võib-olla ka selles postitusest.
Nüüd on aeg rääkida tootlikkusest.

Tootlikkus

Tootlikkuse võrdlemiseks kasutasin EXPLAIN ANALYZE päringutes, et mõõta täitmise aega. Iga päringut käidi läbi vähemalt kolm korda, kuna esmakordselt kulub päringute planeerijale rohkem aega. Alustasin päringute täitmisega ilma igasuguste indeksiteta. Ilmselt andis see JSONB-le eelise, kuna EAV jaoks vajalikud liitumised ei saanud kasutada indekseid (välistest võtmeväljadest ei olnud indekseid). Pärast seda lõin 2 välist võtmetabeli EAV väärtuste jaoks indeksi, samuti indeksi GIN JSONB välja jaoks.

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

Näeme, et JSONB on märgatavalt (> 50000 korda) kiirem kui EAV, kui indekseid ei kasutata, nagu eelnevalt mainitud. Kui indekseerisime veerge peamiste võtmete alusel, peaaegu kaob erinevus, kuid JSONB on siiski 1,3 korda kiirem kui EAV. Oluline on märkida, et JSONB veeru indeks ei mõjuta siin midagi, kuna me ei kasuta omaduste veergu hindekriteeriumites.

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

On märgata, et JSONB töötab jälle kiiremini kui EAV ilma indekseid, kuid kui EAV-l on indeksid, töötab see siiski kiiremini kui JSONB. Siiski märkasin, et JSONB päringute aeg oli sama, mis tõukaski mind oletama, et GIN-indeks ei tööta. Ilmselt, kui kasutate GIN-indeksit veerus, kus on omadused, kehtib see ainult kaudse operaatori @> kasutamise korral. Kasutasin seda uues testis, mis avaldas suure mõju ajale: vaid 0,153 ms! See on 15000 korda kiirem kui EAV ja 25000 korda kiirem kui operaator ->>.

Arvan, et see oli piisavalt kiire!

Andmebaasi tabelite suurus

Võrdleme tabelite suurusi mõlema lähenemisviisi korral. Psql-is saame näidata kõigi tabelite ja indeksite suurust käsuga dti+

EAV lähenemise korral on tabelite suurus umbes 3068 MB ja indeksite suurus kuni 3427 MB, kokku 6,43 GB. JSONB lähenemise puhul kasutatakse tabeli jaoks 1817 MB ja indeksite jaoks 318 MB, kokku 2,08 GB. See on kolm korda vähem! See fakt üllatas mind natuke, sest hoiame omaduste nimesid igas JSONB objekti.

Aga numbrid räägivad enda eest: EAV-s hoiame 2 täisarvulist välisvõtit atribuudi väärtusele, mille tulemusena saame 8 baiti lisanduvaid andmeid. Lisaks sellele on EAV-s kõik omaduste väärtused tekstina, samas kui JSONB kasutab seal, kus võimalik, numbrilisi ja loogilisi väärtusi, mille tõttu saadakse väiksem maht.

Kokkuvõte

Kokkuvõttes arvan, et entsiti omaduste säilitamine JSONB formaadis võib teie andmebaasi projekteerimist ja haldamist oluliselt lihtsustada. Kui teete palju päringuid, siis kõik, mis on salvestatud ühte tabelisse koos entiteediga, töötab tõepoolest efektiivsemalt. Ja see, et see lihtsustab andmete vahelisi seoseid, on juba pluss, aga ka tulemusena saadud andmebaasi maht on kolm korda väiksem.

Samuti on tehtud testide põhjal võimalik järeldada, et jõudluse kaod on väga vähesed. Mõnel juhul töötab JSONB isegi kiiremini kui EAV, mis teeb selle veelgi paremaks. Siiski ei kata see referentstest kindlasti kõiki aspekte (näiteks entiteedid, millel on väga palju omadusi, olemasolevate andmete omaduste arvu märkimisväärne suurenemine jne), seega, kui teil on mingeid ettepanekuid, kuidas neid parandada, ärge kartke jätta kommentaare!

Allikas: habr.com