TL;DR: JSONB kan forenkle utvikling av databaseskjemaer betraktelig uten å ofre spørreytelsen.
Innledning
La oss ta et klassisk eksempel, sannsynligvis et av de eldste brukstilfellene i relasjonsdatabasenes verden: Vi har en entitet, og vi må lagre visse egenskaper (attributter) for denne entiteten. Men ikke alle instanser har kanskje det samme settet med egenskaper, og flere egenskaper kan bli lagt til i fremtiden.
Den enkleste løsningen på dette problemet er å opprette en kolonne i databasetabellen for hver egenskapsverdi og ganske enkelt fylle ut de som trengs for en bestemt enhetsforekomst. Flott! Problem løst ... helt til tabellen din inneholder millioner av poster, og du må legge til en ny post.
La oss se på EAV-mønsteret (), er det ganske vanlig. Én tabell inneholder enheter (poster), en annen tabell inneholder egenskapsnavn (attributter), og en tredje tabell kobler enheter til attributtene deres og inneholder verdiene til disse attributtene for den gjeldende enheten. Dette lar deg ha forskjellige sett med egenskaper for forskjellige objekter, samt legge til egenskaper på sparket, uten å endre databasestrukturen.
Jeg ville imidlertid ikke skrevet dette innlegget hvis det ikke fantes noen ulemper med EVA-tilnærmingen. For eksempel krever det å hente én eller flere enheter med ett attributt hver to koblinger i spørringen: den første koblingen med attributttabellen, den andre koblingen med verditabellen. Hvis en enhet har to attributter, kreves det fire koblinger! Videre lagres vanligvis alle attributter som strenger, noe som resulterer i typetvang for både resultatet og WHERE-klausulen. Hvis du skriver mange spørringer, er dette ganske sløsende med tanke på ressursbruk.
Til tross for disse åpenbare manglene har EAV lenge blitt brukt til å løse denne typen problemer. Dette var uunngåelige mangler, og det fantes rett og slett ikke et bedre alternativ.
Men så dukket det opp en ny «teknologi» i PostgreSQL…
Fra og med PostgreSQL 9.4 ble JSONB-datatypen lagt til for lagring av binære JSON-data. Selv om lagring av JSON i dette formatet vanligvis tar litt mer plass og tid enn ren tekst-JSON, er operasjoner med det mye raskere. JSONB støtter også indeksering, noe som gjør spørringer enda raskere.
JSONB-datatypen lar oss erstatte det tungvinte EAV-mønsteret ved å legge til bare én JSONB-kolonne i entitetstabellen vår, noe som forenkler databasedesignet betydelig. Mange hevder imidlertid at dette kommer med en kostnad i ytelse ... Det er grunnen til at jeg skrev denne artikkelen.
Sette opp en testdatabase
For denne sammenligningen opprettet jeg databasen på en ny installasjon av PostgreSQL 9.5 på $80-bygget. Ubuntu 14.04 Etter å ha konfigurert noen parametere i postgresql.conf kjørte jeg skript ved hjelp av psql. Følgende tabeller ble opprettet for å presentere dataene som EAV:
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
);
Nedenfor er en tabell der de samme dataene vil bli lagret, men med attributter i en JSONB-kolonne – egenskaper.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Ser mye enklere ut, ikke sant? Så ble det lagt til i entitetstabellene (enhet & entity_jsonb) 10 millioner poster, og tabellen ble følgelig fylt med identiske data der EAV-mønsteret og tilnærmingen med JSONB-kolonnen ble brukt – entity_jsonb.propertiesDermed fikk vi flere forskjellige datatyper på tvers av hele settet med egenskaper. Eksempeldata:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Så, nå har vi identiske data for begge alternativene. La oss begynne å sammenligne implementeringene i virkeligheten!
Forenkling av designet
Det ble nevnt tidligere at databasedesignet ble betydelig forenklet: én tabell, med en JSONB-kolonne for egenskaper, i stedet for tre tabeller for EAV. Men hvordan oversettes dette til spørringer? Oppdatering av en enkelt enhetsegenskap ser slik ut:
-- 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;
Som du kan se, ser ikke den siste spørringen enklere ut. For å oppdatere verdien av en egenskap i et JSONB-objekt, må vi bruke funksjonen , og må sende den nye verdien vår som et JSONB-objekt. Vi trenger imidlertid ikke å vite noen identifikator på forhånd. Når vi ser på EAV-eksemplet, må vi vite både entity_id og entity_attribute_id for å utføre oppdateringen. Hvis du vil oppdatere en egenskap i en JSONB-kolonne basert på objektnavnet, gjøres alt på én enkel linje.
La oss nå velge enheten vi nettopp oppdaterte basert på den nye fargen:
-- 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';
Jeg tror vi kan være enige om at den andre er kortere (uten sammenføyningen!) og derfor mer lesbar. JSONB vinner her! Vi bruker JSON ->>-operatoren for å hente fargen som en tekstverdi fra JSONB-objektet. Det finnes også en annen måte å oppnå samme resultat i JSONB-modellen ved å bruke @>-operatoren:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Dette er litt mer komplisert: vi sjekker om JSON-objektet i egenskapskolonnen inneholder objektet på høyre side av @>-operatoren. Mindre lesbart, mer effektivt (se nedenfor).
La oss forenkle bruken av JSONB ytterligere når du trenger å velge flere egenskaper samtidig. Det er her JSONB-tilnærmingen virkelig skinner: vi velger ganske enkelt egenskaper som ekstra kolonner i resultatsettet vårt, uten behov for koblinger:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Med EAV trenger du to koblinger for hver egenskap du vil spørre. Etter min mening viser spørringene ovenfor en betydelig forenkling i databasedesign. Du kan også se flere eksempler på hvordan du skriver JSONB-spørringer i stolpe.
Nå er det på tide å snakke om ytelse.
Производительность
For å sammenligne ytelsen jeg brukte i spørringer for å beregne utførelsestid. Hver spørring ble utført minst tre ganger, fordi spørreplanleggeren tar lengre tid første gang. Først kjørte jeg spørringene uten indekser. Dette tjente åpenbart som en fordel med JSONB, siden koblingene som kreves for EAV ikke kunne bruke indekser (fremmednøkkelfelt ble ikke indeksert). Etter det opprettet jeg en indeks på de to fremmednøkkelkolonnene i EAV-verditabellen, samt en indeks for JSONB-kolonnen.
Dataoppdateringer viste følgende tidsresultater (i ms). Merk at skalaen er logaritmisk:
Vi ser at JSONB er betydelig (>50 000 ganger) raskere enn EAV uten indekser, av grunnen nevnt ovenfor. Når vi indekserer primærnøkkelkolonner, forsvinner forskjellen nesten, men JSONB er fortsatt 1,3 ganger raskere enn EAV. Merk at indeksen på JSONB-kolonnen ikke har noen effekt her, siden vi ikke bruker egenskapskolonnen i evalueringskriteriene.
For datautvelgelse basert på eiendomsverdi får vi følgende resultater (normal skala):
Du kan se at JSONB igjen er raskere enn EAV uten indekser, men når EAV er indeksert, er det fortsatt raskere enn JSONB. Men så så jeg at tidene for JSONB-spørringene var de samme, noe som førte meg til at GIN-indekser ikke utløses. Tilsynelatende, når du bruker en GIN-indeks på en kolonne med utfylte egenskaper, trer den bare i kraft når du bruker inkluderingsoperatoren @>. Jeg brukte dette i en ny test, og det hadde en enorm innvirkning på tiden: bare 0,153 ms! Det er 15 000 ganger raskere enn EAV og 25 000 ganger raskere enn ->>-operatoren.
Jeg synes det gikk ganske raskt!
Størrelsen på databasetabeller
La oss sammenligne tabellstørrelser for begge tilnærmingene. I psql kan vi vise størrelsen på alle tabeller og indekser ved hjelp av kommandoen dti+
Med EAV-tilnærmingen er tabellstørrelsene omtrent 3068 MB, og indeksene er opptil 3427 MB, totalt 6,43 GB. Ved bruk av JSONB-tilnærmingen bruker tabellen 1817 MB og indeksene 318 MB, totalt 2,08 GB. Det er en tredjedel av størrelsen! Dette overrasket meg litt, siden vi lagrer egenskapsnavn i hvert JSONB-objekt.
Men tallene taler for seg selv: i EAV lagrer vi to heltalls fremmednøkler per attributtverdi, noe som resulterer i 8 byte med ekstra data. Videre lagres alle egenskapsverdier som tekst i EAV, mens JSONB vil bruke numeriske og logiske verdier internt der det er mulig, noe som resulterer i et mindre fotavtrykk.
Resultater av
Alt i alt tror jeg at lagring av enhetsegenskaper i JSONB-format kan forenkle design og vedlikehold av databasen din betydelig. Hvis du utfører mange spørringer, vil det å lagre alt i samme tabell som enheten virkelig være mer effektivt. Det at det forenkler datainteraksjoner er allerede et pluss, men den resulterende databasen er også tre ganger mindre i størrelse.
Basert på resultatene fra referansetesten kan vi også konkludere med at ytelsesunderskuddet er svært lite. I noen tilfeller yter JSONB til og med raskere enn EAV, noe som gjør den enda bedre. Denne referansetesten dekker imidlertid absolutt ikke alle aspekter (f.eks. enheter med et veldig stort antall egenskaper, en betydelig økning i antall egenskaper i eksisterende data osv.), så hvis du har noen forslag til forbedringer, kan du gjerne legge dem igjen i kommentarfeltet!
Kilde: www.habr.com
