{"id":52584,"date":"2019-11-12T00:00:00","date_gmt":"2019-11-11T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/zamena-eav-na-jsonb-v-postgresql"},"modified":"2020-02-18T14:00:21","modified_gmt":"2020-02-18T11:00:21","slug":"zamena-eav-na-jsonb-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","title":{"rendered":"Z\u00ebvend\u00ebsimi i EAV me JSONB n\u00eb PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

TL; DR: JSONB mund t\u00eb thjeshtoj\u00eb ndjesh\u00ebm zhvillimin e skem\u00ebs s\u00eb DB pa e sakrifikuar performanc\u00ebn n\u00eb pyetje.<\/p><\/blockquote>\n

<\/p>\n

Hyrje<\/h3>\n

\nLe t\u00eb kontribuojm\u00eb nj\u00eb shembull klasik, ndoshta nj\u00eb nga p\u00ebrdorimet m\u00eb t\u00eb vjetra n\u00eb bot\u00ebn e DB-ve relacional (database): kemi nj\u00eb entitet dhe duhet t\u00eb ruajm\u00eb disa veti (atribute) t\u00eb k\u00ebtij entiteti. Por jo t\u00eb gjitha instancat mund t\u00eb ken\u00eb t\u00eb nj\u00ebjtin set vetish, dhe gjithashtu n\u00eb t\u00eb ardhmen, mund t\u00eb ket\u00eb shtim t\u00eb vetive t\u00eb tjera.<\/p>\n

M\u00ebnyra m\u00eb e thjesht\u00eb p\u00ebr t\u00eb zgjidhur k\u00ebt\u00eb problem \u00ebsht\u00eb t\u00eb krijoni nj\u00eb kolon\u00eb n\u00eb tabel\u00ebn e DB-s\u00eb p\u00ebr \u00e7do vler\u00eb t\u00eb vetis\u00eb, dhe thjesht t\u00eb plot\u00ebsoni ato q\u00eb nevojiten p\u00ebr nj\u00eb instanc\u00eb t\u00eb caktuar t\u00eb entitetit. Super! Problemi \u00ebsht\u00eb zgjidhur\u2026 deri n\u00eb momentin kur tabela juaj p\u00ebrmban miliona regjistrime dhe ju nevojitet t\u00eb shtoni nj\u00eb regjistrim t\u00eb ri.<\/p>\n

Le t\u00eb shqyrtojm\u00eb modelin EAV (Entity-Attribute-Value<\/a><\/noindex>), ai takon shpesh. Nj\u00eb tavolin\u00eb p\u00ebrmban entitete (sh\u00ebnime), nj\u00eb tavolin\u00eb tjet\u00ebr p\u00ebrmban emrat e vetive (atributeve), dhe tavolina e tret\u00eb lidh entitetet me atributet e tyre dhe mban vler\u00ebn e k\u00ebtyre atributeve p\u00ebr entitetin aktual. Kjo ju ofron mund\u00ebsin\u00eb p\u00ebr t\u00eb pasur grupe t\u00eb ndryshme vetish p\u00ebr objekte t\u00eb ndryshme, si dhe p\u00ebr t\u00eb shtuar vetit\u00eb \u201cn\u00eb flak\u00eb\u201d, pa ndryshuar strukturat e DB-s\u00eb.
\n<\/a><\/noindex>
\n\u0422\u0435\u043c \u043d\u0435 \u043c\u0435\u043d\u0435\u0435, \u044f \u0431\u044b \u043d\u0435 \u043f\u0438\u0441\u0430\u043b \u044d\u0442\u0443 \u0437\u0430\u043c\u0435\u0442\u043a\u0443, \u0435\u0441\u043b\u0438 \u0431\u044b \u043d\u0435 \u0431\u044b\u043b\u043e \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u0442\u043a\u043e\u0432 \u0432 \u043f\u043e\u0434\u0445\u043e\u0434\u0435 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c EVA. \u0422\u0430\u043a, \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0434\u043b\u044f \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u044f \u043e\u0434\u043d\u043e\u0439 \u0438\u043b\u0438 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u0435\u0439, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0438\u043c\u0435\u044e\u0442 \u043f\u043e 1 \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u0443 \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f 2 join’\u0430 (\u043e\u0431\u044a\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f) \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0435: \u043f\u0435\u0440\u0432\u044b\u0439 \u2013 \u043e\u0431\u044a\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0435 \u0441 \u0442\u0430\u0431\u043b\u0438\u0446\u0435\u0439 \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u043e\u0432, \u0432\u0442\u043e\u0440\u043e\u0439 \u2013 \u043e\u0431\u044a\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0435 \u0441 \u0442\u0430\u0431\u043b\u0438\u0446\u0435\u0439 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439. \u0415\u0441\u043b\u0438 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u044c \u0438\u043c\u0435\u044e\u0442 2 \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b, \u0442\u043e \u043d\u0443\u0436\u043d\u043e \u0443\u0436\u0435 4 join’\u0430! \u041a\u0440\u043e\u043c\u0435 \u0442\u043e\u0433\u043e, \u0432\u0441\u0435 \u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b \u043e\u0431\u044b\u0447\u043d\u043e \u0445\u0440\u0430\u043d\u044f\u0442\u0441\u044f \u0432 \u0432\u0438\u0434\u0435 \u0441\u0442\u0440\u043e\u043a, \u0447\u0442\u043e \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442 \u043a \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u0438\u044e \u0442\u0438\u043f\u043e\u0432, \u043a\u0430\u043a \u0434\u043b\u044f \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0430, \u0442\u0430\u043a \u0438 \u0434\u043b\u044f \u0443\u0441\u043b\u043e\u0432\u0438\u044f WHERE. \u0415\u0441\u043b\u0438 \u0432\u044b \u043f\u0438\u0448\u0435\u0442\u0435 \u043c\u043d\u043e\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u0442\u043e \u044d\u0442\u043e \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u0440\u0430\u0441\u0442\u043e\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e, \u0441 \u0442\u043e\u0447\u043a\u0438 \u0437\u0440\u0435\u043d\u0438\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432.<\/p>\n

Megjithat\u00eb, pavar\u00ebsisht k\u00ebtyre mang\u00ebsive t\u00eb dukshme, EAV ka qen\u00eb prej koh\u00ebsh n\u00eb p\u00ebrdorim p\u00ebr zgjidhjen e k\u00ebtij lloji problemi. K\u00ebto ishin mang\u00ebsi t\u00eb pashmangshme, dhe nuk kishte nj\u00eb alternativ\u00eb m\u00eb t\u00eb mir\u00eb.
\nPor pastaj n\u00eb PostgreSQL u shfaq nj\u00eb 'teknologji' e re\u2026<\/p>\n

Q\u00eb nga PostgreSQL 9.4, u shtua nj\u00eb tip t\u00eb dh\u00ebnash JSONB p\u00ebr t\u00eb ruajtur t\u00eb dh\u00ebnat nj\u00ebshe n\u00eb formatin JSON. Nd\u00ebrsa ruajtja e JSON n\u00eb k\u00ebt\u00eb format zakonisht k\u00ebrkon pak m\u00eb shum\u00eb hap\u00ebsir\u00eb dhe koh\u00eb se sa JSON-i i thjesht\u00eb tekstor, operacionet me t\u00eb ndodhin shum\u00eb m\u00eb shpejt. Po ashtu, JSONB mb\u00ebshtet indeksimin, gj\u00eb q\u00eb i b\u00ebn k\u00ebrkesat ndaj tij edhe m\u00eb t\u00eb shpejta.<\/p>\n

Tipi i t\u00eb dh\u00ebnave JSONB na lejon t\u00eb z\u00ebvend\u00ebsojm\u00eb modelin e r\u00ebnd\u00eb EAV duke shtuar vet\u00ebm nj\u00eb kolon\u00eb JSONB n\u00eb tabel\u00ebn ton\u00eb t\u00eb entiteteve, \u00e7ka e thjeshton ndjesh\u00ebm projektimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Por shum\u00eb njer\u00ebz pretendojn\u00eb se kjo duhet t\u00eb shoq\u00ebrohet me nj\u00eb r\u00ebnie t\u00eb performanc\u00ebs\u2026 P\u00ebr k\u00ebt\u00eb arsye shkruhet ky artikull.<\/p>\n

Konfigurimi i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave p\u00ebr testim<\/h3>\n

\nP\u00ebr k\u00ebt\u00eb krahasim, kam krijuar nj\u00eb baz\u00eb t\u00eb dh\u00ebnash n\u00eb nj\u00eb instalim t\u00eb ri t\u00eb PostgreSQL 9.5 n\u00eb nj\u00eb konfigurim prej 80 dollar\u00ebsh. DigitalOcean<\/a><\/noindex> Ubuntu 14.04. Pasi kam vendosur disa parametra n\u00eb postgresql.conf, kam nisur k\u00ebt\u00eb<\/a><\/noindex> skripti me psql. P\u00ebr t\u00eb paraqitur t\u00eb dh\u00ebnat n\u00eb formatin EAV, u krijuan tabelat e m\u00ebposhtme:<\/p>\n

KRIJO TABEL\u00cb enti (\n  id           SERIAL KRYESORE, \n  emri         TEKST, \n  p\u00ebrshkrimi   TEKST\n);\nKRIJO TABEL\u00cb entity_attribute (\n  id          SERIAL KRYESORE, \n  emri        TEKST\n);\nKRIJO TABEL\u00cb entity_attribute_value (\n  id                  SERIAL KRYESORE, \n  entity_id           INT    REFERENCAT entity(id), \n  entity_attribute_id INT    REFERENCAT entity_attribute(id), \n  vlera               TEKST\n);\n<\/code><\/pre>\n
\nM\u00eb posht\u00eb \u00ebsht\u00eb tabela ku do t\u00eb ruhen t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna, por me atribute n\u00eb kolon\u00ebn e tipit JSONB \u2013 propertietet<\/i>.<\/p>\n
KRIJO TABEL\u00cb entity_jsonb (\n  id          SERIAL KRYESORE, \n  emri        TEKST, \n  p\u00ebrshkrimi TEKST,\n  propertietet  JSONB\n);\n<\/code><\/pre>\n
\nDuket shum\u00eb m\u00eb e thjesht\u00eb, apo jo? M\u00eb pas u shtuan n\u00eb tabelat e entiteteve (enti<\/i> & entity_jsonb<\/i>) 10 milijon regjistrime, dhe si p\u00ebrfundim, u plot\u00ebsuan t\u00eb dh\u00ebnat e nj\u00ebjta tabelave q\u00eb p\u00ebrdorin modelin EAV dhe qasjen me kolon\u00ebn JSONB \u2013 entity_jsonb.propertietet<\/i>. K\u00ebshtu, mor\u00ebm disa lloje t\u00eb ndryshme t\u00eb t\u00eb dh\u00ebnave n\u00eb t\u00ebr\u00eb grupin e pronave. Shembuj t\u00eb dh\u00ebnash:<\/p>\n
{\n  id:          1\n  emri:        \"Entity1\"\n  p\u00ebrshkrimi: \"Entitet testi nr. 1\"\n  propertietet:  {\n    ngjyra:        \"e kuqe\"\n    gjat\u00ebsia:       120\n    gjer\u00ebsi:        3.1882420\n    ka\u00e7kavall: true\n    vendi:      \"Belgjika\"\n  } \n}<\/code><\/pre>\n
\nPra, tani kemi t\u00eb dh\u00ebna t\u00eb nj\u00ebjta p\u00ebr dy variante. Le t\u00eb fillojm\u00eb t\u00eb krahasojm\u00eb realizimet n\u00eb pun\u00eb!<\/p>\n
Thjeshtimi i dizajnit<\/h3>\n
\nKemi th\u00ebn\u00eb m\u00eb par\u00eb se dizajni i DB \u00ebsht\u00eb simplifikuar ndjesh\u00ebm: nj\u00eb tabel\u00eb, p\u00ebrmes p\u00ebrdorimit t\u00eb kolon\u00ebs JSONB p\u00ebr vetit\u00eb, n\u00eb vend t\u00eb p\u00ebrdorimit t\u00eb tre tabelave p\u00ebr EAV. Por si reflektohet kjo n\u00eb pyetje?<\/p>\n
-- EAV\nUPDATE entity_attribute_value \nSET value = 'blue' \nWHERE entity_attribute_id = 1 \n  AND entity_id = 120;\n\n-- JSONB\nUPDATE entity_jsonb \nSET properties = jsonb_set(properties, '{\"color\"}', '\"blue\"') \nWHERE id = 120;\n<\/code><\/pre>\n
\nSi\u00e7 e shohim, k\u00ebrkesa e fundit nuk duket m\u00eb e thjesht\u00eb. Q\u00eb t\u00eb p\u00ebrdit\u00ebsojm\u00eb vler\u00ebn e nj\u00eb vetie n\u00eb objektin JSONB, ne duhet t\u00eb p\u00ebrdorim funksionin jsonb_set()<\/a><\/noindex>, dhe duhet t\u00eb kalojm\u00eb vler\u00ebn ton\u00eb t\u00eb re si nj\u00eb objekt JSONB. Megjithat\u00eb, nuk na nevojitet t\u00eb dim\u00eb ndonj\u00eb identifikues paraprakisht. Duke par\u00eb shembullin me EAV, na nevojitet t\u00eb dim\u00eb si entity_id, ashtu edhe entity_attribute_id p\u00ebr t\u00eb kryer p\u00ebrdit\u00ebsimin. N\u00ebse d\u00ebshiron t\u00eb p\u00ebrdit\u00ebsosh nj\u00eb vetie n\u00eb kolon\u00ebn JSONB bazuar n\u00eb emrin e objektit, \u2013 kjo b\u00ebhet me nj\u00eb rresht t\u00eb thjesht\u00eb.<\/p>\n
Tani le t\u00eb zgjedhim at\u00eb entitet q\u00eb sapo e p\u00ebrdit\u00ebsuam, sipas kushtit t\u00eb ngjyr\u00ebs s\u00eb tij t\u00eb re:<\/p>\n
-- EAV\nSELECT e.name \nFROM entity e \n  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id\n  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id\nWHERE ea.name = 'color' AND eav.value = 'blue';\n\n-- JSONB\nSELECT name \nFROM entity_jsonb \nWHERE properties ->> 'color' = 'blue';\n<\/code><\/pre>\n
\nMendoj se se mund t\u00eb pajtohemi q\u00eb e dyta \u00ebsht\u00eb m\u00eb e shkurt\u00ebr (pa join!), dhe p\u00ebr rrjedhoj\u00eb m\u00eb e lexueshme. K\u00ebtu fiton JSONB! Ne e p\u00ebrdorim operatorin JSON ->>, p\u00ebr t\u00eb marr\u00eb ngjyr\u00ebn si vler\u00eb tekstuale nga objekti JSONB. Ekziston gjithashtu nj\u00eb m\u00ebnyr\u00eb tjet\u00ebr p\u00ebr t\u00eb arritur t\u00eb nj\u00ebjtin rezultat n\u00eb modelin JSONB duke p\u00ebrdorur operatorin @>:<\/p>\n
-- JSONB \nSELECT name \nFROM entity_jsonb \nWHERE properties @> '{\"color\": \"blue\"}';\n<\/code><\/pre>\n
\nKjo \u00ebsht\u00eb pak m\u00eb e komplikuar: ne kontrollojm\u00eb n\u00ebse objekti JSON n\u00eb kolon\u00ebn e pronave p\u00ebrmban objektin q\u00eb ndodhet n\u00eb t\u00eb djatht\u00eb t\u00eb operatorit @>. M\u00eb pak e lexueshme, m\u00eb e prodhueshme (shih m\u00eb posht\u00eb). <\/p>\n
Le t\u00eb thjeshtojm\u00eb p\u00ebrdorimin e JSONB edhe m\u00eb shum\u00eb, kur ju nevojitet t\u00eb zgjidhni disa prona n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb. K\u00ebtu \u00ebsht\u00eb ku i p\u00ebrshtatet v\u00ebrtet qasja JSONB: ne thjesht zgjedhim pronat si kolona shtes\u00eb n\u00eb grupin ton\u00eb t\u00eb rezultateve pa nevoj\u00ebn p\u00ebr bashkime:<\/p>\n
-- JSONB \nSELECT name\n  , properties ->> 'color'\n  , properties ->> 'country'\nFROM entity_jsonb \nWHERE id = 120;\n<\/code><\/pre>\n
\nMe EAV nevojiten 2 bashkime p\u00ebr \u00e7do pron\u00eb q\u00eb d\u00ebshironi t\u00eb k\u00ebrkoni. Sipas mendimit tim, k\u00ebrkesat e lartp\u00ebrmendura tregojn\u00eb nj\u00eb thjeshtim t\u00eb madh n\u00eb dizajnin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Shihni m\u00eb shum\u00eb shembuj se si t\u00eb shkruani k\u00ebrkesa p\u00ebr JSONB, gjithashtu n\u00eb k\u00ebt\u00eb<\/a><\/noindex> post.
\nTani \u00ebsht\u00eb koha p\u00ebr t\u00eb folur rreth performanc\u00ebs.<\/p>\n
Performanca<\/h3>\n
\nP\u00ebr t\u00eb krahasuar performanc\u00ebn, p\u00ebrdora EXPLAIN ANALYZE<\/a><\/noindex> n\u00eb k\u00ebrkesat, p\u00ebr t\u00eb llogaritur koh\u00ebn e ekzekutimit. \u00c7do k\u00ebrkes\u00eb u ekzekutua t\u00eb pakt\u00ebn tre her\u00eb, sepse hera e par\u00eb planifikuesit e k\u00ebrkesave k\u00ebrkon m\u00eb shum\u00eb koh\u00eb. E fillova duke ekzekutuar k\u00ebrkesat pa ndonj\u00eb indeks. Pa dyshim, kjo i sh\u00ebrbente JSONB, pasi bashkimet e nevojshme p\u00ebr EAV nuk mund t\u00eb p\u00ebrdorin indekset (fushat e \u00e7el\u00ebsit t\u00eb jasht\u00ebm nuk ishin indeksuar). Pas k\u00ebsaj, krijova nj\u00eb indeks p\u00ebr 2 kolonat e \u00e7el\u00ebsave t\u00eb jasht\u00ebm n\u00eb tabel\u00ebn EAV si dhe nj\u00eb indeks GIN<\/a><\/noindex> p\u00ebr kolon\u00ebn JSONB.<\/p>\n
Rritjet e t\u00eb dh\u00ebnave treguan rezultatet e m\u00ebposhtme n\u00eb koh\u00eb (n\u00eb ms). Kushtojini v\u00ebmendje se shkalla \u00ebsht\u00eb logarithmike:<\/p>\n
\"Z\u00ebvend\u00ebsimi
\n
\nShohim se se JSONB \u00ebsht\u00eb shum\u00eb m\u00eb i shpejt\u00eb (> 50000-x) se EAV, n\u00ebse nuk p\u00ebrdoren indekset, p\u00ebr arsye t\u00eb p\u00ebrmendura m\u00eb sip\u00ebr. Kur indeksojm\u00eb kolonat me \u00e7el\u00ebsa primar\u00eb, diferenca pothuajse zhduket, por JSONB \u00ebsht\u00eb akoma 1,3 her\u00eb m\u00eb i shpejt\u00eb se EAV. Vini re se indeksi n\u00eb kolon\u00ebn JSONB k\u00ebtu nuk ka asnj\u00eb ndikim, pasi nuk e p\u00ebrdorim kolon\u00ebn e pronave n\u00eb kriteret e vler\u00ebsimit. <\/p>\n
P\u00ebr t\u00eb zgjedhur t\u00eb dh\u00ebnat mbi baz\u00ebn e nj\u00eb vlere t\u00eb pron\u00ebs, kemi rezultatet e m\u00ebposhtme (shkall\u00eb normale):<\/p>\n
\"Z\u00ebvend\u00ebsimi
\n
\nV\u00ebrehet se JSONB p\u00ebrs\u00ebri funksionon m\u00eb shpejt se EAV pa indekse, por kur EAV ka indekse \u2013 akoma funksionon m\u00eb shpejt se JSONB. Por m\u00eb pas pash\u00eb se koha p\u00ebr k\u00ebrkesat JSONB ishte e nj\u00ebjt\u00eb, kjo m\u00eb shtyu n\u00eb faktin se indekset GIN nuk punonin. Duket se kur p\u00ebrdorni indeksin GIN p\u00ebr nj\u00eb kolon\u00eb me pronat e mbushura, ai vepron vet\u00ebm kur p\u00ebrdoret operatori i p\u00ebrfshirjes @>. E p\u00ebrdora k\u00ebt\u00eb n\u00eb nj\u00eb test t\u00eb ri, i cili kishte nj\u00eb ndikim t\u00eb madh n\u00eb koh\u00ebn: vet\u00ebm 0,153 ms! Kjo \u00ebsht\u00eb 15000 her\u00eb m\u00eb e shpejt\u00eb se EAV, dhe 25000 her\u00eb m\u00eb e shpejt\u00eb se operatori ->>. <\/p>\n
Mendoj se ishte mjaft shpejt!<\/p>\n
Madh\u00ebsia e tabelave t\u00eb BD<\/h3>\n
\nLe t\u00eb krahasojm\u00eb p\u00ebrmasat e tabelave n\u00eb t\u00eb dy qasjet. N\u00eb psql mund t\u00eb tregojm\u00eb madh\u00ebsin\u00eb e t\u00eb gjitha tabelave dhe indekseve p\u00ebrmes komand\u00ebs dti+<\/b><\/p>\n
\"Z\u00ebvend\u00ebsimi
\n
\nP\u00ebr qasjen EAV, p\u00ebrmasat e tabelave arrijn\u00eb rreth 3068 MB, nd\u00ebrsa indekset deri n\u00eb 3427 MB, duke dh\u00ebn\u00eb gjithsej 6,43 GB. Duke p\u00ebrdorur qasjen me JSONB, p\u00ebrdoren 1817 MB p\u00ebr tabel\u00ebn dhe 318 MB p\u00ebr indekset, duke b\u00ebr\u00eb gjithsej 2,08 GB. Kjo \u00ebsht\u00eb tri her\u00eb m\u00eb pak! Ky fakt m\u00eb befasoi pak, sepse ne ruajm\u00eb emrat e pronave n\u00eb \u00e7do objekt JSONB. <\/p>\n
Por numrat flasin vet\u00eb: n\u00eb EAV ne ruajm\u00eb 2 \u00e7el\u00ebsa t\u00eb jasht\u00ebm t\u00eb plot\u00eb mbi vler\u00ebn e atributit, duke rezultuar n\u00eb 8 byte t\u00eb dh\u00ebna shtes\u00eb. P\u00ebr m\u00eb tep\u00ebr, n\u00eb EAV t\u00eb gjitha vlerat e pronave ruhen si tekst, nd\u00ebrsa JSONB do t\u00eb p\u00ebrdor\u00eb vlera numerike dhe logjike brenda, ku \u00ebsht\u00eb e mundur, duke rezultuar n\u00eb nj\u00eb volum m\u00eb t\u00eb vog\u00ebl.<\/p>\n
P\u00ebrfundimet<\/h3>\n
\nN\u00eb p\u00ebrgjith\u00ebsi, mendoj se ruajtja e vetive t\u00eb entiteteve n\u00eb formatin JSONB mund t\u00eb thjeshtoj\u00eb ndjesh\u00ebm dizenjimin dhe mir\u00ebmbajtjen e baz\u00ebs suaj t\u00eb t\u00eb dh\u00ebnave. N\u00ebse b\u00ebni shum\u00eb k\u00ebrkesa, at\u00ebher\u00eb gjith\u00e7ka q\u00eb ruhet n\u00eb nj\u00eb tabel\u00eb me entitetin do t\u00eb funksionoj\u00eb v\u00ebrtet m\u00eb efektivisht. Dhe fakti q\u00eb kjo e thjeshton nd\u00ebrveprimin midis t\u00eb dh\u00ebnave \u00ebsht\u00eb nj\u00eb p\u00ebrfitim, por gjithashtu baza e t\u00eb dh\u00ebnave rezultuese \u00ebsht\u00eb 3 her\u00eb m\u00eb e vog\u00ebl n\u00eb volum.<\/p>\n
Gjithashtu, sipas testeve t\u00eb kryera, mund t\u00eb p\u00ebrfundosh se humbjet e performanc\u00ebs jan\u00eb shum\u00eb t\u00eb pakta. N\u00eb disa raste, JSONB madje funksionon m\u00eb shpejt se EAV, gj\u00eb q\u00eb e b\u00ebn at\u00eb edhe m\u00eb t\u00eb mir\u00eb. Megjithat\u00eb, ky test standard natyrisht nuk kap t\u00eb gjitha aspektet (p\u00ebr shembull, entitete me nj\u00eb num\u00ebr shum\u00eb t\u00eb madh vetish, rritje t\u00eb konsiderueshme t\u00eb numrit t\u00eb vetive t\u00eb t\u00eb dh\u00ebnave ekzistuese,...), ndaj, n\u00ebse keni ndonj\u00eb sugjerim se si t'i p\u00ebrmir\u00ebsoni ato, ju lutemi mos ngurroni t\u00eb lini komentet tuaja!
\n
Burimi: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
TL; DR: JSONB \u043c\u043e\u0436\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u0442\u044c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0443 \u0441\u0445\u0435\u043c\u044b \u0411\u0414 \u0431\u0435\u0437 \u0443\u0449\u0435\u0440\u0431\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445. \u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0434\u0435\u043c \u043a\u043b\u0430\u0441\u0441\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u043f\u0440\u0438\u043c\u0435\u0440, \u043d\u0430\u0432\u0435\u0440\u043d\u043e\u0435, \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0441\u0442\u0430\u0440\u0435\u0439\u0448\u0438\u0445 \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u043e\u0432 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u043c\u0438\u0440\u0435 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0411\u0414 (\u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445): \u0443 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u044c, \u0438 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u0435 \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u0430 (\u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b) \u044d\u0442\u043e\u0439 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u0438. \u041d\u043e \u043d\u0435 \u0432\u0441\u0435 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u044b \u043c\u043e\u0433\u0443\u0442 \u0438\u043c\u0435\u044e\u0442 \u043e\u0434\u0438\u043d\u0430\u043a\u043e\u0432\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0441\u0432\u043e\u0439\u0441\u0442\u0432, \u043a \u0442\u043e\u043c\u0443 \u0436\u0435 \u0432 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-52584","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Z\u00ebvend\u00ebsimi i EAV me JSONB n\u00eb PostgreSQL | ProHoster","description":"TL; DR: JSONB mund t\u00eb thjeshtoj\u00eb ndjesh\u00ebm zhvillimin e skemave t\u00eb DB pa compromitime n\u00eb performanc\u00ebn e k\u00ebrkesave. Hyrje Le t\u00eb japim nj\u00eb shembull klasik, ndoshta nj\u00eb nga opsionet m\u00eb t\u00eb vjetra t\u00eb p\u00ebrdorimit n\u00eb bot\u00ebn e DB-ve relacional (databaza): kemi nj\u00eb entitet dhe duhet t\u00eb ruajm\u00eb disa ve\u00e7ori (atribute) t\u00eb k\u00ebtij entiteti. Por jo t\u00eb gjith\u00eb instancat mund t\u00eb ken\u00eb t\u00eb nj\u00ebjtin set ve\u00e7orish, p\u00ebrve\u00e7 k\u00ebsaj n\u00eb","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0417\u0430\u043c\u0435\u043d\u0430 EAV \u043d\u0430 JSONB \u0432 PostgreSQL | ProHoster","og:description":"TL; DR: JSONB \u043c\u043e\u0436\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u0442\u044c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0443 \u0441\u0445\u0435\u043c\u044b \u0411\u0414 \u0431\u0435\u0437 \u0443\u0449\u0435\u0440\u0431\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445. \u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0434\u0435\u043c \u043a\u043b\u0430\u0441\u0441\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u043f\u0440\u0438\u043c\u0435\u0440, \u043d\u0430\u0432\u0435\u0440\u043d\u043e\u0435, \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0441\u0442\u0430\u0440\u0435\u0439\u0448\u0438\u0445 \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u043e\u0432 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u043c\u0438\u0440\u0435 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0411\u0414 (\u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445): \u0443 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u044c, \u0438 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u0435 \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u0430 (\u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b) \u044d\u0442\u043e\u0439 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u0438. \u041d\u043e \u043d\u0435 \u0432\u0441\u0435 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u044b \u043c\u043e\u0433\u0443\u0442 \u0438\u043c\u0435\u044e\u0442 \u043e\u0434\u0438\u043d\u0430\u043a\u043e\u0432\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0441\u0432\u043e\u0439\u0441\u0442\u0432, \u043a \u0442\u043e\u043c\u0443 \u0436\u0435 \u0432","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-11-11T21:00:00+00:00","article:modified_time":"2020-02-18T11:00:21+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"52584","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-24 04:07:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 20:40:27","updated":"2026-01-24 04:07:19"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/52584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=52584"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/52584\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=52584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=52584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=52584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}