🥇 Erstatter EAV med JSONB i PostgreSQL

TL; DR: JSONB kan i stor grad forenkle databaseskjemautvikling uten å ofre spørringsytelsen.

Innledning

La oss gi et klassisk eksempel på sannsynligvis en av de eldste brukstilfellene i verden av en relasjonsdatabase (database): vi har en enhet, og vi må lagre visse egenskaper (attributter) til denne enheten. Men ikke alle forekomster kan ha samme sett med egenskaper, og flere egenskaper kan bli lagt til i fremtiden.

Den enkleste måten å løse dette problemet på er å lage en kolonne i databasetabellen for hver egenskapsverdi, og ganske enkelt fylle ut de som trengs for en spesifikk enhetsforekomst. Flott! Problem løst ... til tabellen inneholder millioner av poster og du må legge til en ny post.

Vurder EAV-mønsteret (Entitet-attributt-verdi), forekommer det ganske ofte. En tabell inneholder entiteter (poster), en annen tabell inneholder egenskapsnavn (attributter), og en tredje tabell assosierer enheter med deres attributter og inneholder verdien av disse attributtene for gjeldende enhet. Dette gir deg muligheten til å ha forskjellige sett med egenskaper for forskjellige objekter, og også legge til egenskaper på farten uten å endre databasestrukturen.

Jeg ville imidlertid ikke skrevet dette innlegget hvis det ikke var noen ulemper med EVA-tilnærmingen. Så, for eksempel, for å få en eller flere enheter som har 1 attributt hver, kreves 2 sammenføyninger i spørringen: den første er en sammenføyning med attributttabellen, den andre er en sammenføyning med verditabellen. Hvis en enhet har 2 attributter, er det nødvendig med 4 sammenføyninger! I tillegg er alle attributter vanligvis lagret som strenger, noe som resulterer i typecasting for både resultatet og WHERE-leddet. Hvis du skriver mange spørsmål, så er dette ganske bortkastet med tanke på ressursbruk.

Til tross for disse åpenbare manglene, har EAV lenge vært brukt til å løse denne typen problemer. Dette var uunngåelige mangler, og det fantes rett og slett ikke noe bedre alternativ.
Men så dukket det opp en ny "teknologi" i PostgreSQL ...

Fra og med PostgreSQL 9.4 ble JSONB-datatypen lagt til for å lagre binære JSON-data. Selv om lagring av JSON i dette formatet vanligvis tar litt mer plass og tid enn vanlig tekst JSON, er det mye raskere å utføre operasjoner på det. 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 enhetstabellen vår, noe som i stor grad forenkler databasedesign. Men mange hevder at dette bør ledsages av en nedgang i produktiviteten... Det er derfor jeg skrev denne artikkelen.

Sette opp en testdatabase

For denne sammenligningen opprettet jeg databasen på en ny installasjon av PostgreSQL 9.5 på $80 build DigitalOcean Ubuntu 14.04. Etter å ha satt noen parametere i postgresql.conf kjørte jeg dette skript med psql. Følgende tabeller ble opprettet for å presentere dataene i EAV-form:

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-typekolonne – egenskaper.

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

Ser mye enklere ut, gjør det ikke? Deretter ble den lagt til enhetstabellene (enhet & entity_jsonb) 10 millioner poster, og følgelig ble tabellen fylt med de samme dataene ved å bruke EAV-mønsteret og tilnærmingen med en JSONB-kolonne - entity_jsonb.properties. Dermed mottok vi flere forskjellige datatyper blant 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 samme data for begge alternativene. La oss begynne å sammenligne implementeringer på jobben!

Forenkle designet ditt

Det ble tidligere uttalt at databasedesignet ble kraftig forenklet: én tabell, ved å bruke en JSONB-kolonne for egenskaper, i stedet for å bruke tre tabeller for EAV. Men hvordan gjenspeiles dette i forespørsler? Oppdatering av én enhetseiendom 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 forespørselen enklere ut. For å oppdatere verdien av en egenskap i et JSONB-objekt må vi bruke funksjonen jsonb_set(), og bør sende vår nye verdi som et JSONB-objekt. Vi trenger imidlertid ikke å vite noen identifikator på forhånd. Når vi ser på EAV-eksemplet, må vi kjenne både entity_id og entity_attribute_id for å utføre oppdateringen. Hvis du ønsker å oppdatere en egenskap i en JSONB-kolonne basert på objektnavnet, så gjøres det hele på en 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 bli enige om at den andre er kortere (ingen join!), og derfor mer lesbar. JSONB vinner her! Vi bruker JSON ->>-operatoren for å få fargen som en tekstverdi fra JSONB-objektet. Det er 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 et objekt som er til høyre for @>-operatoren. Mindre lesbar, mer produktiv (se nedenfor).

La oss gjøre bruken av JSONB enda enklere når du trenger å velge flere egenskaper samtidig. Det er her JSONB-tilnærmingen virkelig kommer inn: vi velger ganske enkelt egenskaper som ekstra kolonner i resultatsettet vårt uten behov for sammenføyninger:

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

Med EAV trenger du 2 sammenføyninger for hver eiendom du vil spørre etter. Etter min mening viser spørsmålene ovenfor en stor forenkling i databasedesign. Se flere eksempler på hvordan du skriver JSONB-spørringer, også i dette post.
Nå er det på tide å snakke om ytelse.

Производительность

For å sammenligne ytelse brukte jeg FORKLAR ANALYSER i spørringer, for å beregne utførelsestid. Hver spørring ble utført minst tre ganger fordi spørringsplanleggeren tar lengre tid første gang. Først kjørte jeg spørringene uten noen indekser. Dette var åpenbart en fordel med JSONB, siden sammenføyningene som kreves for EAV ikke kunne bruke indekser (utenlandske nøkkelfelt ble ikke indeksert). Etter dette opprettet jeg en indeks på de 2 fremmednøkkelkolonnene i EAV-verditabellen, samt en indeks GIN for en JSONB-kolonne.

Dataoppdateringen viste følgende resultater i form av tid (i ms). Merk at skalaen er logaritmisk:

Vi ser at JSONB er mye (> 50000-x) raskere enn EAV hvis du ikke bruker indekser, av grunnen nevnt ovenfor. Når vi indekserer kolonner med primærnøkler, forsvinner nesten forskjellen, 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 valg av data basert på egenskapsverdi får vi følgende resultater (normal skala):

Du kan legge merke til at JSONB igjen fungerer raskere enn EAV uten indekser, men når EAV med indekser fungerer det fortsatt raskere enn JSONB. Men så så jeg at tidspunktene for JSONB-spørringer var de samme, dette førte meg til det faktum at GIN-indekser ikke fungerer. Tilsynelatende når du bruker en GIN-indeks på en kolonne med fylte egenskaper, trer den bare i kraft når du bruker include-operatoren @>. Jeg brukte dette i en ny test, og det hadde en enorm innvirkning på tiden: bare 0,153 ms! Dette er 15000 25000 ganger raskere enn EAV og XNUMX XNUMX ganger raskere enn ->> operatøren.

Jeg synes det var raskt nok!

Databasetabellstørrelse

La oss sammenligne tabellstørrelsene for begge tilnærmingene. I psql kan vi vise størrelsen på alle tabeller og indekser ved å bruke kommandoen dti+

For EAV-tilnærmingen er tabellstørrelser rundt 3068 MB og indekserer opp til 3427 MB for totalt 6,43 GB. JSONB-tilnærmingen bruker 1817 MB for tabellen og 318 MB for indeksene, som er 2,08 GB. Det viser seg 3 ganger mindre! Dette faktum overrasket meg litt fordi vi lagrer eiendomsnavn i hvert JSONB-objekt.

Men likevel taler tallene for seg selv: i EAV lagrer vi 2 heltalls fremmednøkler per attributtverdi, noe som resulterer i 8 byte med ekstra data. I tillegg lagrer EAV alle egenskapsverdier som tekst, mens JSONB vil bruke numeriske og boolske verdier internt der det er mulig, noe som resulterer i et mindre fotavtrykk.

Resultater av

Totalt sett tror jeg lagring av enhetsegenskaper i JSONB-format kan gjøre utforming og vedlikehold av databasen mye enklere. Hvis du kjører mange søk, vil det faktisk fungere mer effektivt å holde alt i samme tabell som enheten. Og det faktum at dette forenkler interaksjonen mellom data er allerede et pluss, men den resulterende databasen er 3 ganger mindre i volum.

Basert på de utførte testene kan vi også konkludere med at ytelsestapene er svært ubetydelige. I noen tilfeller er JSONB enda raskere enn EAV, noe som gjør den enda bedre. Men denne referansen dekker selvfølgelig ikke alle aspekter (f.eks. enheter med et veldig stort antall egenskaper, en betydelig økning i antall egenskaper for eksisterende data,...), så hvis du har noen forslag til hvordan du kan forbedre dem , legg gjerne igjen i kommentarfeltet!

Kilde: www.habr.com

Erstatter EAV med JSONB i PostgreSQL