A’ cur JSONB an àite EAV ann am PostgreSQL

TL;DR: Faodaidh JSONB leasachadh sgeama stòr-dàta a dhèanamh gu mòr nas sìmplidhe gun a bhith a’ ìobairt coileanadh cheistean.

Ro-ràdh

Gabhamaid eisimpleir clasaigeach, is dòcha aon de na cùisean cleachdaidh as sine ann an saoghal stòran-dàta dàimheil: tha eintiteas againn, agus feumaidh sinn feartan sònraichte den eintiteas seo a stòradh. Ach is dòcha nach bi an aon sheata de fheartan aig a h-uile eisimpleir, agus dh’ fhaodadh barrachd fheartan a bhith air an cur ris san àm ri teachd.

Is e am fuasgladh as sìmplidh don duilgheadas seo colbh a chruthachadh ann an clàr an stòr-dàta airson gach luach feart agus dìreach na luachan a tha a dhìth airson eisimpleir sònraichte de dh’eintiteas a lìonadh. Sgoinneil! Duilgheadas air fhuasgladh... gus am bi milleanan de chlàran anns a’ chlàr agad agus feumaidh tu clàr ùr a chur ris.

Beachdaicheamaid air pàtran EAV (Luach-Buadh-Eintiteas), tha e gu math cumanta. Tha eintiteasan (clàran) ann an aon chlàr, tha ainmean thogalaichean (feartan) ann an clàr eile, agus tha treas clàr a’ ceangal eintiteasan ris na feartan aca agus tha luachan nan feartan sin ann airson an eintiteas làithreach. Leigidh seo leat seataichean eadar-dhealaichte de fheartan a bhith agad airson diofar nithean, a bharrachd air feartan a chur ris sa bhad, gun structar an stòr-dàta atharrachadh.

Ach, cha bhiodhinn a’ sgrìobhadh na dreuchd seo mura biodh cuid de eas-bhuannachdan ann an dòigh-obrach EVA. Mar eisimpleir, feumaidh dà cheangal a bhith anns a’ cheist gus aon eintiteas no barrachd fhaighinn le aon fheart anns gach fear: a’ chiad cheangal leis a’ chlàr fheartan, an dàrna ceangal leis a’ chlàr luachan. Ma tha dà fheart aig eintiteas, tha feum air ceithir ceanglaichean! A bharrachd air an sin, mar as trice bidh na feartan uile air an stòradh mar shreangan, a tha ag adhbhrachadh èigneachadh seòrsa airson an toradh agus a’ chlàs WHERE. Ma sgrìobhas tu tòrr cheistean, tha seo gu math sgudail a thaobh cleachdadh ghoireasan.

A dh’aindeoin nan easbhaidhean follaiseach seo, tha EAV air a bhith air a chleachdadh o chionn fhada gus na seòrsaichean dhuilgheadasan seo fhuasgladh. B’ e easbhaidhean do-sheachanta a bh’ annta sin, agus cha robh roghainn eile nas fheàrr ann idir.
Ach an uairsin nochd “teicneòlas” ùr ann am PostgreSQL…

A’ tòiseachadh le PostgreSQL 9.4, chaidh an seòrsa dàta JSONB a chur ris airson dàta dà-chànanach JSON a stòradh. Ged a bheir stòradh JSON san fhòrmat seo beagan a bharrachd àite is ùine na JSON teacsa lom, tha obrachaidhean leis tòrr nas luaithe. Tha JSONB cuideachd a’ toirt taic do chlàr-amais, a’ dèanamh cheistean eadhon nas luaithe.

Leigidh an seòrsa dàta JSONB leinn am pàtran EAV trom a chur an àite le bhith a’ cur dìreach aon cholbh JSONB ris a’ chlàr eintiteas againn, a’ sìmpleachadh dealbhadh an stòr-dàta gu mòr. Ach, tha mòran ag argamaid gu bheil seo a’ tighinn aig cosgais a thaobh coileanadh… Sin as coireach gun do sgrìobh mi an artaigil seo.

A’ stèidheachadh stòr-dàta deuchainn

Airson a’ choimeas seo, chruthaich mi an stòr-dàta air stàladh ùr de PostgreSQL 9.5 air an togail $80. DigitalOcean Ubuntu 14.04 Às dèidh dhomh cuid de pharaimearan a rèiteachadh ann am postgresql.conf ruith mi seo sgriobt a’ cleachdadh psql. Gus an dàta a thaisbeanadh mar EAV, chaidh na clàran a leanas a chruthachadh:

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

Gu h-ìosal tha clàr far an tèid an aon dàta a stòradh, ach le feartan ann an colbh JSONB – seilbhean.

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

Tha e a’ coimhead tòrr nas sìmplidh, nach eil? An uairsin chaidh a chur ris na clàran eintiteas (eintiteas & eintiteas_jsonb) 10 millean clàr, agus mar sin, bha an clàr air a lìonadh le dàta co-ionann far an deach am pàtran EAV agus an dòigh-obrach leis a’ cholbh JSONB a chleachdadh – feartan eintiteas_jsonbMar sin, fhuair sinn grunn sheòrsaichean dàta eadar-dhealaichte thar an t-seata iomlan de thogalaichean. Eisimpleir dàta:

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

Mar sin, tha dàta co-ionann againn a-nis airson an dà roghainn. Tòisichidh sinn a’ dèanamh coimeas eadar na buileachadh ann am fìor bheatha!

A’ sìmpleachadh an dealbhaidh

Chaidh iomradh a thoirt roimhe seo gun deach dealbhadh an stòr-dàta a dhèanamh gu math nas sìmplidhe: aon chlàr, a’ cleachdadh colbh JSONB airson feartan, an àite trì clàran airson EAV. Ach ciamar a tha seo ag eadar-theangachadh gu ceistean? Tha ùrachadh feart aon eintiteas coltach ris an seo:

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

Mar a chì thu, chan eil an t-iarrtas mu dheireadh a’ coimhead nas sìmplidh. Gus luach feart ann an nì JSONB ùrachadh, feumaidh sinn an gnìomh a chleachdadh jsonb_set(), agus feumaidh sinn an luach ùr againn a thoirt seachad mar nì JSONB. Ach, chan fheum sinn comharraiche sam bith a bhith againn ro-làimh. A’ coimhead air eisimpleir EAV, feumaidh fios a bhith againn air an dà chuid an entity_id agus an entity_attribute_id gus an ùrachadh a dhèanamh. Ma tha thu airson feart ùrachadh ann an colbh JSONB stèidhichte air ainm an nì, thèid a dhèanamh ann an aon loidhne shìmplidh.

A-nis taghamaid an eintiteas a dh’ ùraich sinn stèidhichte air a dhath ùr:

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

Tha mi a’ smaoineachadh gun urrainn dhuinn aontachadh gu bheil an dàrna fear nas giorra (às aonais an join!) agus mar sin nas so-leughaidh. Bidh JSONB a’ buannachadh an seo! Bidh sinn a’ cleachdadh an obraiche JSON ->> gus an dath fhaighinn air ais mar luach teacsa bhon nì JSONB. Tha dàrna dòigh ann cuideachd airson an aon toradh fhaighinn anns a’ mhodail JSONB a’ cleachdadh an obraiche @>:

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

Tha seo beagan nas iom-fhillte: bidh sinn a’ sgrùdadh a bheil an nì JSON anns a’ cholbh thogalaichean a’ gabhail a-steach an nì air taobh deas an obraiche @>. Nas lugha so-leughaidh, nas èifeachdaiche (faic gu h-ìosal).

Sìmpleamaid cleachdadh JSONB eadhon nas fhaide nuair a dh’ fheumas tu iomadh feart a thaghadh aig an aon àm. Seo far a bheil dòigh-obrach JSONB a’ deàrrsadh: bidh sinn dìreach a’ taghadh fheartan mar cholbhan a bharrachd anns an t-seata thoraidhean againn, gun fheum air joints:

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

Le EAV, feumaidh tu dà cheangal airson gach feart a tha thu airson ceist a chur air. Nam bheachd-sa, tha na ceistean gu h-àrd a’ sealltainn sìmpleachadh mòr ann an dealbhadh stòr-dàta. Chì thu cuideachd barrachd eisimpleirean air mar a sgrìobhas tu ceistean JSONB ann an seo post.
A-nis tha an t-àm ann bruidhinn mu choileanadh.

Coileanadh

Gus coimeas a dhèanamh eadar an coileanadh a chleachd mi MÌNICH ANALYS ann an ceistean gus an ùine cur gu bàs obrachadh a-mach. Chaidh gach ceist a chur gu bàs co-dhiù trì tursan, leis gu bheil am planaiche ceist a’ toirt nas fhaide a’ chiad uair. An toiseach, ruith mi na ceistean gun chlàr-amais sam bith. Gu follaiseach, bha seo na bhuannachd do JSONB, leis nach b’ urrainn dha na ceanglaichean a bha a dhìth airson EAV clàran-amais a chleachdadh (cha robh raointean iuchair chèin air an clàr-amais). Às deidh sin, chruthaich mi clàr-amais air an dà cholbh iuchair chèin de chlàr luach EAV, a bharrachd air clàr-amais Gin airson colbh JSONB.

Sheall ùrachaidhean dàta na toraidhean ùine a leanas (ann am ms). Thoir an aire gu bheil an sgèile logaritmach:

Tha sinn a’ faicinn gu bheil JSONB gu math (>50000x) nas luaithe na EAV às aonais clàran-amais, air an adhbhar a chaidh ainmeachadh gu h-àrd. Nuair a bhios sinn a’ clàradh colbhan prìomh iuchair, cha mhòr nach eil an diofar a’ dol à bith, ach tha JSONB fhathast 1,3x nas luaithe na EAV. Thoir an aire nach eil buaidh sam bith aig a’ chlàr-amais air colbh JSONB an seo, leis nach eil sinn a’ cleachdadh colbh nan togalaichean anns na slatan-tomhais measaidh.

Airson taghadh dàta stèidhichte air luach seilbh, gheibh sinn na toraidhean a leanas (sgèile àbhaisteach):

Chì thu gu bheil JSONB a-rithist nas luaithe na EAV às aonais chlàr-amais, ach nuair a thèid EAV a chlàr-amais, tha e fhathast nas luaithe na JSONB. Ach an uairsin chunnaic mi gu robh na h-amannan airson ceistean JSONB mar an ceudna, agus thug sin orm a bhith a’ faicinn nach tèid clàran-amais GIN a bhrosnachadh. A rèir choltais, nuair a chleachdas tu clàr-amais GIN air colbh le feartan làn-shluaigh, chan eil e a’ toirt buaidh ach nuair a chleachdas tu an gnìomhaiche in-ghabhail @>. Chleachd mi seo ann an deuchainn ùr, agus bha buaidh mhòr aige air an ùine: dìreach 0,153 ms! Tha sin 15000 uair nas luaithe na EAV agus 25000 uair nas luaithe na an gnìomhaiche ->>.

Tha mi a’ smaoineachadh gun robh e gu math luath!

Meud chlàran stòr-dàta

Dèanamaid coimeas eadar meudan nan clàran airson an dà dhòigh-obrach. Ann am psql, is urrainn dhuinn meud nan clàran agus nan clàran-amais uile a thaisbeanadh leis an àithne dti+

Leis an dòigh-obrach EAV, tha meudan nan clàran timcheall air 3068 MB, agus tha na clàran-amais suas ri 3427 MB, airson 6,43 GB san iomlan. A’ cleachdadh an dòigh-obrach JSONB, bidh an clàr a’ cleachdadh 1817 MB agus a’ dèanamh chlàr-amais de 318 MB, airson 2,08 GB san iomlan. Sin an treas cuid den mheud! Chuir seo iongnadh orm beagan, leis gu bheil sinn a’ stòradh ainmean thogalaichean anns gach nì JSONB.

Ach tha na h-àireamhan a’ bruidhinn air an son fhèin: ann an EAV, bidh sinn a’ stòradh dà iuchair cèin slàn-àireamh gach luach feart, agus mar thoradh air sin bidh 8 bytes de dhàta a bharrachd ann. A bharrachd air an sin, ann an EAV, thèid a h-uile luach feart a stòradh mar theacsa, agus cleachdaidh JSONB luachan àireamhach is loidsigeach gu h-inntinneach far a bheil sin comasach, agus mar thoradh air sin bidh lorg-coise nas lugha ann.

Builean

Gu h-iomlan, tha mi a’ smaoineachadh gum faod stòradh fheartan eintiteas ann an cruth JSONB dealbhadh is cumail suas an stòr-dàta agad a dhèanamh gu math nas sìmplidhe. Ma nì thu tòrr cheistean, bidh e dha-rìribh nas èifeachdaiche a h-uile càil a stòradh san aon chlàr ris an eintiteas. Tha e na bhuannachd mar-thà gu bheil e a’ sìmpleachadh eadar-obrachadh dàta, ach tha an stòr-dàta a thig às trì tursan nas lugha ann am meud cuideachd.

Cuideachd, stèidhichte air toraidhean a’ chomharra-tomhais, is urrainn dhuinn a cho-dhùnadh gu bheil am peanas coileanaidh glè bheag. Ann an cuid de chùisean, bidh JSONB eadhon a’ coileanadh nas luaithe na EAV, ga dhèanamh eadhon nas fheàrr. Ach, gu cinnteach chan eil an comharra-tomhais seo a’ còmhdach a h-uile taobh (me, eintiteasan le àireamh mhòr de fheartan, àrdachadh mòr ann an àireamh nan feartan ann an dàta a th’ ann mar-thà, msaa.), mar sin ma tha molaidhean sam bith agad airson leasachadh, na bi leisg an leigeil seachad anns na beachdan!

Source: www.habr.com