L'architettura dei microservizi, come ogni cosa in questo mondo, ha i suoi pro e contro. Alcuni processi diventano più facili, altri più difficili. E per garantire la velocità del cambiamento e una migliore scalabilità, è necessario fare dei sacrifici. Uno di questi è la crescente complessità dell’analisi. Se in un monolite tutte le analisi operative possono essere ridotte a query SQL su una replica analitica, in un'architettura multiservizio ogni servizio ha il proprio database e sembra che una query non possa essere eseguita (o forse sì?). Per coloro che sono interessati a come abbiamo risolto il problema dell'analisi operativa nella nostra azienda e come abbiamo imparato a convivere con questa soluzione, benvenuti.

Mi chiamo Pavel Sivash, in DomClick lavoro in un team responsabile della manutenzione del data warehouse analitico. Convenzionalmente, le nostre attività possono essere classificate come ingegneria dei dati, ma, in realtà, la gamma di compiti è molto più ampia. Esistono standard ETL/ELT per l'ingegneria dei dati, il supporto e l'adattamento di strumenti per l'analisi dei dati e lo sviluppo di strumenti propri. In particolare, per il reporting operativo, abbiamo deciso di “far finta” di avere un monolite e di fornire agli analisti un database che conterrà tutti i dati di cui hanno bisogno.

In generale, abbiamo considerato diverse opzioni. È stato possibile costruire un repository completo - ci abbiamo anche provato, ma, a dire il vero, non siamo stati in grado di combinare cambiamenti abbastanza frequenti nella logica con il processo piuttosto lento di costruzione di un repository e di apportarvi modifiche (se qualcuno ci è riuscito , scrivi nei commenti come). Era possibile dire agli analisti: "Ragazzi, imparate Python e andate alle repliche analitiche", ma questo è un requisito aggiuntivo per il reclutamento e sembrava che ciò dovesse essere evitato se possibile. Abbiamo deciso di provare a utilizzare la tecnologia FDW (Foreign Data Wrapper): essenzialmente si tratta di un dblink standard, che è nello standard SQL, ma con una propria interfaccia molto più comoda. Sulla base di ciò abbiamo trovato una soluzione che alla fine ha preso piede e su cui ci siamo accordati. I suoi dettagli sono argomento di un articolo a parte, e forse più di uno, poiché di questo voglio parlare molto: dalla sincronizzazione degli schemi dei database al controllo degli accessi e alla spersonalizzazione dei dati personali. È inoltre necessario riservare che questa soluzione non sostituisca i database e i repository analitici reali, ma risolve solo un problema specifico.

Al livello più alto appare così:

Esiste un database PostgreSQL in cui gli utenti possono archiviare i propri dati di lavoro e, cosa più importante, le repliche analitiche di tutti i servizi sono collegate a questo database tramite FDW. Ciò consente di scrivere una query su diversi database e non importa quale sia: PostgreSQL, MySQL, MongoDB o qualcos'altro (file, API, se all'improvviso non esiste un wrapper adatto, puoi scriverne uno tuo). Bene, sembra tutto fantastico! Ci stiamo lasciando?

Se tutto finisse così rapidamente e semplicemente, probabilmente non ci sarebbe un articolo.

È importante essere chiari su come Postgres elabora le richieste ai server remoti. Questo sembra logico, ma spesso le persone non ci prestano attenzione: Postgres divide la richiesta in parti che vengono eseguite indipendentemente su server remoti, raccoglie questi dati ed esegue autonomamente i calcoli finali, quindi la velocità di esecuzione della query dipenderà molto da come è scritto. Va anche notato: quando i dati arrivano da un server remoto, non hanno più indici, non c'è nulla che possa aiutare lo scheduler, quindi solo noi stessi possiamo aiutarlo e consigliarlo. Ed è proprio di questo che voglio parlare più in dettaglio.

Una semplice query e un piano con essa

Per mostrare come Postgres interroga una tabella da 6 milioni di righe su un server remoto, diamo un'occhiata a un piano semplice.

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

L'uso dell'istruzione VERBOSE ci permette di vedere la query che verrà inviata al server remoto e i cui risultati riceveremo per un'ulteriore elaborazione (riga RemoteSQL).

Andiamo un po' oltre e aggiungiamo diversi filtri alla nostra richiesta: uno per booleano campo, uno per occorrenza timestamp nell'intervallo e uno 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

È qui che si trova il punto a cui devi prestare attenzione quando scrivi le query. I filtri non sono stati trasferiti sul server remoto, il che significa che per eseguirlo Postgres estrae tutte le 6 milioni di righe per poi filtrarle localmente (Filter row) ed eseguire l'aggregazione. La chiave del successo è scrivere una query in modo che i filtri vengano trasferiti alla macchina remota e riceviamo e aggreghiamo solo le righe necessarie.

Sono stronzate booleane

Con i campi booleani tutto è semplice. Nella richiesta originaria il problema era dovuto all'operatore is. Se lo sostituisci con =, allora otteniamo il seguente risultato:

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

Come puoi vedere, il filtro è volato su un server remoto e il tempo di esecuzione è stato ridotto da 27 a 19 secondi.

Vale la pena notare che l'operatore is diverso dall'operatore = perché può funzionare con il valore Null. Significa che non è vero lascerà i valori False e Null nel filtro, mentre != Vero lascerà solo valori False. Pertanto, quando si sostituisce l'operatore non è due condizioni con l'operatore OR dovrebbero essere passate al filtro, ad esempio, DOVE (col!= True) O (col è nullo).

Ci siamo occupati di booleano, andiamo avanti. Per ora, riportiamo il filtro booleano alla sua forma originale per considerare in modo indipendente l'effetto di altre modifiche.

timestamp? hz

In generale, spesso devi sperimentare come scrivere correttamente una richiesta che coinvolge server remoti e solo allora cercare una spiegazione del motivo per cui ciò accade. Su Internet si possono trovare pochissime informazioni al riguardo. Quindi, negli esperimenti abbiamo scoperto che un filtro con data fissa vola sul server remoto con un botto, ma quando vogliamo impostare la data in modo dinamico, ad esempio now() o CURRENT_DATE, ciò non accade. Nel nostro esempio, abbiamo aggiunto un filtro in modo che la colonna create_at contenesse i dati esattamente di 1 mese nel passato (BETWEEN CURRENT_DATE - INTERVAL '7 Month' AND CURRENT_DATE - INTERVAL '6 Month'). Cosa abbiamo fatto in questo caso?

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

Abbiamo detto al pianificatore di calcolare in anticipo la data nella sottoquery e di passare la variabile già pronta al filtro. E questo suggerimento ci ha dato un ottimo risultato, la richiesta è diventata quasi 6 volte più veloce!

Anche qui è importante fare attenzione: il tipo di dati nella subquery deve essere lo stesso del campo su cui stiamo filtrando, altrimenti il ​​pianificatore deciderà che, poiché i tipi sono diversi, è necessario prima ottenere tutti i dati e filtrarli localmente.

Riportiamo il filtro della data al suo valore originale.

Freddy contro Jsonb

In generale, i campi e le date booleani hanno già accelerato sufficientemente la nostra query, ma rimaneva ancora un tipo di dati. La battaglia contro il filtraggio, a dire il vero, non è ancora finita, anche se anche qui si registrano dei successi. Quindi è così che siamo riusciti a superare il filtro jsonb campo al server 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 filtrare gli operatori, è necessario utilizzare la presenza di un operatore jsonb in un diverso. 7 secondi invece dei 29 originali. Finora questa è l'unica opzione riuscita per la trasmissione dei filtri tramite jsonb ad un server remoto, ma qui è importante tenere conto di una limitazione: stiamo utilizzando la versione 9.6 del database, ma entro la fine di aprile contiamo di completare gli ultimi test e passare alla versione 12. Una volta aggiornato, scriveremo come ha influenzato, perché ci sono molti cambiamenti per i quali c'è molta speranza: json_path, nuovo comportamento CTE, push down (esistente dalla versione 10). Voglio davvero provarlo presto.

Finiscilo

Abbiamo testato individualmente il modo in cui ciascuna modifica ha influenzato la velocità delle richieste. Vediamo ora cosa succede quando tutti e tre i filtri vengono scritti correttamente.

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

Sì, la richiesta sembra più complicata, si tratta di una tariffa forzata, ma la velocità di esecuzione è di 2 secondi, ovvero più di 10 volte più veloce! E stiamo parlando di una semplice query su un set di dati relativamente piccolo. Su richieste reali abbiamo ricevuto un aumento fino a diverse centinaia di volte.

Riassumendo: se usi PostgreSQL con FDW, controlla sempre che tutti i filtri vengano inviati al server remoto, e sarai felice... Almeno fino ad arrivare ai join tra tabelle di server diversi. Ma questa è una storia per un altro articolo.

Grazie per l'attenzione! Mi piacerebbe sentire domande, commenti e storie sulle tue esperienze nei commenti.

Fonte: habr.com