TL;DR: A JSONB nagymértékben leegyszerűsítheti az adatbázis-séma fejlesztését a lekérdezési teljesítmény feláldozása nélkül.
Bevezetés
Vegyünk egy klasszikus példát, valószínűleg az egyik legrégebbi használati esetet a relációs adatbázisok világában: van egy entitásunk, és tárolnunk kell ennek az entitásnak bizonyos tulajdonságait (attribútumait). De nem minden példány rendelkezik ugyanazokkal a tulajdonságokkal, és a jövőben további tulajdonságok is hozzáadhatók.
A probléma legegyszerűbb megoldása, ha minden tulajdonságértékhez létrehozunk egy oszlopot az adatbázistáblában, és egyszerűen kitöltjük az adott entitáspéldányhoz szükséges oszlopokat. Nagyszerű! A probléma megoldva... amíg a táblázat több millió rekordot nem tartalmaz, és új rekordot kell hozzáadnunk.
Tekintsük az EAV mintázatot (), ez elég gyakori. Az egyik tábla entitásokat (rekordokat) tartalmaz, a másik tábla tulajdonságneveket (attribútumokat), a harmadik tábla pedig az entitásokat az attribútumaikhoz kapcsolja, és ezen attribútumok értékeit tartalmazza az aktuális entitáshoz. Ez lehetővé teszi, hogy különböző objektumokhoz különböző tulajdonságkészleteket használjunk, valamint menet közben is hozzáadjunk tulajdonságokat anélkül, hogy módosítanánk az adatbázis szerkezetét.
Azonban nem írnám ezt a bejegyzést, ha nem lennének hátrányai az EVA megközelítésnek. Például egy vagy több, egy-egy attribútummal rendelkező entitás lekérése két joint-ot igényel a lekérdezésben: az első joint-ot az attribútumtáblával, a második joint-ot az értéktáblával. Ha egy entitásnak két attribútuma van, akkor négy join-ra van szükség! Továbbá, az összes attribútum jellemzően karakterláncként tárolódik, ami típuskényszerítést eredményez mind az eredmény, mind a WHERE záradék esetében. Ha sok lekérdezést írsz, ez meglehetősen pazarló az erőforrás-felhasználás szempontjából.
Ezen nyilvánvaló hiányosságok ellenére az EAV-t régóta használják az ilyen jellegű problémák megoldására. Ezek elkerülhetetlen hiányosságok voltak, és egyszerűen nem volt jobb alternatíva.
Aztán megjelent egy új „technológia” a PostgreSQL-ben…
A PostgreSQL 9.4-es verziójától kezdődően hozzáadták a JSONB adattípust a bináris JSON adatok tárolására. Bár a JSON ebben a formátumban történő tárolása jellemzően valamivel több helyet és időt igényel, mint a sima szöveges JSON, a műveletek sokkal gyorsabbak vele. A JSONB támogatja az indexelést is, így a lekérdezések még gyorsabbak.
A JSONB adattípus lehetővé teszi számunkra, hogy a nehézkes EAV mintát egyetlen JSONB oszlop hozzáadásával helyettesítsük az entitástáblánkhoz, jelentősen leegyszerűsítve az adatbázis-tervezést. Sokan azonban azzal érvelnek, hogy ez a teljesítmény rovására megy… Ezért írtam ezt a cikket.
Tesztadatbázis beállítása
Ehhez az összehasonlításhoz az adatbázist a PostgreSQL 9.5 egy friss telepítésén hoztam létre a 80 dolláros builden. Ubuntu 14.04 Miután beállítottam néhány paramétert a postgresql.conf fájlban, lefuttattam a következőt: psql-t használó szkript. Az adatok EAV-ként való megjelenítéséhez a következő táblázatokat hoztuk létre:
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
);
Az alábbi táblázatban ugyanazok az adatok tárolódnak, de az attribútumok egy JSONB oszlopban lesznek – ingatlanait.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sokkal egyszerűbbnek tűnik, nem igaz? Aztán hozzáadták az entitástáblákhoz (egység & entity_jsonb) 10 millió rekord, és ennek megfelelően a táblázatot azonos adatokkal töltöttük fel, ahol az EAV mintát és a JSONB oszloppal történő megközelítést alkalmaztuk – entity_jsonb.propertiesÍgy a tulajdonságok teljes halmazán több különböző adattípust kaptunk. Példaadatok:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Tehát most már mindkét lehetőségre azonos adatokkal rendelkezünk. Kezdjük összehasonlítani a megvalósításokat a való életben!
A tervezés egyszerűsítése
Korábban említettük, hogy az adatbázis felépítése jelentősen leegyszerűsödött: egyetlen tábla, amely egy JSONB oszlopot használ a tulajdonságokhoz, a három EAV tábla helyett. De hogyan fordítható ez lekérdezésekre? Egyetlen entitás tulajdonságának frissítése így néz ki:
-- 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;
Amint látható, az utolsó lekérdezés nem tűnik egyszerűbbnek. Egy JSONB objektumban lévő tulajdonság értékének frissítéséhez a következő függvényt kell használnunk: , és az új értékünket JSONB objektumként kell átadnunk. Azonban nem kell előre tudnunk semmilyen azonosítót. Az EAV példát tekintve, a frissítés végrehajtásához ismernünk kell mind az entity_id, mind az entity_attribute_id értékeket. Ha egy JSONB oszlopban lévő tulajdonságot az objektum neve alapján szeretnénk frissíteni, akkor mindezt egyetlen egyszerű sorban megtehetjük.
Most válasszuk ki az újonnan frissített entitást az új színe alapján:
-- 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';
Azt hiszem, abban egyetérthetünk, hogy a második rövidebb (az illesztés nélkül!), és ezért olvashatóbb. A JSONB itt nyer! A JSON ->> operátort használjuk a szín szöveges értékként való kinyerésére a JSONB objektumból. Van egy második módja is annak, hogy ugyanezt az eredményt elérjük a JSONB modellben a @> operátor használatával:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Ez egy kicsit bonyolultabb: ellenőrizzük, hogy a tulajdonságok oszlopban lévő JSON objektum tartalmazza-e a @> operátor jobb oldalán lévő objektumot. Kevésbé olvasható, nagyobb teljesítményű (lásd alább).
Egyszerűsítsük le még tovább a JSONB használatát, amikor egyszerre több tulajdonságot kell kiválasztanunk. Itt ragyog igazán a JSONB megközelítés: egyszerűen további oszlopokként választjuk ki a tulajdonságokat az eredményhalmazban, illesztések nélkül:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Az EAV használatával minden lekérdezni kívánt tulajdonsághoz két illesztésre lesz szükséged. Véleményem szerint a fenti lekérdezések jelentős egyszerűsítést mutatnak az adatbázis-tervezésben. További példákat is láthatsz a JSONB lekérdezések írására a következőben: poszt.
Most itt az ideje, hogy a teljesítményről beszéljünk.
termelékenység
Az általam használt teljesítmény összehasonlítása lekérdezésekben a végrehajtási idő kiszámításához. Minden lekérdezést legalább háromszor végrehajtottam, mivel a lekérdezéstervező elsőre több időt vesz igénybe. Először indexek nélkül futtattam le a lekérdezéseket. Ez nyilvánvalóan a JSONB előnye volt, mivel az EAV-hoz szükséges illesztések nem használhattak indexeket (az idegen kulcsmezők nem voltak indexelve). Ezután létrehoztam egy indexet az EAV értéktábla két idegen kulcs oszlopán, valamint egy indexet a JSONB oszlophoz.
Az adatfrissítések a következő időeredményeket mutatták (ms-ban). Megjegyzendő, hogy a skála logaritmikus:
Azt látjuk, hogy a JSONB jelentősen (>50 000-szer) gyorsabb, mint az indexek nélküli EAV, a fent említett ok miatt. Amikor az elsődleges kulcs oszlopait indexeljük, a különbség szinte teljesen eltűnik, de a JSONB még mindig 1,3-szor gyorsabb, mint az EAV. Megjegyzendő, hogy a JSONB oszlop indexének itt nincs hatása, mivel nem használjuk a tulajdonság oszlopot az értékelési kritériumokban.
Tulajdonságérték alapján történő adatkiválasztás esetén a következő eredményeket kapjuk (normál skála):
Látható, hogy a JSONB ismét gyorsabb, mint az EAV indexek nélkül, de ha az EAV indexelve van, akkor is gyorsabb, mint a JSONB. Aztán láttam, hogy a JSONB lekérdezések ideje megegyezett, ami arra a tényre vezetett, hogy a GIN indexek nem aktiválódnak. Nyilvánvalóan, amikor egy GIN indexet használsz egy feltöltött tulajdonságokkal rendelkező oszlopon, az csak a @> include operátor használatakor lép életbe. Ezt egy új tesztben használtam, és hatalmas hatással volt az időre: mindössze 0,153 ms! Ez 15 000-szer gyorsabb, mint az EAV, és 25 000-szer gyorsabb, mint a ->> operátor.
Szerintem elég gyors volt!
Az adatbázistáblák mérete
Hasonlítsuk össze a táblaméreteket mindkét megközelítés esetében. A psql-ben az összes tábla és index méretét a következő paranccsal jeleníthetjük meg: dti+
Az EAV megközelítéssel a táblák mérete körülbelül 3068 MB, az indexek mérete pedig akár 3427 MB is lehet, összesen 6,43 GB. A JSONB megközelítéssel a tábla 1817 MB-ot használ, az indexek pedig 318 MB-ot, összesen 2,08 GB-ot. Ez a méret harmada! Ez a tény kissé meglepett, mivel minden JSONB objektumban tulajdonságneveket tárolunk.
De a számok magukért beszélnek: az EAV-ban minden attribútumértékhez két egész idegen kulcsot tárolunk, ami 8 bájtnyi további adatot eredményez. Továbbá az EAV-ban minden tulajdonságérték szövegként tárolódik, míg a JSONB ahol lehetséges, belsőleg numerikus és logikai értékeket használ, ami kisebb helyigénnyel jár.
Eredményei
Összességében úgy gondolom, hogy az entitástulajdonságok JSONB formátumban történő tárolása jelentősen leegyszerűsítheti az adatbázis tervezését és karbantartását. Ha sok lekérdezést hajt végre, akkor valóban hatékonyabb lesz mindent ugyanabban a táblázatban tárolni, mint az entitást. Az a tény, hogy leegyszerűsíti az adatinterakciókat, már önmagában is előny, de a kapott adatbázis mérete is háromszor kisebb.
A benchmark eredmények alapján arra a következtetésre juthatunk, hogy a teljesítménybeli hátrány nagyon csekély. Bizonyos esetekben a JSONB még az EAV-nál is gyorsabban teljesít, ami még jobbá teszi. Ez a benchmark azonban biztosan nem fed le minden aspektust (pl. nagyon sok tulajdonsággal rendelkező entitások, a meglévő adatokban lévő tulajdonságok számának jelentős növekedése stb.), ezért ha bármilyen javaslatod van a fejlesztésre, nyugodtan oszd meg a hozzászólásokban!
Forrás: will.com
