Arsitéktur Microservice, sapertos sadayana di dunya ieu, gaduh pro sareng kontra. Sababaraha prosés jadi gampang jeung eta, batur leuwih hese. Sareng demi laju parobihan sareng skalabilitas anu langkung saé, anjeun kedah berkorban. Salah sahijina nyaéta paningkatan pajeulitna analitik. Lamun dina monolith a sadayana analytics operasional bisa diréduksi jadi queries SQL ka replica analitik, teras dina arsitektur multiservice unggal jasa boga database sorangan jeung sigana hiji query teu bisa dipigawé (atawa meureun bisa?). Pikeun anu resep kumaha urang ngarengsekeun masalah analitik operasional di perusahaan urang sareng kumaha urang diajar hirup sareng solusi ieu - wilujeng sumping.
Nami abdi Pavel Sivash, di DomClick abdi damel di tim anu tanggung jawab pikeun ngajaga gudang data analitik. Sacara konvensional, kagiatan urang tiasa digolongkeun kana rékayasa data, tapi, kanyataanna, lingkup tugasna langkung lega. Aya standar ETL / ELT pikeun rékayasa data, dukungan sareng adaptasi alat pikeun analisa data sareng pamekaran alat anjeun nyalira. Khususna, pikeun ngalaporkeun operasional, urang mutuskeun pikeun "pura-pura" yén urang gaduh monolith sareng masihan analis hiji database anu bakal ngandung sadaya data anu diperyogikeun.
Sacara umum, urang dianggap pilihan béda. Ieu mungkin pikeun ngawangun gudang pinuh-fledged - urang malah diusahakeun, tapi, mun jujur, urang teu bisa ngagabungkeun parobahan cukup sering dina logika jeung prosés rada slow ngawangun gudang sarta nyieun parobahan eta (lamun batur suksés. , tulis di komentar kumaha). Kasebut nyaéta dimungkinkeun pikeun ngabejaan analis: "Guys, diajar python tur buka réplika analitik," tapi ieu mangrupa sarat tambahan pikeun recruiting, sarta eta seemed yén ieu kudu dihindari lamun mungkin. Urang mutuskeun pikeun nyobaan ngagunakeun téhnologi FDW (Asing Data Wrapper): dasarna, ieu téh dblink baku, nu aya dina standar SQL, tapi kalawan panganteur leuwih merenah sorangan. Dumasar kana éta, kami ngadamel solusi, anu tungtungna nangkep, sareng kami netepkeunana. Rincianna mangrupikeun topik tina tulisan anu misah, sareng panginten langkung ti hiji, sabab kuring hoyong seueur pisan: ti nyinkronkeun skéma database pikeun aksés kontrol sareng depersonalisasi data pribadi. Éta ogé perlu nyieun reservasi yén solusi ieu sanes gaganti pikeun database analitik nyata jeung repositories; eta solves ngan masalah husus.
Dina tingkat luhur eta kasampak kawas kieu:
Aya database PostgreSQL dimana pamaké bisa nyimpen data karya maranéhanana, sarta paling importantly, réplika analitik sadaya jasa disambungkeun ka database ieu via FDW. Hal ieu ngamungkinkeun nulis query ka sababaraha basis data, sarta henteu masalah naon éta: PostgreSQL, MySQL, MongoDB atawa hal sejenna (file, API, lamun ujug-ujug euweuh wrapper cocog, anjeun bisa nulis sorangan). Muhun, sagalana sigana hébat! Naha urang putus?
Lamun sagalana réngsé jadi gancang sarta basajan, teras, meureun, moal aya artikel.
Kadé janten jelas ngeunaan kumaha Postgres prosés requests ka server jauh. Ieu sigana logis, tapi mindeng jalma teu nengetan eta: Postgres ngabagi pamundut kana bagian anu dieksekusi sacara mandiri dina server jauh, ngumpulkeun data ieu, sarta ngalakukeun itungan final sorangan, jadi laju palaksanaan query bakal greatly gumantung kana. kumaha ditulisna. Éta ogé kedah diperhatoskeun: nalika data sumping ti server jauh, éta henteu deui gaduh indéks, teu aya anu bakal ngabantosan jadwal, janten, ngan ukur urang anu tiasa ngabantosan sareng mamatahan anjeunna. Sareng ieu anu kuring hoyong bahas sacara langkung rinci.
A query basajan tur rencana kalawan eta
Pikeun nunjukkeun kumaha Postgres naroskeun tabel baris 6 juta dina server jauh, hayu urang tingali rencana anu sederhana.
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 msNgagunakeun pernyataan VERBOSE ngamungkinkeun urang pikeun nempo query nu bakal dikirim ka server jauh jeung hasil nu urang bakal nampa pikeun processing salajengna (RemoteSQL garis).
Hayu urang langkung jauh sareng tambahkeun sababaraha saringan kana pamundut urang: hiji kanggo boolean sawah, hiji ku kajadian timestamp dina interval jeung hiji ku 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 msIeu mangrupikeun titik anu anjeun kedah perhatosan nalika nyerat patarosan. Saringan henteu dialihkeun ka server jauh, anu hartosna pikeun ngaéksekusi éta, Postgres narik sadaya 6 juta barisan supados teras nyaring sacara lokal (Saringan baris) sareng ngalaksanakeun agrégasi. Konci pikeun kasuksesan nyaéta nyerat pamundut supados saringan ditransferkeun ka mesin jauh, sareng kami nampi sareng agrégat ngan ukur baris anu diperyogikeun.
Éta sababaraha booleanshit
Kalayan widang boolean sadayana saderhana. Dina pamundut aslina, masalah éta alatan operator is. Lamun diganti ku =, teras urang kéngingkeun hasil ieu:
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 msSakumaha anjeun tiasa tingali, saringan ngalayang ka server jauh, sareng waktos palaksanaan dikirangan tina 27 dugi ka 19 detik.
Eta sia noting yén operator is béda ti operator = sabab tiasa dianggo kalayan nilai Null. Maksudna éta henteu Leres bakal ninggalkeun nilai Palsu sareng Null dina saringan, sedengkeun != Leres bakal ninggalkeun ukur nilai Palsu. Ku alatan éta, nalika ngaganti operator anu teu dua kaayaan sareng operator OR kedah dialihkeun ka saringan, contona, WHERE (col!= True) ATAWA (col is null).
Kami parantos ngurus boolean, hayu urang teraskeun. Pikeun ayeuna, hayu urang mulangkeun saringan Boolean kana bentuk aslina supados sacara mandiri nganggap pangaruh parobahan anu sanés.
timestamptz? hz
Sacara umum, anjeun sering kedah ékspérimén sareng kumaha leres nyerat pamenta anu ngalibatkeun server jauh, teras milarian penjelasan naha ieu kajadian. Saeutik pisan inpormasi ngeunaan ieu tiasa dipendakan dina Internét. Ku kituna, dina percobaan kami kapanggih yén hiji tanggal tetep filter flies ka server jauh kalawan bang, tapi lamun urang rék nyetél tanggal dinamis, contona, ayeuna () atanapi CURRENT_DATE, ieu teu lumangsung. Dina conto urang, urang ditambahkeun filter a supados kolom created_at ngandung data pikeun persis 1 bulan kaliwat (ANTARA CURRENT_DATE - INTERVAL '7 bulan' AND CURRENT_DATE - INTERVAL '6 bulan'). Naon anu urang laksanakeun dina hal ieu?
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 msUrang ngawartoskeun Nu Ngarencana keur ngitung tanggal dina subquery sateuacanna tur lulus variabel siap-dijieun pikeun filter nu. Sareng petunjuk ieu masihan kami hasil anu saé, pamundut janten ampir 6 kali langkung gancang!
Sakali deui, penting pikeun ati-ati di dieu: jinis data dina subquery kedah sami sareng lapangan anu urang nyaring, upami henteu, perencana bakal mutuskeun yén sabab jinisna béda-béda, anjeun kedah kéngingkeun sadayana. data sareng nyaring sacara lokal.
Hayu urang balikkeun tanggal filter kana nilai aslina.
Freddy vs. Jsonb
Sacara umum, widang Boolean sareng kaping parantos nyepetkeun pamundut urang, tapi aya hiji deui jinis data anu sésana. Pertempuran sareng nyaring ku éta, jujur, masih tacan réngsé, sanaos aya kasuksésan di dieu ogé. Janten, ieu kumaha urang tiasa ngalangkungan saringan jsonb widang ka server jauh.
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 msGantina nyaring operator, anjeun kedah nganggo ayana hiji operator jsonb dina béda. 7 detik tinimbang aslina 29. Sajauh ieu hijina pilihan suksés pikeun ngirimkeun saringan via jsonb ka server jauh, tapi di dieu hal anu penting pikeun tumut kana akun hiji watesan: urang ngagunakeun vérsi 9.6 database, tapi dina ahir April urang rencanana pikeun ngarengsekeun tés panungtungan sarta pindah ka versi 12. Sakali kami ngamutahirkeun, urang bakal nulis ngeunaan kumaha eta kapangaruhan, sabab aya rada loba parobahan nu aya loba harepanana: json_path, kabiasaan CTE anyar, push handap (aya saprak versi 10). Abdi hoyong pisan nyobian eta geura-giru.
Bérés manéhna
Urang diuji kumaha unggal robah mangaruhan speed pamundut individual. Hayu urang tingali naon anu lumangsung nalika tilu saringan ditulis leres.
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 msSumuhun, pamundut teh Sigana leuwih pajeulit, ieu téh fee kapaksa, tapi speed palaksanaan 2 detik, nu leuwih ti 10 kali leuwih gancang! Sarta kami nuju ngawangkong ngeunaan hiji query basajan ngalawan set data relatif leutik. Dina pamundut nyata, kami nampi paningkatan dugi ka sababaraha ratus kali.
Pikeun nyimpulkeun: lamun make PostgreSQL kalawan FDW, salawasna pariksa yen sakabeh saringan dikirim ka server jauh, tur anjeun bakal senang ... Sahenteuna dugi ka meunang ka gabung antara tabel ti server béda. Tapi éta carita pikeun artikel séjén.
Nuhun kana perhatosanana! Abdi hoyong ngadangu patarosan, koméntar, sareng carita ngeunaan pangalaman anjeun dina koméntar.
sumber: www.habr.com