L'architettura di u microserviziu, cum'è tuttu in questu mondu, hà i so pros è cuns. Certi prucessi diventanu più faciuli cun ellu, altri più difficili. È per a fine di a rapidità di u cambiamentu è una scalabilità megliu, avete bisognu di sacrificà. Unu di elli hè a cumplessità di l'analitiche. Se in un monolitu tutte l'analitiche operative ponu esse ridutte à e dumande SQL à una replica analitica, allora in una architettura multiserviziu ogni serviziu hà a so propria basa di dati è pare chì una dumanda ùn hè micca abbastanza (o forse serà?). Per quelli chì anu interessatu in quantu avemu risoltu u prublema di analitica operativa in a nostra cumpagnia è cumu avemu amparatu à vive cù sta suluzione - benvenutu.

Mi chjamu Pavel Sivash, in DomClick aghju travagliatu in una squadra chì hè rispunsevule per mantene u magazzinu di dati analitici. Convenzionalmente, e nostre attività ponu esse attribuite à l'ingegneria di dati, ma, in fattu, a gamma di e funzioni hè assai più larga. Ci sò standard di ingegneria di dati ETL / ELT, supportu è adattazione di strumenti di analisi di dati è u sviluppu di i so strumenti. In particulare, per u rapportu operativu, avemu decisu di "pretende" chì avemu un monolitu è ​​dà à l'analista una basa di dati chì cuntene tutte e dati chì anu bisognu.

In generale, avemu cunsideratu diverse opzioni. Hè statu pussibule di custruisce un repositoriu cumpletu - avemu ancu pruvatu, ma, per esse onestu, ùn pudemu micca fà amici cù cambiamenti abbastanza frequenti in a logica cù un prucessu piuttostu lento di custruisce un repository è fà cambiamenti in questu ( se qualchissia hà successu, scrivite in i cumenti cumu). Puderete dì à l'analista: "Ragazzi, amparate python è andate à e linee analitiche", ma questu hè un requisitu di reclutamentu supplementu, è pareva chì questu deve esse evitatu se pussibule. Avemu decisu di pruvà à utilizà a tecnulugia FDW (Foreign Data Wrapper): in fattu, questu hè un dblink standard, chì hè in u standard SQL, ma cù a so interfaccia assai più còmuda. Basatu nantu à questu, avemu fattu una decisione, chì eventualmente hà arradicatu, avemu stallatu nantu à questu. I so ditagli sò u tema di un articulu separatu, è forsi più di unu, perchè vogliu parlà assai: da a sincronizazione di schema di basa di dati à u cuntrollu di l'accessu è a depersonalizazione di e dati persunali. Hè ancu deve esse nutatu chì sta suluzione ùn hè micca un sustitutu di basa di dati analitiche reali è repositori, solu solu solu un prublema specificu.

À u livellu più altu, pare cusì:

Ci hè una basa di dati PostgreSQL induve l'utilizatori ponu almacenà e so dati di u travagliu, è più impurtante, repliche analitiche di tutti i servizii sò cunnessi à sta basa di dati via FDW. Questu permette di scrive una dumanda à parechje basa di dati, è ùn importa micca ciò chì hè: PostgreSQL, MySQL, MongoDB o qualcosa altru (file, API, se di colpu ùn ci hè micca un wrapper adattatu, pudete scrive u vostru propiu). Ebbè, tuttu pare esse grande! Rupture ?

Se tuttu finisci cusì rapidamente è simplicemente, allora, probabilmente, l'articulu ùn esiste micca.

Hè impurtante per esse chjaru cumu postgres gestisce e dumande à i servitori remoti. Questu pare lògicu, ma spessu a ghjente ùn presta micca attenzione à questu: postgres divide a dumanda in parti chì sò eseguite indipindentamente nantu à i servitori remoti, raccoglie queste dati, è eseguisce i calculi finali stessu, cusì a velocità di esecuzione di a query dependerà assai di cumu. hè scrittu. Hè ancu deve esse nutatu: quandu i dati venenu da un servitore remotu, ùn anu più indici, ùn ci hè nunda chì aiutarà u pianificatore, per quessa, solu noi stessi pudemu aiutà è suggerisce. È questu hè ciò chì vogliu parlà in più detail.

Una dumanda simplice è un pianu cun ella

Per dimustrà cumu Postgres interruga una tavola di 6 milioni di fila in un servitore remoto, fighjemu un pianu simplice.

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

Utilizà a dichjarazione VERBOSE permette di vede a quistione chì serà mandata à u servitore remotu è i risultati di quale avemu da riceve per più prucessu (stringa RemoteSQL).

Andemu un pocu più è aghjunghje parechji filtri à a nostra dumanda: unu per booleanu campu, unu per entrata sicunnu per intervallu è unu per 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

Hè quì chì u mumentu si trova, chì deve esse attentu à quandu scrivite e dumande. I filtri ùn sò micca stati trasferiti à u servitore remotu, chì significa chì per eseguisce, postgres tira tutte e 6 milioni di fila per filtrà in u locu (a linea di Filtru) è eseguisce l'aggregazione dopu. A chjave per u successu hè di scrive una quistione per chì i filtri sò trasmessi à a macchina remota, è avemu ricevutu è aggregate solu e fila necessariu.

Eccu alcuni booleanshit

Cù campi booleani, tuttu hè simplice. In a quistione originale, u prublema era duvuta à l'operatore is. Se l'avemu rimpiazzatu cù =, allora avemu u risultatu seguente:

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

Comu pudete vede, u filtru volò à u servitore remoto, è u tempu d'esekzione hè stata ridutta da 27 à 19 seconde.

Hè devi esse nutatu chì l'operatore is differente da l'operatore = quellu chì pò travaglià cù u valore Null. Significa chì ùn hè micca Veru in u filtru lasciarà i valori False è Null, mentri != Veru lasciarà solu i valori falsi. Dunque, quandu si rimpiazza l'operatore ùn hè micca duvete passà duie cundizioni à u filtru cù l'operatore OR, per esempiu, WHERE (col != True) OR (col hè nulla).

Cù boolean capitu, andendu avanti. Intantu, vultemu u filtru per valore booleanu à a so forma originale per cunsiderà indipindente l'effettu di altri cambiamenti.

timestamptz? hz

In generale, spessu avete da sperimentà cumu scrive currettamente una dumanda chì implica servitori remoti, è solu allora cercate una spiegazione di perchè questu succede. Pochi infurmazione nantu à questu pò esse truvata in Internet. Allora, in esperimenti, avemu truvatu chì un filtru di data fissa vola à un servitore remotu cù un bang, ma quandu vulemu stabilisce a data dinamicamente, per esempiu, avà () o CURRENT_DATE, questu ùn succede micca. In u nostru esempiu, avemu aghjustatu un filtru per chì a colonna created_at cuntene dati per esattamente 1 mese in u passatu (FRA CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Chì avemu fattu in stu casu?

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

Avemu dumandatu à u pianificatore per calculà a data in anticipu in a subquery è passà a variabile digià preparata à u filtru. È questu suggerimentu ci hà datu un grande risultatu, a dumanda hè diventata quasi 6 volte più veloce!

In novu, hè impurtante d'esse attentu quì: u tipu di dati in a subquery deve esse uguali à quellu di u campu da quale filtremu, altrimente u pianificatore decide chì, postu chì i tipi sò diffirenti è hè necessariu prima di ottene tutte e dati è filtrà lu lucale.

Riturnemu u filtru per data à u so valore originale.

Freddy vs. jsonb

In generale, i campi booleani è e date anu digià abbastanza acceleratu a nostra dumanda, ma ci era un altru tipu di dati. A battaglia cù u filtru da ellu, per esse onestu, ùn hè ancu finita, ancu s'ellu ci sò successi ancu quì. Allora, eccu cumu avemu riisciutu à passà u filtru jsonb campu à un servitore remoto.

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

Invece di filtrà l'operatori, duvete aduprà a presenza di un operatore. jsonb in un differente. 7 seconde invece di l'uriginale 29. Finu à avà, questu hè l'unica opzione riescita per u trasferimentu di filtri. jsonb à un servitore remotu, ma quì hè impurtante di piglià in contu una limitazione: usemu a versione 9.6 di a basa di dati, ma à a fine d'aprile avemu pensatu à compie l'ultimi testi è passà à a versione 12. Appena avemu aghjurnatu, scriveremu cumu hà affettatu, perchè ci sò assai cambiamenti per i quali ci sò parechje speranze: json_path, novu cumpurtamentu CTE, push down (esistenti da a versione 10). Vogliu veramente pruvà prestu.

Finisci lu

Avemu verificatu cumu ogni cambiamentu afecta a velocità di a dumanda individualmente. Videmu avà ciò chì succede quandu tutti i trè filtri sò scritti currettamente.

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

Iè, a quistione pare più cumplicata, hè un prezzu furzatu, ma a velocità di esecuzione hè di 2 seconde, chì hè più di 10 volte più veloce! È parlemu di una dumanda simplice nantu à un settore relativamente chjucu di dati. Nantu à e dumande reali, avemu ricevutu un aumentu di parechji centu volte.

Per riassume: sè vo avete aduprà PostgreSQL cù FDW, verificate sempre se tutti i filtri sò mandati à u servitore remotu è sarete felice ... Almenu finu à avè ghjuntu à unisce trà e tavule da diversi servitori. Ma hè una storia per un altru articulu.

Grazie per a vostra attenzione! Mi piacerebbe à sente dumande, cumenti è storie nantu à e vostre sperienze in i cumenti.

Source: www.habr.com