EAV asendamine JSONB-ga PostgreSQL-is

TL; DR: JSONB võib oluliselt lihtsustada andmebaasi skeemi arendamist ilma päringu jõudlust ohverdamata.

Sissejuhatus

Toome klassikalise näite ilmselt ühest vanimast kasutusjuhtumist maailmas relatsiooniandmebaasi (andmebaasi) kohta: meil on olem ja me peame salvestama selle olemi teatud atribuudid (atribuudid). Kuid mitte kõigil eksemplaridel ei pruugi olla sama atribuutide komplekt ja tulevikus võidakse lisada rohkem atribuute.

Lihtsaim viis selle probleemi lahendamiseks on luua andmebaasi tabelis iga atribuudi väärtuse jaoks veerg ja lihtsalt täita need, mis on konkreetse olemi eksemplari jaoks vajalikud. Suurepärane! Probleem lahendatud... kuni teie tabel sisaldab miljoneid kirjeid ja peate lisama uue kirje.

Mõelge EAV mustrile (Üksus-atribuut-väärtus), esineb seda üsna sageli. Üks tabel sisaldab oleme (kirjeid), teine ​​tabel sisaldab atribuutide nimesid (atribuute) ja kolmas tabel seob olemid nende atribuutidega ja sisaldab nende atribuutide väärtust praeguse olemi jaoks. See annab teile võimaluse omada erinevate objektide jaoks erinevaid atribuutide komplekte ja lisada atribuute käigupealt ilma andmebaasi struktuuri muutmata.

Kuid ma ei kirjutaks seda postitust, kui EVA-lähenemisel poleks mingeid varjukülgi. Näiteks ühe või mitme olemi saamiseks, millel on igaühel 1 atribuut, on päringus vaja 2 ühendust: esimene on ühendus atribuutide tabeliga, teine ​​​​on väärtuste tabeliga ühendamine. Kui olemil on 2 atribuuti, siis on vaja 4 liitumist! Lisaks salvestatakse kõik atribuudid tavaliselt stringidena, mille tulemuseks on nii tulemuse kui ka WHERE-klausli tüübi ülekandmine. Kui kirjutate palju päringuid, on see ressursikasutuse seisukohalt üsna raiskav.

Vaatamata nendele ilmsetele puudustele on EAV-d seda tüüpi probleemide lahendamiseks pikka aega kasutatud. Need olid vältimatud puudused ja paremat alternatiivi lihtsalt polnud.
Kuid siis ilmus PostgreSQL-i uus "tehnoloogia" ...

Alates versioonist PostgreSQL 9.4 lisati JSON-i binaarandmete salvestamiseks JSONB andmetüüp. Kuigi JSON-i salvestamine selles vormingus võtab tavaliselt veidi rohkem ruumi ja aega kui lihtteksti JSON-i, on sellega toimingute tegemine palju kiirem. JSONB toetab ka indekseerimist, mis muudab päringud veelgi kiiremaks.

JSONB andmetüüp võimaldab meil tülika EAV mustri asendada, lisades meie olemitabelisse vaid ühe JSONB veeru, mis lihtsustab oluliselt andmebaasi ülesehitust. Kuid paljud vaidlevad vastu, et sellega peaks kaasnema tootlikkuse langus... Sellepärast ma selle artikli kirjutasin.

Testide andmebaasi loomine

Selle võrdluse jaoks lõin andmebaasi PostgreSQL 9.5 värske installi kohta 80-dollarise järguga DigitalOcean Ubuntu 14.04 Pärast postgresql.conf failis mõnede parameetrite konfigureerimist käivitasin see skript psql-i abil. Andmete esitamiseks EAV kujul koostati 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, kuhu salvestatakse samad andmed, kuid atribuutidega JSONB tüüpi veerus – omadused.

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

Tundub palju lihtsam, kas pole? Seejärel lisati see olemitabelitesse (üksus & entity_jsonb) 10 miljonit kirjet ja vastavalt sellele täideti tabel samade andmetega, kasutades EAV mustrit ja lähenemist JSONB veeruga - entity_jsonb.properties. Seega saime kogu atribuutide hulgast mitu erinevat andmetüüpi. Näidisandmed:

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

Nüüd on meil mõlema variandi kohta samad andmed. Alustame juurutuste võrdlemist tööl!

Lihtsustage oma disaini

Varem väideti, et andmebaasi kujundust on oluliselt lihtsustatud: üks tabel, kasutades atribuutide jaoks JSONB veergu, selle asemel, et kasutada EAV jaoks kolme tabelit. Aga kuidas see taotlustes kajastub? Ühe olemi atribuudi 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 näete, ei tundu viimane taotlus lihtsam. JSONB-objekti atribuudi väärtuse värskendamiseks peame kasutama funktsiooni jsonb_set()ja peaks edastama meie uue väärtuse JSONB-objektina. Kuid me ei pea teadma ühtegi identifikaatorit ette. Vaadates EAV näidet, peame värskenduse tegemiseks teadma nii atribuuti entity_id kui ka entity_attribute_id. Kui soovite värskendada JSONB veerus asuvat atribuuti objekti nime alusel, tehakse seda kõik ühe lihtsa reaga.

Nüüd valime äsja värskendatud olemi selle uue värvi põhjal:

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

Arvan, et võime nõustuda, et teine ​​on lühem (no join!) ja seetõttu loetavam. JSONB võidab siin! Värvi saamiseks JSONB-objektilt tekstiväärtusena kasutame operaatorit JSON ->>. JSONB-mudelis on sama tulemuse saavutamiseks ka teine ​​viis, kasutades operaatorit @>:

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

See on veidi keerulisem: kontrollime, kas JSON-objekt selle atribuutide veerus sisaldab objekti, mis asub operaatorist @> paremal. Vähem loetav, produktiivsem (vt allpool).

Teeme JSONB kasutamise veelgi lihtsamaks, kui peate valima mitu atribuuti korraga. Siin tulebki JSONB-lähenemine tõeliselt kasutusele: me lihtsalt valime atribuudid oma tulemuste komplekti täiendavate veergudena, ilma et oleks vaja liite:

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

EAV-ga on teil vaja 2 liitumist iga kinnisvara kohta, mida soovite pärida. Minu arvates näitavad ülaltoodud päringud andmebaasi kujundamise suurt lihtsustamist. Vaadake rohkem näiteid selle kohta, kuidas kirjutada päringuid JSONB-le, mis on samuti võimalik see postitus.
Nüüd on aeg rääkida jõudlusest.

Производительность

Kasutasin jõudluse võrdlemiseks SELGITAGE ANALÜÜSI päringutes täitmisaja arvutamiseks. Iga päring täideti vähemalt kolm korda, kuna päringuplaneerija võtab esimesel korral kauem aega. Kõigepealt käivitasin päringud ilma indeksiteta. Ilmselgelt oli see JSONB eelis, kuna EAV jaoks vajalikud liitumised ei saanud kasutada indekseid (võõra võtme väljad ei olnud indekseeritud). Pärast seda lõin indeksi EAV väärtuste tabeli kahele võõrvõtme veerule ja indeksi GIN JSONB veeru jaoks.

Andmevärskendus näitas ajaliselt (ms) järgmisi tulemusi. Pange tähele, et skaala on logaritmiline:

Näeme, et JSONB on palju (> 50000-x) kiirem kui EAV, kui te indekseid ei kasuta, ülaltoodud põhjusel. Kui indekseerime primaarvõtmetega veerge, siis erinevus peaaegu kaob, kuid JSONB on siiski 1,3 korda kiirem kui EAV. Pange tähele, et JSONB veeru indeks ei oma siin mingit mõju, kuna me ei kasuta hindamiskriteeriumides atribuutide veergu.

Kinnisvara väärtuse põhjal andmete valimisel saame järgmised tulemused (tavaskaala):

Võite märgata, et JSONB töötab jälle kiiremini kui EAV ilma indeksiteta, kuid kui EAV koos indeksitega, töötab see ikkagi kiiremini kui JSONB. Kuid siis nägin, et JSONB-päringute ajad olid samad, see ajendas mind tõsiasjale, et GIN-indeksid ei tööta. Ilmselt, kui kasutate GIN-indeksit täidetud atribuutidega veerus, jõustub see ainult siis, kui kasutate kaasamisoperaatorit @>. Kasutasin seda uues testis ja sellel oli ajale tohutu mõju: ainult 0,153 ms! See on 15000 25000 korda kiirem kui EAV ja XNUMX XNUMX korda kiirem kui operaator ->>.

Ma arvan, et see oli piisavalt kiire!

Andmebaasi tabeli suurus

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

EAV-meetodi puhul on tabelite suurus umbes 3068 MB ja indeksid kuni 3427 MB, kokku 6,43 GB. JSONB lähenemisviis kasutab tabeli jaoks 1817 MB ja indeksite jaoks 318 MB, mis on 2,08 GB. Selgub 3 korda vähem! See asjaolu üllatas mind veidi, kuna salvestame atribuutide nimed igas JSONB-objektis.

Kuid siiski räägivad numbrid enda eest: EAV-s salvestame iga atribuudi väärtuse kohta 2 täisarvulist võõrvõtit, mille tulemuseks on 8 baiti lisaandmeid. Lisaks salvestab EAV kõik atribuutide väärtused tekstina, samas kui JSONB kasutab võimaluse korral sisemiselt arvulisi ja tõeväärtusi, mille tulemuseks on väiksem jalajälg.

Tulemused

Üldiselt arvan, et olemi atribuutide salvestamine JSONB-vormingus võib teie andmebaasi kujundamise ja haldamise palju lihtsamaks muuta. Kui käitate palju päringuid, toimib olemiga kõike samas tabelis hoidmine tegelikult tõhusamalt. Ja see, et see lihtsustab andmete omavahelist suhtlust, on juba pluss, kuid tulemuseks olev andmebaas on mahult 3 korda väiksem.

Samuti võime tehtud testide põhjal järeldada, et jõudluskaod on väga ebaolulised. Mõnel juhul on JSONB isegi kiirem kui EAV, muutes selle veelgi paremaks. Kuid see võrdlusalus ei hõlma muidugi kõiki aspekte (nt väga suure hulga atribuutidega üksused, olemasolevate andmete atribuutide arvu märkimisväärne suurenemine jne), nii et kui teil on ettepanekuid nende parandamiseks , jätke julgelt kommentaaridesse!

Allikas: www.habr.com