Microservice-arsjitektuer, lykas alles yn dizze wrâld, hat syn foar- en neidielen. Guon prosessen wurde dêrmei makliker, oaren dreger. En om 'e wille fan' e snelheid fan feroaring en bettere skalberens, moatte jo offers meitsje. Ien fan harren is de kompleksiteit fan analytics. As yn in monolit alle operasjonele analytyk kin wurde fermindere ta SQL-fragen nei in analytyske replika, dan yn in multiservice-arsjitektuer hat elke tsjinst in eigen databank en it liket derop dat ien query net genôch is (of miskien wol?). Foar dyjingen dy't ynteressearre binne yn hoe't wy it probleem fan operasjonele analytyk yn ús bedriuw hawwe oplost en hoe't wy learden te libjen mei dizze oplossing - wolkom.
Myn namme is Pavel Sivash, by DomClick wurkje ik yn in team dat ferantwurdlik is foar it behâld fan it analytyske datapakhús. Konvinsjoneel kinne ús aktiviteiten wurde taskreaun oan data-engineering, mar yn feite is it oanbod fan taken folle breder. D'r binne standert data-engineering ETL / ELT, stipe en oanpassing fan ark foar gegevensanalyse en de ûntwikkeling fan har eigen ark. Yn it bysûnder, foar operasjonele rapportaazje, wy besletten om "pretearje" dat wy hawwe in monolith en jou analysts ien database dy't sil befetsje alle gegevens dy't se nedich hawwe.
Yn 't algemien hawwe wy ferskate opsjes beskôge. It wie mooglik om in folweardich repository te bouwen - wy hawwe sels besocht, mar, om earlik te wêzen, wiene wy net yn steat om freonen te meitsjen mei frij faak feroarings yn 'e logika mei in nochal stadich proses fan it bouwen fan in repository en it meitsjen fan feroaringen ( as immen slagge, skriuw dan yn 'e opmerkings hoe). Jo kinne sizze tsjin analisten: "Jongens, learje python en gean nei analytyske rigels," mar dit is in ekstra eask foar werving, en it like dat dit as mooglik foarkommen wurde moat. Wy besletten om te besykjen mei de FDW (Foreign Data Wrapper) technology: yn feite is dit in standert dblink, dy't yn 'e SQL-standert is, mar mei syn folle handiger ynterface. Op grûn dêrfan hawwe wy in beslút nommen, dy't úteinlik woartele hat, dêr ha wy op ôfsletten. De details dêrfan binne it ûnderwerp fan in apart artikel, en miskien mear as ien, om't ik in protte oer prate wol: fan syngronisaasje fan databankskema oant tagongskontrôle en depersonalisaasje fan persoanlike gegevens. It moat ek opmurken wurde dat dizze oplossing gjin ferfanging is foar echte analytyske databases en repositories, it lost allinich in spesifyk probleem op.
Op it boppeste nivo sjocht it der sa út:
D'r is in PostgreSQL-database wêr't brûkers har wurkgegevens kinne opslaan, en it wichtichste binne analytyske replika's fan alle tsjinsten ferbûn mei dizze databank fia FDW. Dit makket it mooglik om te skriuwen in fraach nei ferskate databases, en it makket neat út wat it is: PostgreSQL, MySQL, MongoDB of wat oars (bestân, API, as ynienen is der gjin geskikte wrapper, kinne jo skriuwe jo eigen). No, alles liket geweldich te wêzen! Út meitsje?
As alles einige sa fluch en ienfâldich, dan, wierskynlik, it artikel soe net bestean.
It is wichtich om dúdlik te wêzen oer hoe't postgres fersiken omgiet nei servers op ôfstân. Dit liket logysk, mar faak besteegje minsken der gjin oandacht oan: postgres dielt de query yn dielen dy't selsstannich útfierd wurde op tsjinners op ôfstân, sammelet dizze gegevens en fiert de definitive berekkeningen sels út, sadat de útfieringssnelheid fan 'e query sterk ôfhingje fan hoe't it is skreaun. It moat ek opmurken wurde: as de gegevens fan in tsjinner op ôfstân komme, hawwe se gjin yndeksen mear, d'r is neat dat de planner sil helpe, dus allinich wy sels kinne it helpe en foarstelle. En dêr wol ik yn mear detail oer prate.
In ienfâldich fersyk en in plan dêrby
Om sjen te litten hoe't Postgres in 6 miljoen rige tabel op in tsjinner op ôfstân freget, litte wy nei in ienfâldich plan sjen.
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 msIt brûken fan de VERBOSE-statement lit jo de fraach sjen dy't stjoerd wurdt nei de tsjinner op ôfstân en de resultaten wêrfan wy sille ûntfange foar fierdere ferwurking (RemoteSQL-string).
Litte wy in bytsje fierder gean en ferskate filters tafoegje oan ús query: ien foar booleaanske fjild, ien troch yngong tiidstempel per ynterval en ien troch 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 msDit is wêr't it momint leit, wêr't jo op moatte betelje by it skriuwen fan fragen. De filters waarden net oerbrocht nei de tsjinner op ôfstân, wat betsjut dat om it út te fieren, postgres alle 6 miljoen rigen lûkt om lokaal te filterjen (de filterline) en letter aggregaasje út te fieren. De kaai foar sukses is om in query te skriuwen sadat de filters wurde oerdroegen oan 'e remote masine, en wy ûntfange en aggregearje allinich de nedige rigen.
Dat is wat booleanshit
Mei Booleaanske fjilden is alles ienfâldich. Yn 'e oarspronklike query wie it probleem troch de operator is. As wy ferfange it mei =, dan krije wy it folgjende resultaat:
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 msSa't jo sjen kinne, fleach it filter nei de tsjinner op ôfstân, en de útfieringstiid waard fermindere fan 27 nei 19 sekonden.
Dêrby moat opmurken wurde dat de operator is oars as operator = de iene dy't kin wurkje mei de Null wearde. It betsjut dat is net Wier yn it filter sil de wearden False en Null ferlitte, wylst != Wier sil ferlitte allinne False wearden. Dêrom, by it ferfangen fan de operator is net jo moatte twa betingsten trochjaan oan it filter mei de OR-operator, bygelyks, WHERE (col != Wier) OR (col is null).
Mei boolean útfûn, trochgean. Litte wy yn 'e tuskentiid it filter troch Booleaanske wearde werombringe nei syn oarspronklike foarm om it effekt fan oare wizigingen selsstannich te beskôgjen.
timestamptz? hz
Yn 't algemien moatte jo faaks eksperimintearje mei hoe't jo in query korrekt skriuwe kinne dy't tsjinners op ôfstân belûke, en pas dan sykje nei in ferklearring wêrom't dit bart. Hiel lyts ynformaasje oer dit is te finen op it ynternet. Dat, yn eksperiminten, fûnen wy dat in fêste datumfilter mei in knal nei in tsjinner op ôfstân fljocht, mar as wy de datum dynamysk ynstelle wolle, bygelyks no() of CURRENT_DATE, bart dit net. Yn ús foarbyld hawwe wy in filter tafoege sadat de kolom create_at gegevens befettet foar krekt 1 moanne yn it ferline (tusken CURRENT_DATE - YNTERVAL '7 moanne' EN CURRENT_DATE - YNTERVAL '6 moanne'). Wat hawwe wy dien yn dit gefal?
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 msWy hawwe de planner frege om de datum foarôf te berekkenjen yn 'e subquery en de al taret fariabele troch te jaan oan it filter. En dizze hint joech ús in geweldich resultaat, de query waard hast 6 kear rapper!
Nochris is it wichtich om hjir foarsichtich te wêzen: it gegevenstype yn 'e subquery moat itselde wêze as dat fan it fjild wêrmei't wy filterje, oars sil de planner beslute dat, om't de soarten oars binne en it needsaaklik is om earst alle gegevens en filterje it lokaal.
Litte wy it filter op datum werombringe nei de oarspronklike wearde.
Freddy vs. jsonb
Yn 't algemien hawwe Booleaanske fjilden en datums ús query al genôch fersneld, mar d'r wie noch ien gegevenstype. De striid mei der troch te filterjen is earlik sein noch net foarby, al binne der ek hjir suksessen. Dat, hjir is hoe't wy it filter slaggen om troch te gean jsonb fjild nei in tsjinner op ôfstân.
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 msYnstee fan operators te filterjen, moatte jo de oanwêzigens fan ien operator brûke. jsonb yn in oar. 7 sekonden ynstee fan it orizjineel 29. Oant no ta is dit de ienige suksesfolle opsje foar it oerbringen fan filters oer jsonb nei in tsjinner op ôfstân, mar hjir is it wichtich om rekken te hâlden mei ien beheining: wy brûke ferzje 9.6 fan 'e databank, mar oan 'e ein fan april planje wy de lêste tests te foltôgjen en nei ferzje 12 te gean. Sadree't wy bywurkje, sille wy skriuwe hoe't it beynfloede is, om't d'r in protte feroaringen binne wêr't in protte hope binne: json_path, nij CTE-gedrach, push down (besteand út ferzje 10). Ik wol it echt gau besykje.
Meitsje him ôf
Wy hawwe kontrolearre hoe't elke feroaring de querysnelheid yndividueel beynfloedet. Litte wy no sjen wat der bart as alle trije filters goed skreaun binne.
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 msJa, de fraach sjocht der yngewikkelder út, it is in twongen priis, mar de útfieringssnelheid is 2 sekonden, dat is mear as 10 kear flugger! En wy hawwe it oer in ienfâldige query op in relatyf lytse set fan gegevens. Op echte oanfragen krigen wy in ferheging fan oant inkele hûnderten kearen.
Om it gear te nimmen: as jo PostgreSQL brûke mei FDW, kontrolearje dan altyd oft alle filters nei de tsjinner op ôfstân stjoerd binne en jo sille bliid wêze ... Alteast oant jo komme ta joins tusken tabellen fan ferskate tsjinners. Mar dat is in ferhaal foar in oar artikel.
Tank foar jo oandacht! Ik wol graach fragen, opmerkings en ferhalen hearre oer jo ûnderfiningen yn 'e opmerkings.
Boarne: www.habr.com