TL; DR: JSONB može uvelike pojednostaviti razvoj šeme baze podataka bez žrtvovanja performansi upita.
Uvod
Navedimo klasičan primjer vjerovatno jednog od najstarijih slučajeva upotrebe u svijetu relacijske baze podataka (baze podataka): imamo entitet i moramo sačuvati određena svojstva (atribute) ovog entiteta. Ali ne moraju sve instance imati isti skup svojstava, a u budućnosti se može dodati još svojstava.
Najlakši način za rješavanje ovog problema je kreiranje stupca u tabeli baze podataka za svaku vrijednost svojstva i jednostavno popunjavanje onih koje su potrebne za određenu instancu entiteta. Odlično! Problem rešen... dok vaša tabela ne sadrži milione zapisa i ne morate da dodate novi zapis.
Uzmite u obzir EAV obrazac (), javlja se prilično često. Jedna tabela sadrži entitete (zapise), druga tabela sadrži imena svojstava (atribute), a treća tabela povezuje entitete sa njihovim atributima i sadrži vrednost tih atributa za trenutni entitet. Ovo vam daje mogućnost da imate različite skupove svojstava za različite objekte, kao i da dodajete svojstva u hodu bez promjene strukture baze podataka.
Međutim, ne bih pisao ovaj post da nema nekih nedostataka EVA pristupa. Tako, na primjer, da biste dobili jedan ili više entiteta koji imaju po 1 atribut, potrebna su 2 spoja u upitu: prvi je spoj s tablicom atributa, drugi je spoj s tablicom vrijednosti. Ako entitet ima 2 atributa, tada su potrebna 4 spoja! Dodatno, svi atributi se obično pohranjuju kao stringovi, što rezultira ulijevanjem tipa i za rezultat i za klauzulu WHERE. Ako pišete puno upita, to je prilično rasipno u smislu korištenja resursa.
Uprkos ovim očiglednim nedostacima, EAV se dugo koristi za rješavanje ovih vrsta problema. To su bili neizbježni nedostaci i jednostavno nije bilo bolje alternative.
Ali onda se u PostgreSQL pojavila nova “tehnologija”...
Počevši od PostgreSQL 9.4, tip podataka JSONB je dodat za skladištenje JSON binarnih podataka. Iako pohranjivanje JSON-a u ovom formatu obično oduzima malo više prostora i vremena nego JSON običnog teksta, izvođenje operacija na njemu je mnogo brže. JSONB takođe podržava indeksiranje, što upite čini još bržima.
JSONB tip podataka nam omogućava da zamijenimo glomazan EAV obrazac dodavanjem samo jednog JSONB stupca našoj tablici entiteta, što uvelike pojednostavljuje dizajn baze podataka. Ali mnogi tvrde da bi to trebalo da bude praćeno smanjenjem produktivnosti... Zato sam i napisao ovaj članak.
Postavljanje testne baze podataka
Za ovo poređenje, kreirao sam bazu podataka na novoj instalaciji PostgreSQL 9.5 na verziji od 80 dolara Ubuntu 14.04 Nakon konfigurisanja nekih parametara u postgresql.conf, pokrenuo sam skriptu koristeći psql. Sljedeće tabele su kreirane za prikaz podataka u EAV obliku:
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 biti pohranjeni isti podaci, ali sa atributima u stupcu tipa JSONB – svojstva.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Izgleda mnogo jednostavnije, zar ne? Zatim je dodan u tabele entiteta (entitet & entity_jsonb) 10 miliona zapisa, te je shodno tome tabela popunjena istim podacima koristeći EAV obrazac i pristup sa JSONB kolonom - entity_jsonb.properties. Tako smo dobili nekoliko različitih tipova podataka među cjelokupnim skupom svojstava. Primjer podataka:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Dakle, sada imamo iste podatke za obje opcije. Počnimo porediti implementacije na poslu!
Pojednostavite svoj dizajn
Ranije je rečeno da je dizajn baze podataka uveliko pojednostavljen: jedna tabela, korišćenjem JSONB kolone za svojstva, umesto korišćenja tri tabele za EAV. Ali kako se to odražava u zahtjevima? Ažuriranje svojstva jednog 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 vidite, posljednji zahtjev ne izgleda jednostavnije. Za ažuriranje vrijednosti svojstva u JSONB objektu moramo koristiti funkciju , i treba da prenese našu novu vrijednost kao JSONB objekat. Međutim, ne moramo unaprijed znati nijedan identifikator. Gledajući EAV primjer, moramo znati i entity_id i entity_attribute_id da bismo izvršili ažuriranje. Ako želite da ažurirate svojstvo u JSONB koloni na osnovu naziva objekta, onda se sve radi u jednom jednostavnom redu.
Sada odaberimo entitet koji smo upravo ažurirali na osnovu njegove nove boje:
-- 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 kraći (bez spajanja!), pa samim tim i čitljiviji. JSONB pobjeđuje ovdje! Koristimo JSON ->> operator da dobijemo boju kao tekstualnu vrijednost 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 komplikovanije: proveravamo da li JSON objekat u svojoj koloni sa svojstvima sadrži objekat koji se nalazi desno od operatora @>. Manje čitljivo, produktivnije (vidi dolje).
Učinimo korištenje JSONB-a još lakšim kada trebate odabrati više svojstava odjednom. Ovdje zaista dolazi JSONB pristup: jednostavno biramo svojstva kao dodatne stupce u našem skupu rezultata bez potrebe za spajanjima:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Sa EAV-om će vam trebati 2 spoja za svako svojstvo koje želite upitati. Po mom mišljenju, gornji upiti pokazuju veliko pojednostavljenje u dizajnu baze podataka. Pogledajte još primjera kako pisati JSONB upite, također u pošta.
Sada je vrijeme da razgovaramo o performansama.
Produktivnost
Za upoređivanje performansi koristio sam u upitima, za izračunavanje vremena izvršenja. Svaki upit je izvršen najmanje tri puta jer planeru upita prvi put treba duže. Prvo sam pokrenuo upite bez ikakvih indeksa. Očigledno, ovo je bila prednost JSONB-a, budući da spojevi potrebni za EAV nisu mogli koristiti indekse (polja stranog ključa nisu bila indeksirana). Nakon toga sam kreirao indeks na 2 kolone stranog ključa u tablici vrijednosti EAV, kao i indeks za JSONB kolonu.
Ažuriranje podataka pokazalo je sljedeće rezultate u vremenskom smislu (u ms). Imajte na umu da je skala logaritamska:
Vidimo da je JSONB mnogo (> 50000-x) brži od EAV-a ako ne koristite indekse, iz gore navedenog razloga. Kada indeksiramo kolone primarnim ključevima, razlika gotovo nestaje, ali je JSONB i dalje 1,3 puta brži od EAV-a. Imajte na umu da indeks na koloni JSONB ovdje nema efekta jer ne koristimo kolonu svojstva u kriterijima procjene.
Za odabir podataka na osnovu vrijednosti svojstva dobijamo sljedeće rezultate (normalna skala):
Možete primijetiti da JSONB opet radi brže od EAV-a bez indeksa, ali kada EAV sa indeksima, i dalje radi brže od JSONB-a. Ali onda sam vidio da su vremena za JSONB upite ista, to me je navelo na činjenicu da GIN indeksi ne rade. Očigledno kada koristite GIN indeks na koloni sa popunjenim svojstvima, on ima efekta samo kada koristite operator uključivanja @>. Koristio sam ovo u novom testu i imao je ogroman uticaj na vreme: samo 0,153ms! Ovo je 15000 puta brže od EAV-a i 25000 puta brže od operatora ->>.
Mislim da je bilo dovoljno brzo!
Veličina tabele baze podataka
Uporedimo veličine tablica za oba pristupa. U psql-u možemo prikazati veličinu svih tabela i indeksa pomoću naredbe dti+
Za EAV pristup, veličine tabele su oko 3068 MB, a indeksi do 3427 MB za ukupno 6,43 GB. JSONB pristup koristi 1817 MB za tabelu i 318 MB za indekse, što je 2,08 GB. Ispada 3 puta manje! Ova činjenica me je malo iznenadila jer pohranjujemo imena svojstava u svaki JSONB objekt.
Ali ipak, brojke govore same za sebe: u EAV-u pohranjujemo 2 cjelobrojna strana ključa po vrijednosti atributa, što rezultira 8 bajtova dodatnih podataka. Osim toga, EAV pohranjuje sve vrijednosti svojstava kao tekst, dok će JSONB interno koristiti numeričke i logičke vrijednosti gdje je to moguće, što rezultira manjim otiskom.
Ishodi
Sve u svemu, mislim da spremanje svojstava entiteta u JSONB formatu može znatno olakšati dizajniranje i održavanje vaše baze podataka. Ako pokrećete puno upita, držanje svega u istoj tabeli kao entitet će zapravo raditi efikasnije. A činjenica da ovo pojednostavljuje interakciju između podataka već je plus, ali rezultirajuća baza podataka je 3 puta manja po obimu.
Takođe, na osnovu izvršenih testova možemo zaključiti da su gubici performansi veoma neznatni. U nekim slučajevima, JSONB je čak brži od EAV-a, što ga čini još boljim. Međutim, ovo mjerilo naravno ne pokriva sve aspekte (npr. entitete sa vrlo velikim brojem svojstava, značajno povećanje broja svojstava postojećih podataka,...), pa ako imate bilo kakve prijedloge kako ih poboljšati , slobodno ostavite u komentarima!
izvor: www.habr.com
