{"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":"Analiza operative n\u00eb arkitektur\u00ebn mikrosh\u00ebrbimeve: t\u00eb ndihmoj\u00eb dhe udh\u00ebzoj\u00eb Postgres FDW","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Arkitektura mikroserve, si \u00e7do gj\u00eb tjet\u00ebr n\u00eb k\u00ebt\u00eb bot\u00eb, ka avantazhet dhe disavantazhet e saj. Disa procese b\u00ebhen m\u00eb t\u00eb lehta me t\u00eb, nd\u00ebrsa t\u00eb tjerat b\u00ebhen m\u00eb t\u00eb komplikuara. P\u00ebr t'u p\u00ebrshtatur me shpejt\u00ebsin\u00eb e ndryshimeve dhe p\u00ebr shkak t\u00eb shkall\u00ebzueshm\u00ebris\u00eb m\u00eb t\u00eb mir\u00eb, \u00ebsht\u00eb e nevojshme t\u00eb b\u00ebhen disa sakrifica. Nj\u00eb prej tyre \u00ebsht\u00eb komplikuar analiza. N\u00eb nj\u00eb arkitektur\u00eb monolite, gjith\u00eb analiza operative mund t\u00eb reduktohet n\u00eb pyetje SQL p\u00ebr replik\u00ebn analitike, por n\u00eb arkitektur\u00ebn me shum\u00eb sh\u00ebrbime, \u00e7do sh\u00ebrbim ka baz\u00ebn e vet, dhe duket se nuk mjafton nj\u00eb pyetje (apo ndoshta mjafton?). P\u00ebr ata q\u00eb jan\u00eb t\u00eb interesuar se si e zgjidh\u00ebm problemin e analiz\u00ebs operative n\u00eb kompanin\u00eb ton\u00eb dhe si m\u00ebsuam t\u00eb jetojm\u00eb me k\u00ebt\u00eb zgjidhje \u2014 mir\u00ebserdh\u00ebt.<\/p>\n

\"Analiza
\nM\u00eb quajn\u00eb Pavel Siva\u0161, n\u00eb DomClick punoj n\u00eb ekipin q\u00eb \u00ebsht\u00eb p\u00ebrgjegj\u00ebs p\u00ebr mb\u00ebshtetje t\u00eb depozit\u00ebs analitike t\u00eb t\u00eb dh\u00ebnave. N\u00eb m\u00ebnyr\u00eb t\u00eb kusht\u00ebzuar, aktiviteti yn\u00eb mund t\u00eb klasifikohet si inxhinieri e 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 inxhinierin\u00eb e t\u00eb dh\u00ebnave ETL\/ELT, mb\u00ebshtetje dhe adaptim t\u00eb mjeteve p\u00ebr analiz\u00ebn e t\u00eb dh\u00ebnave dhe zhvillimi i mjeteve tona. N\u00eb ve\u00e7anti, p\u00ebr raportimin operativ kemi vendosur 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 t\u00eb v\u00ebrtet\u00eb, ne shqyrtuam disa mund\u00ebsi. Mund t\u00eb ishim nd\u00ebrtuar nj\u00eb depo t\u00eb plot\u00eb \u2014 madje e provuam, por, p\u00ebr t\u00eb qen\u00eb t\u00eb sinqert\u00eb, nuk arrit\u00ebm ta pajtonim ndryshimet e shpeshta n\u00eb logjik\u00eb me procesin mjaft t\u00eb ngadalt\u00eb t\u00eb nd\u00ebrtimit t\u00eb depo dhe p\u00ebrfshirjes s\u00eb ndryshimeve n\u00eb t\u00eb (n\u00ebse dikush e arriti k\u00ebt\u00eb, na shkruani n\u00eb komentet si). Mund t\u00eb kishim th\u00ebn\u00eb analist\u00ebve: \"Djem, m\u00ebsoni python dhe shkoni n\u00eb replikat analitike\", por kjo do t\u00eb ishte nj\u00eb k\u00ebrkes\u00eb shtes\u00eb p\u00ebr pun\u00ebsimin e personelit, dhe dukej se duhej ta evitonim k\u00ebt\u00eb, n\u00ebse ishte e mundur. Vendos\u00ebm t\u00eb provonim teknologjin\u00eb FDW (Foreign Data Wrapper): n\u00eb thelb, \u00ebsht\u00eb nj\u00eb dblink standard 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 krijuam nj\u00eb zgjidhje, e cila p\u00ebrfundimisht mbeti n\u00eb p\u00ebrdorim. 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 dua t\u00eb flas mbi shum\u00eb aspekte: nga sinkronizimi i skemave t\u00eb bazave deri te menaxhimi i aksesit dhe anonimizimi i t\u00eb dh\u00ebnave personale. Gjithashtu, duhet t\u00eb sakt\u00ebsoj se kjo zgjidhje nuk \u00ebsht\u00eb nj\u00eb z\u00ebvend\u00ebsim p\u00ebr bazat analitike t\u00eb v\u00ebrteta dhe depo, ajo zgjidh vet\u00ebm nj\u00eb problem t\u00eb caktuar.<\/p>\n

K\u00ebshtu duket n\u00eb nivelin m\u00eb t\u00eb lart\u00eb:<\/p>\n

\"Analiza
\nKa 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 \u2014 n\u00eb k\u00ebt\u00eb baz\u00eb jan\u00eb t\u00eb lidhura replikat analitike t\u00eb t\u00eb gjitha sh\u00ebrbimeve n\u00ebp\u00ebrmjet FDW. Kjo jep mund\u00ebsin\u00eb p\u00ebr t\u00eb shkruar nj\u00eb k\u00ebrkes\u00eb p\u00ebr disa baza, pa marr\u00eb parasysh se \u00e7far\u00eb jan\u00eb ato: PostgreSQL, MySQL, MongoDB, ose di\u00e7ka tjet\u00ebr (skedar, API, n\u00ebse ndonj\u00ebher\u00eb nuk ka nj\u00eb vrapues t\u00eb p\u00ebrshtatsh\u00ebm, mund t\u00eb shkruajm\u00eb nj\u00eb tonin tonin tonin t\u00eb vetin). E duket se \u00ebsht\u00eb gjith\u00e7ka, super! A do t\u00eb dal\u00eb t\u00eb gjith\u00eb?<\/p>\n

N\u00ebse gjith\u00e7ka do t\u00eb p\u00ebrfundonte aq shpejt dhe thjesht, ndoshta nuk do t\u00eb kishte artikuj.<\/p>\n

\u00cbsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb kuptohet qart\u00eb se si PostgreSQL p\u00ebrpunon k\u00ebrkesat p\u00ebr server\u00eb t\u00eb larg\u00ebt. Kjo duket logjike, megjithat\u00eb shpesh nuk i kushtohet v\u00ebmendje: PostgreSQL ndan k\u00ebrkes\u00ebn n\u00eb pjes\u00eb q\u00eb ekzekutohen n\u00eb server\u00eb t\u00eb larg\u00ebt n\u00eb m\u00ebnyr\u00eb t\u00eb pavarur, mbledh t\u00eb dh\u00ebnat dhe kryen llogaritjet p\u00ebrfundimtare vet\u00eb, prandaj 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 i ndihmon planifikuesit, p\u00ebrve\u00e7se ne vet\u00eb. Dhe pik\u00ebrisht p\u00ebr k\u00ebt\u00eb do t\u00eb d\u00ebshironim t\u00eb flasim m\u00eb shum\u00eb.<\/p>\n

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

\nP\u00ebr t\u00eb ilustruar se si Postgres ekzekuton nj\u00eb k\u00ebrkes\u00eb n\u00eb nj\u00eb tabel\u00eb me 6 milion rreshta n\u00eb distanc\u00eb serveri<\/a>, le t\u00eb shikojm\u00eb nj\u00eb plan t\u00eb thjesht\u00eb.<\/p>\n

shpjegoni analizoni n\u00eb detaje  \nZgjidhni count(1)\nK\u00cbNDSHM\u00cb fdw_schema.tabela;\n\nAgregat  (koshti=418383.23..418383.24 radh\u00eb=1 gjer\u00ebsi=8) (koha reale=3857.198..3857.198 radh\u00eb=1 loops=1)\n  Dalja: count(1)\n  ->  Skano i huaj mbi fdw_schema."tabela"  (koshti=100.00..402376.14 radh\u00eb=6402838 gjer\u00ebsi=0) (koha reale=4.874..3256.511 radh\u00eb=6406868 loops=1)\n        Dalja: "tabela".id, "tabela".is_active, "tabela".meta, "tabela".created_dt\n        SQL i larg\u00ebt: Zgjidhni NULL nga fdw_schema.tabela\nKoha e planifikimit: 0.986 ms\nKoha e ekzekutimit: 3857.436 ms<\/code><\/pre>\n
\nP\u00ebrdorimi i instrukcionit VERBOSE lejon t\u00eb shohim k\u00ebrkes\u00ebn q\u00eb do t\u00eb d\u00ebrgohet n\u00eb serverin e larg\u00ebt dhe rezultatet t\u00eb cilat do t\u00eb marrim p\u00ebr p\u00ebrpunim t\u00eb m\u00ebtejsh\u00ebm (rreshti RemoteSQL).<\/p>\n
Le t\u00eb shkojm\u00eb pak m\u00eb tutje dhe t\u00eb shtojm\u00eb disa filtra n\u00eb k\u00ebrkes\u00ebn ton\u00eb: nj\u00eb p\u00ebr boolean<\/b> fush\u00ebn, nj\u00eb p\u00ebr p\u00ebrfshirjen timestamp<\/b> n\u00eb interval dhe nj\u00eb p\u00ebr jsonb<\/b>.<\/p>\n
shpjegoni analizoni n\u00eb detaje\nZgjidhni count(1)\nK\u00cbNDSHM\u00cb fdw_schema.tabela \nKU is_active \u00ebsht\u00eb e V\u00ebrtet\u00eb\nDHE created_dt ND\u00cbRGJEGJA CURRENT_DATE - INTERVAL '7 muaj' \nDHE CURRENT_DATE - INTERVAL '6 muaj'\nDHE meta->>'source' = 'test';\n\nAgregat  (koshti=577487.69..577487.70 radh\u00eb=1 gjer\u00ebsi=8) (koha reale=27473.818..25473.819 radh\u00eb=1 loops=1)\n  Dalja: count(1)\n  ->  Skano i huaj mbi fdw_schema."tabela"  (koshti=100.00..577469.21 radh\u00eb=7390 gjer\u00ebsi=0) (koha reale=31.369..25372.466 radh\u00eb=1360025 loops=1)\n        Dalja: "tabela".id, "tabela".is_active, "tabela".meta, "tabela".created_dt\n        Filtri: (("tabela".is_active \u00cbSHT\u00cb E V\u00cbRTET\u00cb) DHE (("tabela".meta ->> 'source'::text) = 'test'::text) DHE ("tabela".created_dt >= (('tani'::cstring)::date - '7 muaj'::interval)) DHE ("tabela".created_dt <= ((('tani'::cstring)::date)::timestamp me zon\u00ebn e koh\u00ebs - '6 muaj'::interval)))\n        Radh\u00ebt e hequra nga Filtri: 5046843\n        SQL i larg\u00ebt: Zgjidhni created_dt, is_active, meta nga fdw_schema.tabela\nKoha e planifikimit: 0.665 ms\nKoha e ekzekutimit: 27474.118 ms<\/code><\/pre>\n
\nPik\u00ebrisht k\u00ebtu \u00ebsht\u00eb momenti, p\u00ebr t\u00eb cilin duhet t\u00eb kushtohet v\u00ebmendje gjat\u00eb hartimit t\u00eb k\u00ebrkesave. Filtrit nuk iu kaluan n\u00eb serverin e larg\u00ebt, q\u00eb do t\u00eb thot\u00eb se p\u00ebr ekzekutimin e tij, Postgres merr t\u00eb gjitha 6 milion rreshta p\u00ebr t\u00eb filtruar m\u00eb pas lokal (rreshti Filtri) dhe p\u00ebr t\u00eb kryer agregimin. Sekreti i suksesit \u00ebsht\u00eb t\u00eb shkruhet k\u00ebrkesa n\u00eb m\u00ebnyr\u00eb q\u00eb filtrat t\u00eb kalojn\u00eb n\u00eb makin\u00ebn e larg\u00ebt, dhe ne t\u00eb marrim dhe agregojm\u00eb vet\u00ebm rreshtat e nevojsh\u00ebm. <\/p>\n
Kjo \u00ebsht\u00eb disa booleanshit<\/h1>\n
\nMe fushat boolean \u2014 gjith\u00e7ka \u00ebsht\u00eb e thjesht\u00eb. N\u00eb k\u00ebrkes\u00ebn origjinale problemi lindte p\u00ebr shkak t\u00eb operatorit \u00ebsht\u00eb<\/b>. N\u00ebse e ndryshojm\u00eb at\u00eb n\u00eb =<\/b>, do t\u00eb marrim rezultatin e m\u00ebposht\u00ebm:<\/p>\n
shpjego analiz\u00ebn e holl\u00ebsishme\nSELECT count(1)\nFROM fdw_schema.tabela\nWHERE is_active = True\nAND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 muaj' \nAND CURRENT_DATE - INTERVAL '6 muaj'\nAND meta->>'source' = 'test';\n\nAgregati  (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 t\u00eb 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 ->> 'source'::text) = 'test'::text) AND ("tabela".created_dt >= (('tani'::cstring)::date - '7 muaj'::interval)) AND ("tabela".created_dt <= ((('tani'::cstring)::date)::timestamp me zon\u00ebn e koh\u00ebs - '6 muaj'::interval)))\n        Rreshtat e hequra nga Filtri: 3567989\n        SQL e Larg\u00ebt: SELECT created_dt, meta FROM fdw_schema.tabela WHERE (is_active)\nKoha e planifikimit: 0.834 ms\nKoha e ekzekutimit: 19064.534 ms<\/code><\/pre>\n
\nSi\u00e7 e shihni, filtri shkoi n\u00eb serverin e larg\u00ebt, dhe koha e ekzekutimit u ul nga 27 n\u00eb 19 sekonda. <\/p>\n
Vlen t\u00eb theksohet se operatori \u00ebsht\u00eb<\/b> diferon nga operatori =<\/b> p\u00ebr faktin se di t\u00eb punoj\u00eb me vler\u00ebn Null. Kjo do t\u00eb thot\u00eb se is not True<\/b> n\u00eb filtrin do t\u00eb l\u00ebr\u00eb vlerat False dhe Null, nd\u00ebrsa != True<\/b> do t\u00eb l\u00ebr\u00eb vet\u00ebm vlerat False. Prandaj, kur z\u00ebvend\u00ebsohet operatori is not<\/b> duhet t\u00eb kaloni dy kushte n\u00eb filtrin me operatorin OR, p\u00ebr shembull, WHERE (col != True) OR (col is null)<\/b>.<\/p>\n
Me boolean jemi zotuar, le t\u00eb vazhdojm\u00eb. Nd\u00ebrkoh\u00eb, do ta kthejm\u00eb filtrin e vler\u00ebs boolean n\u00eb form\u00ebn e tij origjinale, p\u00ebr t\u00eb shqyrtuar veprimin e ndryshimeve t\u00eb tjera.<\/p>\n
timestamptz? hz<\/h1>\n
\nK\u00ebshtu, shpesh na duhet t\u00eb eksperimentojm\u00eb me m\u00ebnyr\u00ebn e sakt\u00eb t\u00eb shkruarjes s\u00eb k\u00ebrkesave q\u00eb p\u00ebrfshijn\u00eb servera t\u00eb larg\u00ebt, dhe m\u00eb pas t\u00eb k\u00ebrkojm\u00eb shpjegime pse ndodhin pik\u00ebrisht k\u00ebshtu. Informacioni n\u00eb internet p\u00ebr k\u00ebt\u00eb tem\u00eb \u00ebsht\u00eb shum\u00eb i kufizuar. Gjat\u00eb eksperimenteve, zbuluam se filtri p\u00ebr datat e fikshme shkon me sukses n\u00eb serverin e larg\u00ebt, por kur d\u00ebshirojm\u00eb t\u00eb caktojm\u00eb datat dinamikisht, p.sh. now() ose CURRENT_DATE, kjo nuk ndodh. N\u00eb shembullin ton\u00eb, ne e shtuam nj\u00eb filtru t\u00eb till\u00eb, q\u00eb kolona created_at p\u00ebrmbante t\u00eb dh\u00ebna pik\u00ebrisht p\u00ebr 1 muaj m\u00eb par\u00eb (BETWEEN CURRENT_DATE \u2014 INTERVAL '7 month' AND CURRENT_DATE \u2014 INTERVAL '6 month'). \u00c7far\u00eb kemi b\u00ebr\u00eb ne n\u00eb k\u00ebt\u00eb rast?<\/p>\n
shpjego analiz\u00ebn n\u00eb 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 (cost=306875.17..306875.18 rows=1 width=8) (kosto reale 4789.114..4789.115 rreshta=1 loops=1)\n  Nxjerrja: count(1)\n  InitPlan 1 (kthen $0)\n    ->  Rezultati (cost=0.00..0.02 rows=1 width=8) (kosto reale 0.007..0.008 rreshta=1 loops=1)\n          Nxjerrja: ((('tani'::cstring)::date)::timestamp me zona kohore - '7 muaj'::interval)\n  InitPlan 2 (kthen $1)\n    ->  Rezultati (cost=0.00..0.02 rows=1 width=8) (kosto reale 0.002..0.002 rreshta=1 loops=1)\n          Nxjerrja: ((('tani'::cstring)::date)::timestamp me zona kohore - '6 muaj'::interval)\n  ->  Skanimi i Huaj n\u00eb fdw_schema.\"table\"  (kosto=100.02..306874.86 rows=105 width=0) (kosto reale 23.475..4681.419 rreshta=1360025 loops=1)\n        Nxjerrja: \"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        Rreshta t\u00eb hequra nga Filtri: 76934\n        SQL i Larg\u00ebt: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp me zon\u00eb kohore)) AND ((created_dt < $2::timestamp me zon\u00eb kohore))\nKoha e planifikimit: 0.703 ms\nKoha e ekzekutimit: 4789.379 ms<\/code><\/pre>\n
\nI kemi sugjeruar planifikuesit t\u00eb llogaris\u00eb paraprakisht dat\u00ebn n\u00eb n\u00ebnk\u00ebrkes\u00eb dhe t\u00eb kaloj\u00eb tashm\u00eb nj\u00eb ndryshor t\u00eb gatsh\u00ebm n\u00eb filtrin. 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
P\u00ebrs\u00ebri, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb jemi t\u00eb kujdessh\u00ebm: tipi i t\u00eb dh\u00ebnave n\u00eb n\u00ebnpyetje duhet t\u00eb jet\u00eb i nj\u00ebjt\u00eb me at\u00eb t\u00eb fush\u00ebs, mbi t\u00eb cil\u00ebn po filtrojm\u00eb, nd otherwise the scheduler will decide that since the types are different, it is necessary to first fetch all the data and then filter it locally.<\/p>\n
Le ta kthejm\u00eb filtrin sipas dat\u00ebs n\u00eb vler\u00ebn origjinale.<\/p>\n
Freddy vs. Jsonb<\/h1>\n
\nN\u00eb thelb, fushat logjike dhe datat tashm\u00eb e kan\u00eb p\u00ebrshpejtuar k\u00ebrkes\u00ebn ton\u00eb, megjithat\u00eb kishte ende nj\u00eb lloj t\u00eb dh\u00ebnash. Lufta me filtrimin p\u00ebr t\u00eb, sinqerisht, ende nuk ka p\u00ebrfunduar, megjith\u00ebse k\u00ebtu ka edhe suksese. Pra, ja si arrit\u00ebm ta kalojm\u00eb filtrin p\u00ebr jsonb<\/b> fush\u00ebn n\u00eb serverin e larg\u00ebt.<\/p>\n
shpjego analiz\u00ebn e detajuar\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\nAgregati  (kostot=245463.60..245463.61 rresht=1 gjer\u00ebsi=8) (koha aktuale 6727.589..6727.590 rresht=1 loops=1)\n  Dalja: count(1)\n  ->  Skanime t\u00eb Huaja mbi fdw_schema.\"table\"  (kostot=1100.00..245459.90 rresht=1478 gjer\u00ebsi=0) (koha aktuale 16.213..6634.794 rresht=1360025 loops=1)\n        Dalja: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        Filtri: ((\"table\".is_active IS TRUE) AND (\"table\".created_dt >= (('tani'::cstring)::dat\u00eb - '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 t\u00eb p\u00ebrdoret operatori i pranis\u00eb s\u00eb nj\u00eb jsonb<\/b> n\u00eb tjetrin. 7 sekonda n\u00eb vend t\u00eb 29. Deri tani, ky \u00ebsht\u00eb varianti i vet\u00ebm i suksessh\u00ebm i kalimit t\u00eb filtrave p\u00ebr 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'ju njoftojm\u00eb se si ndikon kjo, sepse ka shum\u00eb ndryshime p\u00ebr t\u00eb cilat kemi shum\u00eb shpres\u00eb: json_path, sjellje t\u00eb reja CTE, push down (i pranish\u00ebm q\u00eb nga versioni 10). Na p\u00eblqen t\u00eb provojm\u00eb sa m\u00eb shpejt.<\/p>\n
Finish him<\/h1>\n
\nNe kemi verifikuar se si \u00e7do ndryshim ndikon n\u00eb shpejt\u00ebsin\u00eb e k\u00ebrkimit n\u00eb ve\u00e7anti. Tani le t\u00eb shohim se \u00e7far\u00eb do t\u00eb ndodh\u00eb kur t\u00eb tre filtrat t\u00eb shkruhen si\u00e7 duhet.<\/p>\n
explain analyze verbose\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active = True\nAND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') \nAND created_dt  '{\"source\":\"test\"}'::jsonb;\n\nAggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)\n  Output: count(1)\n  InitPlan 1 (returns $0)\n    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)\n          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)\n  InitPlan 2 (returns $1)\n    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)\n          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)\n  ->  Foreign Scan on fdw_schema.\"table\"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)\n        Output: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt  '{\"source\": \"test\"}'::jsonb))\nPlanning time: 0.820 ms\nExecution time: 2279.087 ms<\/code><\/pre>\n
\nPo, pyetja duket m\u00eb e nd\u00ebrlikuar; kjo \u00ebsht\u00eb nj\u00eb \u00e7mim i detyruar, por shpejt\u00ebsia e ekzekutimit \u00ebsht\u00eb 2 sekonda, q\u00eb \u00ebsht\u00eb m\u00eb shum\u00eb se 10 her\u00eb m\u00eb shpejt! Dhe ne po flasim p\u00ebr nj\u00eb pyetje t\u00eb thjesht\u00eb ndaj nj\u00eb grupi t\u00eb dh\u00ebnash relativisht t\u00eb vog\u00ebl. N\u00eb pyetje reale kemi marr\u00eb rritje deri n\u00eb disa qindra her\u00eb.<\/p>\n
T\u00eb japim disa p\u00ebrmbledhje: n\u00ebse p\u00ebrdorni PostgreSQL me FDW, kontrolloni gjithmon\u00eb n\u00ebse t\u00eb gjitha filtrat d\u00ebrgohen n\u00eb serverin e larg\u00ebt, dhe do t\u00eb keni fat\u2026 T\u00eb pakt\u00ebn, derisa t\u00eb arrini deri te bashkimet midis tabelave t\u00eb ndryshme. server\u00ebve<\/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 lumtur t\u00eb d\u00ebgjoja pyetje, komente dhe histori p\u00ebr p\u00ebrvoj\u00ebn tuaj 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\udd47Analitika operacionale n\u00eb arkitektur\u00ebn e mikrosh\u00ebrbimeve: ndihmon dhe sugjeron Postgres FDW | ProHoster","description":"Arkitektura e mikrosh\u00ebrbimeve, si gjith\u00e7ka tjet\u00ebr n\u00eb k\u00ebt\u00eb bot\u00eb, ka p\u00ebrfitimet dhe disavantazhet e saj. Disa procese b\u00ebhen m\u00eb t\u00eb lehta, t\u00eb tjerat \u2014 m\u00eb t\u00eb komplikuara. Dhe n\u00eb em\u00ebr t\u00eb shpejt\u00ebsis\u00eb s\u00eb ndryshimeve dhe p\u00ebrmir\u00ebsimit t\u00eb shkall\u00ebzueshm\u00ebris\u00eb, ka sakrifica q\u00eb duhen b\u00ebr\u00eb. Nj\u00eb nga to \u00ebsht\u00eb komplikuimi i analitik\u00ebs. N\u00ebse n\u00eb monolit, t\u00eb gjitha analitik\u00ebn operacionale mund ta reduktoni n\u00eb k\u00ebrkesat SQL p\u00ebr replik\u00ebn analitike,","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. \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,","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"},"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}]}}