Ang arkitektura ng microservice, tulad ng lahat sa mundong ito, ay may mga kalamangan at kahinaan. Ang ilang mga proseso ay nagiging mas madali kasama nito, ang iba ay mas mahirap. At para sa kapakanan ng bilis ng pagbabago at mas mahusay na scalability, kailangan mong magsakripisyo. Isa sa mga ito ay ang pagtaas ng pagiging kumplikado ng analytics. Kung sa isang monolith ang lahat ng operational analytics ay maaaring bawasan sa mga query sa SQL sa isang analytical replica, kung gayon sa isang multiservice architecture ang bawat serbisyo ay may sariling database at tila ang isang query ay hindi magagawa (o marahil ito?). Para sa mga interesado sa kung paano namin nalutas ang problema ng operational analytics sa aming kumpanya at kung paano namin natutunang mamuhay sa solusyon na ito - maligayang pagdating.

Ang pangalan ko ay Pavel Sivash, sa DomClick nagtatrabaho ako sa isang team na responsable sa pagpapanatili ng analytical data warehouse. Karaniwan, ang aming mga aktibidad ay maaaring mauri bilang data engineering, ngunit, sa katunayan, ang hanay ng mga gawain ay mas malawak. Mayroong ETL/ELT na pamantayan para sa data engineering, suporta at adaptasyon ng mga tool para sa pagsusuri ng data at pagbuo ng iyong sariling mga tool. Sa partikular, para sa pag-uulat sa pagpapatakbo, nagpasya kaming "magpanggap" na mayroon kaming monolith at bigyan ang mga analyst ng isang database na maglalaman ng lahat ng data na kailangan nila.

Sa pangkalahatan, isinasaalang-alang namin ang iba't ibang mga pagpipilian. Posibleng bumuo ng isang ganap na repositoryo - sinubukan pa namin, ngunit, sa totoo lang, hindi namin nagawang pagsamahin ang medyo madalas na pagbabago sa lohika sa medyo mabagal na proseso ng pagbuo ng repositoryo at paggawa ng mga pagbabago dito (kung may nagtagumpay , isulat sa mga komento kung paano). Posibleng sabihin sa mga analyst: "Guys, matuto ng python at pumunta sa analytical replicas," ngunit ito ay isang karagdagang kinakailangan para sa pagre-recruit, at tila dapat itong iwasan kung maaari. Napagpasyahan naming subukang gumamit ng teknolohiyang FDW (Foreign Data Wrapper): mahalagang, ito ay isang karaniwang dblink, na nasa pamantayan ng SQL, ngunit may sarili nitong mas maginhawang interface. Batay dito, gumawa kami ng isang solusyon, na kalaunan ay nahuli, at naayos namin ito. Ang mga detalye nito ay ang paksa ng isang hiwalay na artikulo, at maaaring higit pa sa isa, dahil marami akong gustong pag-usapan: mula sa pag-synchronize ng mga schema ng database hanggang sa pag-access ng kontrol at depersonalization ng personal na data. Kinakailangan din na gumawa ng isang reserbasyon na ang solusyon na ito ay hindi isang kapalit para sa mga tunay na analytical database at repository; nalulutas lamang nito ang isang partikular na problema.

Sa pinakamataas na antas, ganito ang hitsura:

Mayroong isang database ng PostgreSQL kung saan maiimbak ng mga user ang kanilang data sa trabaho, at higit sa lahat, ang mga analytical na replika ng lahat ng serbisyo ay konektado sa database na ito sa pamamagitan ng FDW. Ginagawa nitong posible na magsulat ng isang query sa ilang mga database, at hindi mahalaga kung ano ito: PostgreSQL, MySQL, MongoDB o iba pa (file, API, kung biglang walang angkop na wrapper, maaari mong isulat ang iyong sarili). Well, ang lahat ay mukhang mahusay! break na tayo?

Kung ang lahat ay natapos nang napakabilis at simple, kung gayon, marahil, hindi magkakaroon ng isang artikulo.

Mahalagang maging malinaw tungkol sa kung paano pinoproseso ng Postgres ang mga kahilingan sa mga malalayong server. Mukhang lohikal ito, ngunit kadalasan ay hindi ito binibigyang pansin ng mga tao: Hinahati ng mga Postgres ang kahilingan sa mga bahagi na iisasagawa nang nakapag-iisa sa mga malalayong server, kinokolekta ang data na ito, at ginagawa ang mismong huling mga kalkulasyon, kaya ang bilis ng pagsasagawa ng query ay lubos na nakasalalay sa kung paano ito nakasulat. Dapat ding tandaan: kapag dumating ang data mula sa isang malayong server, wala na itong mga index, walang makakatulong sa scheduler, samakatuwid, tayo lamang ang makakatulong at makapagpapayo sa kanya. At ito mismo ang gusto kong pag-usapan nang mas detalyado.

Isang simpleng query at isang plano kasama nito

Upang ipakita kung paano nagtatanong ang Postgres sa isang 6 na milyong row table sa isang malayuang server, tingnan natin ang isang simpleng plano.

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

Ang paggamit ng pahayag na VERBOSE ay nagbibigay-daan sa amin na makita ang query na ipapadala sa malayong server at ang mga resulta na matatanggap namin para sa karagdagang pagpoproseso (RemoteSQL line).

Lumayo pa tayo nang kaunti at magdagdag ng ilang mga filter sa aming kahilingan: isa para sa boolean field, isa ayon sa pangyayari timestamp sa pagitan at isa sa pamamagitan ng 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

Dito matatagpuan ang punto na kailangan mong bigyang pansin kapag nagsusulat ng mga query. Ang mga filter ay hindi inilipat sa malayong server, na nangangahulugan na upang maisagawa ito, ang mga Postgres ay kumukuha ng lahat ng 6 na milyong mga hilera upang pagkatapos ay mag-filter nang lokal (Filter row) at magsagawa ng pagsasama-sama. Ang susi sa tagumpay ay ang pagsulat ng query upang ang mga filter ay mailipat sa remote na makina, at ang mga kinakailangang row lang ang natatanggap namin at pinagsama-sama.

Iyon ay ilang booleanshit

Sa mga boolean field, ang lahat ay simple. Sa orihinal na kahilingan, ang problema ay dahil sa operator is. Kung papalitan mo ito ng =, pagkatapos ay makuha namin ang sumusunod na resulta:

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

Tulad ng nakikita mo, lumipad ang filter sa isang malayong server, at ang oras ng pagpapatupad ay nabawasan mula 27 hanggang 19 segundo.

Ito ay nagkakahalaga ng noting na ang operator is iba sa operator = dahil maaari itong gumana sa Null na halaga. Ibig sabihin nito ay hindi totoo iiwan ang mga halaga na Mali at Null sa filter, samantalang != Totoo mag-iiwan lamang ng mga False na halaga. Samakatuwid, kapag pinapalitan ang operator Hindi dalawang kundisyon sa OR operator ang dapat ipasa sa filter, halimbawa, WHERE (col != True) O (col is null).

Nakipag-usap tayo sa boolean, magpatuloy tayo. Sa ngayon, ibalik natin ang Boolean filter sa orihinal nitong anyo upang malayang isaalang-alang ang epekto ng iba pang mga pagbabago.

timestamptz? hz

Sa pangkalahatan, madalas kang kailangang mag-eksperimento sa kung paano magsulat nang tama ng isang kahilingan na nagsasangkot ng mga malalayong server, at pagkatapos ay maghanap ng paliwanag kung bakit ito nangyayari. Napakakaunting impormasyon tungkol dito ay matatagpuan sa Internet. Kaya, sa mga eksperimento, nalaman namin na ang isang nakapirming filter ng petsa ay lumilipad sa malayong server nang malakas, ngunit kapag gusto naming itakda ang petsa nang pabago-bago, halimbawa, ngayon() o CURRENT_DATE, hindi ito nangyayari. Sa aming halimbawa, nagdagdag kami ng filter upang ang column na created_at ay naglalaman ng data para sa eksaktong 1 buwan sa nakaraan (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Ano ang ginawa namin sa kasong ito?

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

Sinabi namin sa tagaplano na kalkulahin ang petsa sa subquery nang maaga at ipasa ang handa na variable sa filter. At ang pahiwatig na ito ay nagbigay sa amin ng isang mahusay na resulta, ang kahilingan ay naging halos 6 na beses na mas mabilis!

Muli, mahalagang maging maingat dito: ang uri ng data sa subquery ay dapat na kapareho ng sa field kung saan kami nagfi-filter, kung hindi ay magpapasya ang tagaplano na dahil magkaiba ang mga uri, kailangan munang makuha ang lahat. ang data at i-filter ito nang lokal.

Ibalik natin ang filter ng petsa sa orihinal na halaga nito.

Freddy vs. Jsonb

Sa pangkalahatan, sapat nang napabilis ng mga field at petsa ng Boolean ang aming query, ngunit may natitira pang uri ng data. Ang labanan sa pagsasala nito, sa totoo lang, ay hindi pa rin tapos, bagama't may tagumpay din dito. Kaya, ito ay kung paano namin nagawang ipasa ang filter jsonb field sa malayong server.

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

Sa halip na i-filter ang mga operator, dapat mong gamitin ang presensya ng isang operator jsonb sa ibang. 7 segundo sa halip na ang orihinal na 29. Sa ngayon ito ang tanging matagumpay na opsyon para sa pagpapadala ng mga filter sa pamamagitan ng jsonb sa isang malayong server, ngunit narito, mahalagang isaalang-alang ang isang limitasyon: gumagamit kami ng bersyon 9.6 ng database, ngunit sa katapusan ng Abril plano naming kumpletuhin ang mga huling pagsubok at lumipat sa bersyon 12. Sa sandaling mag-update kami, magsusulat kami tungkol sa kung paano ito naapektuhan, dahil napakaraming pagbabago kung saan mayroong maraming pag-asa: json_path, bagong pag-uugali ng CTE, itulak pababa (umiiral na mula sa bersyon 10). Gusto ko talagang subukan ito sa lalong madaling panahon.

Tapusin mo sya

Sinubukan namin kung paano naapektuhan ng bawat pagbabago ang bilis ng kahilingan nang paisa-isa. Tingnan natin ngayon kung ano ang mangyayari kapag naisulat nang tama ang lahat ng tatlong filter.

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

Oo, mukhang mas kumplikado ang kahilingan, ito ay isang sapilitang bayad, ngunit ang bilis ng pagpapatupad ay 2 segundo, na higit sa 10 beses na mas mabilis! At pinag-uusapan natin ang tungkol sa isang simpleng query laban sa isang medyo maliit na set ng data. Sa mga totoong kahilingan, nakatanggap kami ng pagtaas ng hanggang ilang daang beses.

Upang buod: kung gagamit ka ng PostgreSQL sa FDW, palaging suriin kung ang lahat ng mga filter ay ipinadala sa malayong server, at ikaw ay magiging masaya... Kahit na hanggang sa ikaw ay makasali sa pagitan ng mga talahanayan mula sa iba't ibang mga server. Ngunit iyon ay isang kuwento para sa isa pang artikulo.

Salamat sa iyong atensyon! Gusto kong makarinig ng mga tanong, komento, at kwento tungkol sa iyong mga karanasan sa mga komento.

Pinagmulan: www.habr.com