{"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\/sk\/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":"Operat\u00edvna analytika v mikroservisnej architekt\u00fare: p\u0336o\u0336n\u0336a\u0336t\u0336a\u0336 \u0336i\u0336 \u0336p\u0336r\u0336o\u0336s\u0336t\u0336i\u0336t\u0336 \u0336pom\u00f4c\u0165 a poradi\u0165 Postgres FDW","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Mikroslu\u017ebov\u00e1 architekt\u00fara, rovnako ako v\u0161etko na tomto svete, m\u00e1 svoje v\u00fdhody a nev\u00fdhody. Niektor\u00e9 procesy sa s \u0148ou st\u00e1vaj\u00fa jednoduch\u0161\u00edmi, in\u00e9 \u2014 zlo\u017eitej\u0161\u00edmi. A na \u00fakor r\u00fdchlosti zmien a lep\u0161ej \u0161k\u00e1lovate\u013enosti je potrebn\u00e9 prin\u00e1\u0161a\u0165 ur\u010dit\u00e9 obete. Jedna z nich \u2014 skomplikovanie analytiky. Ak sa v monolite cel\u00e1 opera\u010dn\u00e1 analytika d\u00e1 z\u00fa\u017ei\u0165 na SQL dopyty na analytick\u00fa repliku, tak v mult slu\u017ebe m\u00e1 ka\u017ed\u00fd servis svoju datab\u00e1zu a zd\u00e1 sa, \u017ee jedn\u00fdm dopytom sa to ned\u00e1 ob\u00eds\u0165 (ale mo\u017eno aj d\u00e1?). Pre t\u00fdch, ktor\u00fdch zauj\u00edma, ako sme vyrie\u0161ili probl\u00e9m opera\u010dnej analytiky v na\u0161ej firme a ako sme sa nau\u010dili \u017ei\u0165 s t\u00fdmto rie\u0161en\u00edm \u2014 vitajte.<\/p>\n

\"Operat\u00edvna
\nVol\u00e1m sa Pavol Siva\u0161, v DomKliku pracujem v t\u00edme, ktor\u00fd zodpoved\u00e1 za spravovanie analytick\u00e9ho d\u00e1tov\u00e9ho skladu. Na\u0161u \u010dinnos\u0165 m\u00f4\u017eeme z poh\u013eadu ozna\u010di\u0165 za d\u00e1tov\u00fa in\u017einieriu, ale v skuto\u010dnosti je spektrum \u00faloh omnoho \u0161ir\u0161ie. Zvy\u010dajn\u00e9 \u00falohy pre d\u00e1tov\u00fa in\u017einieriu ETL\/ELT, podpora a prisp\u00f4sobenie n\u00e1strojov na anal\u00fdzu d\u00e1t a v\u00fdvoj na\u0161ich vlastn\u00fdch n\u00e1strojov. Konkr\u00e9tne, pre opera\u010dn\u00e9 reportovanie sme sa rozhodli \u201epredstiera\u0165\u201c, \u017ee m\u00e1me monolit a poskytn\u00fa\u0165 analytikom jednu datab\u00e1zu, v ktorej bud\u00fa v\u0161etky potrebn\u00e9 d\u00e1ta. <\/a><\/noindex><\/p>\n

V skuto\u010dnosti sme uva\u017eovali o r\u00f4znych mo\u017enostiach. Mohli sme vybudova\u0165 plnohodnotn\u00e9 \u00falo\u017eisko \u2014 dokonca sme to sk\u00fa\u0161ali, ale \u00faprimne povedan\u00e9, nikdy sa n\u00e1m nepodarilo zladi\u0165 dos\u0165 \u010dast\u00e9 zmeny v logike s dos\u0165 pomal\u00fdm procesom v\u00fdstavby \u00falo\u017eiska a zmenami v \u0148om (ak sa niekomu podarilo, nap\u00ed\u0161te to pros\u00edm do koment\u00e1rov). Mohli sme poveda\u0165 analytikom: \u201eChlapi, u\u010dte sa Python a cho\u010fte do analytick\u00fdch replik\u201c, ale to by bolo dodato\u010dn\u00e9 po\u017eiadavky na v\u00fdber zamestnancov, a zdalo sa, \u017ee tomuto by sme sa mali vyhn\u00fa\u0165, ak je to mo\u017en\u00e9. Rozhodli sme sa vysk\u00fa\u0161a\u0165 technol\u00f3giu FDW (Foreign Data Wrapper): v podstate ide o \u0161tandardn\u00fd dblink, ktor\u00fd je v \u0161tandarde SQL, ale s ove\u013ea pohodlnej\u0161\u00edm rozhran\u00edm. Na jej z\u00e1klade sme vyvinuli rie\u0161enie, ktor\u00e9 sa nakoniec uchytilo, na ktorom sme sa zastavili. Jeho podrobnosti s\u00fa t\u00e9mou samostatn\u00e9ho \u010dl\u00e1nku, mo\u017eno aj viacer\u00fdch, preto\u017ee m\u00e1me ve\u013ea o \u010dom hovori\u0165: od synchroniz\u00e1cie sch\u00e9m datab\u00e1z po spr\u00e1vu pr\u00edstupu a anonymiz\u00e1ciu osobn\u00fdch \u00fadajov. Takisto je potrebn\u00e9 poznamena\u0165, \u017ee toto rie\u0161enie nie je n\u00e1hradou za re\u00e1lne analytick\u00e9 datab\u00e1zy a \u00falo\u017eisk\u00e1, rie\u0161i iba konkr\u00e9tny probl\u00e9m.<\/p>\n

Na vy\u0161\u0161ej \u00farovni to vyzer\u00e1 takto:<\/p>\n

\"Operat\u00edvna
\nExistuje datab\u00e1za PostgreSQL, kde si pou\u017e\u00edvate\u013e m\u00f4\u017ee uchov\u00e1va\u0165 svoje pracovn\u00e9 \u00fadaje, a najd\u00f4le\u017eitej\u0161ie je, \u017ee k tejto datab\u00e1ze s\u00fa prostredn\u00edctvom FDW pripojen\u00e9 analytick\u00e9 repliky v\u0161etk\u00fdch slu\u017eieb. To umo\u017e\u0148uje nap\u00edsa\u0165 dopyt na nieko\u013eko datab\u00e1z, pri\u010dom nez\u00e1le\u017e\u00ed na tom, \u010di ide o PostgreSQL, MySQL, MongoDB alebo nie\u010do in\u00e9 (s\u00fabor, API, ak neexistuje vhodn\u00fd wrapper, m\u00f4\u017eete nap\u00edsa\u0165 svoj). Tak\u017ee, v\u0161etko super! M\u00f4\u017eeme \u00eds\u0165 prerozdeli\u0165?<\/p>\n

Keby to v\u0161etko kon\u010dilo tak r\u00fdchlo a jednoducho, pravdepodobne by tento \u010dl\u00e1nok ani neexistoval.<\/p>\n

Je d\u00f4le\u017eit\u00e9 jasne pochopi\u0165, ako PostgreSQL spracov\u00e1va dopyty na vzdialen\u00fdch serveroch. To sa zd\u00e1 logick\u00e9, av\u0161ak \u010dasto si na to nikto nev\u0161imne: PostgreSQL rozdel\u00ed dopyt na \u010dasti, ktor\u00e9 sa vykon\u00e1vaj\u00fa na vzdialen\u00fdch serveroch nez\u00e1visle, zhroma\u017e\u010fuje tieto \u00fadaje, a kone\u010dn\u00e9 v\u00fdpo\u010dty vykon\u00e1va samotn\u00e9, tak\u017ee r\u00fdchlos\u0165 vykonania dopytu bude silno z\u00e1visie\u0165 od toho, ako je nap\u00edsan\u00fd. Takisto treba poznamena\u0165: ke\u010f \u00fadaje prich\u00e1dzaj\u00fa z vzdialen\u00e9ho servera, u\u017e nemaj\u00fa indexy, nemaj\u00fa ni\u010d, \u010do by pomohlo pl\u00e1nova\u010dovi, a preto mu m\u00f4\u017eeme pom\u00f4c\u0165 a poradi\u0165 len my sami. A pr\u00e1ve o tom by som chcel rozpr\u00e1va\u0165 podrobnej\u0161ie.<\/p>\n

Jednoduch\u00fd dotaz a pl\u00e1n s n\u00edm<\/h1>\n

\nAby sme uk\u00e1zali, ako PostgreSQL vykon\u00e1va dotaz na tabu\u013eku s 6 mili\u00f3nmi riadkov na vzdialenom serveri<\/a>, pozrime sa na jednoduch\u00fd pl\u00e1n.<\/p>\n

vysvetli\u0165 anal\u00fdzu podrobne  \nSELECT count(1)\nFROM fdw_schema.table;\n\nAgreg\u00e1cia  (n\u00e1klady=418383.23..418383.24 riadky=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=3857.198..3857.198 riadky=1 cykly=1)\n  V\u00fdstup: count(1)\n  ->  Cudz\u00ed sken na fdw_schema."table"  (n\u00e1klady=100.00..402376.14 riadky=6402838 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=4.874..3256.511 riadky=6406868 cykly=1)\n        V\u00fdstup: "table".id, "table".is_active, "table".meta, "table".created_dt\n        Dia\u013ekov\u00fd SQL: SELECT NULL FROM fdw_schema.table\nPl\u00e1novanie \u010dasu: 0.986 ms\n\u010cas vykon\u00e1vania: 3857.436 ms<\/code><\/pre>\n
\nPou\u017eitie in\u0161trukcie VERBOSE umo\u017e\u0148uje vidie\u0165 dotaz, ktor\u00fd bude odoslan\u00fd na vzdialen\u00fd server a v\u00fdsledky, ktor\u00e9 z\u00edskame na \u010fal\u0161ie spracovanie (riadok RemoteSQL).<\/p>\n
Po\u010fme o nie\u010do \u010falej a pridajme do n\u00e1\u0161ho dotazu nieko\u013eko filtrov: jeden pod\u013ea boolean<\/b> po\u013ea, jeden pod\u013ea v\u00fdskytu timestamp<\/b> v intervale a jeden pod\u013ea jsonb<\/b>.<\/p>\n
vysvetli\u0165 anal\u00fdzu podrobne\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active is True\nAND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 months' \nAND CURRENT_DATE - INTERVAL '6 months'\nAND meta->>'source' = 'test';\n\nAgreg\u00e1cia  (n\u00e1klady=577487.69..577487.70 riadky=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=27473.818..25473.819 riadky=1 cykly=1)\n  V\u00fdstup: count(1)\n  ->  Cudz\u00ed sken na fdw_schema."table"  (n\u00e1klady=100.00..577469.21 riadky=7390 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=31.369..25372.466 riadky=1360025 cykly=1)\n        V\u00fdstup: "table".id, "table".is_active, "table".meta, "table".created_dt\n        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 months'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 months'::interval)))\n        Riadky odstr\u00e1nen\u00e9 filtrom: 5046843\n        Dia\u013ekov\u00fd SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table\nPl\u00e1novanie \u010dasu: 0.665 ms\n\u010cas vykon\u00e1vania: 27474.118 ms<\/code><\/pre>\n
\nPr\u00e1ve tu spo\u010d\u00edva moment, na ktor\u00fd je potrebn\u00e9 si d\u00e1va\u0165 pozor pri p\u00edsan\u00ed dotazov. Filtrov\u00e9 podmienky neboli odovzdan\u00e9 na vzdialen\u00fd server, \u010do znamen\u00e1, \u017ee na jeho vykonanie PostgreSQL \u0165ah\u00e1 v\u0161etk\u00fdch 6 mili\u00f3nov riadkov, aby ich n\u00e1sledne lok\u00e1lne filtroval (riadok Filter) a vykonal agreg\u00e1ciu. K\u013e\u00fa\u010dom k \u00faspechu je nap\u00edsa\u0165 dotaz tak, aby sa filtre odovzdali na vzdialen\u00fd stroj a my sme z\u00edskali a agregovali len potrebn\u00e9 riadky. <\/p>\n
To je nejak\u00fd boole\u00e1nsky odpad.<\/h1>\n
\nS boolean poliami je v\u0161etko jasn\u00e9. V p\u00f4vodnom dotaze nastal probl\u00e9m v d\u00f4sledku oper\u00e1tora is<\/b>. Ak ho nahrad\u00edme =<\/b>, dostaneme nasleduj\u00faci v\u00fdsledok:<\/p>\n
vysvetli\u0165 anal\u00fdzu podrobne\nSELECT count(1)\nFROM fdw_schema.table\nWHERE is_active = True\nAND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 months' \nAND CURRENT_DATE - INTERVAL '6 months'\nAND meta->>'source' = 'test';\n\nAgreg\u00e1cia  (n\u00e1klady=508010.14..508010.15 riadky=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=19064.314..19064.314 riadky=1 cykly=1)\n  V\u00fdstup: count(1)\n  ->  Cudz\u00ed sken na fdw_schema."table"  (n\u00e1klady=100.00..507988.44 riadky=8679 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=33.035..18951.278 riadky=1360025 cykly=1)\n        V\u00fdstup: "table".id, "table".is_active, "table".meta, "table".created_dt\n        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 months'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 months'::interval)))\n        Riadky odstr\u00e1nen\u00e9 filtrom: 3567989\n        Dia\u013ekov\u00fd SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)\nPl\u00e1novanie \u010dasu: 0.834 ms\n\u010cas vykon\u00e1vania: 19064.534 ms<\/code><\/pre>\n
\nAko vid\u00edte, filter sa presunul na vzdialen\u00fd server a \u010das vykon\u00e1vania sa skr\u00e1til z 27 na 19 sek\u00fand. <\/p>\n
Je potrebn\u00e9 poznamena\u0165, \u017ee oper\u00e1tor is<\/b> sa l\u00ed\u0161i od oper\u00e1tora =<\/b> t\u00fdm, \u017ee dok\u00e1\u017ee pracova\u0165 so hodnotou Null. To znamen\u00e1, \u017ee is not True<\/b> vo filtri ponech\u00e1 hodnoty False a Null, zatia\u013e \u010do != True<\/b> ponech\u00e1 len hodnoty False. Preto pri nahraden\u00ed oper\u00e1tora is not<\/b> je potrebn\u00e9 do filtra zahrn\u00fa\u0165 dve podmienky s oper\u00e1torom OR, napr\u00edklad, WHERE (col != True) OR (col is null)<\/b>.<\/p>\n
S boolean hodnotami sme sa vysporiadali, pos\u00favame sa \u010falej. A zatia\u013e vr\u00e1time filter pod\u013ea boolean hodnoty do p\u00f4vodn\u00e9ho stavu, aby sme nez\u00e1visle presk\u00famali efekt in\u00fdch zmien.<\/p>\n
timestamptz? hz<\/h1>\n
\n\u010casto je potrebn\u00e9 experimentova\u0165 s t\u00fdm, ako spr\u00e1vne nap\u00edsa\u0165 dotaz, ktor\u00fd zah\u0155\u0148a vzdialen\u00e9 servery, a a\u017e potom h\u013eada\u0165 vysvetlenie, pre\u010do sa to deje. Na internete je ve\u013emi m\u00e1lo inform\u00e1ci\u00ed o tejto t\u00e9me. Tak sme v experimentoch zistili, \u017ee filter pod\u013ea pevn\u00e9ho d\u00e1tumu ide na vzdialen\u00fd server bez probl\u00e9mov, ale ke\u010f chceme zada\u0165 d\u00e1tum dynamicky, napr\u00edklad now() alebo CURRENT_DATE, tak to nefunguje. V na\u0161om pr\u00edklade sme pridali tak\u00fd filter, aby st\u013apec created_at obsahoval \u00fadaje presne za 1 mesiac do minulosti (BETWEEN CURRENT_DATE - INTERVAL '7 months' AND CURRENT_DATE - INTERVAL '6 months'). \u010co sme v tomto pr\u00edpade podnikli?<\/p>\n
vysvetlite analyzova\u0165 podrobne\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active is True\nAND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 mesiac') \nAND created_dt >'source' = 'test';\n\nAgreg\u00e1cie  (n\u00e1klady=306875.17..306875.18 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=4789.114..4789.115 riadkov=1 slu\u010diek=1)\n  V\u00fdstup: count(1)\n  InitPlan 1 (vracia $0)\n    ->  V\u00fdsledok  (n\u00e1klady=0.00..0.02 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=0.007..0.008 riadkov=1 slu\u010diek=1)\n          V\u00fdstup: ((('teraz'::cstring)::date)::timestamp with time zone - '7 mesiac'::interval)\n  InitPlan 2 (vracia $1)\n    ->  V\u00fdsledok  (n\u00e1klady=0.00..0.02 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=0.002..0.002 riadkov=1 slu\u010diek=1)\n          V\u00fdstup: ((('teraz'::cstring)::date)::timestamp with time zone - '6 mesiac'::interval)\n  ->  Vzdialen\u00e9 skenovanie na fdw_schema."table"  (n\u00e1klady=100.02..306874.86 riadkov=105 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=23.475..4681.419 riadkov=1360025 slu\u010diek=1)\n        V\u00fdstup: "table".id, "table".is_active, "table".meta, "table".created_dt\n        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))\n        Odstr\u00e1nen\u00e9 riadky pomocou filtra: 76934\n        Vzdialen\u00e9 SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))\n\u010cas pl\u00e1novania: 0.703 ms\n\u010cas vykonania: 4789.379 ms<\/code><\/pre>\n
\nDali sme pl\u00e1nova\u010du n\u00e1vrh na predv\u00fdpo\u010det d\u00e1tumu v poddotaze a prenos u\u017e pripraven\u00fdch premenn\u00fdch do filtra. A t\u00e1to rada n\u00e1m priniesla skvel\u00fd v\u00fdsledok, dopyt sa stal r\u00fdchlej\u0161\u00edm takmer 6-kr\u00e1t!<\/p>\n
Op\u00e4\u0165 je d\u00f4le\u017eit\u00e9 by\u0165 pozorn\u00fd: typ \u00fadajov v poddotaze mus\u00ed by\u0165 tak\u00fd ist\u00fd, ako pole, pod\u013ea ktor\u00e9ho filtrujeme, inak pl\u00e1nova\u010d rozhodne, \u017ee ke\u010f\u017ee typy s\u00fa r\u00f4zne, je potrebn\u00e9 najprv z\u00edska\u0165 v\u0161etky \u00fadaje a a\u017e n\u00e1sledne ich filtrova\u0165 lok\u00e1lne.<\/p>\n
Vr\u00e1time filter pod\u013ea d\u00e1tumu na p\u00f4vodn\u00fa hodnotu.<\/p>\n
Freddy vs. Jsonb<\/h1>\n
\nVo v\u0161eobecnosti u\u017e booleovsk\u00e9 polia a d\u00e1tumy dostato\u010dne zr\u00fdchlili n\u00e1\u0161 dopyt, av\u0161ak zost\u00e1val e\u0161te jeden typ \u00fadajov. Boj s filtr\u00e1ciou na \u0148om, \u00faprimne povedan\u00e9, st\u00e1le nie je ukon\u010den\u00fd, aj ke\u010f aj tu s\u00fa \u00faspechy. Tak\u017ee, takto sa n\u00e1m podarilo prenies\u0165 filter pod\u013ea jsonb<\/b> pola na vzdialen\u00fd server.<\/p>\n
vysvetlite analyzova\u0165 podrobne\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active is True\nAND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 mesiac' \nAND CURRENT_DATE - INTERVAL '6 mesiac'\nAND meta @> '{\"source\":\"test\"}'::jsonb;\n\nAgreg\u00e1cie  (n\u00e1klady=245463.60..245463.61 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=6727.589..6727.590 riadkov=1 slu\u010diek=1)\n  V\u00fdstup: count(1)\n  ->  Vzdialen\u00e9 skenovanie na fdw_schema."table"  (n\u00e1klady=1100.00..245459.90 riadkov=1478 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=16.213..6634.794 riadkov=1360025 slu\u010diek=1)\n        V\u00fdstup: "table".id, "table".is_active, "table".meta, "table".created_dt\n        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('teraz'::cstring)::date - '7 mesiac'::interval)) AND ("table".created_dt  '{\"source\": \"test\"}'::jsonb))\n\u010cas pl\u00e1novania: 0.747 ms\n\u010cas vykonania: 6727.815 ms<\/code><\/pre>\n
\nNamiesto filtr\u00e1cie oper\u00e1tormi je potrebn\u00e9 pou\u017ei\u0165 oper\u00e1tor pr\u00edtomnosti jedn\u00e9ho jsonb<\/b> v inom. 7 sek\u00fand namiesto p\u00f4vodn\u00fdch 29. Zatia\u013e je to jedin\u00e1 \u00faspe\u0161n\u00e1 varianta pren\u00e1\u0161ania filtrov cez jsonb<\/b> na vzdialen\u00fd server, ale je tu d\u00f4le\u017eit\u00e9 zoh\u013eadni\u0165 jednu obmedzenie: pou\u017e\u00edvame verziu datab\u00e1zy 9.6, ale do konca apr\u00edla pl\u00e1nujeme dokon\u010di\u0165 posledn\u00e9 testy a prejs\u0165 na verziu 12. Ak sa aktualizujeme, nap\u00ed\u0161eme, ako to ovplyvnilo, preto\u017ee zmien, na ktor\u00e9 m\u00e1me ve\u013ek\u00e9 o\u010dak\u00e1vania, je dos\u0165: json_path, nov\u00e9 spr\u00e1vanie CTE, push down (existuje od verzie 10). Ve\u013emi sa te\u0161\u00edme, \u017ee to \u010doskoro vysk\u00fa\u0161ame.<\/p>\n
Finish him<\/h1>\n
\nSkontrolovali sme, ako ka\u017ed\u00e1 zmena ovplyv\u0148uje r\u00fdchlos\u0165 dotazu jednotlivo. Po\u010fme sa teraz pozrie\u0165, \u010do sa stane, ke\u010f bud\u00fa v\u0161etky tri filtre nap\u00edsan\u00e9 spr\u00e1vne.<\/p>\n
vysvetli analyzova\u0165 podrobne\nSELECT count(1)\nFROM fdw_schema.table \nWHERE is_active = True\nAND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 mesiac') \nAND created_dt  '{\"source\":\"test\"}'::jsonb;\n\nAgreg\u00e1cia (n\u00e1klady=322041.51..322041.52 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=2278.867..2278.867 riadkov=1 slu\u010diek=1)\n  V\u00fdstup: count(1)\n  InitPlan 1 (vracia $0)\n    -> V\u00fdsledok (n\u00e1klady=0.00..0.02 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=0.010..0.010 riadkov=1 slu\u010diek=1)\n          V\u00fdstup: ((('teraz'::cstring)::date)::timestamp s \u010dasovou z\u00f3nou - '7 mesiac'::interval)\n  InitPlan 2 (vracia $1)\n    -> V\u00fdsledok (n\u00e1klady=0.00..0.02 riadkov=1 \u0161\u00edrka=8) (skuto\u010dn\u00fd \u010das=0.003..0.003 riadkov=1 slu\u010diek=1)\n          V\u00fdstup: ((('teraz'::cstring)::date)::timestamp s \u010dasovou z\u00f3nou - '6 mesiac'::interval)\n  ->  Foreign Scan na fdw_schema.\"table\" (n\u00e1klady=100.02..322041.41 riadkov=25 \u0161\u00edrka=0) (skuto\u010dn\u00fd \u010das=8.597..2153.809 riadkov=1360025 slu\u010diek=1)\n        V\u00fdstup: \"table\".id, \"table\".is_active, \"table\".meta, \"table\".created_dt\n        Dia\u013ekov\u00e9 SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp s \u010dasovou z\u00f3nou)) AND ((created_dt  '{\"source\": \"test\"}'::jsonb))\n\u010cas pl\u00e1novania: 0.820 ms\n\u010cas vykonania: 2279.087 ms<\/code><\/pre>\n
\n\u00c1no, dotaz vyzer\u00e1 zlo\u017eitej\u0161ie, to je n\u00faten\u00e1 da\u0148, ale r\u00fdchlos\u0165 vykonania je 2 sekundy, \u010do je viac ako 10-kr\u00e1t r\u00fdchlej\u0161ie! A toto hovor\u00edme o jednoduchom dotaze na relat\u00edvne mal\u00fd s\u00fabor \u00fadajov. Pri re\u00e1lnych dotazoch sme dostali n\u00e1rast a\u017e o nieko\u013eko stoviek percent.<\/p>\n
Zhr\u0148me: ak pou\u017e\u00edvate PostgreSQL s FDW, v\u017edy skontrolujte, \u010di s\u00fa v\u0161etky filtre odoslan\u00e9 na vzdialen\u00fd server, a bude sa v\u00e1m dari\u0165... Aspo\u0148 dovtedy, k\u00fdm sa nedostanete k joinom medzi tabu\u013ekami z r\u00f4znych serverov<\/a>. Ale to u\u017e je pr\u00edbeh na \u010fal\u0161\u00ed \u010dl\u00e1nok.<\/p>\n
\u010eakujem za pozornos\u0165! Budem r\u00e1d, ak dostanem ot\u00e1zky, koment\u00e1re, ako aj pr\u00edbehy o va\u0161ich sk\u00fasenostiach v koment\u00e1roch.
\n
Zdroj: 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\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\udd47Operat\u00edvna analytika v mikroslu\u017ebovej architekt\u00fare: pom\u00f4c\u0165 a poradi\u0165 Postgres FDW | ProHoster","description":"Mikroslu\u017ebov\u00e1 architekt\u00fara, rovnako ako v\u0161etko na tomto svete, m\u00e1 svoje klady a z\u00e1pory. Niektor\u00e9 procesy sa s \u0148ou st\u00e1vaj\u00fa jednoduch\u0161\u00edmi, in\u00e9 \u2014 zlo\u017eitej\u0161\u00edmi.","canonical_url":"https:\/\/prohoster.info\/sk\/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":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/79467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=79467"}],"version-history":[{"count":2,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/79467\/revisions"}],"predecessor-version":[{"id":159871,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/79467\/revisions\/159871"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/79468"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=79467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=79467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=79467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}