{"id":79467,"date":"2020-04-27T07:42:23","date_gmt":"2020-04-27T05:42:23","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/operativnaya-analitika-v-mikroservisnoj-arhitekture-p%cc%b6o%cc%b6n%cc%b6ya%cc%b6t%cc%b6%cc%b6-%cc%b6i%cc%b6-%cc%b6p%cc%b6r%cc%b6o%cc%b6s%cc%b6t%cc%b6i%cc%b6t%cc%b6%cc%b6-pomoch-i-podskazat-postgres"},"modified":"2020-04-27T07:42:23","modified_gmt":"2020-04-27T05:42:23","slug":"operativnaya-analitika-v-mikroservisnoj-arhitekture-p%cc%b6o%cc%b6n%cc%b6ya%cc%b6t%cc%b6%cc%b6-%cc%b6i%cc%b6-%cc%b6p%cc%b6r%cc%b6o%cc%b6s%cc%b6t%cc%b6i%cc%b6t%cc%b6%cc%b6-pomoch-i-podskazat-postgres","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/operativnaya-analitika-v-mikroservisnoj-arhitekture-p%cc%b6o%cc%b6n%cc%b6ya%cc%b6t%cc%b6%cc%b6-%cc%b6i%cc%b6-%cc%b6p%cc%b6r%cc%b6o%cc%b6s%cc%b6t%cc%b6i%cc%b6t%cc%b6%cc%b6-pomoch-i-podskazat-postgres","title":{"rendered":"Analitika operacionale n\u00eb arkitektur\u00ebn mikro-sh\u00ebrbim: p\u0336o\u0336n\u0336j\u0336a\u0336t\u0336 \u0336i\u0336 \u0336p\u0336r\u0336o\u0336s\u0336t\u0336i\u0336t\u0336 \u0336ndihmoj dhe tregoj Postgres FDW","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Arkitektura mikros\u00ebrvices, si\u00e7 \u00ebsht\u00eb gjith\u00e7ka n\u00eb k\u00ebt\u00eb bot\u00eb, ka p\u00ebrpar\u00ebsit\u00eb dhe disavantazhet e saj. Disa procese b\u00ebhen m\u00eb t\u00eb thjeshta me t\u00eb, t\u00eb tjera - m\u00eb t\u00eb komplikuara. Dhe n\u00eb em\u00ebr t\u00eb shpejt\u00ebsis\u00eb s\u00eb ndryshimeve dhe nj\u00eb shkall\u00ebzueshm\u00ebrie m\u00eb t\u00eb mir\u00eb, ne duhet t\u00eb b\u00ebjm\u00eb disa sakrifica. Nj\u00eb prej tyre \u00ebsht\u00eb komplikuar analitika. N\u00eb nj\u00eb monolit, t\u00eb gjitha analitik\u00ebt operacional\u00eb mund t\u00eb reduktohen n\u00eb k\u00ebrkesa SQL n\u00eb nj\u00eb replik\u00eb analitike, por n\u00eb arkitektur\u00ebn e shum\u00eb sh\u00ebrbimeve, \u00e7do sh\u00ebrbim ka baz\u00ebn e tij dhe duket se nj\u00eb k\u00ebrkes\u00eb nuk mjafton (apo ndoshta mjafton?). P\u00ebr ata q\u00eb jan\u00eb t\u00eb interesuar se si zgjidh\u00ebm problemin e analitik\u00ebs operative n\u00eb kompanin\u00eb ton\u00eb dhe si m\u00ebsuam t\u00eb jetojm\u00eb me k\u00ebt\u00eb zgjidhje - mir\u00ebseerdh\u00ebt.<\/p>\n

\"Analitika
\nQuhem Pavel Sivas, n\u00eb DomKlik punoj n\u00eb ekipin q\u00eb \u00ebsht\u00eb p\u00ebrgjegj\u00ebs p\u00ebr mb\u00ebshtetje n\u00eb depozitat analitike t\u00eb t\u00eb dh\u00ebnave. Aktivitetin ton\u00eb mund ta kategorizojm\u00eb n\u00eb inxhinieri t\u00eb t\u00eb dh\u00ebnave, por, n\u00eb t\u00eb v\u00ebrtet\u00eb, spektri i detyrave \u00ebsht\u00eb shum\u00eb m\u00eb i gjer\u00eb. Ka standardet e zakonshme p\u00ebr inxhinieri t\u00eb t\u00eb dh\u00ebnave ETL\/ELT, mb\u00ebshtetje dhe adaptim t\u00eb veglave p\u00ebr analiz\u00ebn e t\u00eb dh\u00ebnave dhe zhvillimin e veglave tona. N\u00eb ve\u00e7anti, p\u00ebr raportimin operativ ne vendos\u00ebm t\u00eb 'pretendojm\u00eb' se kemi nj\u00eb monolit dhe t'u japim analist\u00ebve nj\u00eb baz\u00eb, ku do t\u00eb jen\u00eb t\u00eb gjitha t\u00eb dh\u00ebnat e nevojshme p\u00ebr ta. <\/a><\/noindex><\/p>\n

N\u00eb p\u00ebrgjith\u00ebsi, ne shqyrtuam alternativa t\u00eb ndryshme. Mund t\u00eb nd\u00ebrtosh nj\u00eb depo t\u00eb plot\u00eb \u2013 madje e provuam, por, p\u00ebr t\u00eb qen\u00eb t\u00eb sinqert\u00eb, nuk arrit\u00ebm ta lidhim mjaft shpesh ndryshimin n\u00eb logjik\u00eb me nj\u00eb proces nd\u00ebrtimi t\u00eb depozit\u00ebs dhe t\u00eb b\u00ebrit ndryshime n\u00eb t\u00eb q\u00eb ishte mjaft i ngadalt\u00eb (n\u00ebse dikush e ka arritur, na shkruani n\u00eb komentet si). Duhej t\u00eb shkonim tek analist\u00ebt dhe t'u thoshim: \u2018Djem, m\u00ebsoni python dhe shkoni n\u00eb kopjet analitike\u2019, por kjo \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb shtes\u00eb p\u00ebr rekrutimin e personelit dhe dukej se duhej ta evitonim, n\u00ebse \u00ebsht\u00eb e mundur. Vendos\u00ebm t\u00eb provonim teknologjin\u00eb FDW (Foreign Data Wrapper): n\u00eb thelb, kjo \u00ebsht\u00eb nj\u00eb standard dblink q\u00eb ekziston n\u00eb standardin SQL, por me nj\u00eb nd\u00ebrfaqe shum\u00eb m\u00eb t\u00eb p\u00ebrshtatshme. N\u00eb baz\u00eb t\u00eb saj ne zhvilluam nj\u00eb zgjidhje, e cila p\u00ebrfundimisht u konsolidua, dhe mbi t\u00eb u ndaluam. Detajet e saj jan\u00eb nj\u00eb tem\u00eb p\u00ebr nj\u00eb artikull t\u00eb ve\u00e7ant\u00eb, ndoshta edhe p\u00ebr m\u00eb shum\u00eb se nj\u00eb, pasi do t\u00eb doja t\u00eb flisja p\u00ebr shum\u00eb gj\u00ebra: nga sinkronizimi i skemave t\u00eb bazave deri te menaxhimi i aksesit dhe anonimizimi i t\u00eb dh\u00ebnave personale. Po ashtu, duhet t\u00eb sqarohet se kjo zgjidhje nuk \u00ebsht\u00eb nj\u00eb z\u00ebvend\u00ebsim p\u00ebr bazat e t\u00eb dh\u00ebnave analitike dhe depozet reale, ajo zgjidh vet\u00ebm nj\u00eb detyr\u00eb t\u00eb caktuar.<\/p>\n

N\u00eb nivelin e lart\u00eb, kjo duket k\u00ebshtu:<\/p>\n

\"Analitika
\nEkziston nj\u00eb baz\u00eb PostgreSQL, ku p\u00ebrdoruesit mund t\u00eb ruajn\u00eb t\u00eb dh\u00ebnat e tyre t\u00eb pun\u00ebs, dhe m\u00eb e r\u00ebnd\u00ebsishmja \u2013 n\u00eb k\u00ebt\u00eb baz\u00eb p\u00ebrmes FDW jan\u00eb lidhur kopjet analitike t\u00eb t\u00eb gjitha sh\u00ebrbimeve. Kjo mund\u00ebson t\u00eb shkruani nj\u00eb k\u00ebrkes\u00eb ndaj disa bazave, dhe nuk ka r\u00ebnd\u00ebsi \u00e7far\u00eb jan\u00eb: PostgreSQL, MySQL, MongoDB ose di\u00e7ka tjet\u00ebr (nj\u00eb skedar, API, n\u00ebse ndonj\u00ebher\u00eb nuk ka ndonj\u00eb avullues t\u00eb p\u00ebrshtatsh\u00ebm, mund t\u00eb shkruani tuajin). Duket mjaft e p\u00ebrshtatshme, apo jo? A duhet t\u00eb shp\u00ebrndahemi?<\/p>\n

Po t\u00eb ishte gjith\u00e7ka kaq e shpejt\u00eb dhe e thjesht\u00eb, ndoshta nuk do t\u00eb kishim asnj\u00eb artikull.<\/p>\n

\u00cbsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb kuptohet qart\u00eb se si PostgreSQL trajton k\u00ebrkesat ndaj server\u00ebve t\u00eb larg\u00ebt. Kjo duket logjike, megjithat\u00eb shpesh ne nuk i kushtojm\u00eb v\u00ebmendje: PostgreSQL ndan k\u00ebrkes\u00ebn n\u00eb pjes\u00eb, t\u00eb cilat ekzekutohen ndaras n\u00eb server\u00ebt e larg\u00ebt, mbledh k\u00ebto t\u00eb dh\u00ebna dhe llogaritjet p\u00ebrfundimtare i b\u00ebn vet\u00eb, p\u00ebr k\u00ebt\u00eb arsye shpejt\u00ebsia e ekzekutimit t\u00eb k\u00ebrkes\u00ebs do t\u00eb varet shum\u00eb nga m\u00ebnyra se si \u00ebsht\u00eb shkruar ajo. Duhet gjithashtu t\u00eb theksohet: kur t\u00eb dh\u00ebnat vijn\u00eb nga nj\u00eb server i larg\u00ebt, ato nuk kan\u00eb m\u00eb indekse, nuk ka asgj\u00eb q\u00eb do t\u00eb ndihmoj\u00eb planifikuesin, k\u00ebshtu q\u00eb mund ta ndihmojm\u00eb dhe t'i japim udh\u00ebzime vet\u00ebm ne vet\u00eb. Dhe pik\u00ebrisht p\u00ebr k\u00ebt\u00eb do doja t\u00eb flisja m\u00eb n\u00eb detaje.<\/p>\n

K\u00ebrkes\u00eb e thjesht\u00eb dhe plani me t\u00eb<\/h1>\n

\nP\u00ebr t\u00eb treguar se si PostgreSQL ekzekuton nj\u00eb k\u00ebrkes\u00eb n\u00eb nj\u00eb tabel\u00eb me 6 milion\u00eb rreshta n\u00eb distanc\u00eb server<\/a>, le t\u00eb shohim nj\u00eb plan t\u00eb thjesht\u00eb.<\/p>\n

shpjego analiza t\u00eb detajuar  \nZgjidhni count(1)\nNga fdw_schema.tabela;\n\nAggregat  (kostot=418383.23..418383.24 rreshta=1 gjer\u00ebsi=8) (koha aktuale=3857.198..3857.198 rreshta=1 loops=1)\n  Dalja: count(1)\n  ->  Skano i huaj mbi fdw_schema."tabela"  (kostot=100.00..402376.14 rreshta=6402838 gjer\u00ebsi=0) (koha aktuale=4.874..3256.511 rreshta=6406868 loops=1)\n        Dalja: "tabela".id, "tabela".is_active, "tabela".meta, "tabela".created_dt\n        SQL e larg\u00ebt: Zgjidh NULL Nga fdw_schema.tabela\nKoha e planifikimit: 0.986 ms\nKoha e ekzekutimit: 3857.436 ms<\/code><\/pre>\n
\nP\u00ebrdorimi i komand\u00ebs VERBOSE mund\u00ebson t\u00eb shohim k\u00ebrkes\u00ebn q\u00eb do t\u00eb d\u00ebrgohet n\u00eb serverin e larg\u00ebt dhe rezultatet e t\u00eb cilit do t\u00eb marrim p\u00ebr p\u00ebrpunim t\u00eb m\u00ebtejsh\u00ebm (rreshti RemoteSQL).<\/p>\n
T\u00eb shkojm\u00eb pak m\u00eb thell\u00eb dhe t\u00eb shtojm\u00eb n\u00eb k\u00ebrkes\u00ebn ton\u00eb disa filtra: nj\u00eb sipas boolean<\/b> fush\u00ebs, nj\u00eb sipas p\u00ebrfshirjes timestamp<\/b> n\u00eb interval dhe nj\u00eb sipas jsonb<\/b>.<\/p>\n
shpjego analiza t\u00eb detajuar\nZgjidhni count(1)\nNga fdw_schema.tabela \nKu is_active \u00ebsht\u00eb E VERTET\u00cb\nDhe created_dt MIDIS DAT\u00cbS S\u00cb TANISHME - INTERVAL '7 muaj' \nDhe DAT\u00cbS S\u00cb TANISHME - INTERVAL '6 muaj'\nDhe meta->>'burimi' = 'test';\n\nAggregat  (kostot=577487.69..577487.70 rreshta=1 gjer\u00ebsi=8) (koha aktuale=27473.818..25473.819 rreshta=1 loops=1)\n  Dalja: count(1)\n  ->  Skano i huaj mbi fdw_schema."tabela"  (kostot=100.00..577469.21 rreshta=7390 gjer\u00ebsi=0) (koha aktuale=31.369..25372.466 rreshta=1360025 loops=1)\n        Dalja: "tabela".id, "tabela".is_active, "tabela".meta, "tabela".created_dt\n        Filtri: (("tabela".is_active \u00cbSHT\u00cb E VERTET\u00cb) DHE (("tabela".meta ->> 'burimi'::tekst) = 'test'::tekst) DHE ("tabela".created_dt >= (('tan\u00eb'::cstring)::dat\u00eb - '7 muaj'::interval)) DHE ("tabela".created_dt <= ((('tan\u00eb'::cstring)::dat\u00eb)::timestamp me zon\u00eb kohore - '6 muaj'::interval)))\n        Rreshta t\u00eb hequra nga Filtri: 5046843\n        SQL e larg\u00ebt: Zgjidh created_dt, is_active, meta Nga fdw_schema.tabela\nKoha e planifikimit: 0.665 ms\nKoha e ekzekutimit: 27474.118 ms<\/code><\/pre>\n
\nK\u00ebtu \u00ebsht\u00eb momenti, n\u00eb t\u00eb cilin duhet t\u00eb kemi parasysh gjat\u00eb hartimit t\u00eb k\u00ebrkesave. Filtrat nuk u d\u00ebrguan n\u00eb serverin e larg\u00ebt, \u00e7ka do t\u00eb thot\u00eb se p\u00ebr t'u ekzekutuar PostgreSQL t\u00ebrheq t\u00eb gjitha 6 milion\u00eb rreshta, p\u00ebr t'i filtruar m\u00eb pas lokal (rreshti Filter) dhe p\u00ebr t\u00eb b\u00ebr\u00eb agregimin. \u00c7el\u00ebsi i suksesit \u00ebsht\u00eb t\u00eb hartojm\u00eb k\u00ebrkes\u00ebn n\u00eb m\u00ebnyr\u00eb q\u00eb filtrat t\u00eb d\u00ebrgohen n\u00eb makin\u00eb, e q\u00eb ne t\u00eb marrim dhe t\u00eb agregojm\u00eb vet\u00ebm rreshtat e nevojsh\u00ebm. <\/p>\n
Kjo \u00ebsht\u00eb nj\u00eb marr\u00ebzi e v\u00ebrtet\u00eb<\/h1>\n
\nMe fushat boolean \u00ebsht\u00eb shum\u00eb e thjesht\u00eb. Problemi n\u00eb k\u00ebrkes\u00ebn origjinale ndodhi p\u00ebr shkak t\u00eb operatorit is<\/b>. N\u00ebse e z\u00ebvend\u00ebsojm\u00eb at\u00eb me =<\/b>, at\u00ebher\u00eb ne do t\u00eb marrim rezultatin e m\u00ebposht\u00ebm:<\/p>\n
shpjego analiza t\u00eb detajuar\nZgjidhni count(1)\nNga fdw_schema.tabela\nKu is_active = E VERTET\u00cb\nDhe created_dt MIDIS DAT\u00cbS S\u00cb TANISHME - INTERVAL '7 muaj' \nDhe DAT\u00cbS S\u00cb TANISHME - INTERVAL '6 muaj'\nDhe meta->>'burimi' = 'test';\n\nAggregat  (kostot=508010.14..508010.15 rreshta=1 gjer\u00ebsi=8) (koha aktuale=19064.314..19064.314 rreshta=1 loops=1)\n  Dalja: count(1)\n  ->  Skano i huaj mbi fdw_schema."tabela"  (kostot=100.00..507988.44 rreshta=8679 gjer\u00ebsi=0) (koha aktuale=33.035..18951.278 rreshta=1360025 loops=1)\n        Dalja: "tabela".id, "tabela".is_active, "tabela".meta, "tabela".created_dt\n        Filtri: ((("tabela".meta ->> 'burimi'::tekst) = 'test'::tekst) DHE ("tabela".created_dt >= (('tan\u00eb'::cstring)::dat\u00eb - '7 muaj'::interval)) DHE ("tabela".created_dt <= ((('tan\u00eb'::cstring)::dat\u00eb)::timestamp me zon\u00eb kohore - '6 muaj'::interval)))\n        Rreshta t\u00eb hequra nga Filtri: 3567989\n        SQL e larg\u00ebt: Zgjidh created_dt, meta Nga fdw_schema.tabela Ku (is_active)\nKoha e planifikimit: 0.834 ms\nKoha e ekzekutimit: 19064.534 ms<\/code><\/pre>\n
\nSi\u00e7 e shihni, filtri kaloi n\u00eb serverin e larg\u00ebt, dhe koha e ekzekutimit u reduktua nga 27 n\u00eb 19 sekonda. <\/p>\n
Duhet theksuar se operatori is<\/b> dalloj\u00eb nga operatori =<\/b> n\u00eb at\u00eb q\u00eb ai mund t\u00eb punoj\u00eb me vler\u00ebn Null. Kjo do t\u00eb thot\u00eb se is not True<\/b> n\u00eb filtri do t\u00eb l\u00ebr\u00eb vlera False dhe Null, nd\u00ebrsa != True<\/b> do t\u00eb l\u00ebr\u00eb vet\u00ebm vlera False. Prandaj, kur z\u00ebvend\u00ebsoni operatorin is not<\/b> duhet t\u00eb kaloni n\u00eb filtrin dy kushte me operatorin OR, p\u00ebr shembull, WHERE (col != True) OR (col is null)<\/b>.<\/p>\n
Kemi kuptuar p\u00ebr boolean, tani vazhdojm\u00eb m\u00eb tej. Por le t\u00eb kthejm\u00eb filtrin mbi vler\u00ebn boolean n\u00eb versionin e tij origjinal, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb shqyrtojm\u00eb efektin nga ndryshimet e tjera.<\/p>\n
timestamptz? hz<\/h1>\n
\nN\u00eb t\u00eb v\u00ebrtet\u00eb, shpesh \u00ebsht\u00eb e nevojshme t\u00eb eksperimentosh me m\u00ebnyr\u00ebn se si t\u00eb shkruash nj\u00eb k\u00ebrkes\u00eb q\u00eb p\u00ebrfshin server\u00eb t\u00eb larg\u00ebt dhe pastaj t\u00eb k\u00ebrkosh shpjegimin p\u00ebrse ndodhin gj\u00ebrat n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb. Ka shum\u00eb pak informacion mbi k\u00ebt\u00eb tem\u00eb n\u00eb Internet. N\u00eb eksperimentet tona, zbuluam se filtrimi me nj\u00eb dat\u00eb fikse kalon me sukses n\u00eb serverin e larg\u00ebt, nd\u00ebrsa kur duam t\u00eb caktuar nj\u00eb dat\u00eb dinamikisht, si p.sh., now() ose CURRENT_DATE, kjo nuk ndodh. N\u00eb shembullin ton\u00eb, ne shtuam nj\u00eb filtri t\u00eb till\u00eb q\u00eb kolona created_at p\u00ebrmban t\u00eb dh\u00ebna q\u00eb jan\u00eb sakt\u00ebsisht p\u00ebr 1 muaj mbrapa (MIDIS DAT\u00cbS S\u00cb TANISHME - INTERVAL ‘7 muaj’ DHE DAT\u00cbS S\u00cb TANISHME - INTERVAL ‘6 muaj’). \u00c7far\u00eb kemi nd\u00ebrmarr\u00eb n\u00eb k\u00ebt\u00eb rast?<\/p>\n
shpjegoni analizen me detaje\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active is True\nAND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 muaj') \nAND created_dt >'source' = 'test';\n\nAgregat\u00eb  (cost=306875.17..306875.18 rows=1 width=8) (koha aktuale 4789.114..4789.115 rows=1 loops=1)\n  Output: count(1)\n  InitPlan 1 (kthen $0)\n    ->  Result  (cost=0.00..0.02 rows=1 width=8) (koha aktuale 0.007..0.008 rows=1 loops=1)\n          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 muaj'::interval)\n  InitPlan 2 (kthen $1)\n    ->  Result  (cost=0.00..0.02 rows=1 width=8) (koha aktuale 0.002..0.002 rows=1 loops=1)\n          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 muaj'::interval)\n  ->  Skani i Huaj mbi fdw_schema.\"table\"  (cost=100.02..306874.86 rows=105 width=0) (koha aktuale 23.475..4681.419 rows=1360025 loops=1)\n        Output: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        Filtri: ((\"table\".is_active IS TRUE) AND ((\"table\".meta ->> 'source'::text) = 'test'::text))\n        Rreshtat e hequr nga Filtri: 76934\n        SQL e Larg\u00ebt: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))\nKoha e planifikimit: 0.703 ms\nKoha e ekzekutimit: 4789.379 ms<\/code><\/pre>\n
\nKemi sugjeruar planifikuesit t\u00eb llogaris\u00eb paraprakisht dat\u00ebn n\u00eb n\u00ebnk\u00ebrkes\u00eb dhe t\u00eb kaloj\u00eb nj\u00eb variab\u00ebl t\u00eb gatshme n\u00eb filt\u00ebr. Dhe kjo sugjerim na dha nj\u00eb rezultat t\u00eb shk\u00eblqyer, k\u00ebrkesa u b\u00eb m\u00eb e shpejt\u00eb pothuajse 6 her\u00eb!<\/p>\n
Edhe nj\u00ebher\u00eb, k\u00ebtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb jeni t\u00eb kujdessh\u00ebm: tipi i t\u00eb dh\u00ebnave n\u00eb n\u00ebnk\u00ebrkes\u00eb duhet t\u00eb jet\u00eb i nj\u00ebjt\u00eb si ai i fush\u00ebs, mbi t\u00eb cil\u00ebn filtrojm\u00eb; ndryshe planifikuesi do t\u00eb vendos\u00eb se tipet jan\u00eb t\u00eb ndryshme dhe \u00ebsht\u00eb e nevojshme s\u00eb pari t\u00eb nxjerr\u00eb t\u00eb gjitha t\u00eb dh\u00ebnat dhe m\u00eb pas t\u00eb filtroj\u00eb lokal.<\/p>\n
T\u00eb rikthejm\u00eb filtrin p\u00ebr dat\u00ebn n\u00eb vler\u00ebn e tij fillestare.<\/p>\n
Freddy kund\u00ebr Jsonb<\/h1>\n
\nN\u00eb p\u00ebrgjith\u00ebsi, fushat Boolean dhe datat tashm\u00eb e p\u00ebrshpejtuar k\u00ebrkes\u00ebn ton\u00eb, megjithat\u00eb kishte edhe nj\u00eb tip t\u00eb dh\u00ebnash tjet\u00ebr. Lufta me filtrimin mbi t\u00eb, p\u00ebr ta th\u00ebn\u00eb t\u00eb drejt\u00ebn, ende nuk \u00ebsht\u00eb p\u00ebrfunduar; megjithat\u00eb k\u00ebtu ka dhe suksese. Pra, ja si na rezultoi t\u00eb kalojm\u00eb filtrin mbi jsonb<\/b> fush\u00ebn n\u00eb serverin e larg\u00ebt.<\/p>\n
shpjegoni analizen me detaje\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active is True\nAND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 muaj' \nAND CURRENT_DATE - INTERVAL '6 muaj'\nAND meta @> '{\"source\":\"test\"}'::jsonb;\n\nAgregat\u00eb  (cost=245463.60..245463.61 rows=1 width=8) (koha aktuale 6727.589..6727.590 rows=1 loops=1)\n  Output: count(1)\n  ->  Skani i Huaj mbi fdw_schema.\"table\"  (cost=1100.00..245459.90 rows=1478 width=0) (koha aktuale 16.213..6634.794 rows=1360025 loops=1)\n        Output: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        Filtri: ((\"table\".is_active IS TRUE) AND (\"table\".created_dt >= (('now'::cstring)::date - '7 muaj'::interval)) AND (\"table\".created_dt  '{\"source\": \"test\"}'::jsonb))\nKoha e planifikimit: 0.747 ms\nKoha e ekzekutimit: 6727.815 ms<\/code><\/pre>\n
\nN\u00eb vend t\u00eb operator\u00ebve t\u00eb filtrimit, duhet p\u00ebrdorur operatori i pranis\u00eb s\u00eb nj\u00eb jsonb<\/b> n\u00eb nj\u00eb tjet\u00ebr. 7 sekonda n\u00eb vend t\u00eb 29. Deri tani, kjo \u00ebsht\u00eb alternativa e vetme e suksesshme p\u00ebr transferimin e filtrave nga jsonb<\/b> n\u00eb serverin e larg\u00ebt, por k\u00ebtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb merret parasysh nj\u00eb kufizim: ne po p\u00ebrdorim versionin e baz\u00ebs 9.6, megjithat\u00eb deri n\u00eb fund t\u00eb prillit planifikojm\u00eb t\u00eb p\u00ebrfundojm\u00eb testet e fundit dhe t\u00eb kalojm\u00eb n\u00eb versionin 12. Pasi t\u00eb p\u00ebrdit\u00ebsohemi, do t\u00eb shkruajm\u00eb se si ndikon kjo, sepse ndryshimet p\u00ebr t\u00eb cilat ka shum\u00eb shpres\u00eb jan\u00eb mjaft t\u00eb shumta: json_path, sjellja e re e CTE, push down (e pranishme q\u00eb nga versione 10). E presim me padurim.<\/p>\n
Finish him<\/h1>\n
\nNe kemi kontroluar se si \u00e7do ndryshim ndikon n\u00eb shpejt\u00ebsin\u00eb e k\u00ebrkes\u00ebs ve\u00e7mas. Le t\u00eb shikojm\u00eb tani se \u00e7far\u00eb do t\u00eb ndodh\u00eb kur t\u00eb tre filtrat t\u00eb shkruhen si\u00e7 duhet.<\/p>\n
shpjegoni analiz\u00ebn e detajuar\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active = True\nAND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 muaj') \nAND created_dt  '{\"source\":\"test\"}'::jsonb;\n\nAgregat  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)\n  Dalja: count(1)\n  InitPlan 1 (kthen $0)\n    ->  Rezultat  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)\n          Dalja: ((('tani'::cstring)::date)::timestamp with time zone - '7 muaj'::interval)\n  InitPlan 2 (kthen $1)\n    ->  Rezultat  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)\n          Dalja: ((('tani'::cstring)::date)::timestamp with time zone - '6 muaj'::interval)\n  ->  Skans i Huaj n\u00eb fdw_schema.\"table\"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)\n        Dalja: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        SQL i Larg\u00ebt: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt  '{\"source\": \"test\"}'::jsonb))\nKoha e planifikimit: 0.820 ms\nKoha e ekzekutimit: 2279.087 ms<\/code><\/pre>\n
\nPo, k\u00ebrkesa duket m\u00eb e komplikuar, kjo \u00ebsht\u00eb nj\u00eb \u00e7mim i detyruar, por shpejt\u00ebsia e ekzekutimit arrin 2 sekonda, q\u00eb \u00ebsht\u00eb m\u00eb shum\u00eb se 10 her\u00eb m\u00eb shpejt! Dhe po flasim p\u00ebr nj\u00eb k\u00ebrkes\u00eb shum\u00eb t\u00eb thjesht\u00eb ndaj nj\u00eb s\u00ebr\u00eb t\u00eb dh\u00ebnash relativisht t\u00eb vogla. N\u00eb k\u00ebrkesat reale kemi marr\u00eb p\u00ebrmir\u00ebsime deri n\u00eb disa qindra her\u00eb.<\/p>\n
T\u00eb p\u00ebrmendim p\u00ebrmbledhjen: n\u00ebse po p\u00ebrdorni PostgreSQL me FDW, gjithmon\u00eb kontrolloni n\u00ebse t\u00eb gjith\u00eb filtrat d\u00ebrgohen n\u00eb serverin e larg\u00ebt, dhe do t\u00eb keni fat... T\u00eb pakt\u00ebn derisa t\u00eb arrini n\u00eb join-et nd\u00ebrmjet tabelave nga tabela t\u00eb ndryshme server\u00ebsh<\/a>. Por kjo \u00ebsht\u00eb nj\u00eb histori p\u00ebr nj\u00eb artikull tjet\u00ebr.<\/p>\n
Faleminderit p\u00ebr v\u00ebmendjen! Do t\u00eb isha i g\u00ebzuar t\u00eb d\u00ebgjoja pyetje, komente, si dhe histori p\u00ebr p\u00ebrvojat tuaja n\u00eb komentet.
\n
Burimi: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u043d\u0430\u044f \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0430, \u043a\u0430\u043a \u0438 \u0432\u0441\u0435 \u0432 \u044d\u0442\u043e\u043c \u043c\u0438\u0440\u0435, \u0438\u043c\u0435\u0435\u0442 \u0441\u0432\u043e\u0438 \u043f\u043b\u044e\u0441\u044b \u0438 \u0441\u0432\u043e\u0438 \u043c\u0438\u043d\u0443\u0441\u044b. \u041e\u0434\u043d\u0438 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b \u0441 \u043d\u0435\u0439 \u0441\u0442\u0430\u043d\u043e\u0432\u044f\u0442\u0441\u044f \u043f\u0440\u043e\u0449\u0435, \u0434\u0440\u0443\u0433\u0438\u0435 \u2014 \u0441\u043b\u043e\u0436\u043d\u0435\u0435. \u0418 \u0432 \u0443\u0433\u043e\u0434\u0443 \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u0438 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 \u0438 \u043b\u0443\u0447\u0448\u0435\u0439 \u043c\u0430\u0441\u0448\u0442\u0430\u0431\u0438\u0440\u0443\u0435\u043c\u043e\u0441\u0442\u0438 \u043d\u0443\u0436\u043d\u043e \u043f\u0440\u0438\u043d\u043e\u0441\u0438\u0442\u044c \u0441\u0432\u043e\u0438 \u0436\u0435\u0440\u0442\u0432\u044b. \u041e\u0434\u043d\u0430 \u0438\u0437 \u043d\u0438\u0445 \u2014 \u0443\u0441\u043b\u043e\u0436\u043d\u0435\u043d\u0438\u0435 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0438. \u0415\u0441\u043b\u0438 \u0432 \u043c\u043e\u043d\u043e\u043b\u0438\u0442\u0435 \u0432\u0441\u044e \u043e\u043f\u0435\u0440\u0430\u0442\u0438\u0432\u043d\u0443\u044e \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0443 \u043c\u043e\u0436\u043d\u043e \u0441\u0432\u0435\u0441\u0442\u0438 \u043a SQL \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u043c \u043a \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0435, […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":79468,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-79467","post","type-post","status-publish","format-standard","has-post-thumbnail","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\udd47Analiza operativ\u00eb n\u00eb arkitektur\u00ebn mikroservis: ndihmon dhe sugjeron Postgres FDW | ProHoster","description":"Arkitektura mikrosh\u00ebrbimeve, si\u00e7 \u00ebsht\u00eb gjith\u00e7ka n\u00eb k\u00ebt\u00eb bot\u00eb, ka pluset dhe minuset e saj. Disa procese b\u00ebhen m\u00eb t\u00eb lehta, t\u00eb tjerat \u2014 m\u00eb t\u00eb v\u00ebshtira.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/operativnaya-analitika-v-mikroservisnoj-arhitekture-p%cc%b6o%cc%b6n%cc%b6ya%cc%b6t%cc%b6%cc%b6-%cc%b6i%cc%b6-%cc%b6p%cc%b6r%cc%b6o%cc%b6s%cc%b6t%cc%b6i%cc%b6t%cc%b6%cc%b6-pomoch-i-podskazat-postgres","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\u041e\u043f\u0435\u0440\u0430\u0442\u0438\u0432\u043d\u0430\u044f \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0430 \u0432 \u043c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u043d\u043e\u0439 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0435: \u043f\u0336\u043e\u0336\u043d\u0336\u044f\u0336\u0442\u0336\u044c\u0336 \u0336\u0438\u0336 \u0336\u043f\u0336\u0440\u0336\u043e\u0336\u0441\u0336\u0442\u0336\u0438\u0336\u0442\u0336\u044c\u0336 \u043f\u043e\u043c\u043e\u0447\u044c \u0438 \u043f\u043e\u0434\u0441\u043a\u0430\u0437\u0430\u0442\u044c Postgres FDW | ProHoster","og:description":"\u041c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u043d\u0430\u044f \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0430, \u043a\u0430\u043a \u0438 \u0432\u0441\u0435 \u0432 \u044d\u0442\u043e\u043c \u043c\u0438\u0440\u0435, \u0438\u043c\u0435\u0435\u0442 \u0441\u0432\u043e\u0438 \u043f\u043b\u044e\u0441\u044b \u0438 \u0441\u0432\u043e\u0438 \u043c\u0438\u043d\u0443\u0441\u044b. \u041e\u0434\u043d\u0438 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b \u0441 \u043d\u0435\u0439 \u0441\u0442\u0430\u043d\u043e\u0432\u044f\u0442\u0441\u044f \u043f\u0440\u043e\u0449\u0435, \u0434\u0440\u0443\u0433\u0438\u0435 \u2014 \u0441\u043b\u043e\u0436\u043d\u0435\u0435.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/operativnaya-analitika-v-mikroservisnoj-arhitekture-p%cc%b6o%cc%b6n%cc%b6ya%cc%b6t%cc%b6%cc%b6-%cc%b6i%cc%b6-%cc%b6p%cc%b6r%cc%b6o%cc%b6s%cc%b6t%cc%b6i%cc%b6t%cc%b6%cc%b6-pomoch-i-podskazat-postgres","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":"2020-04-27T05:42:23+00:00","article:modified_time":"2020-04-27T05:42:23+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"79467","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":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 16:37:24","updated":"2026-02-09 21:38:01","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/79467","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=79467"}],"version-history":[{"count":2,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/79467\/revisions"}],"predecessor-version":[{"id":159871,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/79467\/revisions\/159871"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/79468"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=79467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=79467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=79467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}