L-arkitettura tal-mikroservizz, bħal kull ħaġa f'din id-dinja, għandha l-vantaġġi u l-iżvantaġġi tagħha. Xi proċessi jsiru aktar faċli magħha, oħrajn aktar diffiċli. U għall-fini tal-veloċità tal-bidla u l-iskalabbiltà aħjar, għandek bżonn tagħmel sagrifiċċji. Waħda minnhom hija l-kumplessità dejjem tikber tal-analitika. Jekk f'monolith l-analiżi operattiva kollha tista 'titnaqqas għal mistoqsijiet SQL għal replika analitika, allura f'arkitettura multiservizz kull servizz għandu d-database tiegħu u jidher li mistoqsija waħda ma tistax issir (jew forsi tista'?). Għal dawk li huma interessati dwar kif solvejna l-problema tal-analitika operattiva fil-kumpanija tagħna u kif tgħallimna ngħixu b'din is-soluzzjoni - merħba.

Jisimni Pavel Sivash, f'DomClick naħdem f'tim li huwa responsabbli għaż-żamma tal-maħżen tad-dejta analitika. Konvenzjonalment, l-attivitajiet tagħna jistgħu jiġu kklassifikati bħala inġinerija tad-dejta, iżda, fil-fatt, il-firxa ta 'kompiti hija ħafna usa'. Hemm standard ETL/ELT għall-inġinerija tad-dejta, appoġġ u adattament ta 'għodod għall-analiżi tad-dejta u żvilupp tal-għodod tiegħek stess. B'mod partikolari, għar-rappurtar operattiv, iddeċidejna li "nippretendu" li għandna monolitu u nagħtu lill-analisti database waħda li jkun fiha d-dejta kollha li jeħtieġu.

B'mod ġenerali, ikkunsidrajna għażliet differenti. Kien possibbli li nibnu repożitorju sħiħ - saħansitra ppruvajna, iżda, biex inkun onest, ma konniex kapaċi ngħaqqdu bidliet pjuttost frekwenti fil-loġika mal-proċess pjuttost bil-mod li nibnu repożitorju u nagħmlu bidliet fih (jekk xi ħadd irnexxielu , ikteb fil-kummenti kif). Kien possibbli li tgħid lill-analisti: "Guys, jitgħallmu python u mur repliki analitiċi," iżda dan huwa rekwiżit addizzjonali għar-reklutaġġ, u deher li dan għandu jiġi evitat jekk possibbli. Iddeċidejna li nippruvaw nużaw it-teknoloġija FDW (Foreign Data Wrapper): essenzjalment, dan huwa dblink standard, li huwa fl-istandard SQL, iżda bl-interface tagħha stess ħafna aktar konvenjenti. Ibbażat fuqha, għamilna soluzzjoni, li eventwalment qabdet, u issetilna fuqha. Id-dettalji tiegħu huma s-suġġett ta 'artiklu separat, u forsi aktar minn wieħed, peress li nixtieq nitkellem dwar ħafna: mis-sinkronizzazzjoni ta' skemi ta 'database għal kontroll ta' aċċess u depersonalizzazzjoni ta 'data personali. Huwa wkoll meħtieġ li ssir riżerva li din is-soluzzjoni mhix sostitut għal databases u repożitorji analitiċi reali; issolvi biss problema speċifika.

Fl-ogħla livell jidher bħal dan:

Hemm database PostgreSQL fejn l-utenti jistgħu jaħżnu d-dejta tax-xogħol tagħhom, u l-aktar importanti, repliki analitiċi tas-servizzi kollha huma konnessi ma 'din id-database permezz tal-FDW. Dan jagħmilha possibbli li tikteb mistoqsija għal diversi databases, u ma jimpurtax x'inhu: PostgreSQL, MySQL, MongoDB jew xi ħaġa oħra (fajl, API, jekk f'daqqa waħda ma jkunx hemm tgeżwir adattat, tista 'tikteb tiegħek). Ukoll, kollox jidher kbir! Qed inkissru?

Jekk kollox spiċċa daqshekk malajr u sempliċi, allura, probabbilment, ma jkunx hemm artiklu.

Huwa importanti li tkun ċara dwar kif Postgres tipproċessa t-talbiet lil servers remoti. Dan jidher loġiku, iżda ħafna drabi n-nies ma jagħtux attenzjoni għaliha: Postgres jaqsam it-talba f'partijiet li huma esegwiti b'mod indipendenti fuq servers remoti, jiġbor din id-dejta, u jwettaq il-kalkoli finali innifsu, għalhekk il-veloċità tal-eżekuzzjoni tal-mistoqsija se tiddependi ħafna fuq kif inhi miktuba. Għandu jiġi nnutat ukoll: meta d-data tasal minn server remot, m'għadx għandha indiċi, m'hemm xejn li se jgħin lill-iskedar, għalhekk, aħna biss nistgħu ngħinu u nagħtu parir. U dan huwa eżattament dak li rrid nitkellem dwaru f'aktar dettall.

Mistoqsija sempliċi u pjan magħha

Biex turi kif Postgres jistaqsi tabella ta’ 6 miljun ringiela fuq server remot, ejja nħarsu lejn pjan sempliċi.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

L-użu tad-dikjarazzjoni VERBOSE jippermettilna naraw il-mistoqsija li se tintbagħat lis-server remot u r-riżultati tagħha se nirċievu għal aktar proċessar (linja RemoteSQL).

Ejja mmorru ftit aktar u żid diversi filtri mat-talba tagħna: wieħed għal boolean qasam, wieħed mill-okkorrenza timestamp fl-intervall u wieħed minn jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

Dan huwa fejn jinsab il-punt li għandek bżonn tagħti attenzjoni meta tikteb mistoqsijiet. Il-filtri ma ġewx trasferiti lis-server remot, li jfisser li biex tesegwixxiha, Postgres jiġbed is-6 miljun ringieli kollha sabiex imbagħad jiffiltra lokalment (filtru ringiela) u jwettaq aggregazzjoni. Iċ-ċavetta għas-suċċess hija li tikteb mistoqsija sabiex il-filtri jiġu trasferiti għall-magna remota, u nirċievu u naggregaw biss ir-ringieli meħtieġa.

Dak xi booleanshit

B'oqsma boolean kollox huwa sempliċi. Fit-talba oriġinali, il-problema kienet dovuta lill-operatur is. Jekk tissostitwiha bi =, allura nġibu r-riżultat li ġej:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Kif tistgħu taraw, il-filtru tellgħu għal server remot, u l-ħin ta 'eżekuzzjoni tnaqqas minn 27 għal 19-il sekonda.

Ta 'min jinnota li l-operatur is differenti mill-operatur = għaliex jista 'jaħdem bil-valur Null. Dan ifisser li mhuwiex Veru se jħalli l-valuri False u Null fil-filtru, filwaqt li != Veru se tħalli biss valuri foloz. Għalhekk, meta tissostitwixxi l-operatur mhux żewġ kundizzjonijiet mal-operatur OR għandhom jiġu mgħoddija lill-filtru, pereżempju, FEJN (kol != Veru) JEW (kol huwa null).

Aħna ttrattati boolean, ejja nimxu fuq. Għalissa, ejja nerġgħu lura l-filtru Boolean għall-forma oriġinali tiegħu sabiex nikkunsidraw b'mod indipendenti l-effett ta 'bidliet oħra.

timestamptz? hz

B'mod ġenerali, ħafna drabi jkollok tesperimenta dwar kif tikteb b'mod korrett talba li tinvolvi servers remoti, u mbagħad biss tfittex spjegazzjoni dwar għaliex jiġri dan. Ftit li xejn informazzjoni dwar dan tista' tinstab fuq l-Internet. Allura, f'esperimenti sibna li filtru ta 'data fissa jtir lejn is-server remot b'bang, iżda meta rridu nissettjaw id-data b'mod dinamiku, pereżempju, issa () jew CURRENT_DATE, dan ma jseħħx. Fl-eżempju tagħna, aħna żidna filtru sabiex il-kolonna created_at kien fiha dejta għal eżattament xahar 1 fil-passat (BEJN CURRENT_DATE - INTERVAL '7 month' U CURRENT_DATE - INTERVAL '6 months'). X’għamilna f’dan il-każ?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote 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))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Aħna għedna lill-pjanifikatur biex jikkalkula d-data fis-subquery bil-quddiem u jgħaddi l-varjabbli lest lill-filtru. U dan il-ħjiel tana riżultat eċċellenti, it-talba saret kważi 6 darbiet aktar mgħaġġla!

Għal darb'oħra, huwa importanti li toqgħod attenta hawnhekk: it-tip tad-dejta fis-subquery għandu jkun l-istess bħal dak tal-qasam li fuqu qed niffiltraw, inkella l-pjanifikatur jiddeċiedi li peress li t-tipi huma differenti, huwa meħtieġ li l-ewwel tikseb kollha id-data u ffiltraha lokalment.

Ejja nerġgħu lura l-filtru tad-data għall-valur oriġinali tiegħu.

Freddy vs. Jsonb

B'mod ġenerali, l-oqsma u d-dati Boolean diġà għaġġlu l-mistoqsija tagħna biżżejjed, iżda kien fadal tip ta 'dejta ieħor. Il-battalja bil-filtrazzjoni minnha, biex inkun onest, għadha mhix spiċċat, għalkemm hawn suċċess ukoll. Allura, hekk irnexxielna ngħaddu l-filtru minn jsonb qasam lis-server remot.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Minflok tiffiltra l-operaturi, trid tuża l-preżenza ta 'operatur wieħed jsonb fi differenti. 7 sekondi minflok l-oriġinali 29. S'issa din hija l-unika għażla ta 'suċċess għat-trażmissjoni tal-filtri permezz jsonb għal server remot, iżda hawnhekk huwa importanti li nqisu limitazzjoni waħda: qed nużaw il-verżjoni 9.6 tad-database, iżda sal-aħħar ta 'April qed nippjanaw li nlestu l-aħħar testijiet u nimxu għall-verżjoni 12. Ladarba naġġornaw, aħna ser niktbu dwar kif affettwat, għaliex hemm ħafna bidliet li għalihom hemm ħafna tama: json_path, imġieba CTE ġdida, push down (eżistenti mill-verżjoni 10). Irrid verament nipprovaha dalwaqt.

Temm lilu

Ittestjajna kif kull bidla affettwat il-veloċità tat-talba individwalment. Ejja issa naraw x'jiġri meta t-tliet filtri jinkitbu b'mod korrett.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  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)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Iva, it-talba tidher aktar ikkumplikata, din hija tariffa sfurzata, iżda l-veloċità tal-eżekuzzjoni hija ta '2 sekondi, li hija aktar minn 10 darbiet aktar mgħaġġla! U qed nitkellmu dwar mistoqsija sempliċi kontra sett ta 'dejta relattivament żgħir. Fuq talbiet reali, irċevejna żieda sa diversi mijiet ta' darbiet.

Fil-qosor: jekk tuża PostgreSQL ma 'FDW, dejjem iċċekkja li l-filtri kollha jintbagħtu lis-server remot, u tkun kuntent... Mill-inqas sakemm tasal għal joins bejn tabelli minn servers differenti. Imma dik hija storja għal artiklu ieħor.

Grazzi tal-attenzjoni tiegħek! Nixtieq nisma' mistoqsijiet, kummenti, u stejjer dwar l-esperjenzi tiegħek fil-kummenti.

Sors: www.habr.com