TL;DR: JSONB može uvelike pojednostaviti razvoj sheme baze podataka bez žrtvovanja performansi upita.
Uvod
Uzmimo klasičan primjer, vjerojatno jedan od najstarijih slučajeva upotrebe u svijetu relacijskih baza podataka: imamo entitet i trebamo pohraniti određena svojstva (atribute) tog entiteta. Ali ne moraju sve instance imati isti skup svojstava, a u budućnosti se mogu dodati još svojstava.
Najjednostavnije rješenje ovog problema je stvaranje stupca u tablici baze podataka za svaku vrijednost svojstva i jednostavno popunjavanje onih potrebnih za određenu instancu entiteta. Izvrsno! Problem riješen... sve dok vaša tablica ne sadrži milijune zapisa i ne trebate dodati novi zapis.
Razmotrimo EAV obrazac (), to je prilično uobičajeno. Jedna tablica sadrži entitete (zapise), druga tablica sadrži nazive svojstava (atribute), a treća tablica povezuje entitete s njihovim atributima i sadrži vrijednosti tih atributa za trenutni entitet. To vam omogućuje 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 EVA pristup nema nekih nedostataka. Na primjer, dohvaćanje jednog ili više entiteta s po jednim atributom zahtijeva dva spajanja u upitu: prvo spajanje s tablicom atributa, drugo spajanje s tablicom vrijednosti. Ako entitet ima dva atributa, tada su potrebna četiri spajanja! Nadalje, svi atributi se obično pohranjuju kao nizovi znakova, što rezultira primjenom tipa i za rezultat i za klauzulu WHERE. Ako pišete puno upita, to je prilično rasipno u smislu korištenja resursa.
Unatoč tim očitim nedostacima, EAV se dugo koristi za rješavanje ovakvih problema. To su bili neizbježni nedostaci i jednostavno nije postojala bolja alternativa.
Ali onda se u PostgreSQL-u pojavila nova „tehnologija“…
Počevši od PostgreSQL-a 9.4, dodan je tip podataka JSONB za pohranu binarnih JSON podataka. Iako pohrana JSON-a u ovom formatu obično zauzima malo više prostora i vremena nego pohrana običnog tekstualnog JSON-a, operacije s njim su puno brže. JSONB također podržava indeksiranje, što upite čini još bržima.
Tip podataka JSONB omogućuje nam zamjenu nezgrapnog EAV uzorka dodavanjem samo jednog JSONB stupca u našu tablicu entiteta, što značajno pojednostavljuje dizajn baze podataka. Međutim, mnogi tvrde da to dolazi na štetu performansi... To je razlog zašto sam napisao ovaj članak.
Postavljanje testne baze podataka
Za ovu usporedbu, kreirao sam bazu podataka na novoj instalaciji PostgreSQL-a 9.5 na verziji od 80 dolara. Ubuntu 14.04 Nakon konfiguriranja nekih parametara u postgresql.conf pokrenuo sam skripta pomoću psql-a. Za prikaz podataka kao EAV-a, kreirane su sljedeće tablice:
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 tablica u kojoj će se pohraniti isti podaci, ali s atributima u JSONB stupcu – Svojstva.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Izgleda puno jednostavnije, zar ne? Zatim je dodano u tablice entiteta (osoba & entity_jsonb) 10 milijuna zapisa, te je sukladno tome tablica bila popunjena identičnim podacima gdje je korišten EAV uzorak i pristup s JSONB stupcem – entity_jsonb.propertiesTako smo dobili nekoliko različitih tipova podataka za cijeli skup 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 identične podatke za obje opcije. Krenimo uspoređivati implementacije u stvarnom životu!
Pojednostavljivanje dizajna
Ranije je spomenuto da je dizajn baze podataka značajno pojednostavljen: jedna tablica, koristeći JSONB stupac za svojstva, umjesto tri tablice za EAV. Ali kako se to prevodi u upite? 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 upit ne izgleda jednostavnije. Za ažuriranje vrijednosti svojstva u JSONB objektu moramo koristiti funkciju , i moramo proslijediti našu novu vrijednost kao JSONB objekt. Međutim, ne moramo unaprijed znati nikakav identifikator. Gledajući EAV primjer, moramo znati i entity_id i entity_attribute_id da bismo izvršili ažuriranje. Ako želite ažurirati svojstvo u JSONB stupcu na temelju naziva objekta, sve se to radi u jednom jednostavnom retku.
Sada odaberimo entitet koji smo upravo ažurirali na temelju 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!) i stoga čitljiviji. JSONB ovdje pobjeđuje! Koristimo JSON ->> operator za dohvaćanje boje kao tekstualne vrijednosti iz JSONB objekta. Postoji i drugi način za postizanje istog rezultata u JSONB modelu pomoću @> operatora:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Ovo je malo kompliciranije: provjeravamo sadrži li JSON objekt u stupcu svojstava objekt s desne strane operatora @>. Manje čitljivo, bolje performanse (vidi dolje).
Pojednostavimo korištenje JSONB-a još više kada trebate odabrati više svojstava odjednom. Tu JSONB pristup zaista blista: jednostavno odabiremo 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;
S EAV-om će vam trebati dva spajanja za svako svojstvo koje želite upitati. Po mom mišljenju, gore navedeni upiti pokazuju značajno pojednostavljenje u dizajnu baze podataka. Također možete vidjeti više primjera kako pisati JSONB upite u objava.
Sada je vrijeme da razgovaramo o performansama.
Performanse
Za usporedbu performansi koje sam koristio u upitima za izračun vremena izvršavanja. Svaki upit je izvršen barem tri puta, jer planer upita prvi put traje dulje. Prvo sam pokrenuo upite bez ikakvih indeksa. To je očito poslužilo kao prednost JSONB-a, budući da spajanja potrebna za EAV nisu mogla koristiti indekse (polja stranog ključa nisu bila indeksirana). Nakon toga sam stvorio indeks na dva stupca stranog ključa tablice vrijednosti EAV-a, kao i indeks za JSONB stupac.
Ažuriranja podataka pokazala su sljedeće vremenske rezultate (u ms). Imajte na umu da je skala logaritamska:
Vidimo da je JSONB značajno (>50 000 puta) brži od EAV-a bez indeksa, iz gore navedenog razloga. Kada indeksiramo stupce primarnog ključa, razlika gotovo nestaje, ali JSONB je i dalje 1,3 puta brži od EAV-a. Imajte na umu da indeks na JSONB stupcu ovdje nema učinka, budući da ne koristimo stupac svojstava u kriterijima evaluacije.
Za odabir podataka na temelju vrijednosti nekretnine dobivamo sljedeće rezultate (normalna skala):
Možete vidjeti da je JSONB opet brži od EAV-a bez indeksa, ali kada je EAV indeksiran, i dalje je brži od JSONB-a. Ali onda sam vidio da su vremena za JSONB upite ista, što me dovelo do činjenice da se GIN indeksi ne aktiviraju. Navodno, kada koristite GIN indeks na stupcu s popunjenim svojstvima, on stupa na snagu samo kada se koristi operator uključivanja @>. Koristio sam to u novom testu i imalo je ogroman utjecaj na vrijeme: samo 0,153 ms! To je 15 000 puta brže od EAV-a i 25 000 puta brže od operatora ->>.
Mislim da je bilo prilično brzo!
Veličina tablica baze podataka
Usporedimo veličine tablica za oba pristupa. U psql-u možemo prikazati veličinu svih tablica i indeksa pomoću naredbe dti+
S EAV pristupom, veličine tablica su približno 3068 MB, a indeksi su do 3427 MB, što je ukupno 6,43 GB. Korištenjem JSONB pristupa, tablica koristi 1817 MB, a indeksira 318 MB, što je ukupno 2,08 GB. To je trećina veličine! Ova me činjenica malo iznenadila, budući da nazive svojstava pohranjujemo u svaki JSONB objekt.
Ali brojke govore same za sebe: u EAV-u pohranjujemo dva cjelobrojna strana ključa po vrijednosti atributa, što rezultira s 8 bajtova dodatnih podataka. Nadalje, u EAV-u se sve vrijednosti svojstava pohranjuju kao tekst, dok će JSONB interno koristiti numeričke i logičke vrijednosti gdje je to moguće, što rezultira manjim otiskom.
Rezultati
Sveukupno, mislim da pohranjivanje svojstava entiteta u JSONB formatu može značajno pojednostaviti dizajn i održavanje vaše baze podataka. Ako izvršavate puno upita, pohranjivanje svega u istu tablicu kao i entitet bit će zaista učinkovitije. Činjenica da pojednostavljuje interakcije podataka već je plus, ali rezultirajuća baza podataka je također tri puta manja.
Također, na temelju rezultata benchmarka možemo zaključiti da je pad performansi vrlo mali. U nekim slučajevima, JSONB čak radi brže od EAV-a, što ga čini još boljim. Međutim, ovaj benchmark zasigurno ne pokriva sve aspekte (npr. entitete s vrlo velikim brojem svojstava, značajno povećanje broja svojstava u postojećim podacima itd.), stoga ako imate bilo kakve prijedloge za poboljšanje, slobodno ih ostavite u komentarima!
Izvor: www.habr.com
