Fanoloana ny EAV amin'ny JSONB ao amin'ny PostgreSQL

TL; DR: JSONB dia afaka manatsotra be dia be ny fivoaran'ny schema database nefa tsy manao sorona ny fahombiazan'ny fangatahana.

fampidirana

Andeha isika hanome ohatra mahazatra momba ny iray amin'ireo tranga fampiasana tranainy indrindra eo amin'ny tontolon'ny angon-drakitra mifandraika (database): manana enti-manana isika, ary mila mitahiry fananana (attribute) sasany amin'ity fikambanana ity. Saingy tsy ny tranga rehetra dia mety manana fananana mitovy, ary mety hisy fananana bebe kokoa amin'ny ho avy.

Ny fomba tsotra indrindra hamahana ity olana ity dia ny mamorona tsanganana ao amin'ny latabatra database ho an'ny sandan'ny fananana tsirairay, ary fenoy fotsiny ireo izay ilaina ho an'ny orinasa iray manokana. Mahafinaritra! Voavaha ny olana... mandra-pahatongan'ny latabatrao misy rakitsoratra an-tapitrisany ary mila manampy rakitra vaovao ianao.

Diniho ny lamina EAV (Entity-Attribute-Value), mitranga matetika izany. Ny latabatra iray dia misy enti-manana (rakitra), latabatra iray hafa misy anarana fananana (attributes), ary tabilao fahatelo dia mampifandray ireo enti-manana amin'ny toetrany ary misy ny sandan'ireo toetra ireo ho an'ny enti-manana ankehitriny. Izany dia manome anao fahafahana hanana karazana fananana isan-karazany ho an'ny zavatra samihafa, ary koa manampy fananana amin'ny lalitra tsy manova ny rafitry ny tahiry.

Na izany aza, tsy hanoratra ity lahatsoratra ity aho raha tsy misy ny tsy fahampian'ny fomba fiasa EVA. Noho izany, ohatra, mba hahazoana enti-manana iray na maromaro izay manana toetra 1 tsirairay avy, dia ilaina ny mitambatra 2 ao amin'ny fanontaniana: ny voalohany dia miaraka amin'ny latabatra toetra, ny faharoa dia miaraka amin'ny latabatra sanda. Raha manana toetra 2 ny enti-manana, dia ilaina ny mitambatra 4! Fanampin'izany, ny toetra rehetra dia voatahiry matetika ho tady, izay miteraka karazana fanariana ho an'ny valiny sy ny fehezanteny WHERE. Raha manoratra fanontaniana be dia be ianao, dia tena fandaniam-poana izany amin'ny resaka fampiasana loharano.

Na dia eo aza ireo lesoka miharihary ireo, efa ela ny EAV no nampiasaina hamahana ireo karazana olana ireo. Tsy azo ihodivirana ireo, ary tsy nisy safidy tsara kokoa.
Saingy nisy "teknolojia" vaovao niseho tao amin'ny PostgreSQL ...

Manomboka amin'ny PostgreSQL 9.4, ny karazana data JSONB dia nampiana mba hitahiry ny angona binary JSON. Na dia mitaky toerana sy fotoana kely kokoa noho ny JSON soratra tsotra aza ny fitahirizana JSON amin'ity endrika ity, dia haingana kokoa ny fanatanterahana azy. JSONB koa dia manohana ny fanondroana, izay mahatonga ny fangatahana haingana kokoa.

Ny karazana data JSONB dia ahafahantsika manolo ny lamina EAV manahirana amin'ny alàlan'ny fampidirana tsanganana JSONB iray monja amin'ny latabatra enti-manana, manatsotra tanteraka ny famolavolana angona. Maro anefa no milaza fa tokony hiarahana amin’ny fihenan’ny vokatra izany... Izay no nanoratako ity lahatsoratra ity.

Fametrahana angon-drakitra fitsapana

Ho an'ity fampitahana ity dia namorona angon-drakitra momba ny fametrahana PostgreSQL 9.5 vaovao amin'ny fananganana $80 aho. DigitalOcean Ubuntu 14.04. Rehefa avy nametraka paramètre sasany tao amin'ny postgresql.conf aho dia nihazakazaka izany script mampiasa psql. Ireto tabilao manaraka ireto dia noforonina hanehoana ny angona amin'ny endrika EAV:

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

Ity ambany ity ny latabatra iray izay hitehirizana ny angona mitovy, fa miaraka amin'ny toetra ao amin'ny tsanganana karazana JSONB - fananana.

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

Toa tsotra kokoa, sa tsy izany? Avy eo dia nampidirina tao amin'ny latabatra enti-manana (fikambanana & entity_jsonb) firaketana 10 tapitrisa, ary araka izany, feno angon-drakitra mitovy ny latabatra amin'ny fampiasana ny lamina EAV sy ny fomba fiasa miaraka amin'ny tsanganana JSONB - entity_jsonb.properties. Noho izany, nahazo karazana angon-drakitra maro samihafa izahay tamin'ny fitambaran'ny fananana rehetra. angona ohatra:

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

Noho izany dia manana angon-drakitra mitovy izahay amin'ny safidy roa. Andao hanomboka hampitaha ny fampiharana any am-piasana!

Ataovy tsotra ny famolavolanao

Efa voalaza teo aloha fa nohamafisina be ny famolavolana angon-drakitra: latabatra iray, amin'ny fampiasana tsanganana JSONB ho an'ny fananana, fa tsy mampiasa latabatra telo ho an'ny EAV. Ahoana anefa no isehoan’izany amin’ny fangatahana? Toy izao ny fanavaozana ny fananana enti-manana iray:

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

Araka ny hitanao dia tsy mora kokoa ny fangatahana farany. Mba hanavaozana ny sandan'ny fananana iray amin'ny zavatra JSONB dia tsy maintsy mampiasa ny fiasa isika jsonb_set(), ary tokony handalo ny sandantsika vaovao ho zavatra JSONB. Na izany aza, tsy mila mahafantatra famantarana mialoha isika. Raha mijery ny ohatra EAV isika dia mila mahafantatra ny entity_id sy ny entity_attribute_id mba hanatanterahana ny fanavaozana. Raha te hanavao fananana ao amin'ny tsanganana JSONB mifototra amin'ny anaran'ny zavatra ianao, dia vita amin'ny andalana tsotra izany rehetra izany.

Andeha isika hisafidy ny singa vao nohavaozina mifototra amin'ny lokony vaovao:

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

Heveriko fa afaka manaiky isika fa ny faharoa dia fohy kokoa (tsy mikambana!), ary noho izany dia mora vakiana kokoa. JSONB no mandresy eto! Mampiasa ny JSON ->> operator izahay mba hahazoana ny loko ho sanda lahatsoratra avy amin'ny zavatra JSONB. Misy ihany koa fomba faharoa hahazoana vokatra mitovy amin'ny modely JSONB amin'ny fampiasana ny @> operator:

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

Sarotra kokoa ity: jereo raha toa ka misy zavatra eo ankavanan'ny operator @> ny zavatra JSON ao amin'ny tsanganana fananany. Tsy mora vakina, mamokatra kokoa (jereo eto ambany).

Andao hanamora kokoa ny fampiasana JSONB rehefa mila mifantina fananana maromaro indray mandeha ianao. Eto no tena idiran'ny fomba fiasa JSONB: mifidy fananana fotsiny isika ho tsanganana fanampiny ao amin'ny valin'ny valiny tsy mila miditra:

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

Miaraka amin'ny EAV ianao dia mila mitambatra 2 isaky ny fananana tianao hanontaniana. Raha ny hevitro, ireo fanontaniana etsy ambony ireo dia mampiseho fanatsorana lehibe amin'ny famolavolana database. Jereo ny ohatra misimisy kokoa amin'ny fanoratana fanontaniana JSONB, ao amin'ny izany lahatsoratra.
Fotoana izao hiresahana momba ny fampisehoana.

vokatra

Mba hampitahana ny fampisehoana nampiasaiko HAzavao ny famakafakana amin'ny fanontaniana, kajy ny fotoana famonoana. Ny fangatahana tsirairay dia in-telo notanterahina fara-fahakeliny satria maharitra kokoa ny drafitry ny fangatahana raha vao voalohany. Voalohany dia nihazakazaka ny fanontaniana aho tsy nisy index. Mazava ho azy fa tombony ho an'ny JSONB izany, satria ny fidirana ilaina amin'ny EAV dia tsy afaka mampiasa indexes (tsy misy index ny saha fanalahidy vahiny). Taorian'izany dia namorona fanondroana amin'ny tsanganana fanalahidy vahiny 2 amin'ny tabilao sanda EAV aho, ary koa fanondroana. tonta ho an'ny tsanganana JSONB.

Ny fanavaozam-baovao dia mampiseho ireto vokatra manaraka ireto amin'ny lafin'ny fotoana (amin'ny ms). Mariho fa logarithmic ny maridrefy:

Hitanay fa JSONB dia haingana kokoa (> 50000-x) noho ny EAV raha tsy mampiasa index ianao, noho ny antony voalaza etsy ambony. Rehefa manisy tsanganana amin'ny fanalahidy fototra isika dia saika hanjavona ny fahasamihafana, fa ny JSONB dia mbola avo 1,3 heny noho ny EAV. Mariho fa ny index amin'ny tsanganana JSONB dia tsy misy fiantraikany eto satria tsy mampiasa ny tsanganana fananana ao amin'ny fepetra fanombanana izahay.

Ho an'ny fifantenana angona mifototra amin'ny sandan'ny fananana dia mahazo ireto vokatra manaraka ireto isika (marika mahazatra):

Azonao atao ny manamarika fa ny JSONB indray dia miasa haingana kokoa noho ny EAV tsy misy index, fa rehefa EAV miaraka amin'ny index dia mbola miasa haingana kokoa noho ny JSONB. Saingy hitako fa mitovy ny fotoana ho an'ny fanontaniana JSONB, nanosika ahy izany fa tsy mandeha ny index GIN. Raha toa ianao ka mampiasa tondro GIN amin'ny tsanganana misy fananana be mponina, dia tsy misy vokany izany rehefa mampiasa ny operator fampidirana @>. Nampiasaiko tamina fitsapana vaovao ity ary nisy fiantraikany lehibe tamin'ny fotoana: 0,153ms ihany! Izany dia avo 15000 heny noho ny EAV ary 25000 heny mahery noho ny ->> operator.

Heveriko fa haingana be izany!

Haben'ny latabatra database

Andao hampitaha ny haben'ny latabatra ho an'ny fomba roa. Ao amin'ny psql dia azontsika atao ny mampiseho ny haben'ny latabatra sy ny index rehetra mampiasa ny baiko dti+

Ho an'ny fomba fiasa EAV, manodidina ny 3068 MB ny haben'ny latabatra ary ny fanondroana hatramin'ny 3427 MB amin'ny fitambaran'ny 6,43 GB. Ny fomba JSONB dia mampiasa 1817 MB ho an'ny latabatra ary 318 MB ho an'ny fanondroana, izany hoe 2,08 GB. Mihena in-3 izany! Ity zava-misy ity dia nahagaga ahy kely satria mitahiry anarana fananana amin'ny zavatra JSONB rehetra izahay.

Na izany aza, ny isa dia miteny ho an'ny tenany ihany: ao amin'ny EAV dia mitahiry fanalahidy vahiny 2 integer isaky ny sandan'ny toetra, ka miteraka angona fanampiny 8 bytes. Fanampin'izany, ny EAV dia mitahiry ny soatoavin'ny fananana rehetra ho lahatsoratra, raha toa kosa ny JSONB dia hampiasa ny soatoavina nomerika sy boolean anatiny raha azo atao, ka miteraka dian-tongotra kely kokoa.

vokatra

Amin'ny ankapobeny, heveriko fa ny fitehirizana ny fananana enti-manana amin'ny endrika JSONB dia afaka manamora kokoa ny famolavolana sy fitazonana ny angon-drakitrao. Raha manao fanontaniana be dia be ianao, dia ny fitazonana ny zava-drehetra ao amin'ny latabatra mitovy amin'ny orinasa dia hiasa tsara kokoa. Ary ny zava-misy fa manamora ny fifandraisana eo amin'ny angon-drakitra dia efa plus, fa ny angon-drakitra vokarina dia in-3 kely kokoa ny volume.

Ary koa, mifototra amin'ny fitsapana natao, dia azontsika atao ny manatsoaka hevitra fa ny fahaverezan'ny asa dia tsy dia misy dikany loatra. Amin'ny tranga sasany, JSONB dia haingana kokoa noho ny EAV, ka mahatonga azy ho tsara kokoa. Na izany aza, mazava ho azy, io benchmark io dia tsy mandrakotra ny lafiny rehetra (ohatra ny enti-manana manana fananana marobe, fitomboana lehibe amin'ny isan'ny fananana data efa misy, ...), ka raha manana soso-kevitra momba ny fanatsarana azy ianao , aza misalasala miala amin'ny fanehoan-kevitra!

Source: www.habr.com