Ersätter EAV med JSONB i PostgreSQL

TL; DR: JSONB kan avsevärt förenkla utvecklingen av databasschema utan att offra frågeprestanda.

Inledning

Låt oss ge ett klassiskt exempel på förmodligen ett av de äldsta användningsfallen i världen av en relationsdatabas (databas): vi har en entitet, och vi måste spara vissa egenskaper (attribut) för denna entitet. Men alla instanser kanske inte har samma uppsättning egenskaper, och fler egenskaper kan läggas till i framtiden.

Det enklaste sättet att lösa detta problem är att skapa en kolumn i databastabellen för varje egenskapsvärde och helt enkelt fylla i de som behövs för en specifik entitetsinstans. Bra! Problem löst... tills din tabell innehåller miljontals poster och du behöver lägga till en ny post.

Tänk på EAV-mönstret (Entitet-Attribut-Värde), förekommer det ganska ofta. En tabell innehåller entiteter (poster), en annan tabell innehåller egenskapsnamn (attribut) och en tredje tabell associerar enheter med deras attribut och innehåller värdet av dessa attribut för den aktuella enheten. Detta ger dig möjligheten att ha olika uppsättningar egenskaper för olika objekt, och även lägga till egenskaper i farten utan att ändra databasstrukturen.

Jag skulle dock inte skriva det här inlägget om det inte fanns några nackdelar med EVA-metoden. Så, till exempel, för att få en eller flera entiteter som har 1 attribut vardera, krävs 2 joins i frågan: den första är en join med attributtabellen, den andra är en join med värdetabellen. Om en entitet har 2 attribut behövs 4 kopplingar! Dessutom lagras alla attribut vanligtvis som strängar, vilket resulterar i typcasting för både resultatet och WHERE-satsen. Om du skriver många frågor är detta ganska slösaktigt när det gäller resursanvändning.

Trots dessa uppenbara brister har EAV länge använts för att lösa den här typen av problem. Dessa var oundvikliga brister, och det fanns helt enkelt inget bättre alternativ.
Men så dök en ny "teknik" upp i PostgreSQL...

Från och med PostgreSQL 9.4 lades JSONB-datatypen till för att lagra binär JSON-data. Även om lagring av JSON i det här formatet vanligtvis tar lite mer utrymme och tid än JSON med vanlig text, är det mycket snabbare att utföra operationer på det. JSONB stöder även indexering, vilket gör frågorna ännu snabbare.

JSONB-datatypen tillåter oss att ersätta det krångliga EAV-mönstret genom att lägga till bara en JSONB-kolumn i vår entitetstabell, vilket avsevärt förenklar databasdesign. Men många hävdar att detta borde åtföljas av en minskning av produktiviteten... Det var därför jag skrev den här artikeln.

Skapa en testdatabas

För denna jämförelse skapade jag databasen på en ny installation av PostgreSQL 9.5 på $80 build DigitalOcean Ubuntu 14.04 Efter att ha konfigurerat några parametrar i postgresql.conf körde jag detta skript med psql. Följande tabeller skapades för att presentera data 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
);

Nedan finns en tabell där samma data kommer att lagras, men med attribut i en kolumn av JSONB-typ – egenskaper.

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

Ser mycket enklare ut, eller hur? Sedan lades det till i entitetstabellerna (enhet & entity_jsonb) 10 miljoner poster, och följaktligen fylldes tabellen med samma data med hjälp av EAV-mönstret och tillvägagångssättet med en JSONB-kolumn - entity_jsonb.properties. Således fick vi flera olika datatyper bland hela uppsättningen av egenskaper. Exempeldata:

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

Så nu har vi samma data för båda alternativen. Låt oss börja jämföra implementeringar på jobbet!

Förenkla din design

Det har tidigare sagts att databasdesignen förenklades kraftigt: en tabell, genom att använda en JSONB-kolumn för egenskaper, istället för att använda tre tabeller för EAV. Men hur återspeglas detta i förfrågningar? Att uppdatera en enhetsegenskap ser ut så här:

-- 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 den sista begäran inte enklare ut. För att uppdatera värdet på en egenskap i ett JSONB-objekt måste vi använda funktionen jsonb_set(), och bör skicka vårt nya värde som ett JSONB-objekt. Vi behöver dock inte känna till någon identifierare i förväg. Om vi ​​tittar på EAV-exemplet måste vi känna till både entity_id och entity_attribute_id för att kunna utföra uppdateringen. Om du vill uppdatera en egenskap i en JSONB-kolumn baserat på objektnamnet, görs allt på en enkel rad.

Låt oss nu välja den enhet vi just uppdaterade baserat på dess nya färg:

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

Jag tror att vi kan komma överens om att den andra är kortare (ingen anslutning!), och därför mer läsbar. JSONB vinner här! Vi använder operatorn JSON ->> för att få färgen som ett textvärde från ett JSONB-objekt. Det finns också ett andra sätt att uppnå samma resultat i JSONB-modellen med @>-operatorn:

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

Det här är lite mer komplicerat: vi kontrollerar om JSON-objektet i egenskapskolumnen innehåller ett objekt som är till höger om @>-operatorn. Mindre läsbar, mer produktiv (se nedan).

Låt oss göra det ännu enklare att använda JSONB när du behöver välja flera egenskaper samtidigt. Det är här som JSONB-metoden verkligen kommer in: vi väljer helt enkelt egenskaper som ytterligare kolumner i vår resultatuppsättning utan behov av kopplingar:

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

Med EAV behöver du 2 anslutningar för varje fastighet du vill fråga. Enligt min åsikt visar ovanstående frågor en stor förenkling i databasdesign. Se fler exempel på hur man skriver JSONB-frågor, även i detta posta.
Nu är det dags att prata om prestation.

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

För att jämföra prestanda använde jag FÖRKLARA ANALYSERA i frågor, för att beräkna exekveringstiden. Varje fråga kördes minst tre gånger eftersom frågeplaneraren tar längre tid första gången. Först körde jag frågorna utan några index. Uppenbarligen var detta en fördel med JSONB, eftersom de kopplingar som krävs för EAV inte kunde använda index (utländska nyckelfält indexerades inte). Efter detta skapade jag ett index på de två främmande nyckelkolumnerna i EAV-värdestabellen, samt ett index GIN för en JSONB-kolumn.

Datauppdateringen visade följande resultat i termer av tid (i ms). Observera att skalan är logaritmisk:

Vi ser att JSONB är mycket (> 50000-x) snabbare än EAV om du inte använder index, av anledningen som anges ovan. När vi indexerar kolumner med primärnycklar försvinner nästan skillnaden, men JSONB är fortfarande 1,3 gånger snabbare än EAV. Observera att indexet på JSONB-kolumnen inte har någon effekt här eftersom vi inte använder egenskapskolumnen i utvärderingskriterierna.

För att välja data baserat på egenskapsvärde får vi följande resultat (normal skala):

Du kan märka att JSONB återigen fungerar snabbare än EAV utan index, men när EAV med index fungerar det fortfarande snabbare än JSONB. Men sedan såg jag att tiderna för JSONB-frågor var desamma, detta fick mig att inse att GIN-index inte fungerar. Tydligen när du använder ett GIN-index på en kolumn med ifyllda egenskaper, träder det bara i kraft när du använder include-operatorn @>. Jag använde detta i ett nytt test och det hade en enorm inverkan på tiden: bara 0,153ms! Detta är 15000 25000 gånger snabbare än EAV och XNUMX XNUMX gånger snabbare än ->> operatören.

Jag tycker det var tillräckligt snabbt!

Databastabellstorlek

Låt oss jämföra tabellstorlekarna för båda tillvägagångssätten. I psql kan vi visa storleken på alla tabeller och index med kommandot dti+

För EAV-metoden är tabellstorlekarna cirka 3068 MB och indexerar upp till 3427 MB för totalt 6,43 GB. JSONB-metoden använder 1817 MB för tabellen och 318 MB för indexen, vilket är 2,08 GB. Det blir 3 gånger mindre! Detta faktum förvånade mig lite eftersom vi lagrar fastighetsnamn i varje JSONB-objekt.

Men ändå talar siffrorna för sig själva: i EAV lagrar vi 2 heltals främmande nycklar per attributvärde, vilket resulterar i 8 byte med ytterligare data. Dessutom lagrar EAV alla egenskapsvärden som text, medan JSONB kommer att använda numeriska och booleska värden internt där det är möjligt, vilket resulterar i ett mindre fotavtryck.

Resultat av

Sammantaget tror jag att att spara entitetsegenskaper i JSONB-format kan göra design och underhåll av din databas mycket enklare. Om du kör många frågor kommer det att fungera mer effektivt om du håller allt i samma tabell som enheten. Och det faktum att detta förenklar interaktionen mellan data är redan ett plus, men den resulterande databasen är 3 gånger mindre i volym.

Baserat på utförda tester kan vi också dra slutsatsen att prestationsförlusterna är mycket obetydliga. I vissa fall är JSONB ännu snabbare än EAV, vilket gör den ännu bättre. Men detta riktmärke täcker naturligtvis inte alla aspekter (t.ex. enheter med ett mycket stort antal egenskaper, en betydande ökning av antalet egenskaper för befintliga data,...), så om du har några förslag på hur man kan förbättra dem , lämna gärna i kommentarerna!

Källa: will.com