Ang arkitektura sa microservice, sama sa tanan niini nga kalibutan, adunay mga bentaha ug disbentaha. Ang ubang mga proseso nahimong mas sayon ​​niini, ang uban mas lisud. Ug alang sa katulin sa pagbag-o ug mas maayo nga scalability, kinahanglan nimo nga magsakripisyo. Usa niini mao ang nagkadako nga pagkakomplikado sa analytics. Kung sa usa ka monolith ang tanan nga pag-analisa sa operasyon mahimong mapakunhod sa mga pangutana sa SQL sa usa ka analytical nga kopya, nan sa usa ka arkitektura nga multiservice ang matag serbisyo adunay kaugalingon nga database ug ingon og ang usa ka pangutana dili mahimo (o tingali mahimo?). Alang sa mga interesado kung giunsa namon nasulbad ang problema sa pag-analisa sa operasyon sa among kompanya ug kung giunsa namon nahibal-an nga magpuyo sa kini nga solusyon - welcome.

Ang akong ngalan mao si Pavel Sivash, sa DomClick nagtrabaho ko sa usa ka team nga responsable sa pagmintinar sa analytical data warehouse. Sa naandan, ang atong mga kalihokan mahimong maklasipikar isip data engineering, apan, sa pagkatinuod, ang lain-laing mga buluhaton mas lapad. Adunay ETL/ELT nga sumbanan alang sa data engineering, suporta ug pagpahiangay sa mga himan alang sa pagtuki sa datos ug pagpalambo sa imong kaugalingong mga himan. Sa partikular, alang sa pagreport sa operasyon, nakahukom kami nga "pagpakaaron-ingnon" nga kami adunay usa ka monolith ug hatagan ang mga analista sa usa ka database nga maglangkob sa tanan nga datos nga ilang gikinahanglan.

Sa kinatibuk-an, gikonsiderar namon ang lainlaing mga kapilian. Posible ang paghimo og usa ka hingpit nga repository - gisulayan pa gani namo, apan, sa tinuod, dili namo makombinar ang kanunay nga pagbag-o sa lohika uban ang hinay nga proseso sa pagtukod og repository ug paghimo og mga pagbag-o niini (kung adunay milampos. , isulat sa mga komento kung giunsa). Posible nga sultihan ang mga analista: "Mga lalaki, pagkat-on sa python ug adto sa analytical replicas," apan kini usa ka dugang nga kinahanglanon alang sa pagrekrut, ug ingon og kini kinahanglan nga likayan kung mahimo. Nakahukom kami nga sulayan ang paggamit sa teknolohiya sa FDW (Foreign Data Wrapper): sa panguna, kini usa ka sumbanan nga dblink, nga naa sa sukaranan sa SQL, apan adunay kaugalingon nga labi ka dali nga interface. Pinasukad niini, naghimo kami usa ka solusyon, nga sa katapusan nakuha, ug among gihusay kini. Ang mga detalye niini mao ang hilisgutan sa usa ka bulag nga artikulo, ug tingali labaw pa sa usa, tungod kay gusto nako maghisgot bahin sa daghang: gikan sa pag-synchronize sa mga schemas sa database hangtod sa pag-access sa kontrol ug depersonalization sa personal nga datos. Kinahanglan usab nga maghimo usa ka reserbasyon nga kini nga solusyon dili usa ka kapuli sa tinuod nga analitikal nga mga database ug mga repository; kini nagsulbad lamang sa usa ka piho nga problema.

Sa taas nga lebel kini ingon niini:

Adunay PostgreSQL database diin ang mga tiggamit makatipig sa ilang data sa trabaho, ug labaw sa tanan, ang analytical replika sa tanang serbisyo konektado niini nga database pinaagi sa FDW. Kini nagpaposible sa pagsulat sa usa ka pangutana sa daghang mga database, ug dili igsapayan kung unsa kini: PostgreSQL, MySQL, MongoDB o uban pa (file, API, kung kalit nga wala’y angay nga wrapper, mahimo nimong isulat ang imong kaugalingon). Aw, morag nindot ang tanan! Nagbuwag mi?

Kung ang tanan natapos nga dali ug yano, nan, tingali, wala’y usa ka artikulo.

Importante nga mahimong tin-aw kon sa unsang paagi giproseso sa Postgres ang mga hangyo sa hilit nga mga server. Morag makatarunganon kini, apan kasagaran ang mga tawo wala magtagad niini: Gibahin sa mga postgres ang hangyo sa mga bahin nga gipatuman nga independente sa mga hilit nga server, gikolekta kini nga datos, ug gihimo ang katapusan nga mga kalkulasyon mismo, mao nga ang katulin sa pagpatuman sa pangutana magdepende pag-ayo sa giunsa kini pagkasulat. Kinahanglan usab nga hinumdoman: kung ang datos moabut gikan sa usa ka hilit nga server, wala na kini mga indeks, wala’y bisan unsa nga makatabang sa scheduler, busa, kita ra ang makatabang ug makatambag kaniya. Ug kini gyud ang gusto nakong hisgutan sa mas detalyado.

Usa ka yano nga pangutana ug usa ka plano uban niini

Aron ipakita kung giunsa pagpangutana sa Postgres ang usa ka 6 milyon nga row table sa usa ka hilit nga server, tan-awon naton ang usa ka yano nga 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 sa VERBOSE nga pahayag nagtugot kanamo nga makita ang pangutana nga ipadala sa hilit nga server ug ang mga resulta nga among madawat alang sa dugang nga pagproseso (RemoteSQL nga linya).

Mopadayon kita ug dugang ug daghang mga filter sa atong hangyo: usa para boolean uma, usa pinaagi sa panghitabo timestamp sa interval ug usa sa 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

Dinhi naa ang punto nga kinahanglan nimong hatagan pagtagad kung nagsulat mga pangutana. Ang mga pagsala wala gibalhin ngadto sa hilit nga server, nga nagpasabot nga aron ipatuman kini, ang mga Postgres mobira sa tanang 6 ka milyon nga mga laray aron dayon ma-filter sa lokal (Filter row) ug maghimo sa paghugpong. Ang yawe sa kalampusan mao ang pagsulat sa usa ka pangutana aron ang mga pagsala mabalhin sa hilit nga makina, ug kami makadawat ug magtipon lamang sa gikinahanglan nga mga laray.

Kana usa ka booleanshit

Uban sa boolean nga mga natad ang tanan yano ra. Sa orihinal nga hangyo, ang problema tungod sa operator is. Kung ilisan nimo kini sa =, unya atong makuha ang mosunod nga 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

Sama sa imong nakita, ang filter milupad sa usa ka hilit nga server, ug ang oras sa pagpatuman gikunhuran gikan sa 27 hangtod 19 segundos.

Kini mao ang bili noting nga ang operator is lahi sa operator = tungod kay mahimo kini nga molihok uban ang Null nga kantidad. Nagpasabot kana dili Tinuod ibilin ang mga kantidad nga False ug Null sa filter, samtang != Tinuod magbilin lamang sa mga False values. Busa, sa pag-ilis sa operator dili duha ka kondisyon sa OR operator kinahanglang ipasa sa filter, pananglitan, DIIN (col != Tinuod) O (ang col kay null).

Nasulbad na nato ang boolean, magpadayon kita. Sa pagkakaron, atong ibalik ang Boolean filter sa orihinal nga porma niini aron independente nga makonsiderar ang epekto sa ubang mga kausaban.

timestamptz? hz

Sa kinatibuk-an, kanunay ka kinahanglan nga mag-eksperimento kung giunsa ang husto nga pagsulat sa usa ka hangyo nga naglambigit sa mga hilit nga server, ug pagkahuman mangita usa ka katin-awan kung ngano nga kini nahitabo. Gamay ra kaayo nga impormasyon bahin niini ang makit-an sa Internet. Mao nga, sa mga eksperimento nakit-an namon nga ang usa ka piho nga filter sa petsa molupad sa hilit nga server nga adunay usa ka bang, apan kung gusto namon nga itakda ang petsa nga dinamiko, pananglitan, karon () o CURRENT_DATE, dili kini mahitabo. Sa among pananglitan, gidugang namo ang usa ka filter aron ang kolum nga gimugna_sa adunay mga datos sa eksaktong 1 ka bulan sa miagi (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Unsa ang atong gibuhat niini nga kaso?

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

Gisultihan namon ang tigplano nga kuwentahon ang petsa sa subquery nga abante ug ipasa ang andam na nga variable sa filter. Ug kini nga pahiwatig naghatag kanamo usa ka maayo kaayo nga sangputanan, ang hangyo nahimong hapit 6 ka beses nga mas paspas!

Sa makausa pa, importante nga mag-amping dinhi: ang tipo sa datos sa subquery kinahanglan nga parehas sa natad nga among gisala, kung dili ang tigplano magdesisyon nga tungod kay lahi ang mga tipo, kinahanglan una nga makuha ang tanan. ang datos ug i-filter kini sa lokal.

Atong ibalik ang filter sa petsa sa orihinal nga kantidad niini.

Freddy vs. Jsonb

Sa kinatibuk-an, ang mga natad sa Boolean ug mga petsa igo na nga nagpadali sa among pangutana, apan adunay usa pa nga tipo sa datos nga nahabilin. Ang pakigbugno sa pagsala niini, sa tinuod, wala pa matapos, bisan kung adunay kalampusan usab dinhi. Mao nga, ingon niini kung giunsa namon naagi ang pagsala jsonb field ngadto sa hilit nga 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

Imbis sa pagsala sa mga operator, kinahanglan nimo nga gamiton ang presensya sa usa ka operator jsonb sa lahi nga. 7 segundos imbes sa orihinal nga 29. Sa pagkakaron kini ang bugtong malampuson nga kapilian alang sa pagpasa sa mga pagsala pinaagi sa jsonb sa usa ka hilit nga server, apan dinhi hinungdanon nga tagdon ang usa ka limitasyon: gigamit namon ang bersyon 9.6 sa database, apan sa katapusan sa Abril nagplano kami nga makompleto ang katapusan nga mga pagsulay ug mobalhin sa bersyon 12. Kung mag-update kami, isulat namon kung giunsa kini naapektuhan, tungod kay adunay daghang mga pagbag-o nga adunay daghang paglaum: json_path, bag-ong pamatasan sa CTE, iduso (naa na sukad sa bersyon 10). Gusto gyud nako nga sulayan kini sa dili madugay.

Tapusa siya

Among gisulayan kung giunsa ang matag pagbag-o nakaapekto sa katulin sa paghangyo sa tinagsa. Atong tan-awon kung unsa ang mahitabo kung ang tanan nga tulo ka mga filter gisulat sa husto.

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, ang hangyo morag mas komplikado, kini usa ka pinugos nga bayad, apan ang katulin sa pagpatuman mao ang 2 segundos, nga labaw pa sa 10 ka beses nga mas paspas! Ug naghisgot kami bahin sa usa ka yano nga pangutana batok sa medyo gamay nga set sa datos. Sa tinuud nga mga hangyo, nakadawat kami usa ka pagtaas hangtod sa pila ka gatos ka beses.

Sa pag-summarize: kung mogamit ka sa PostgreSQL sa FDW, susiha kanunay nga ang tanan nga mga pagsala gipadala sa hilit nga server, ug malipay ka ... Labing menos hangtod nga makaapil ka sa mga lamesa gikan sa lainlaing mga server. Apan kana usa ka istorya alang sa laing artikulo.

Salamat sa imong pagtagad! Ganahan kong makadungog sa mga pangutana, komento, ug istorya bahin sa imong mga kasinatian sa mga komento.

Source: www.habr.com