A arquitetura de microsserviços, como tudo neste mundo, tem seus prós e contras. Alguns processos tornam-se mais fáceis, outros mais complicados. E por uma questão de velocidade da mudança e melhor escalabilidade, sacrifícios devem ser feitos. Um deles é a complicação da análise. Se em um monólito todas as análises operacionais podem ser reduzidas a consultas SQL para uma réplica analítica, em uma arquitetura multisserviço, cada serviço tem sua própria base e parece que uma consulta não pode ser dispensada (ou pode ser dispensada?). Para aqueles que estão interessados em como resolvemos o problema da análise operacional em nossa empresa e em como aprendemos a conviver com essa solução - bem-vindo.

Meu nome é Pavel Sivash. No DomKlik, trabalho em uma equipe responsável pela manutenção do data warehouse analítico. Convencionalmente, nossas atividades podem ser atribuídas à data da engenharia, mas, de fato, a gama de tarefas é muito maior. Existem padrões ETL / ELT para engenharia de datas, suporte e adaptação de ferramentas para análise de dados e desenvolvimento de suas próprias ferramentas. Em particular, para relatórios operacionais, decidimos "fingir" que possuímos um monólito e fornecer aos analistas uma base na qual eles terão todos os dados necessários.

Em geral, consideramos opções diferentes. Foi possível construir um repositório completo - até tentamos, mas para ser sincero, falhamos em fazer amigos mudanças frequentes na lógica com um processo bastante lento de construir um repositório e fazer alterações (se alguém conseguiu, escreva nos comentários como). Poderíamos dizer aos analistas: “Pessoal, aprendem python e seguem dicas analíticas”, mas esse é um requisito adicional para o recrutamento de funcionários, e parecia que isso deveria ser evitado, se possível. Decidimos tentar usar a tecnologia FDW (Foreign Data Wrapper): na verdade, esse é o dblink padrão, que está no padrão SQL, mas com sua interface muito mais conveniente. Com base nisso, tomamos uma decisão, que acabou se estabelecendo, paramos nela. Seus detalhes são objeto de um artigo separado, ou talvez não de um,porque quero falar muito: da sincronização de esquemas de banco de dados ao controle de acesso e anonimização de dados pessoais. Você também precisa fazer uma reserva de que essa solução não substitui bancos de dados e repositórios analíticos reais, pois resolve apenas um problema específico.

De nível superior, é assim:

Existe um banco de dados PostgreSQL, no qual os usuários podem armazenar seus dados de trabalho e, mais importante, as réplicas analíticas de todos os serviços são conectadas a esse banco de dados através do FDW. Isso torna possível escrever uma consulta em vários bancos de dados, não importa o que seja: PostgreSQL, MySQL, MongoDB ou qualquer outra coisa (arquivo, API, se de repente não houver um invólucro adequado, você poderá escrever seu próprio). Bem, tudo parece ser super! Nós discordamos?

Se tudo terminasse com tanta rapidez e simplicidade, provavelmente não haveria artigo.

É importante entender claramente como o postgres lida com solicitações para servidores remotos. Isso parece lógico, mas muitas vezes eles não prestam atenção a isso: o postgres divide a solicitação em partes que são executadas em servidores remotos de forma independente, coleta esses dados e os cálculos finais são executados por si só, portanto a velocidade da solicitação depende muito de como ela é escrita. Também deve ser observado: quando os dados vêm de um servidor remoto, eles não têm mais índices, não há nada que possa ajudar o agendador; portanto, somente nós podemos ajudar e sugerir. E eu gostaria de falar mais sobre isso.

Pedido simples e plano com ele

Para mostrar como o postgres executa uma consulta em uma tabela de 6 milhões de linhas em um servidor remoto, vejamos um plano simples.

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

O uso da instrução VERBOSE permite visualizar a solicitação que será enviada ao servidor remoto e os resultados que receberemos para processamento adicional (linha RemoteSQL).

Vamos um pouco mais além e adicione vários filtros à nossa consulta: um pelo campo booleano , um pelo carimbo de data / hora no intervalo e um pelo 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

É aqui que está o momento em que você precisa prestar atenção ao escrever consultas. Os filtros não foram transferidos para o servidor remoto, o que significa que, para executá-lo, o postgres estende todas as 6 milhões de linhas, só então para filtrar localmente (linha de filtro) e executar a agregação. A chave do sucesso é escrever uma solicitação para que os filtros sejam transferidos para a máquina remota e recebamos e agregamos apenas as linhas necessárias.

Isso é alguma merda booleana

Com campos booleanos, tudo é simples. Na solicitação original, o problema ocorreu devido à instrução is . Se o substituirmos por = , obteremos o seguinte resultado:

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

Como você pode ver, o filtro voou para um servidor remoto e o tempo de execução foi reduzido de 27 para 19 segundos.

Vale ressaltar que o operador is difere do operador = , pois ele pode trabalhar com o valor nulo. Isso significa que não é True no filtro deixará Falso e Nulo, enquanto ! = True deixará apenas Falso. Portanto, ao substituir o operador is not , duas condições com o operador OR devem ser passadas para o filtro, por exemplo, WHERE (col! = True) OR (col é nulo) .

Com booleano resolvido, siga em frente. Enquanto isso, retorne o filtro pelo valor booleano à sua forma original, para considerar independentemente o efeito de outras alterações.

timestamptz? hz

Geralmente, é necessário experimentar como escrever uma consulta que envolva servidores remotos e só então procurar uma explicação de por que isso está acontecendo. Muito pouca informação sobre isso pode ser encontrada na Internet. Portanto, em experimentos, descobrimos que o filtro em uma data fixa voa para o servidor remoto com um estrondo, mas quando queremos definir a data dinamicamente, por exemplo, now () ou CURRENT_DATE, isso não acontece. Em nosso exemplo, adicionamos um filtro para que a coluna created_at contenha dados exatamente de um mês no passado (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). O que fizemos neste 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

Solicitamos que o planejador calculasse a data na subconsulta com antecedência e passasse a variável pronta para o filtro. E essa dica nos deu um excelente resultado, a consulta ficou quase 6 vezes mais rápida!

Novamente, é importante ter cuidado aqui: o tipo de dados na subconsulta deve ser o mesmo que o campo para o qual estamos filtrando; caso contrário, o planejador decidirá isso, já que os tipos são diferentes e você deve primeiro obter todos os dados e filtrá-los localmente.

Retorne o filtro por data ao seu valor original.

Freddy vs. Jsonb

Em geral, os campos e datas booleanos já aceleraram nossa consulta, mas havia mais um tipo de dados. A batalha pela filtragem, francamente, ainda não acabou, embora haja sucessos. Então, aqui está como conseguimos passar o campo filter by jsonb para o servidor 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

Em vez de filtrar operadores, você deve usar o operador de ter um jsonb em outro. 7 segundos em vez dos 29 iniciais. Até agora, esta é a única opção bem-sucedida de transferir filtros via jsonb para um servidor remoto, mas é importante levar em consideração uma limitação: usamos a versão do banco de dados 9.6, no entanto, planejamos concluir os testes mais recentes e passar para a versão 12 até o final de abril. Como atualizar, escreva como isso foi afetado, porque há muitas mudanças para as quais há muitas esperanças: json_path, novo comportamento do CTE, push down (existente na versão 10). Eu gostaria de experimentar em breve.

Acabe com ele

Verificamos como cada alteração afeta a velocidade da solicitação individualmente. Agora vamos ver o que acontece quando todos os três filtros são gravados corretamente.

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

Sim, a solicitação parece mais complicada, é uma placa forçada, mas a velocidade de execução é de 2 segundos, o que é 10 vezes mais rápido! E estamos falando de uma consulta simples em um conjunto de dados relativamente pequeno. Em solicitações reais, recebemos um crescimento de várias centenas de vezes.

Para resumir: se você usa o PostgreSQL com FDW, sempre verifique se todos os filtros foram enviados para o servidor remoto e você ficará feliz ... Pelo menos até chegar às junções entre tabelas de diferentes servidores. Mas esta é a história de outro artigo.

Obrigado pela atenção! Ficarei feliz em ouvir perguntas, comentários e histórias sobre sua experiência nos comentários.

Análise on-line na arquitetura de microsserviços: ajude e sugira o Postgres FDW e o Post Post ̶п̶р̶о̶с̶т̶и̶т e o

Pedido simples e plano com ele

Isso é alguma merda booleana

timestamptz? hz

Freddy vs. Jsonb

Acabe com ele

More articles: