Zamjena EAV s JSONB u PostgreSQL

TL; DR: JSONB može značajno olakšati razvoj Baza podataka bez žrtvovanja performansi u upitima.

Uvod

Uzmimo klasičan primjer, vjerovatno jedan od najstarijih načina korištenja u svijetu relacijskih baza podataka (baza podataka): imamo entitet i treba da sačuvamo određena svojstva (atribute) tog entiteta. Međutim, ne svi primeri moraju imati isti skup svojstava, a i u budućnosti je moguće dodavanje novih svojstava.

Najjednostavniji način rješavanja ovog problema je stvaranje kolone u tabeli baze podataka za svaku vrijednost svojstva i jednostavno popunjavanje onih koje su potrebne za određeni primjer entiteta. Odlično! Problem je rešen… do trenutka kada vaša tabela ne sadrži milione zapisa i kada ne dođe do potrebe za dodavanjem nove stavke.

Razmotrimo EAV obrazac (Entity-Attribute-Value), on se dovoljno često pojavljuje. Jedna tabela sadrži entitete (zapise), druga tabela sadrži imena svojstava (atributa), a treća tabela povezuje entitete s njihovim atributima i sadrži vrijednosti tih atributa za trenutni entitet. Ovo vam omogućava da imate različite skupove svojstava za različite objekte, kao i da dodajete svojstva "u hodu", ne mijenjajući strukturu baze podataka.

Međutim, ne bih pisao ovu bilješku da nije bilo nedostataka u pristupu korištenju EAV. Na primjer, za dobijanje jednog ili više entiteta koji imaju po 1 atribut potrebno je 2 join-a (spajanja) u upitu: prvi – spajanje s tabelom atributa, drugi – spajanje s tabelom vrijednosti. Ako entitet ima 2 atributa, potrebno je već 4 join-a! Osim toga, svi atributi se obično čuvaju kao stringovi, što dovodi do konverzije tipova, kako za rezultat, tako i za WHERE uvjet. Ako pišete mnogo upita, to je prilično rasipno, u smislu korištenja resursa.

Uprkos ovim očiglednim nedostacima, EAV se već dugo koristi za rješavanje ovakvih problema. To su bila neizbježna ograničenja, a bolje alternative jednostavno nije bilo.
Ali tada je u PostgreSQL-u pojavila nova "tehnologija"…

Počinjajući od PostgreSQL 9.4, tip podataka JSONB je dodat za skladištenje binarnih JSON podataka. Iako skladištenje JSON-a u ovom formatu obično zahteva malo više prostora i vremena od prostog tekstualnog JSON-a, operacije nad njim se obavljaju znatno brže. Takođe, JSONB podržava indeksiranje, što čini upite još bržim.

Tip podataka JSONB omogućava nam da zamenimo složeni EAV obrazac jednostavnim dodavanjem jednog JSONB stupca u našu tabelu entiteta, što značajno pojednostavljuje dizajn baze podataka. Međutim, mnogi tvrde da bi to trebalo pratiti smanjenjem performansi… Zbog ovog razloga je ovaj članak i napisan.

Podešavanje testne baze podataka

Za ovu poredbe sam kreirao bazu podataka na novoj instalaciji PostgreSQL 9.5 na 80-dolarskoj konfiguraciji. DigitalOcean Ubuntu 14.04. Nakon što sam podešavao neke parametre u postgresql.conf, pokrenuo sam ovaj script putem psql-a. Za predstavljanje podataka u obliku EAV kreirane su sledeće tabele:

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

Ispod je tabela u kojoj će se čuvati isti podaci, ali sa atributima u stupcu tipa JSONB – properties.

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

Izgleda mnogo jednostavnije, zar ne? Zatim je u tabelama entiteta (entity & entity_jsonb) dodano 10 miliona zapisa, i samim tim su popunjeni isti podaci u tabelama gde se koristi EAV obrazac i pristup sa JSONB stupcem – entity_jsonb.properties. Tako smo dobili nekoliko različitih tipova podataka među celokupnim skupom svojstava. Primer podataka:

{
  id:          1
  name:        "Entity1"
  description: "Test entitet br. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgija"
  } 
}

Dakle, sada imamo iste podatke za dva varijante. Počnimo sa upoređivanjem implementacija u radu!

Pojednostavljenje dizajna

Već je rečeno da je dizajn baze podataka značajno pojednostavljen: jedna tabela, korišćenjem JSONB stupca za svojstva, umesto korišćenja tri tabele za EAV. Kako se to odražava na upitima? Ažuriranje jednog svojstva entiteta izgleda ovako:

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

Kao što vidimo, poslednji upit ne izgleda jednostavnije. Da bismo ažurirali vrednost atributa u JSONB objektu, moramo koristiti funkciju jsonb_set(), i moramo proslediti novu vrednost kao JSONB objekat. Ipak, ne trebamo unapred znati bilo koji identifikator. Gledajući primer sa EAV, potrebno je znati i entity_id i entity_attribute_id da bismo izvršili ažuriranje. Ako želite da ažurirate atribut u JSONB koloni prema imenu objekta, – to se može uraditi u jednoj jednostavnoj liniji.

Sada hajde da izaberemo entitet koji smo upravo ažurirali na osnovu njegovog novog kolor:

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

Mislim da se možemo složiti da je drugi upit kraći (bez join!), i prema tome čitljiviji. Ovde pobedjuje JSONB! Koristimo JSON operator ->> kako bismo dobili boju kao tekstualnu vrednost iz JSONB objekta. Postoji i drugi način da se postigne isti rezultat u JSONB modelu koristeći operator @>:

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

Ovo je malo složenije: proveravamo da li JSON objekat u koloni svojstava sadrži objekat koji se nalazi desno od operatora @>. Manje čitljivo, ali efikasnije (vidi dalje).

Pojednostavimo upotrebu JSONB još više kada vam je potrebno da izaberete više svojstava odjednom. Tu JSONB pristup zapravo dolazi do izražaja: jednostavno biramo svojstva kao dodatne kolone u našem skupu rezultata bez potrebe za spajanjima:

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

Sa EAV biće vam potrebna 2 spajanja za svako svojstvo koje želite da upitate. Po mom mišljenju, navedeni upiti pokazuju značajno pojednostavljenje u dizajnu baze podataka. Više primera kako pisati upite za JSONB možete takođe naći u ovom postu.
Sada je došlo vreme da razgovaramo o performansama.

Performanse

Da bih uporedio performanse, koristio sam EXPLAIN ANALYZE u upitima, za mjerenje vremena izvršavanja. Svaki upit je izvršen najmanje tri puta, jer prvom izvršavanju planeru upita treba više vremena. Prvo sam izvršio upite bez ikakvih indeksa. Očigledno, to je pružilo prednost JSONB-u, jer spojevi potrebni za EAV nisu mogli koristiti indekse (polja stranih ključeva nisu bila indeksirana). Nakon toga sam stvorio indeks za 2 kolone stranih ključeva u EAV tabeli, kao i indeks GIN za kolonu JSONB.

Ažuriranja podataka pokazala su sljedeće rezultate po vremenu (u ms). Imajte na umu da je skala logaritamska:

Zamjena EAV s JSONB u PostgreSQL

Vidimo da je JSONB mnogo (> 50000-x) brži od EAV-a kada se ne koriste indeksi, zbog razloga navedenog iznad. Kada indeksiramo kolone sa primarnim ključevima, razlika gotovo nestaje, ali je JSONB još uvek 1,3 puta brži od EAV-a. Imajte na umu da indeks u koloni JSONB ovde ne utiče, jer ne koristimo kolonu svojstava u kriterijumima ocenjivanja.

Za odabir podataka na temelju vrednosti svojstva dobijamo sljedeće rezultate (obična skala):

Zamjena EAV s JSONB u PostgreSQL

Može se primijetiti da JSONB opet radi brže od EAV-a bez indeksa, ali kada EAV ima indekse – još uvek radi brže od JSONB-a. Međutim, tada sam primetio da je vreme za JSONB upite bilo isto, što me navelo na zaključak da GIN indeks ne funkcioniše. Naime, kada koristite GIN indeks za kolonu sa popunjenim svojstvima, on deluje samo kada koristite operator uključivanja @>. Koristio sam ovo u novom testu, što je imalo ogroman uticaj na vreme: samo 0,153 ms! To je 15000 puta brže od EAV-a i 25000 puta brže od operatora ->>.

Mislim da je to bilo dovoljno brzo!

Veličina tabela Baze Podataka

Poređimo veličine tabela kod oba pristupa. U psql-u možemo prikazati veličinu svih tabela i indeksa koristeći komandu dti+

Zamjena EAV s JSONB u PostgreSQL

Za pristup EAV, veličine tabela su oko 3068 MB, a indeksi do 3427 MB, što ukupno daje 6,43 GB. Kada koristimo pristup sa JSONB-om, koristi se 1817 MB za tabelu i 318 MB za indekse, što čini 2,08 GB. To je tri puta manje! Ova činjenica me malo iznenadila, jer čuvamo imena svojstava u svakom JSONB objektu.

Međutim, brojke same govore za sebe: u EAV-u čuvamo 2 cjelobrojna vanjska ključa za vrijednost atributa, što rezultira dodatnih 8 bajtova podataka. Osim toga, u EAV-u se sve vrijednosti svojstava čuvaju kao tekst, dok JSONB koristi numeričke i logičke vrijednosti gdje je to moguće, što rezultira manjim obimom.

Zaključci

U cjelini, mislim da čuvanje svojstava entiteta u formatu JSONB može znatno pojednostaviti dizajn i održavanje vaše baze podataka. Ako izvodite mnogo upita, sve što se čuva u jednoj tabeli sa entitetom zaista će raditi efikasnije. A to što to pojednostavljuje interakciju između podataka već je plus, ali i rezultat je baza podataka tri puta manja po obimu.

Također, prema provedenim testovima, može se zaključiti da su gubici u performansama vrlo neznatni. U nekim slučajevima, JSONB čak radi brže od EAV-a, što ga čini još boljim. Međutim, ovaj referentni test, naravno, ne pokriva sve aspekte (na primjer, entitete s vrlo velikim brojem svojstava, značajnim povećanjem broja postojećih svojstava,...), pa ako imate bilo kakvih prijedloga o tome kako ih poboljšati, slobodno ostavite u komentarima!

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster