EAV vervangen door JSONB in ​​PostgreSQL

TL; DR: JSONB kan de ontwikkeling van databaseschema's aanzienlijk vereenvoudigen zonder dat dit ten koste gaat van de queryprestaties.

Introductie

Laten we een klassiek voorbeeld geven van waarschijnlijk een van de oudste gebruiksscenario's ter wereld van een relationele database (database): we hebben een entiteit en we moeten bepaalde eigenschappen (attributen) van deze entiteit opslaan. Maar mogelijk hebben niet alle instanties dezelfde set eigenschappen en kunnen er in de toekomst meer eigenschappen worden toegevoegd.

De eenvoudigste manier om dit probleem op te lossen is door voor elke eigenschapswaarde een kolom in de databasetabel te maken en eenvoudigweg de kolommen in te vullen die nodig zijn voor een specifieke entiteitsinstantie. Geweldig! Probleem opgelost... totdat uw tabel miljoenen records bevat en u een nieuw record moet toevoegen.

Beschouw het EAV-patroon (Entiteit-kenmerk-waarde), het komt vrij vaak voor. Eén tabel bevat entiteiten (records), een andere tabel bevat eigenschapsnamen (attributen) en een derde tabel associeert entiteiten met hun attributen en bevat de waarde van die attributen voor de huidige entiteit. Dit geeft u de mogelijkheid om verschillende sets eigenschappen voor verschillende objecten te hebben, en ook direct eigenschappen toe te voegen zonder de databasestructuur te wijzigen.

Ik zou dit bericht echter niet schrijven als er geen nadelen aan de EVA-aanpak kleven. Om bijvoorbeeld een of meer entiteiten te verkrijgen die elk 1 attribuut hebben, zijn er in de query 2 joins nodig: de eerste is een join met de attributentabel, de tweede is een join met de waardentabel. Als een entiteit twee attributen heeft, zijn er vier joins nodig! Bovendien worden alle attributen doorgaans opgeslagen als tekenreeksen, wat resulteert in type-casting voor zowel het resultaat als de WHERE-clausule. Als u veel zoekopdrachten schrijft, is dit behoorlijk verspillend in termen van bronnengebruik.

Ondanks deze duidelijke tekortkomingen wordt EAV al lang gebruikt om dit soort problemen op te lossen. Dit waren onvermijdelijke tekortkomingen en er was eenvoudigweg geen beter alternatief.
Maar toen verscheen er een nieuwe “technologie” in PostgreSQL...

Vanaf PostgreSQL 9.4 is het gegevenstype JSONB toegevoegd om binaire JSON-gegevens op te slaan. Hoewel het opslaan van JSON in dit formaat doorgaans iets meer ruimte en tijd kost dan JSON met platte tekst, is het uitvoeren van bewerkingen erop veel sneller. JSONB ondersteunt ook indexering, waardoor zoekopdrachten nog sneller worden.

Met het JSONB-gegevenstype kunnen we het omslachtige EAV-patroon vervangen door slechts één JSONB-kolom aan onze entiteitentabel toe te voegen, waardoor het databaseontwerp aanzienlijk wordt vereenvoudigd. Maar velen beweren dat dit gepaard zou moeten gaan met een afname van de productiviteit... Daarom heb ik dit artikel geschreven.

Opzetten van een testdatabase

Voor deze vergelijking heb ik de database gemaakt op een nieuwe installatie van PostgreSQL 9.5 op de build van $ 80 DigitalOcean Ubuntu 14.04. Na het instellen van enkele parameters in postgresql.conf begon ik te rennen deze script met psql. De volgende tabellen zijn gemaakt om de gegevens in EAV-vorm te presenteren:

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
);

Hieronder staat een tabel waarin dezelfde gegevens worden opgeslagen, maar met attributen in een kolom van het JSONB-type – vastgoed.

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

Ziet er een stuk eenvoudiger uit, nietwaar? Vervolgens werd het toegevoegd aan de entiteitstabellen (entiteit & entiteit_jsonb) 10 miljoen records, en dienovereenkomstig werd de tabel gevuld met dezelfde gegevens met behulp van het EAV-patroon en de aanpak met een JSONB-kolom - entiteit_jsonb.properties. We hebben dus verschillende gegevenstypen ontvangen voor de volledige set eigenschappen. Voorbeeldgegevens:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

Dus nu hebben we dezelfde gegevens voor beide opties. Laten we beginnen met het vergelijken van implementaties op het werk!

Vereenvoudig uw ontwerp

Eerder werd aangegeven dat het databaseontwerp sterk vereenvoudigd was: één tabel, door een JSONB-kolom te gebruiken voor eigenschappen, in plaats van drie tabellen te gebruiken voor EAV. Maar hoe komt dit tot uiting in verzoeken? Het bijwerken van één entiteitseigenschap ziet er als volgt uit:

-- 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;

Zoals u kunt zien, ziet het laatste verzoek er niet eenvoudiger uit. Om de waarde van een eigenschap in een JSONB-object bij te werken, moeten we de functie gebruiken jsonb_set(), en zou onze nieuwe waarde moeten doorgeven als een JSONB-object. We hoeven echter van tevoren geen identificatie te kennen. Als we naar het EAV-voorbeeld kijken, moeten we zowel de entiteit_id als de entiteit_attribute_id kennen om de update uit te voeren. Als u een eigenschap in een JSONB-kolom wilt bijwerken op basis van de objectnaam, dan gebeurt dit allemaal in één eenvoudige regel.

Laten we nu de entiteit selecteren die we zojuist hebben bijgewerkt op basis van de nieuwe kleur:

-- 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';

Ik denk dat we het erover eens kunnen zijn dat de tweede korter is (no join!), en daarom beter leesbaar. JSONB wint hier! We gebruiken de JSON ->> operator om de kleur als tekstwaarde uit het JSONB-object te halen. Er is ook een tweede manier om hetzelfde resultaat te bereiken in het JSONB-model met behulp van de operator @>:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

Dit is iets ingewikkelder: we controleren of het JSON-object in de eigenschappenkolom een ​​object bevat dat zich rechts van de @>-operator bevindt. Minder leesbaar, productiever (zie hieronder).

Laten we het gebruik van JSONB nog eenvoudiger maken als u meerdere eigenschappen tegelijk moet selecteren. Dit is waar de JSONB-aanpak echt van pas komt: we selecteren eenvoudigweg eigenschappen als extra kolommen in onze resultatenset zonder dat er joins nodig zijn:

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

Met EAV heeft u 2 joins nodig voor elke woning die u wilt bevragen. Naar mijn mening laten de bovenstaande vragen een grote vereenvoudiging zien in het databaseontwerp. Bekijk meer voorbeelden van het schrijven van JSONB-query's, ook in deze na.
Nu is het tijd om over prestaties te praten.

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

Om de prestaties te vergelijken die ik heb gebruikt UITLEG ANALYSE in query's, om de uitvoeringstijd te berekenen. Elke query werd minimaal drie keer uitgevoerd omdat de queryplanner er de eerste keer langer over doet. Eerst heb ik de queries uitgevoerd zonder indexen. Dit was duidelijk een voordeel van JSONB, omdat de joins die nodig zijn voor EAV geen indexen konden gebruiken (foreign key-velden werden niet geïndexeerd). Hierna heb ik een index gemaakt op de 2 refererende sleutelkolommen van de EAV-waardetabel, evenals een index GIN voor een JSONB-kolom.

De gegevensupdate liet de volgende resultaten zien in termen van tijd (in ms). Merk op dat de schaal logaritmisch is:

We zien dat JSONB veel (> 50000-x) sneller is dan EAV als je geen indexen gebruikt, om de hierboven genoemde reden. Wanneer we kolommen met primaire sleutels indexeren, verdwijnt het verschil bijna, maar JSONB is nog steeds 1,3 keer sneller dan EAV. Houd er rekening mee dat de index op de JSONB-kolom hier geen effect heeft, omdat we de eigenschappenkolom niet gebruiken in de evaluatiecriteria.

Voor het selecteren van gegevens op basis van de waarde van onroerend goed krijgen we de volgende resultaten (normale schaal):

Je merkt dat JSONB weer sneller werkt dan EAV zonder indexen, maar bij EAV met indexen werkt het nog steeds sneller dan JSONB. Maar toen zag ik dat de tijden voor JSONB-query's hetzelfde waren, dit bracht mij tot het feit dat GIN-indexen niet werken. Blijkbaar heeft het gebruik van een GIN-index voor een kolom met ingevulde eigenschappen alleen effect als u de include-operator @> gebruikt. Ik gebruikte dit in een nieuwe test en het had een enorme impact op de tijd: slechts 0,153 ms! Dit is 15000 keer sneller dan EAV en 25000 keer sneller dan de ->> operator.

Ik denk dat het snel genoeg was!

Grootte databasetabel

Laten we de tabelformaten voor beide benaderingen vergelijken. In psql kunnen we de grootte van alle tabellen en indexen weergeven met behulp van de opdracht dti+

Voor de EAV-aanpak zijn de tabelgroottes ongeveer 3068 MB en de indexen maximaal 3427 MB, voor een totaal van 6,43 GB. De JSONB-aanpak gebruikt 1817 MB voor de tabel en 318 MB voor de indexen, wat neerkomt op 2,08 GB. Het blijkt 3 keer minder! Dit feit verraste me een beetje omdat we eigenschapsnamen in elk JSONB-object opslaan.

Maar toch spreken de cijfers voor zich: in EAV slaan we 2 externe integer-sleutels per attribuutwaarde op, wat resulteert in 8 bytes aan extra gegevens. Bovendien slaat EAV alle eigenschapswaarden op als tekst, terwijl JSONB waar mogelijk numerieke en Booleaanse waarden intern zal gebruiken, wat resulteert in een kleinere footprint.

Resultaten van

Over het algemeen denk ik dat het opslaan van entiteitseigenschappen in JSONB-indeling het ontwerpen en onderhouden van uw database veel eenvoudiger kan maken. Als u veel query's uitvoert, zal het feitelijk efficiënter werken als u alles in dezelfde tabel als de entiteit houdt. En het feit dat dit de interactie tussen gegevens vereenvoudigt is al een pluspunt, maar de resulterende database is 3 keer kleiner in volume.

Ook kunnen we op basis van de uitgevoerde tests concluderen dat de prestatieverliezen zeer onbeduidend zijn. In sommige gevallen is JSONB zelfs sneller dan EAV, waardoor het nog beter wordt. Deze benchmark dekt uiteraard echter niet alle aspecten (bijvoorbeeld entiteiten met een zeer groot aantal eigenschappen, een aanzienlijke toename van het aantal eigenschappen van bestaande gegevens,...), dus als u suggesties heeft over hoe u deze kunt verbeteren , laat het gerust achter in de reacties!

Bron: www.habr.com