🥇 Skipt um EAV fyrir JSONB í PostgreSQL

TL; DR: JSONB getur mjög einfaldað þróun gagnagrunnsskemu án þess að fórna frammistöðu fyrirspurna.

Inngangur

Við skulum gefa klassískt dæmi um líklega eitt elsta notkunartilvikið í heimi tengslagagnagrunns (gagnagrunns): við höfum einingu og við þurfum að vista ákveðna eiginleika (eiginleika) þessarar einingar. En ekki er víst að öll tilvik hafi sömu eiginleika og fleiri eiginleikar gætu bæst við í framtíðinni.

Auðveldasta leiðin til að leysa þetta vandamál er að búa til dálk í gagnagrunnstöflunni fyrir hvert eignargildi og einfaldlega fylla út þær sem þarf fyrir tiltekið einingstilvik. Frábært! Vandamálið leyst... þar til taflan þín inniheldur milljónir skráa og þú þarft að bæta við nýrri skrá.

Íhugaðu EAV mynstrið (Eining-eiginleiki-Value), það kemur nokkuð oft fyrir. Ein tafla inniheldur einingar (færslur), önnur tafla inniheldur eignaheiti (eiginleika) og þriðja tafla tengir einingar við eiginleika þeirra og inniheldur gildi þessara eiginleika fyrir núverandi aðila. Þetta gefur þér möguleika á að hafa mismunandi sett af eiginleikum fyrir mismunandi hluti, og einnig bæta við eiginleikum á flugu án þess að breyta uppbyggingu gagnagrunnsins.

Hins vegar væri ég ekki að skrifa þessa færslu ef það væru ekki einhverjir gallar við EVA nálgunina. Svo, til dæmis, til að fá eina eða fleiri einingar sem hafa 1 eigind hver, þarf 2 tengingar í fyrirspurninni: sú fyrri er tenging við eigindatöfluna, sú seinni er tenging við gildistöfluna. Ef eining hefur 2 eiginleika, þá þarf 4 joins! Að auki eru allir eiginleikar venjulega geymdir sem strengir, sem leiðir til tegundarsteypu fyrir bæði niðurstöðuna og WHERE-ákvæðið. Ef þú skrifar mikið af fyrirspurnum, þá er þetta frekar sóun hvað varðar auðlindanotkun.

Þrátt fyrir þessa augljósu galla hefur EAV lengi verið notað til að leysa þessa tegund vandamála. Þetta voru óumflýjanlegir annmarkar og það var einfaldlega enginn betri kostur.
En svo birtist ný „tækni“ í PostgreSQL...

Frá og með PostgreSQL 9.4 var JSONB gagnagerðinni bætt við til að geyma JSON tvöfalda gögn. Þó að geymsla JSON á þessu sniði taki venjulega aðeins meira pláss og tíma en JSON með venjulegum texta, þá er mun hraðari að framkvæma aðgerðir á því. JSONB styður einnig flokkun, sem gerir fyrirspurnir enn hraðari.

JSONB gagnagerðin gerir okkur kleift að skipta um fyrirferðarmikið EAV mynstur með því að bæta aðeins einum JSONB dálki við einingatöfluna okkar, sem einfaldar hönnun gagnagrunns til muna. En margir halda því fram að þessu ætti að fylgja minnkun á framleiðni... Þess vegna skrifaði ég þessa grein.

Uppsetning prófunargagnagrunns

Fyrir þennan samanburð bjó ég til gagnagrunninn á nýrri uppsetningu á PostgreSQL 9.5 á $80 byggingunni DigitalOcean Ubuntu 14.04. Eftir að hafa stillt nokkrar breytur í postgresql.conf hljóp ég þetta forskrift með psql. Eftirfarandi töflur voru búnar til til að kynna gögnin á EAV formi:

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

Hér að neðan er tafla þar sem sömu gögn verða geymd, en með eiginleikum í JSONB gerð dálki - eignir.

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

Lítur miklu einfaldara út, er það ekki? Síðan var því bætt við einingartöflurnar (einingin & entity_jsonb) 10 milljón færslur, og í samræmi við það var taflan fyllt með sömu gögnum með því að nota EAV mynstrið og nálgunina með JSONB dálki - entity_jsonb.properties. Þannig fengum við nokkrar mismunandi gagnagerðir meðal alls eignasettsins. Dæmi um gögn:

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

Svo nú höfum við sömu gögn fyrir báða valkostina. Byrjum að bera saman útfærslur í vinnunni!

Einfaldaðu hönnunina þína

Áður kom fram að gagnagrunnshönnunin var einfölduð til muna: Ein tafla, með því að nota JSONB dálk fyrir eiginleika, í stað þess að nota þrjár töflur fyrir EAV. En hvernig kemur þetta fram í beiðnum? Uppfærsla á einni eign lítur svona út:

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

Eins og þú sérð lítur síðasta beiðni ekki einfaldari út. Til að uppfæra gildi eignar í JSONB hlut verðum við að nota aðgerðina jsonb_set(), og ætti að gefa nýja gildi okkar sem JSONB hlut. Hins vegar þurfum við ekki að vita neitt auðkenni fyrirfram. Þegar litið er á EAV dæmið þurfum við að þekkja bæði entity_id og entity_attribute_id til að framkvæma uppfærsluna. Ef þú vilt uppfæra eign í JSONB dálki byggt á nafni hlutar, þá er það allt gert í einni einfaldri línu.

Nú skulum við velja eininguna sem við uppfærðum nýlega miðað við nýja litinn:

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

Ég held að við getum verið sammála um að sú seinni sé styttri (engin join!), og því læsilegri. JSONB vinnur hér! Við notum JSON ->> rekstraraðila til að fá litinn sem textagildi frá JSONB hlutnum. Það er líka önnur leið til að ná sömu niðurstöðu í JSONB líkaninu með @> rekstraraðilanum:

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

Þetta er aðeins flóknara: við athugum hvort JSON hluturinn í eiginleikadálknum inniheldur hlutinn sem er hægra megin við @> rekstraraðilann. Minna læsileg, afkastameiri (sjá hér að neðan).

Við skulum gera notkun JSONB enn auðveldari þegar þú þarft að velja margar eignir í einu. Þetta er þar sem JSONB nálgunin kemur í raun inn: við veljum einfaldlega eiginleika sem viðbótardálka í niðurstöðusettinu okkar án þess að þurfa að sameina:

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

Með EAV þarftu 2 joins fyrir hverja eign sem þú vilt spyrjast fyrir um. Að mínu mati sýna ofangreindar fyrirspurnir mikla einföldun í hönnun gagnagrunns. Sjá fleiri dæmi um hvernig á að skrifa JSONB fyrirspurnir, einnig í þetta færslu.
Nú er kominn tími til að tala um frammistöðu.

Framleiðni

Til að bera saman árangur notaði ég Útskýrðu greiningu í fyrirspurnum, til að reikna út framkvæmdartíma. Hver fyrirspurn var keyrð að minnsta kosti þrisvar sinnum vegna þess að fyrirspurnarskipuleggjandinn tekur lengri tíma í fyrsta skiptið. Fyrst rak ég fyrirspurnirnar án nokkurra vísitölu. Augljóslega var þetta kostur við JSONB, þar sem sameiningarnar sem krafist er fyrir EAV gátu ekki notað vísitölur (erlendir lykilreitir voru ekki verðtryggðir). Eftir þetta bjó ég til vísitölu á 2 erlenda lykildálka EAV gildistöflunnar, auk vísitölu GIN fyrir JSONB dálk.

Gagnauppfærslan sýndi eftirfarandi niðurstöður hvað varðar tíma (í ms). Athugið að kvarðinn er logaritmískur:

Við sjáum að JSONB er miklu (> 50000-x) hraðari en EAV ef þú notar ekki vísitölur, af þeirri ástæðu sem tilgreind er hér að ofan. Þegar við skráum dálka með aðallyklum hverfur munurinn næstum því en JSONB er samt 1,3 sinnum hraðari en EAV. Athugaðu að vísitalan á JSONB dálknum hefur engin áhrif hér þar sem við erum ekki að nota eignardálkinn í matsviðmiðunum.

Til að velja gögn byggð á eignargildi fáum við eftirfarandi niðurstöður (venjulegur mælikvarði):

Þú getur tekið eftir því að JSONB virkar aftur hraðar en EAV án vísitölu, en þegar EAV með vísitölum virkar það samt hraðar en JSONB. En svo sá ég að tímarnir fyrir JSONB fyrirspurnir voru þeir sömu, þetta varð til þess að ég vissi að GIN vísitölur virka ekki. Svo virðist sem þegar þú notar GIN vísitölu á dálki með útfylltum eiginleikum, þá tekur það aðeins gildi þegar þú notar include operator @>. Ég notaði þetta í nýju prófi og það hafði mikil áhrif á tímann: aðeins 0,153ms! Þetta er 15000 sinnum hraðar en EAV og 25000 sinnum hraðar en ->> rekstraraðilinn.

Ég held að það hafi verið nógu hratt!

Stærð gagnagrunnstöflu

Við skulum bera saman töflustærðirnar fyrir báðar aðferðir. Í psql getum við sýnt stærð allra tafla og vísitölu með því að nota skipunina dti+

Fyrir EAV nálgunina eru borðstærðir um 3068 MB og vísitölur allt að 3427 MB fyrir samtals 6,43 GB. JSONB nálgunin notar 1817 MB fyrir borðið og 318 MB fyrir vísitölurnar, sem er 2,08 GB. Það kemur 3 sinnum minna í ljós! Þessi staðreynd kom mér svolítið á óvart vegna þess að við geymum eignarheiti í hverjum JSONB hlut.

En samt tala tölurnar sínu máli: í EAV geymum við 2 heiltölu erlenda lykla fyrir hvert eigindargildi, sem leiðir til 8 bæta af viðbótargögnum. Að auki geymir EAV öll eignagildi sem texta, á meðan JSONB mun nota töluleg og boolean gildi innbyrðis þar sem hægt er, sem leiðir til minna fótspors.

Niðurstöður

Á heildina litið held ég að vistun einingareiginleika á JSONB sniði geti gert hönnun og viðhald gagnagrunnsins mun auðveldara. Ef þú ert að keyra margar fyrirspurnir, þá mun það í raun virka á skilvirkari hátt að halda öllu í sömu töflu og einingin. Og sú staðreynd að þetta einfaldar samskipti milli gagna er nú þegar plús, en gagnagrunnurinn sem myndast er 3 sinnum minni að magni.

Einnig, byggt á prófunum sem gerðar voru, getum við ályktað að árangurstapið sé mjög óverulegt. Í sumum tilfellum er JSONB jafnvel hraðari en EAV, sem gerir það enn betra. Hins vegar nær þetta viðmið að sjálfsögðu ekki til allra þátta (t.d. aðila með mjög mikinn fjölda eigna, verulega fjölgun eiginleika núverandi gagna,...), þannig að ef þú hefur einhverjar tillögur um hvernig megi bæta þau , ekki hika við að skilja eftir í athugasemdunum!

Heimild: www.habr.com

Skipt um EAV fyrir JSONB í PostgreSQL