{"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\/az\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","title":{"rendered":"PostgreSQL-d\u0259 EAV-n\u0131n JSONB il\u0259 \u0259v\u0259zl\u0259nm\u0259si","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

TL; DR: JSONB veril\u0259nl\u0259r bazas\u0131 sxeminin inki\u015faf\u0131n\u0131 m\u00fch\u00fcm d\u0259r\u0259c\u0259d\u0259 asanla\u015fd\u0131ra bil\u0259r, sor\u011fularda performansdan \u00f6d\u00fcn verm\u0259d\u0259n.<\/p><\/blockquote>\n

<\/p>\n

Giri\u015f<\/h3>\n

\nKlassik n\u00fcmun\u0259 ver\u0259k, y\u0259qin ki, \u0259n k\u00f6hn\u0259 \u0259laq\u0259li veril\u0259nl\u0259r bazas\u0131 (baza) istifad\u0259 variantlar\u0131ndan biridir: bizd\u0259 bir varl\u0131q var v\u0259 bu varl\u0131\u011f\u0131n m\u00fc\u0259yy\u0259n x\u00fcsusiyy\u0259tl\u0259rini (atributlar\u0131n\u0131) saxlamaq laz\u0131md\u0131r. Lakin, b\u00fct\u00fcn n\u00fcmun\u0259l\u0259rin eyni x\u00fcsusiyy\u0259tl\u0259r d\u0259stin\u0259 malik olmas\u0131 m\u00fcmk\u00fcn deyil, \u00fcst\u0259lik, g\u0259l\u0259c\u0259kd\u0259 daha \u00e7ox x\u00fcsusiyy\u0259tl\u0259rin \u0259lav\u0259 edilm\u0259si m\u00fcmk\u00fcnd\u00fcr.<\/p>\n

Bu problemin \u0259n sad\u0259 h\u0259lli, veril\u0259nl\u0259r bazas\u0131 c\u0259dv\u0259lind\u0259 h\u0259r bir x\u00fcsusiyy\u0259t d\u0259y\u0259ri \u00fc\u00e7\u00fcn s\u00fctun yaratmaqd\u0131r v\u0259 sad\u0259c\u0259 olaraq x\u00fcsusi varl\u0131\u011f\u0131n laz\u0131m olanlar\u0131 doldurmaqd\u0131r. \u018fla! Problem h\u0259ll olundu... sizd\u0259 milyonlarla qeyd olan bir c\u0259dv\u0259l olana q\u0259d\u0259r v\u0259 yeni bir qeyd \u0259lav\u0259 etm\u0259k z\u0259rur\u0259ti yaranana q\u0259d\u0259r.<\/p>\n

EAV modelini n\u0259z\u0259rd\u0259n ke\u00e7ir\u0259k (Entity-Attribute-Value<\/a><\/noindex>), bu kifay\u0259t q\u0259d\u0259r tez-tez rast g\u0259linir. Bir c\u0259dv\u0259l varl\u0131qlar\u0131 (yaz\u0131lar\u0131), dig\u0259r c\u0259dv\u0259l is\u0259 x\u00fcsusiyy\u0259t adlar\u0131n\u0131 (atributlar\u0131) ehtiva edir, \u00fc\u00e7\u00fcnc\u00fc c\u0259dv\u0259l is\u0259 varl\u0131qlar\u0131 onlar\u0131n atributlar\u0131 il\u0259 birl\u0259\u015fdirir v\u0259 cari varl\u0131q \u00fc\u00e7\u00fcn bu atributlar\u0131n d\u0259y\u0259rini saxlay\u0131r. Bu, siz\u0259 m\u00fcxt\u0259lif obyektl\u0259r \u00fc\u00e7\u00fcn f\u0259rqli x\u00fcsusiyy\u0259tl\u0259r d\u0259stl\u0259rini \u0259ld\u0259 etm\u0259y\u0259 v\u0259 veril\u0259nl\u0259r bazas\u0131 strukturunu d\u0259yi\u015fdirm\u0259d\u0259n \u201ccanl\u0131\u201d olaraq x\u00fcsusiyy\u0259tl\u0259r \u0259lav\u0259 etm\u0259y\u0259 imkan verir.
\n<\/a><\/noindex>
\nBununla bel\u0259, EVA istifad\u0259 ed\u0259n yana\u015fmada \u00e7at\u0131\u015fmazl\u0131qlar olmasayd\u0131 bu qeydi yazmazd\u0131m. M\u0259s\u0259l\u0259n, bir v\u0259 ya bir ne\u00e7\u0259 atributu olan varl\u0131qlar\u0131 \u0259ld\u0259 etm\u0259k \u00fc\u00e7\u00fcn sor\u011fuda 2 join (birl\u0259\u015fm\u0259) t\u0259l\u0259b olunur: birincisi, atributlar c\u0259dv\u0259li il\u0259 birl\u0259\u015fm\u0259, ikincisi is\u0259 d\u0259y\u0259rl\u0259r c\u0259dv\u0259li il\u0259 birl\u0259\u015fm\u0259. \u018fg\u0259r varl\u0131\u011f\u0131n 2 atributu varsa, bu zaman art\u0131q 4 join t\u0259l\u0259b olunur! Bundan \u0259lav\u0259, b\u00fct\u00fcn atributlar ad\u0259t\u0259n m\u0259tn \u015f\u0259klind\u0259 saxlan\u0131l\u0131r ki, bu da h\u0259m n\u0259tic\u0259, h\u0259m d\u0259 WHERE \u015f\u0259rti \u00fc\u00e7\u00fcn tipl\u0259rin \u00e7evrilm\u0259sin\u0259 g\u0259tirib \u00e7\u0131xar\u0131r. \u018fg\u0259r siz \u00e7ox sayda sor\u011fu yaz\u0131rs\u0131n\u0131zsa, bu, resurs istifad\u0259si bax\u0131m\u0131ndan olduqca israf\u00e7\u0131d\u0131r.<\/p>\n

Bu n\u0259z\u0259r\u0259\u00e7arpan \u00e7at\u0131\u015fmazl\u0131qlara baxmayaraq, EAV art\u0131q uzun m\u00fcdd\u0259tdir bu c\u00fcr probleml\u0259rin h\u0259lli \u00fc\u00e7\u00fcn istifad\u0259 olunur. Bunlar qa\u00e7\u0131lmaz \u00e7at\u0131\u015fmazl\u0131qlar idi, v\u0259 daha yax\u015f\u0131 alternativi sad\u0259c\u0259 olaraq yox idi.
\nAmma sonra PostgreSQL-d\u0259 yeni bir \u201ctexnologiya\u201d meydana \u00e7\u0131xd\u0131...<\/p>\n

PostgreSQL 9.4-d\u0259n ba\u015flayaraq, JSON format\u0131nda ikili veril\u0259nl\u0259ri saxlamaq \u00fc\u00e7\u00fcn JSONB m\u0259lumat tipi \u0259lav\u0259 edildi. Bu formatda JSON saxland\u0131qda, ad\u0259t\u0259n, sad\u0259 m\u0259tn JSON-dan bir az daha \u00e7ox yer v\u0259 vaxt t\u0259l\u0259b ets\u0259 d\u0259, onunla \u0259m\u0259liyyatlar daha s\u00fcr\u0259tli h\u0259yata ke\u00e7irilir. H\u0259m\u00e7inin, JSONB indeksl\u0259m\u0259yi d\u0259st\u0259kl\u0259yir, bu da onlara sor\u011fular\u0131 daha da s\u00fcr\u0259tl\u0259ndirir.<\/p>\n

JSONB m\u0259lumat n\u00f6v\u00fc biz\u0259 EAV modelini \u0259v\u0259z etm\u0259k \u00fc\u00e7\u00fcn yaln\u0131z bir JSONB s\u00fctunu \u0259lav\u0259 etm\u0259kl\u0259 \u0259h\u0259miyy\u0259tli d\u0259r\u0259c\u0259d\u0259 sad\u0259l\u0259\u015fdirilmi\u015f veril\u0259nl\u0259r bazas\u0131 dizayn\u0131 imkan\u0131n\u0131 verir. Lakin bir \u00e7oxlar\u0131 bunun performans\u0131n azald\u0131lmas\u0131 il\u0259 m\u00fc\u015fayi\u0259t olunmal\u0131 oldu\u011funu iddia edir\u2026 Buna g\u00f6r\u0259 d\u0259, bu m\u0259qal\u0259 ortaya \u00e7\u0131xd\u0131.<\/p>\n

S\u0131naq veril\u0259nl\u0259r bazas\u0131n\u0131n konfiqurasiyas\u0131<\/h3>\n

\nBu m\u00fcqayis\u0259 \u00fc\u00e7\u00fcn m\u0259n PostgreSQL 9.5-in yeni qura\u015fd\u0131rmas\u0131 \u00fcz\u0259rind\u0259 80 dollar d\u0259y\u0259rind\u0259 bir veril\u0259nl\u0259r bazas\u0131 yaratd\u0131m. DigitalOcean<\/a><\/noindex> Ubuntu 14.04. B\u0259zi parametrl\u0259ri postgresql.conf-da konfiqurasiya etdikd\u0259n sonra ba\u015flad\u0131m bu<\/a><\/noindex> skripti psql vasit\u0259sil\u0259. EAV format\u0131nda m\u0259lumatlar\u0131 t\u0259qdim etm\u0259k \u00fc\u00e7\u00fcn a\u015fa\u011f\u0131dak\u0131 c\u0259dv\u0259ll\u0259r yarad\u0131ld\u0131:<\/p>\n

CREATE TABLE entity ( \n  id           SERIAL PRIMARY KEY, \n  name         TEXT, \n  description  TEXT\n);\nCREATE TABLE entity_attribute (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT\n);\nCREATE TABLE entity_attribute_value (\n  id                  SERIAL PRIMARY KEY, \n  entity_id           INT    REFERENCES entity(id), \n  entity_attribute_id INT    REFERENCES entity_attribute(id), \n  value               TEXT\n);\n<\/code><\/pre>\n
\nA\u015fa\u011f\u0131da JSONB tipli s\u00fctunda atributlarla eyni m\u0259lumatlar\u0131 saxlayacaq c\u0259dv\u0259l t\u0259qdim olunur \u2013 properties<\/i>.<\/p>\n
CREATE TABLE entity_jsonb (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT, \n  description TEXT,\n  properties  JSONB\n);\n<\/code><\/pre>\n
\nDaha sad\u0259 g\u00f6r\u00fcn\u00fcr, el\u0259 deyilmi? Sonra bu c\u0259dv\u0259ll\u0259r\u0259 (entity<\/i> & entity_jsonb<\/i>) 10 milyon qeyd \u0259lav\u0259 olundu v\u0259 m\u00fcvafiq olaraq EAV modelind\u0259 istifad\u0259 olunan c\u0259dv\u0259l\u0259 v\u0259 JSONB s\u00fctunu il\u0259 yana\u015f\u0131 eyni m\u0259lumatlarla dolduruldu \u2013 entity_jsonb.properties<\/i>. Bununla da, biz b\u00fct\u00fcn property setind\u0259 bir ne\u00e7\u0259 f\u0259rqli m\u0259lumat n\u00f6v\u00fc \u0259ld\u0259 etdik. M\u0259lumatlar\u0131n n\u00fcmun\u0259si:<\/p>\n
{\n  id:          1\n  name:        \"Entity1\"\n  description: \"Test entity no. 1\"\n  properties:  {\n    color:        \"red\"\n    lenght:       120\n    width:        3.1882420\n    hassomething: true\n    country:      \"Belgium\"\n  } \n}<\/code><\/pre>\n
\nBel\u0259likl\u0259, indi eyni m\u0259lumatlara sahibik, iki variant \u00fc\u00e7\u00fcn. G\u0259lin, icra t\u0259rzl\u0259rini m\u00fcqayis\u0259 etm\u0259kd\u0259 ba\u015flay\u0131q!<\/p>\n
Dizayn\u0131n sad\u0259l\u0259\u015fdirilm\u0259si<\/h3>\n
\n\u018fvv\u0259lc\u0259, veril\u0259nl\u0259r bazas\u0131n\u0131n dizayn\u0131n\u0131n \u0259h\u0259miyy\u0259tli d\u0259r\u0259c\u0259d\u0259 sad\u0259l\u0259\u015fdirildiyi qeyd edilib: bir c\u0259dv\u0259l, JSONB s\u00fctunu istifad\u0259 etm\u0259kl\u0259 property-l\u0259r \u00fc\u00e7\u00fcn, \u00fc\u00e7 c\u0259dv\u0259l yerin\u0259 EAV \u00fc\u00e7\u00fcn. B\u0259s bu sor\u011fulara nec\u0259 t\u0259sir edir? Bir varl\u0131\u011f\u0131n \u00f6z\u0259lliyinin yenil\u0259nm\u0259si bel\u0259 g\u00f6r\u00fcn\u00fcr:<\/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
\nG\u00f6rd\u00fcy\u00fcn\u00fcz kimi, sonuncu sor\u011fu daha sad\u0259 g\u00f6r\u00fcnm\u00fcr. JSONB obyekti i\u00e7\u0259risind\u0259 \u00f6z\u0259lliyin d\u0259y\u0259rini yenil\u0259m\u0259k \u00fc\u00e7\u00fcn biz funkciyas\u0131 istifad\u0259 etm\u0259liyik jsonb_set()<\/a><\/noindex>, v\u0259 yeni d\u0259y\u0259rimizi JSONB obyekti kimi t\u0259qdim etm\u0259liyik. Buna baxmayaraq, biz \u0259vv\u0259lc\u0259d\u0259n h\u0259r hans\u0131 bir identifikator bilm\u0259k m\u0259cburiyy\u0259tind\u0259 deyilik. EAV n\u00fcmun\u0259sin\u0259 baxsaq, yenil\u0259nm\u0259 apararaq entity_id v\u0259 entity_attribute_id bilm\u0259k laz\u0131md\u0131r. JSONB s\u00fctunundak\u0131 \u00f6z\u0259lliyi obyekti ad\u0131 \u0259sas\u0131nda yenil\u0259m\u0259k ist\u0259s\u0259niz, bunun ham\u0131s\u0131 bir sad\u0259 s\u0259tirl\u0259 edilir.<\/p>\n
\u0130ndi g\u0259lin, yeni r\u0259ngin\u0259 g\u00f6r\u0259 yenil\u0259diyimiz varl\u0131\u011f\u0131 se\u00e7\u0259k:<\/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
\nD\u00fc\u015f\u00fcn\u00fcr\u0259m ki, ikincisinin daha q\u0131sa (join olmadan!) oldu\u011funu q\u0259bul ed\u0259 bil\u0259rik v\u0259 buna g\u00f6r\u0259 daha oxunaql\u0131d\u0131r. Burada JSONB-nin \u00fcst\u00fcnl\u00fcy\u00fc var! Biz JSON-dan m\u0259tb\u0259x d\u0259y\u0259ri \u0259ld\u0259 etm\u0259k \u00fc\u00e7\u00fcn JSON operatoru ->> istifad\u0259 edirik. H\u0259m\u00e7inin, JSONB modelind\u0259 eyni n\u0259tic\u0259y\u0259 \u00e7atma\u011f\u0131n ikinci yolu var, @> operatorunu istifad\u0259 etm\u0259kl\u0259:<\/p>\n
-- JSONB \nSELECT name \nFROM entity_jsonb \nWHERE properties @> '{\"color\": \"blue\"}';\n<\/code><\/pre>\n
\nBu bir az \u00e7\u0259tindir: biz, xass\u0259l\u0259r s\u00fctunundak\u0131 JSON obyektinin sa\u011fdak\u0131 @> operatoruna olan obyekti ehtiva edib-etm\u0259diyini yoxlay\u0131r\u0131q. Daha az oxunaql\u0131, amma daha m\u0259hsuldar (daha sonra bax\u0131n). <\/p>\n
Eyni anda bir ne\u00e7\u0259 x\u00fcsusiyy\u0259ti se\u00e7m\u0259k laz\u0131m oldu\u011funda JSONB istifad\u0259ni daha da asanla\u015fd\u0131raq. Burada JSONB yana\u015fmas\u0131 \u0259slind\u0259 uy\u011fundur: birl\u0259\u015fm\u0259l\u0259r olmadan n\u0259tic\u0259 c\u0259dv\u0259limizd\u0259 \u0259lav\u0259 s\u00fctunlar kimi x\u00fcsusiyy\u0259tl\u0259ri sad\u0259c\u0259 se\u00e7irik:<\/p>\n
-- JSONB \nSELECT name\n  , properties ->> 'color'\n  , properties ->> 'country'\nFROM entity_jsonb \nWHERE id = 120;\n<\/code><\/pre>\n
\nEAV-d\u0259 h\u0259r bir sor\u011fu \u00fc\u00e7\u00fcn t\u0259l\u0259b etdiyiniz h\u0259r bir x\u00fcsusiyy\u0259t \u00fc\u00e7\u00fcn 2 birl\u0259\u015fm\u0259 laz\u0131md\u0131r. M\u0259nim fikrimc\u0259, yuxar\u0131da t\u0259qdim olunan sor\u011fular, veril\u0259nl\u0259r bazas\u0131 dizayn\u0131nda b\u00f6y\u00fck bir sad\u0259l\u0259\u015fdirm\u0259ni n\u00fcmayi\u015f etdirir. JSONB-y\u0259 sor\u011fular yazmaqla ba\u011fl\u0131 daha \u00e7ox n\u00fcmun\u0259l\u0259r\u0259 baxmaq m\u00fcmk\u00fcnd\u00fcr bu<\/a><\/noindex> m\u0259qal\u0259d\u0259.
\n\u0130ndi performansdan dan\u0131\u015fma\u011f\u0131n vaxt\u0131d\u0131r.<\/p>\n
Performans<\/h3>\n
\nPerformans\u0131 m\u00fcqayis\u0259 etm\u0259k \u00fc\u00e7\u00fcn m\u0259n EXPLAIN ANALYZE<\/a><\/noindex> sor\u011fularda icra vaqt\u0131n\u0131 hesablamaq \u00fc\u00e7\u00fcn istifad\u0259 etdim. H\u0259r bir sor\u011fu \u0259n az\u0131 \u00fc\u00e7 d\u0259f\u0259 icra edildi, \u00e7\u00fcnki ilk d\u0259f\u0259 sor\u011fu planla\u015fd\u0131r\u0131c\u0131s\u0131n\u0131n daha \u00e7ox vaxt\u0131na ehtiyac\u0131 var. \u0130lk olaraq, m\u0259n sor\u011fular\u0131 he\u00e7 bir indeks olmadan icra etdim. Ayd\u0131nd\u0131r ki, bu, JSONB \u00fc\u00e7\u00fcn bir \u00fcst\u00fcnl\u00fck yarad\u0131r, \u00e7\u00fcnki EAV \u00fc\u00e7\u00fcn laz\u0131m olan birl\u0259\u015fm\u0259l\u0259r indeksl\u0259ri istifad\u0259 ed\u0259 bilm\u0259dil\u0259r (xarici a\u00e7ar sah\u0259l\u0259ri indeksl\u0259nm\u0259mi\u015fdir). Bundan sonra, EAV d\u0259y\u0259r c\u0259dv\u0259lind\u0259ki 2 xarici a\u00e7ar s\u00fctunu \u00fc\u00e7\u00fcn bir indeks yaratd\u0131m, eyni zamanda GIN<\/a><\/noindex> JSONB s\u00fctunu \u00fc\u00e7\u00fcn.<\/p>\n
M\u0259lumatlar\u0131n yenil\u0259nm\u0259si zaman\u0131 zaman (ms) \u00fczr\u0259 a\u015fa\u011f\u0131dak\u0131 n\u0259tic\u0259l\u0259r \u0259ld\u0259 edildi. Qeyd edin ki, miqyas logaritmidir:<\/p>\n
\"PostgreSQL-d\u0259
\n
\nG\u00f6r\u00fcr\u00fck ki, JSONB EAV-d\u0259n \u00e7ox (< 50000 d\u0259f\u0259) daha tezdir, indeksl\u0259rd\u0259n istifad\u0259 etm\u0259dikd\u0259, yuxar\u0131da g\u00f6st\u0259ril\u0259n s\u0259b\u0259bd\u0259n. Biz \u0259sas a\u00e7arlarla s\u00fctunlar\u0131 indeksl\u0259\u015fdirdikd\u0259, f\u0259rq dem\u0259k olar ki, itir, lakin JSONB h\u0259l\u0259 d\u0259 EAV-d\u0259n 1,3 d\u0259f\u0259 daha tezdir. Qeyd edin ki, burada JSONB s\u00fctunundak\u0131 indeks he\u00e7 bir t\u0259sir etm\u0259z, \u00e7\u00fcnki biz x\u00fcsusiyy\u0259t s\u00fctununu qiym\u0259tl\u0259ndirm\u0259 meyarlar\u0131nda istifad\u0259 etmirik. <\/p>\n
M\u00fclk d\u0259y\u0259rin\u0259 \u0259saslanan m\u0259lumat se\u00e7imi zaman\u0131 a\u015fa\u011f\u0131dak\u0131 n\u0259tic\u0259l\u0259ri \u0259ld\u0259 edirik (adi miqyas):<\/p>\n
\"PostgreSQL-d\u0259
\n
\nG\u00f6r\u00fcl\u00fcr ki, JSONB yenid\u0259n indekssiz EAV-d\u0259n s\u00fcr\u0259tli i\u015fl\u0259yir, lakin EAV indeksl\u0259rl\u0259 oldu\u011funda JSONB-d\u0259n daha s\u00fcr\u0259tli olur. Ancaq sonra JSONB sor\u011fular\u0131n\u0131n m\u00fcdd\u0259tinin eyni oldu\u011funu g\u00f6rd\u00fcm, bu da GIN indeksinin i\u015fl\u0259m\u0259diyini g\u00f6st\u0259rir. G\u00f6r\u00fcn\u00fcr, \u0259g\u0259r bir s\u00fctun \u00fc\u00e7\u00fcn GIN indeksi istifad\u0259 edirsinizs\u0259, yaln\u0131z @> daxil olma operatoru istifad\u0259 edildikd\u0259 \u00e7al\u0131\u015f\u0131r. Bunu yeni testd\u0259 istifad\u0259 etdim, bu da m\u00fcdd\u0259t\u0259 b\u00f6y\u00fck t\u0259sir g\u00f6st\u0259rdi: yaln\u0131z 0.153 ms! Bu, EAV-d\u0259n 15000 d\u0259f\u0259, v\u0259 - >> operatorundan 25000 d\u0259f\u0259 daha s\u00fcr\u0259tlidir. <\/p>\n
D\u00fc\u015f\u00fcn\u00fcr\u0259m ki, bu kifay\u0259t q\u0259d\u0259r s\u00fcr\u0259tli oldu!<\/p>\n
Veritaban\u0131 c\u0259dv\u0259ll\u0259rinin boyutu<\/h3>\n
\nH\u0259r iki yana\u015fma \u00fc\u00e7\u00fcn c\u0259dv\u0259l \u00f6l\u00e7\u00fcl\u0259rini m\u00fcqayis\u0259 ed\u0259k. psql-da biz b\u00fct\u00fcn c\u0259dv\u0259ll\u0259rin v\u0259 indeksl\u0259rin \u00f6l\u00e7\u00fcs\u00fcn\u00fc g\u00f6st\u0259rm\u0259k \u00fc\u00e7\u00fcn komandan\u0131 istifad\u0259 ed\u0259 bil\u0259rik dti+<\/b><\/p>\n
\"PostgreSQL-d\u0259
\n
\nEAV yana\u015fmas\u0131 \u00fc\u00e7\u00fcn c\u0259dv\u0259l \u00f6l\u00e7\u00fcl\u0259ri t\u0259xmin\u0259n 3068 MB, indeksl\u0259r is\u0259 3427 MB-\u0259 q\u0259d\u0259rdir, bu da 6.43 GB edir. JSONB yana\u015fmas\u0131nda c\u0259dv\u0259l 1817 MB v\u0259 indeksl\u0259r 318 MB istifad\u0259 edir, bu da 2.08 GB dem\u0259kdir. Bu, 3 d\u0259f\u0259 azd\u0131r! Bu fakt m\u0259ni bir az t\u0259\u0259cc\u00fcbl\u0259ndirdi, \u00e7\u00fcnki biz JSONB-d\u0259 h\u0259r bir obyektd\u0259 x\u00fcsusiyy\u0259tl\u0259rin adlar\u0131n\u0131 saxlay\u0131r\u0131q. <\/p>\n
Ancaq r\u0259q\u0259ml\u0259r \u00f6zl\u0259ri \u00fc\u00e7\u00fcn dan\u0131\u015f\u0131qlar apar\u0131r: EAV-d\u0259 biz atributun d\u0259y\u0259ri \u00fc\u00e7\u00fcn 2 tam sayl\u0131 xarici a\u00e7ar\u0131 saxlay\u0131r\u0131q, n\u0259tic\u0259d\u0259 \u0259lav\u0259 8 bayt m\u0259lumat \u0259ld\u0259 edirik. Bundan \u0259lav\u0259, EAV-d\u0259 b\u00fct\u00fcn x\u00fcsusiyy\u0259t d\u0259y\u0259rl\u0259ri m\u0259tn \u015f\u0259klind\u0259 saxlan\u0131l\u0131r, halbuki JSONB, m\u00fcmk\u00fcn olan yerl\u0259rd\u0259 \u0259d\u0259di v\u0259 m\u0259ntiqi d\u0259y\u0259rl\u0259r istifad\u0259 edir, n\u0259tic\u0259d\u0259 daha az h\u0259cm meydana g\u0259lir.<\/p>\n
Yekunlar<\/h3>\n
\n\u00dcmumiyy\u0259tl\u0259, d\u00fc\u015f\u00fcn\u00fcr\u0259m ki, varl\u0131qlar\u0131n x\u00fcsusiyy\u0259tl\u0259rini JSONB format\u0131nda saxlamaq, veril\u0259nl\u0259r bazan\u0131z\u0131n dizayn\u0131n\u0131 v\u0259 xidm\u0259tini \u0259h\u0259miyy\u0259tli d\u0259r\u0259c\u0259d\u0259 asanla\u015fd\u0131ra bil\u0259r. \u018fg\u0259r siz bir \u00e7ox sor\u011fular icra edirsinizs\u0259, bir c\u0259dv\u0259ld\u0259 olan b\u00fct\u00fcn m\u0259lumatlar\u0131n entity il\u0259 birlikd\u0259 olmas\u0131 daha effektiv olacaq. V\u0259 bu, m\u0259lumatlar aras\u0131nda qar\u015f\u0131l\u0131ql\u0131 \u0259laq\u0259ni asanla\u015fd\u0131rd\u0131\u011f\u0131 \u00fc\u00e7\u00fcn bir \u00fcst\u00fcnl\u00fckd\u00fcr, h\u0259m d\u0259 n\u0259tic\u0259d\u0259 bu veril\u0259nl\u0259r bazas\u0131 3 d\u0259f\u0259 daha az h\u0259cme malikdir.<\/p>\n
H\u0259m\u00e7inin, apar\u0131lan testl\u0259rd\u0259n n\u0259tic\u0259 \u00e7\u0131xarmaq olar ki, performans itkisi \u00e7ox c\u00fczidir. B\u0259zi hallarda JSONB, h\u0259tta EAV-d\u0259n daha s\u00fcr\u0259tli i\u015fl\u0259yir, bu da onu daha yax\u015f\u0131 edir. Ancaq bu istinad testi, \u0259lb\u0259tt\u0259 ki, b\u00fct\u00fcn aspektl\u0259ri \u0259hat\u0259 etmir (m\u0259s\u0259l\u0259n, \u00e7ox sayda x\u00fcsusiyy\u0259ti olan varl\u0131qlar, m\u00f6vcud m\u0259lumatlar\u0131n x\u00fcsusiyy\u0259tl\u0259rinin \u0259h\u0259miyy\u0259tli art\u0131m\u0131 v\u0259 s.), buna g\u00f6r\u0259 d\u0259 h\u0259r hans\u0131 bir t\u0259klifiniz varsa, \u015f\u0259rhl\u0259rd\u0259 qeyd etm\u0259kd\u0259n \u00e7\u0259kinm\u0259yin!
\n
M\u0259nb\u0259: 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\udd47PostgreSQL-d\u0259 EAV-nin JSONB il\u0259 \u0259v\u0259zl\u0259nm\u0259si | ProHoster","description":"TL; DR: JSONB veril\u0259nl\u0259r bazas\u0131 sxeminin inki\u015faf\u0131n\u0131 xeyli asanla\u015fd\u0131ra bil\u0259r, sor\u011fulardak\u0131 performansdan itki olmadan. Giri\u015f Klassik bir n\u00fcmun\u0259ni t\u0259qdim ed\u0259k, b\u0259lk\u0259 d\u0259 \u0259n q\u0259diml\u0259rind\u0259n biri.","canonical_url":"https:\/\/prohoster.info\/az\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"az_AZ","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.","og:url":"https:\/\/prohoster.info\/az\/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","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/posts\/52584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/comments?post=52584"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/posts\/52584\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/media?parent=52584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/categories?post=52584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/az\/wp-json\/wp\/v2\/tags?post=52584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}