{"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\/et\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","title":{"rendered":"EAV asendamine JSONB-ga PostgreSQL-is","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

TL; DR: JSONB v\u00f5ib oluliselt lihtsustada andmebaasiskeemi arendust, ilma et see kahjustaks p\u00e4ringute j\u00f5udlust.<\/p><\/blockquote>\n

<\/p>\n

Sissejuhatus<\/h3>\n

\nVaatame klassikalist n\u00e4idet, mis on ilmselt \u00fcks vanimaid rakendusi rikkaandmestike maailmas: meil on entiteet, mille omadusi (atribuutide) tuleb salvestada. Kuid k\u00f5ik eksemplarid ei pruugi omada samu omadusi ning tulevikus v\u00f5ib tekkida vajadus lisada veel uusi omadusi.<\/p>\n

Lihtsaim lahendus sellele probleemile on luua andmebaasi tabelis iga atribuudiv\u00e4\u00e4rtuse jaoks veerg ja lihtsalt t\u00e4ita need veerud, mis on vajalikud konkreetse entiteedi eksemplari jaoks. Suurep\u00e4rane! Probleem on lahendatud\u2026 kuni teie tabelis on miljoneid sissekandeid ja teil tekib vajadus lisada uus rida.<\/p>\n

Vaatame EAV mustrit (Entiteet-Atribuut-V\u00e4\u00e4rtus<\/a><\/noindex>), see, it occurs quite often. One table contains entities (records), another table contains property names (attributes), and a third table links entities with their attributes and contains the value of these attributes for the current entity. This allows you to have different sets of properties for different objects and also to add properties 'on the fly' without changing the database structure.
\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

Hoolimata nendest ilmsestest puudustest on EAV juba pikka aega kasutusel selliste probleemide lahendamiseks. Need olid v\u00e4ltimatud puudused ja paremat alternatiivi lihtsalt ei olnud.
\nAga siis ilmus PostgreSQL-i uus \"tehnoloogia\"\u2026<\/p>\n

Alates PostgreSQL 9.4 on lisatud JSONB andmet\u00fc\u00fcbi tugi JSON-i binaarsete andmete salvestamiseks. Kuigi JSON-i salvestamine selles formaadis v\u00f5tab tavaliselt veidi rohkem ruumi ja aega kui lihtne tekstiline JSON, toimub selle t\u00f6\u00f6tlemine palju kiiremini. Samuti toetab JSONB indekseerimist, mis muudab p\u00e4ringud veelgi kiiremaks.<\/p>\n

JSONB andmet\u00fc\u00fcp v\u00f5imaldab meil asendada mahuka EAV mustri, lisades meie entsiti tabelisse vaid \u00fche JSONB veeru, mis lihtsustab andmebaasi kavandamist oluliselt. Kuid paljud v\u00e4idavad, et see peaks kaasnema j\u00f5udluse langusega\u2026 Selle p\u00e4rast see artikkel ilmuski.<\/p>\n

Testiva andmebaasi seadistamine<\/h3>\n

\nSelleks v\u00f5rdluseks l\u00f5in ma andmebaasi uue PostgreSQL 9.5 installatsiooni peal 80-dollarise komplekti jaoks DigitalOcean<\/a><\/noindex> Ubuntu 14.04. P\u00e4rast m\u00f5nede seadete seadistamist postgresql.conf-is k\u00e4ivitasin ma seda<\/a><\/noindex> psql-i abil integreerimine. EAV kujul andmete esitamiseks on loodud j\u00e4rgmised tabelid:<\/p>\n

Loo tabel entity ( \n  id           SERIAL PRIMARY KEY, \n  name         TEXT, \n  description  TEXT\n);\nLoo tabel entity_attribute (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT\n);\nLoo tabel entity_attribute_value (\n  id                  SERIAL PRIMARY KEY, \n  entity_id           INT    REFERENTS entity(id), \n  entity_attribute_id INT    REFERENTS entity_attribute(id), \n  value               TEXT\n);\n<\/code><\/pre>\n
\nAllpool on tabel, kus hoitakse samu andmeid, kuid atribuudid on JSONB t\u00fc\u00fcpi veerus \u2013 properties<\/i>.<\/p>\n
Loo tabel entity_jsonb (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT, \n  description TEXT,\n  properties  JSONB\n);\n<\/code><\/pre>\n
\nTundub palju lihtsam, eks? Siis lisati tabelitesse (entity<\/i> & entity_jsonb<\/i>) 10 miljonit kirjet ja vastavalt t\u00e4ideti sama andmetega tabel, kus kasutatakse EAV mustrit ja JSONB veeru l\u00e4henemist \u2013 entity_jsonb.properties<\/i>. Seega saime mitmeid erinevaid andmet\u00fc\u00fcpe kogu omaduste kogumi hulgas. N\u00e4idetest:<\/p>\n
{\n  id:          1\n  name:        \"Entity1\"\n  description: \"Test entity nr. 1\"\n  properties:  {\n    color:        \"punane\"\n    length:      120\n    width:       3.1882420\n    hassomething: true\n    country:      \"Belgia\"\n  } \n}<\/code><\/pre>\n
\nN\u00fc\u00fcd on meil samad andmed kahes variandis. Alustame rakenduste v\u00f5rdlemisega!<\/p>\n
Disaini lihtsustamine<\/h3>\n
\nVarasemalt on juba \u00f6eldud, et andmebaasi disain on m\u00e4rkimisv\u00e4\u00e4rselt lihtsustatud: \u00fcks tabel, kasutades JSONB veergu omaduste jaoks, selle asemel et kasutada kolme tabelit EAV jaoks. Kuidas see aga p\u00e4ringutes kajastub? \u00dche omaduse v\u00e4rskendamine entiteedi kohta n\u00e4eb v\u00e4lja j\u00e4rgmine:<\/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
\nNagu n\u00e4eme, ei tundu viimane p\u00e4ring lihtsam. Selleks, et v\u00e4rskendada omaduse v\u00e4\u00e4rtust JSONB objekti sees, peame kasutama funktsiooni jsonb_set()<\/a><\/noindex>, ja peame edastama meie uue v\u00e4\u00e4rtuse kui JSONB objekti. Siiski ei pea me eelnevalt teadma mingit ID-d. Vaatame EAV n\u00e4idet, kus peame v\u00e4rskendamiseks teadma nii entity_id kui ka entity_attribute_id. Kui soovite omadust JSONB veerus objekti nime p\u00f5hjal v\u00e4rskendada, siis toimub see k\u00f5ik \u00fche lihtsa reaga.<\/p>\n
N\u00fc\u00fcd valime entiteedi, mida just v\u00e4rskendasime, tema uue v\u00e4rvi alusel:<\/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
\nMa arvan, et v\u00f5ime n\u00f5ustuda, et teine on l\u00fchem (ilma joinita!) ja seega paremini loetav. Siin v\u00f5idab JSONB! Kasutame JSON operaatorit ->>, et saada v\u00e4rv tekstiv\u00e4\u00e4rtusena JSONB objektist. Samuti on olemas teine viis sama tulemuse saavutamiseks JSONB mudelis, kasutades operaatorit @>:<\/p>\n
-- JSONB \nSELECT name \nFROM entity_jsonb \nWHERE properties @> '{\"color\": \"blue\"}';\n<\/code><\/pre>\n
\nSee on veidi keerulisem: kontrollime, kas JSON objekt veerus properties sisaldab objekti, mis asub operaatori @> paremal k\u00fcljel. V\u00e4hem loetav, kuid suurem tootlikkus (vt allpool). <\/p>\n
Teeme JSONB kasutamise veelgi lihtsamaks, kui peate valima mitu omadust korraga. Just siin sobib JSONB l\u00e4henemine t\u00f5eliselt: me lihtsalt valime omadused t\u00e4iendavate veergudena meie tulemuste komplektis ilma vajaduseta \u00fchendada:<\/p>\n
-- JSONB \nSELECT name\n  , properties ->> 'color'\n  , properties ->> 'country'\nFROM entity_jsonb \nWHERE id = 120;\n<\/code><\/pre>\n
\nEAV kasutamisel on igas omaduses, mida soovite p\u00e4rida, vaja 2 liitumist. Minu arvates n\u00e4itavad eeltoodud p\u00e4ringud andmebaasi disainis suurt lihtsustamist. Vaadake rohkem n\u00e4iteid, kuidas kirjutada p\u00e4ringuid JSONB-le, v\u00f5ib-olla ka selles<\/a><\/noindex> postitusest.
\nN\u00fc\u00fcd on aeg r\u00e4\u00e4kida tootlikkusest.<\/p>\n
Tootlikkus<\/h3>\n
\nTootlikkuse v\u00f5rdlemiseks kasutasin EXPLAIN ANALYZE<\/a><\/noindex> p\u00e4ringutes, et m\u00f5\u00f5ta t\u00e4itmise aega. Iga p\u00e4ringut k\u00e4idi l\u00e4bi v\u00e4hemalt kolm korda, kuna esmakordselt kulub p\u00e4ringute planeerijale rohkem aega. Alustasin p\u00e4ringute t\u00e4itmisega ilma igasuguste indeksiteta. Ilmselt andis see JSONB-le eelise, kuna EAV jaoks vajalikud liitumised ei saanud kasutada indekseid (v\u00e4listest v\u00f5tmev\u00e4ljadest ei olnud indekseid). P\u00e4rast seda l\u00f5in 2 v\u00e4list v\u00f5tmetabeli EAV v\u00e4\u00e4rtuste jaoks indeksi, samuti indeksi GIN<\/a><\/noindex> JSONB v\u00e4lja jaoks.<\/p>\n
Andmete v\u00e4rskendamine n\u00e4itas j\u00e4rgmisi tulemusi aja osas (ms). Pange t\u00e4hele, et skaala on logaritmiline:<\/p>\n
\"EAV
\n
\nN\u00e4eme, et JSONB on m\u00e4rgatavalt (> 50000 korda) kiirem kui EAV, kui indekseid ei kasutata, nagu eelnevalt mainitud. Kui indekseerisime veerge peamiste v\u00f5tmete alusel, peaaegu kaob erinevus, kuid JSONB on siiski 1,3 korda kiirem kui EAV. Oluline on m\u00e4rkida, et JSONB veeru indeks ei m\u00f5juta siin midagi, kuna me ei kasuta omaduste veergu hindekriteeriumites. <\/p>\n
Omaduse v\u00e4\u00e4rtuse p\u00f5hjal andmete valimiseks saame j\u00e4rgmised tulemused (tavaline skaalamine):<\/p>\n
\"EAV
\n
\nOn m\u00e4rgata, et JSONB t\u00f6\u00f6tab j\u00e4lle kiiremini kui EAV ilma indekseid, kuid kui EAV-l on indeksid, t\u00f6\u00f6tab see siiski kiiremini kui JSONB. Siiski m\u00e4rkasin, et JSONB p\u00e4ringute aeg oli sama, mis t\u00f5ukaski mind oletama, et GIN-indeks ei t\u00f6\u00f6ta. Ilmselt, kui kasutate GIN-indeksit veerus, kus on omadused, kehtib see ainult kaudse operaatori @> kasutamise korral. Kasutasin seda uues testis, mis avaldas suure m\u00f5ju ajale: vaid 0,153 ms! See on 15000 korda kiirem kui EAV ja 25000 korda kiirem kui operaator ->>. <\/p>\n
Arvan, et see oli piisavalt kiire!<\/p>\n
Andmebaasi tabelite suurus<\/h3>\n
\nV\u00f5rdleme tabelite suurusi m\u00f5lema l\u00e4henemisviisi korral. Psql-is saame n\u00e4idata k\u00f5igi tabelite ja indeksite suurust k\u00e4suga dti+<\/b><\/p>\n
\"EAV
\n
\nEAV l\u00e4henemise korral on tabelite suurus umbes 3068 MB ja indeksite suurus kuni 3427 MB, kokku 6,43 GB. JSONB l\u00e4henemise puhul kasutatakse tabeli jaoks 1817 MB ja indeksite jaoks 318 MB, kokku 2,08 GB. See on kolm korda v\u00e4hem! See fakt \u00fcllatas mind natuke, sest hoiame omaduste nimesid igas JSONB objekti. <\/p>\n
Aga numbrid r\u00e4\u00e4givad enda eest: EAV-s hoiame 2 t\u00e4isarvulist v\u00e4lisv\u00f5tit atribuudi v\u00e4\u00e4rtusele, mille tulemusena saame 8 baiti lisanduvaid andmeid. Lisaks sellele on EAV-s k\u00f5ik omaduste v\u00e4\u00e4rtused tekstina, samas kui JSONB kasutab seal, kus v\u00f5imalik, numbrilisi ja loogilisi v\u00e4\u00e4rtusi, mille t\u00f5ttu saadakse v\u00e4iksem maht.<\/p>\n
Kokkuv\u00f5te<\/h3>\n
\nKokkuv\u00f5ttes arvan, et entsiti omaduste s\u00e4ilitamine JSONB formaadis v\u00f5ib teie andmebaasi projekteerimist ja haldamist oluliselt lihtsustada. Kui teete palju p\u00e4ringuid, siis k\u00f5ik, mis on salvestatud \u00fchte tabelisse koos entiteediga, t\u00f6\u00f6tab t\u00f5epoolest efektiivsemalt. Ja see, et see lihtsustab andmete vahelisi seoseid, on juba pluss, aga ka tulemusena saadud andmebaasi maht on kolm korda v\u00e4iksem.<\/p>\n
Samuti on tehtud testide p\u00f5hjal v\u00f5imalik j\u00e4reldada, et j\u00f5udluse kaod on v\u00e4ga v\u00e4hesed. M\u00f5nel juhul t\u00f6\u00f6tab JSONB isegi kiiremini kui EAV, mis teeb selle veelgi paremaks. Siiski ei kata see referentstest kindlasti k\u00f5iki aspekte (n\u00e4iteks entiteedid, millel on v\u00e4ga palju omadusi, olemasolevate andmete omaduste arvu m\u00e4rkimisv\u00e4\u00e4rne suurenemine jne), seega, kui teil on mingeid ettepanekuid, kuidas neid parandada, \u00e4rge kartke j\u00e4tta kommentaare!
\n
Allikas: 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":"\ud83c\udfc6 JSONB kasutamine PostgreSQL-is EAV asemel | ProHoster","description":"TL; DR: JSONB v\u00f5ib oluliselt lihtsustada andmebaasi skeemi v\u00e4ljat\u00f6\u00f6tamist, ilma et see tooks kaasa p\u00e4ringute j\u00f5udluse langust. Sissejuhatus. Toome v\u00e4lja klassikalise n\u00e4ite, arvatavasti \u00fcht vanimat kasutada olevat varianti rikkaandmebaaside maailmas: meil on mingi entiteet ja on vaja salvestada selle entiteedi teatud omadused (atribuudid). Kuid mitte k\u00f5ikide eksemplaridel ei pruugi olla sama omaduste kogum, lisaks","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/52584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=52584"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/52584\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=52584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=52584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=52584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}