Mîmariya Microservice, mîna her tiştê li vê dinyayê, xwedî erênî û neyînî hene. Hin pêvajo bi wê re hêsantir dibin, yên din jî dijwartir dibin. Û ji bo leza guherînê û mezinbûna çêtir, hûn hewce ne ku qurbanan bidin. Yek ji wan jî zêdebûna tevliheviya analîtîk e. Ger di monolîtek de hemî analîtîkên xebitandinê dikarin ji pirsên SQL-ê re berbi kopiyek analîtîkî ve werin kêm kirin, wê hingê di mîmariya pirkarûbar de her karûbar xwedan databasa xwe ye û wusa dixuye ku yek pirs nayê kirin (an dibe ku ew dikare?). Ji bo kesên ku meraq dikin ka me çawa pirsgirêka analîtîka xebitandinê di pargîdaniya xwe de çareser kir û em çawa fêr bûn ku bi vê çareseriyê re bijîn - bi xêr hatî.
Navê min Pavel Sivash e, li DomClick ez di tîmek ku berpirsiyarê parastina depoya daneyên analîtîk e de dixebitim. Bi kevneşopî, çalakiyên me dikare wekî endezyariya daneyê were dabeş kirin, lê, bi rastî, rêza karan pir berfireh e. Ji bo endezyariya daneyê, piştgirî û adaptasyona amûran ji bo analîzkirina daneyê û pêşvebirina amûrên xwe standard ETL/ELT hene. Bi taybetî, ji bo raporkirina xebitandinê, me biryar da ku em "xuyan bikin" ku me xwedan monolîtek e û databasek bide analîstan ku dê hemî daneyên ku ew hewce ne dihewîne.
Bi gelemperî, me vebijarkên cûda fikirîn. Mimkun bû ku em depoyek bêkêmasî ava bikin - me tewra jî ceriband, lê, rast be, me nekarî guhertinên pir caran di mantiqê de bi pêvajoya pir hêdî ya avakirina depoyek û guheztina wê re bikin yek (eger kesek bi ser ket , di şîroveyan de çawa binivîse). Mimkun bû ku ji analîstan re were gotin: "Gelî, fêrî python bibin û biçin kopiyên analîtîk," lê ev ji bo peywirdarkirinê hewcedariyek zêde ye, û wusa dixuya ku ger gengaz be divê ji vê yekê were dûr xistin. Me biryar da ku em hewl bidin ku teknolojiya FDW (Foreign Data Wrapper) bikar bînin: di bingeh de, ev dblinkek standard e, ku di standarda SQL de ye, lê bi navbeynkariya xweya pir rehettir e. Li ser vê yekê me çareseriyek çêkir, ku di dawiyê de bi ser ket û em li ser rûniştin. Hûrguliyên wê mijara gotarek cihê ne, û dibe ku ji yekê zêdetir be, ji ber ku ez dixwazim li ser gelek tiştan biaxivim: ji hevdengkirina nexşeyên databasê heya gihîştina kontrol û bêkeskirina daneyên kesane. Her weha pêdivî ye ku meriv îhtiraz bike ku ev çareserî ne şûna databas û depoyên analîtîk ên rastîn e, ew tenê pirsgirêkek taybetî çareser dike.
Di asta jorîn de ev xuya dike:
Databasek PostgreSQL heye ku bikarhêner dikarin daneyên xebata xwe hilînin, û ya herî girîng, kopiyên analîtîk ên hemî karûbaran bi FDW ve bi vê databasê ve têne girêdan. Ev yek dihêle ku meriv pirsek li gelek databasan binivîsîne, û ne girîng e ku ew çi ye: PostgreSQL, MySQL, MongoDB an tiştek din (pel, API, heke ji nişkê ve pêça guncan tune, hûn dikarin ya xwe binivîsin). Welê, her tişt pir xweş xuya dike! Ma em ji hev vediqetin?
Ger her tişt ew qas zû û hêsan biqede, wê hingê, dibe ku, gotarek tune be.
Girîng e ku meriv zelal be ka Postgres çawa daxwazên serverên dûr dişoxilîne. Ev mentiqî xuya dike, lê pir caran mirov guh nadin wê: Postgres daxwazê dike beşên ku serbixwe li ser serverên dûr têne darve kirin, van daneyan berhev dike, û hesabên dawî bixwe pêk tîne, ji ber vê yekê leza pêkanîna pirsê dê pir bi ve girêdayî be. çawa hatiye nivîsandin. Di heman demê de divê were zanîn: gava ku dane ji serverek dûr digihîje, êdî ew pêdekek tune, tiştek tune ku alîkariya plansazker bike, ji ber vê yekê, tenê em bixwe dikarin jê re bibin alîkar û şîret bikin. Û ev e ya ku ez dixwazim bi hûrgulî li ser biaxivim.
Lêpirsînek hêsan û planek pê re
Ji bo ku destnîşan bikin ka Postgres çawa li tabloyek rêzek 6 mîlyon li ser serverek dûr dipirse, ka em li planek hêsan binêrin.
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 msBikaranîna daxuyaniya VERBOSE rê dide me ku em lêpirsîna ku dê ji servera dûr re were şandin û encamên ku em ê ji bo pêvajoyek din werbigirin (xeta RemoteSQL) bibînin.
Ka em hinekî pêşde biçin û çend fîlteran li daxwaza xwe zêde bikin: yek ji bo boolean zevî, yek bi rûdanê timestamp di navberê de û yek bi 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 msLi vir xala ku hûn hewce ne ku dema ku pirsan dinivîsin bala xwe bidinê ye. Parzûn ji servera dûr re nehatin veguheztin, ku tê vê wateyê ku ji bo pêkanîna wê, Postgres hemî 6 mîlyon rêzan derdixe da ku dûv re herêmî (Rêza Parzûnê) fîlter bike û berhevkirinê pêk bîne. Mifteya serkeftinê ev e ku meriv pirsek binivîsîne da ku fîlter li makîneya dûr ve werin veguheztin, û em tenê rêzên pêwîst werdigirin û berhev dikin.
Ew hinek bêaqil e
Bi zeviyên boolean re her tişt hêsan e. Di daxwaza bingehîn de, pirsgirêk ji ber operator bû is. Ger hûn wê biguherînin =, paşê em encama jêrîn bistînin:
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 msWekî ku hûn dikarin bibînin, parzûn berbi serverek dûr ve çû, û dema darvekirinê ji 27 ber 19 saniyeyan kêm bû.
Hêjayî gotinê ye ku operator is cuda ji operator = ji ber ku ew dikare bi nirxa Null re bixebite. Wateya wê ye ne Rast e dê nirxên False û Null di parzûnê de bihêle, lê != Rast e dê tenê nirxên Derew bihêle. Ji ber vê yekê, dema ku operatorê veguherîne ne ye du şertên bi operatorê OR re divê derbasî parzûnê bibin, mînakî, WHERE (col != Rast) OR (col null e).
Me boolean ji hev veqetand, em herin. Heya nuha, em werin parzûna Boolean vegerînin forma xweya bingehîn da ku bi rengek serbixwe bandora guheztinên din binirxînin.
timestamptz? hz
Bi gelemperî, divê hûn bi gelemperî biceribînin ka meriv çawa daxwazek rast binivîsîne ku serverên dûr vedihewîne, û tenê hingê li ravekek bigerin ka çima ev diqewime. Agahiyên pir hindik li ser vê yekê li ser Înternetê têne dîtin. Ji ber vê yekê, di ceribandinan de me dît ku parzûnek tarîxek sabît bi dengek difire ser servera dûr, lê gava ku em dixwazin tarîxê bi rengek dînamîkî destnîşan bikin, mînakî, niha() an CURRENT_DATE, ev nabe. Di mînaka xwe de, me parzûnek lê zêde kir ku di stûna afirandin_at de daneya tam 1 meh berê hebe (BETWEEN CURRENT_DATE - INTERVAL '7 meh' Û CURRENT_DATE - INTERVAL '6 meh'). Di vê rewşê de me çi kir?
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 msMe ji plansaz re got ku tarîxa di binê pirsnameyê de pêşwext bihesibîne û guhêrbar a amade derbas bike parzûnê. Û vê nîşanê encamek hêja da me, daxwaz hema hema 6 carî zûtir bû!
Dîsa, girîng e ku meriv li vir baldar be: Divê celebê daneya di binê pirsnameyê de wekî ya qada ku em lê fîlter dikin yek be, wekî din plansaz dê biryar bide ku ji ber ku celeb cûda ne, pêdivî ye ku pêşî hemî bigirin. daneyan û wê li herêmê fîlter bikin.
Ka em parzûna tarîxê vegerînin nirxa wê ya bingehîn.
Freddy vs. Jsonb
Bi gelemperî, zevî û tarîxên Boolean jixwe lêpirsîna me bi têra xwe bilez kirine, lê celebek daneya din mabû. Şerê bi fîlterkirina wê re, rast be, hîn jî neqediyaye, her çend li vir jî serkeftin heye. Ji ber vê yekê, bi vî rengî me karî ku parzûnê derbas bikin jsonb zeviya servera dûr.
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 msLi şûna fîlterkirina operatoran, divê hûn hebûna yek operator bikar bînin jsonb li cihê cuda. 7 saniye li şûna ya orîjînal 29. Heya nuha ev yekane vebijarka serketî ye ji bo veguheztina parzûnan bi riya jsonb ji serverek dûr re, lê li vir girîng e ku meriv yek sînordariyek bihesibîne: em guhertoya 9.6 ya databasê bikar tînin, lê heya dawiya Nîsanê em plan dikin ku ceribandinên paşîn temam bikin û biçin guhertoya 12. Dema ku em nûve bikin, em ê binivîsin ka ew çawa bandor kiriye, ji ber ku gelek guhertin hene ku ji bo wan pir hêvî heye: json_path, tevgera CTE-ya nû, dakêşin (ji guhertoya 10-an ve heye). Ez bi rastî dixwazim wê zû biceribînim.
Wî biqedînin
Me ceriband ka her guhertin çawa bandor li leza daxwazê kir. Ka em niha bibînin ka çi diqewime dema ku her sê fîlter rast werin nivîsandin.
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 msErê, daxwaz tevlihevtir xuya dike, ev heqê zorê ye, lê leza darvekirinê 2 çirke ye, ku ji 10 carî zûtir e! Û em li ser pirsek hêsan li dijî komek daneya piçûktir diaxivin. Li ser daxwazên rastîn, me zêdebûnek heta çend sed carî wergirt.
Bi kurtasî: heke hûn PostgreSQL bi FDW-ê re bikar tînin, her gav kontrol bikin ku hemî fîlter ji servera dûr re têne şandin, û hûn ê kêfxweş bibin... Qet nebe heya ku hûn bigihîjin navbera tabloyên ji serverên cihêreng. Lê ew çîrokek ji bo gotarek din e.
Spas ji bo baldariya we! Ez dixwazim pirs, şîrove û çîrokên li ser serpêhatiyên we di şîroveyan de bibihîzim.
Source: www.habr.com