Achitekti mikwosèvis, tankou tout bagay nan mond sa a, gen avantaj ak dezavantaj li yo. Gen kèk pwosesis ki vin pi fasil ak li, lòt ki pi difisil. Epi pou dedomajman pou vitès chanjman ak pi bon évolutivité, ou bezwen fè sakrifis. Youn nan yo se konpleksite ogmante nan analytics. Si nan yon monolith tout analytics operasyonèl yo ka redwi a SQL queries nan yon kopi analyse, Lè sa a, nan yon achitekti multiservice chak sèvis gen baz done pwòp li yo epi li sanble ke yon sèl rechèch pa ka fè (oswa petèt li kapab?). Pou moun ki enterese nan ki jan nou rezoud pwoblèm nan nan analiz operasyonèl nan konpayi nou an ak ki jan nou te aprann viv ak solisyon sa a - akeyi.

Non mwen se Pavel Sivash, nan DomClick mwen travay nan yon ekip ki responsab pou kenbe depo done analitik la. Konvansyonèlman, aktivite nou yo ka klase kòm jeni done, men, an reyalite, seri travay yo pi laj. Gen estanda ETL / ELT pou jeni done, sipò ak adaptasyon zouti pou analiz done ak devlopman pwòp zouti ou yo. An patikilye, pou rapò operasyonèl, nou deside "pretann" ke nou gen yon monolit epi bay analis yon baz done ki pral genyen tout done yo bezwen.

An jeneral, nou konsidere opsyon diferan. Li te posib yo bati yon depo konplè - nou menm te eseye, men, yo dwe onèt, nou pa t 'kapab konbine chanjman jistis souvan nan lojik ak pwosesis la olye dousman nan bati yon depo ak fè chanjman nan li (si yon moun te reyisi. , ekri nan kòmantè yo ki jan). Li te posib pou di analis yo: "Mesye, aprann python epi ale nan kopi analyse," men sa a se yon kondisyon adisyonèl pou rekritman, e li te sanble ke sa a ta dwe evite si sa posib. Nou deside eseye sèvi ak teknoloji FDW (Foreign Data Wrapper): esansyèlman, sa a se yon dblink estanda, ki se nan estanda SQL la, men ak pwòp koòdone li yo ki pi pratik. Baze sou li, nou te fè yon solisyon, ki evantyèlman kenbe, epi nou rezoud sou li. Detay li yo se sijè a nan yon atik separe, e petèt plis pase yon sèl, depi mwen vle pale sou anpil: soti nan senkronize chema baz done ak aksè kontwòl ak depèsonalizasyon nan done pèsonèl. Li nesesè tou pou fè yon rezèvasyon ke solisyon sa a se pa yon ranplasman pou reyèl baz done analyse ak depo; li rezoud sèlman yon pwoblèm espesifik.

Nan nivo tèt li sanble sa a:

Gen yon baz done PostgreSQL kote itilizatè yo ka estoke done travay yo, epi sa ki pi enpòtan, kopi analyse tout sèvis yo konekte ak baz done sa a atravè FDW. Sa fè li posib pou ekri yon rechèch nan plizyè baz done, epi li pa gen pwoblèm sa li ye: PostgreSQL, MySQL, MongoDB oswa yon lòt bagay (fichye, API, si toudenkou pa gen okenn anbalaj apwopriye, ou ka ekri pwòp ou a). Oke, tout bagay sanble gwo! Èske nou kraze?

Si tout bagay te fini tèlman vit ak tou senpleman, Lè sa a, pwobableman, pa ta gen yon atik.

Li enpòtan pou w klè sou fason Postgres trete demann nan sèvè aleka yo. Sa a sanble lojik, men souvan moun pa peye atansyon sou li: Postgres divize demann lan an pati ki egzekite poukont yo sou sèvè aleka, kolekte done sa yo, epi fè kalkil final yo tèt li, kidonk vitès egzekisyon demann lan pral depann anpil de kijan li ekri. Li ta dwe remake tou: lè done yo rive soti nan yon sèvè aleka, li pa gen endis ankò, pa gen anyen ki pral ede pwogramè a, Se poutèt sa, se sèlman nou menm nou ka ede ak konseye l '. Ak sa a se egzakteman sa mwen vle pale sou nan plis detay.

Yon demann senp ak yon plan avèk li

Pou montre kijan Postgres mande yon tab 6 milyon ranje sou yon sèvè aleka, ann gade yon plan ki senp.

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

Sèvi ak deklarasyon VERBOSE a pèmèt nou wè rechèch la ke yo pral voye nan sèvè a aleka ak rezilta yo ke nou pral resevwa pou plis pwosesis (liy RemoteSQL).

Ann ale yon ti kras pi lwen epi ajoute plizyè filtè nan demann nou an: youn pou Boolean jaden, youn pa ensidan timestamp nan entèval la ak youn pa 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

Sa a se kote pwen ke ou bezwen peye atansyon a lè w ap ekri demann manti. Filtè yo pa te transfere nan sèvè a aleka, ki vle di ke pou egzekite li, Postgres rale tout 6 milyon ranje yo nan lòd yo Lè sa a, filtre lokalman (Filtre ranje) epi fè agrégation. Kle a nan siksè se ekri yon rechèch pou ke filtè yo transfere nan machin nan aleka, epi nou resevwa ak total sèlman ranje ki nesesè yo.

Sa se kèk booleanshit

Avèk jaden boolean tout bagay se senp. Nan demann orijinal la, pwoblèm nan te akòz operatè a is. Si ou ranplase li ak =, Lè sa a, nou jwenn rezilta sa a:

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

Kòm ou ka wè, filtè a te pran vòl nan yon sèvè aleka, epi tan an ekzekisyon te redwi soti nan 27 a 19 segonn.

Li se vo anyen ke operatè a is diferan de operatè = paske li ka travay ak valè a Nil. Sa vle di sa se pa vre pral kite valè yo Fo ak Nil nan filtè a, tandiske != Se vre pral kite sèlman fo valè. Se poutèt sa, lè ranplase operatè a se pa de kondisyon ak operatè OSWA yo ta dwe pase nan filtè a, pou egzanp, KOTE (kol != Vre) OSWA (kol se nil).

Nou te fè fas ak boolean, ann kontinye. Pou kounye a, ann retounen filtè Boolean an nan fòm orijinal li yo nan lòd yo konsidere endepandamman efè a nan lòt chanjman.

timestamptz? hz

An jeneral, ou souvan gen fè eksperyans ak ki jan yo kòrèkteman ekri yon demann ki enplike nan sèvè aleka, epi sèlman Lè sa a, gade pou yon eksplikasyon sou poukisa sa rive. Trè ti enfòmasyon sou sa a ka jwenn sou entènèt la. Se konsa, nan eksperyans nou te jwenn ke yon filtre dat fiks vole nan sèvè a aleka ak yon bang, men lè nou vle mete dat la dinamik, pou egzanp, kounye a () oswa CURRENT_DATE, sa a pa rive. Nan egzanp nou an, nou te ajoute yon filtè pou kolòn created_at la te genyen done pou egzakteman 1 mwa nan tan pase a (ANT CURRENT_DATE - ENTERVAL '7 month' AK CURRENT_DATE - CURRENT_DATE - CURRENT_DATE '6 months'). Kisa nou te fè nan ka sa a?

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

Nou te di planifikatè a pou kalkile dat la nan subquery a davans epi pase varyab pare a nan filtè a. Ak allusion sa a te ban nou yon rezilta ekselan, demann lan te vin prèske 6 fwa pi vit!

Yon fwa ankò, li enpòtan pou fè atansyon isit la: kalite done nan subquery a dwe menm jan ak sa ki nan jaden an sou ki nou ap filtre, otreman planifikatè a pral deside ke depi kalite yo diferan, li nesesè premye jwenn tout. done yo epi filtre li lokalman.

Ann retounen filtre dat la nan valè orijinal li.

Freddy vs. Jsonb

An jeneral, jaden Boolean ak dat yo te deja akselere demann nou an ase, men te gen yon lòt kalite done ki rete. Batay la ak filtraj pa li, yo dwe onèt, se toujou pa fini, byenke gen siksè isit la tou. Se konsa, sa a se ki jan nou jere yo pase filtè a pa jsonb jaden nan sèvè a aleka.

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

Olye pou yo filtre operatè yo, ou dwe itilize prezans yon operatè jsonb nan yon diferan. 7 segonn olye pou yo 29 orijinal la. Jiskaprezan sa a se sèlman opsyon siksè pou transmèt filtè atravè jsonb nan yon sèvè aleka, men isit la li enpòtan pou pran an kont yon sèl limit: n ap itilize vèsyon 9.6 nan baz done a, men nan fen mwa avril nou planifye pou konplete dènye tès yo epi ale nan vèsyon 12. Yon fwa nou mete ajou, nou pral ekri sou ki jan li afekte, paske gen anpil chanjman pou ki gen anpil espwa: json_path, nouvo konpòtman CTE, pouse desann (ki egziste depi vèsyon 10). Mwen reyèlman vle eseye li byento.

Fini l

Nou teste fason chak chanjman afekte vitès demann endividyèlman. Ann wè sa k ap pase lè twa filtè yo ekri kòrèkteman.

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

Wi, demann lan sanble pi konplike, sa a se yon frè fòse, men vitès ekzekisyon an se 2 segonn, ki se plis pase 10 fwa pi vit! Epi nou ap pale de yon rechèch senp kont yon seri done relativman ti. Sou demann reyèl, nou te resevwa yon ogmantasyon jiska plizyè santèn fwa.

Pou rezime: si ou sèvi ak PostgreSQL ak FDW, toujou tcheke ke tout filtè yo voye nan sèvè a aleka, epi ou pral kontan ... Omwen jiskaske ou rive nan rantre ant tab ki soti nan diferan sèvè. Men, sa se yon istwa pou yon lòt atik.

Mèsi pou atansyon ou! Mwen ta renmen tande kesyon, kòmantè, ak istwa sou eksperyans ou nan kòmantè yo.

Sous: www.habr.com