De visualizações materializadas a agregações contínuas: aprimorando o PostgreSQL com análises em tempo real

Permitir que os usuários acessem a análise de dados em tempo real é um recurso essencial de muitos aplicativos modernos. Imagine-se usando sua plataforma SaaS favorita: provavelmente há um painel intuitivo apresentando dados em tempo real e insights históricos. Provavelmente você poderá interagir com a plataforma, criando relatórios personalizados, explorando métricas detalhadas e visualizando tendências que abrangem semanas ou meses.

Você certamente não gostaria que esta plataforma fosse lenta para o usuário. Isso significa que o banco de dados que alimenta esses produtos precisa ser rápido na execução de consultas em grandes volumes de dados, incluindo consultas analíticas complexas.

Enquanto PostgreSQL é o banco de dados mais querido entre os desenvolvedores atualmente , não é conhecido por ser rápido na consulta de grandes volumes de dados. Mas não se preocupe: o Postgres sempre tem uma ferramenta em sua caixa de ferramentas. Uma das melhores são as visualizações materializadas.

O que são visualizações materializadas do PostgreSQL?

Com base na técnica de materialização, as visualizações materializadas do PostgreSQL pré-calculam consultas comumente executadas e armazenam os resultados como uma tabela. Ao contrário das visualizações padrão do PostgreSQL, que executam a consulta subjacente sempre que a visualização é referenciada, as visualizações materializadas persistem o resultado da consulta de origem no banco de dados. A melhor coisa sobre isso é que seu banco de dados não precisa executar a consulta toda vez que você o executa: os resultados já estão acessíveis no disco – você obterá a resposta à sua consulta com muito mais rapidez.

Esta é uma maneira incrível de otimizar respostas de consulta para consultas que exigem muitos recursos para serem computadas. Por exemplo, consultas que possam envolver o processamento de grandes volumes de dados, agregações ou junções múltiplas.

Trabalhar com visualizações materializadas é super simples. Para criar uma visualização, você usaria a instrução CREATE MATERIALIZED VIEW e sua consulta preferida.

Depois que sua visualização materializada for criada, você poderá consultá-la como uma tabela normal do PostgreSQL :

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

Essa visão materializada ficará obsoleta rapidamente até que você a atualize: mesmo que você adicione novos dados à tabela base (ou atualize ou exclua dados), a visão materializada não inclui essas alterações automaticamente; é um instantâneo no momento em que foi criado. Para atualizar a visão materializada, você precisa executar REFRESH MATERIALIZED VIEW .

 REFRESH MATERIALIZED VIEW customer_orders;

Este último ponto (como as atualizações são tratadas) é o calcanhar de Aquiles das visões materializadas, como discutiremos na próxima seção.

E quanto à análise em tempo real? Limitações das visualizações materializadas do PostgreSQL

Como dissemos, as visualizações materializadas do PostgreSQL são uma ferramenta poderosa para acelerar consultas executadas com frequência, especialmente se essas consultas abrangem grandes volumes de dados. Mas as visualizações materializadas apresentam um aspecto nada ideal: para manter suas visualizações materializadas atualizadas, elas precisam ser atualizadas.

Este único problema cria três limitações importantes:

As atualizações são ineficientes e computacionalmente caras

Ao atualizar uma visualização materializada, a consulta é recalculada em todo o conjunto de dados. Nos bastidores, quando você executa uma atualização, os dados materializados antigos são excluídos e substituídos por dados novos e rematerializados. Implementando atualizações incrementais (onde apenas os dados alterados são atualizados) tornaria o processo agregado muito mais eficiente, mas é difícil de implementar corretamente em um banco de dados relacional consistente. Às vezes, soluções alternativas são possíveis com scripts adicionais, mas estão longe de ser simples, especialmente para consultas complexas ou se dados atrasados estiverem chegando.

As atualizações não são executadas automaticamente

Conforme mencionado anteriormente, as visualizações materializadas não incorporarão automaticamente os dados mais recentes. Eles precisam ser atualizados executando REFRESH MATERIALIZED VIEW . A execução de atualizações manuais em um ambiente de produção não é viável: uma configuração muito mais realista seria automatizar a atualização.

Infelizmente, as visualizações materializadas não possuem funcionalidade de atualização automática integrada, portanto, a criação de um cronograma de atualização automática para visualizações materializadas no PostgreSQL requer algum tipo de agendador. Isso pode ser tratado no banco de dados com uma extensão ou fora do banco de dados com um agendador como o cron. No entanto, é gerenciado porque as atualizações são caras e demoradas. É muito fácil acabar em uma situação em que você não consegue atualizar a visualização com rapidez suficiente.

As visualizações materializadas não mostram resultados atualizados

Uma consequência da natureza estática das visualizações materializadas é que, quando consultadas, elas perderão os dados adicionados ou alterados desde a última atualização (mesmo que essa atualização ocorra de acordo com uma programação). Se sua janela de agendamento estiver definida para uma hora, seu total será de até uma hora mais o tempo real para fazer a atualização desatualizada. Mas muitos aplicativos hoje implicam um fluxo constante de dados sendo ingeridos e, muitas vezes, esses aplicativos precisam oferecer resultados atualizados aos seus usuários para garantir que eles recuperem informações precisas ao consultar a visualização.

É uma pena que as visões materializadas sejam limitadas por estas limitações. Se você estiver construindo uma plataforma SaaS a partir de um conjunto de dados em tempo real, com novos dados chegando frequentemente, as visualizações materializadas devem ser completamente descartadas?

A resposta é não. Na Timescale, construímos uma solução que aprimora efetivamente as visualizações materializadas para torná-las mais adequadas para aplicações modernas: agregados contínuos.

Conheça agregados contínuos: visualizações materializadas com atualizações automáticas para análises em tempo real

Imagine um mundo onde as visualizações materializadas não sejam apenas instantâneos estáticos, mas atualizadas de forma dinâmica e eficiente. Você acessaria a melhoria de desempenho da consulta que procura sem se preocupar com mais nada. Bem, parece que descrevemos os agregados contínuos do Timescale.

Agregados contínuos (disponíveis para todos os bancos de dados PostgreSQL por meio da extensão TimescaleDB e na AWS por meio da plataforma Timescale) são visualizações materializadas aprimoradas com recursos de atualização automatizados e eficientes e um elemento em tempo real. Eles se parecem quase exatamente com visualizações materializadas, mas permitem o seguinte:

• Atualizações automáticas por meio de uma política de atualização
• Um processo de atualização mais eficiente: quando uma atualização é executada, ela afetará apenas os dados que foram alterados desde a última atualização
• Resultados atualizados, expandindo os casos de uso onde as visualizações materializadas podem ser aproveitadas (como análises em tempo real, painéis ao vivo, relatórios e outros)

Tornando as atualizações automáticas e eficientes em termos de recursos

Criar uma agregação contínua é muito semelhante à criação de uma visão materializada (e também pode ser consultada como uma tabela PostgreSQL normal):

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

Mas, diferentemente das visualizações materializadas, criar uma política de atualização é simples. Você pode definir facilmente o intervalo de atualização no banco de dados, garantindo que sua agregação contínua seja atualizada automática e periodicamente.

O exemplo abaixo configura uma política de atualização para atualizar o agregado contínuo a cada 30 minutos. O parâmetro end_offset define o intervalo de tempo dos dados a serem atualizados e o schedule_interval define a frequência com que a agregação contínua será atualizada:

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

Quando esta política de atualização entrar em vigor, o processo será muito mais eficiente do que se estivéssemos usando uma visão materializada simples. Ao contrário da execução de REFRESH MATERIALIZED VIEW , quando uma agregação contínua é atualizada, o Timescale não elimina todos os dados antigos e recalcula a agregação em relação a eles: o mecanismo apenas executa a consulta no período de atualização mais recente (por exemplo, 30 minutos) e adiciona-o para a materialização.

Da mesma forma, são identificados UPDATE s e DELETE s realizados neste último período, recalculando o chunk (partição) que os envolve. (Agregados contínuos construídos na escala de tempo hipertabelas , que são tabelas PostgreSQL particionadas automaticamente. Esta é uma enorme vantagem, permitindo que o mecanismo recalcule apenas partições específicas em comparação com a tabela inteira quando os dados forem alterados.)

Mostrando resultados atualizados para análises em tempo real

Mas, como os agregados contínuos resolvem o problema de visualização de resultados atualizados? O que acontece se novos dados forem adicionados após a última atualização e eu consultar a agregação contínua?

Para permitir esta funcionalidade, adicionamos funcionalidade de agregação em tempo real para agregados contínuos. Quando a agregação em tempo real está habilitada e você consultar sua agregação contínua, o resultado que você verá combinará duas partes:

• Os dados materializados na visão materializada subjacente, que foi atualizada na última atualização.
• Os dados brutos mais recentes, ainda não materializados, que ainda residem exclusivamente em sua tabela base (ou hipertabela, para ser exato).

Esta funcionalidade transforma visualizações materializadas de instantâneos estáticos em entidades dinâmicas, garantindo que os dados armazenados não sejam apenas um reflexo histórico, mas uma representação atualizada dos conjuntos de dados subjacentes.

Usando agregações contínuas: exemplo

Mesmo que tudo isso pareça bom, (espero) ficará muito melhor com um exemplo.

Imagine uma plataforma utilizada por agências de transporte e empresas de transporte compartilhado. Esta plataforma contém um dashboard no qual as empresas podem ter uma visão geral do estado da sua frota, incluindo uma tabela com o estado mais recente das principais métricas e duas visualizações que mostram o desempenho das métricas naquele determinado dia e no contexto da semana.

Para potencializar esta aplicação, teríamos primeiro uma hipertabela na qual os dados sobre os passeios são constantemente inseridos. A hipertabela poderia ser algo assim:

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

Hipertabelas são muito rápidas e escaláveis – esta tabela permanecerá em bom desempenho mesmo quando tiver bilhões de linhas.

Para alimentar a tabela fornecendo uma visão geral ao vivo, usaríamos uma agregação contínua para agrupar os dados em 30 minutos. Isso manteria o processo rápido e responsivo:

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

No código anterior, o parâmetro end_offset garante que a agregação não tente atualizar imediatamente os dados mais recentes, permitindo algum tempo de buffer para acomodar quaisquer atrasos na chegada dos dados. Definir end_offset como 10 minutes significa que a agregação atualizará os dados com pelo menos 10 minutos, garantindo que não perca atualizações devido a pequenos atrasos no fluxo de dados. Em um caso de uso real, você ajustaria esse valor com base no atraso médio observado no pipeline de dados.

Para potencializar a visualização que oferece a visualização diária, criaríamos um segundo agregado contínuo. Neste gráfico, os dados estão sendo exibidos por hora, portanto não precisamos de uma granularidade por minuto como no anterior:

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

Por fim, para alimentar o gráfico que oferece a visualização semanal, criaríamos mais um agregado contínuo, desta vez agregando os dados por dia:

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS Para tornar a experiência de definição de agregados contínuos ainda mais eficiente, A escala de tempo introduziu agregados contínuos hierárquicos no TimescaleDB 2.9. Depois de se familiarizar com os agregados contínuos, você poderá começar a criá-los sobre outros agregados contínuos — por exemplo, no exemplo anterior, você também poderia definir os agregados por hora sobre o agregado por minuto.

Conclusão

Mesmo que o PostgreSQL não tenha sido originalmente desenvolvido para aplicações que precisam processar grandes conjuntos de dados em tempo real, adivinhe: esses tipos de cargas de trabalho estão agora em toda parte. Porém, o PostgreSQL vem com recursos que auxiliam nessa tarefa. As visualizações materializadas estão entre as mais poderosas, pois permitem pré-computar os resultados da consulta e armazená-los em disco para recuperação rápida.

No entanto, as visões materializadas têm três limitações importantes. Primeiro, acionar atualizações é muito ineficiente do ponto de vista computacional. Em segundo lugar, mesmo configurar essas atualizações automáticas não é um processo contínuo. Terceiro, as visualizações materializadas não mostram resultados atualizados, pois excluem os dados que foram adicionados ou modificados desde a última atualização.

Essas limitações tornam as visualizações materializadas uma solução pouco prática para muitas aplicações modernas. Para resolver isso, construímos agregados contínuos. Estas são visualizações materializadas do PostgreSQL nas quais você pode definir facilmente uma política de atualização, para que as atualizações aconteçam automaticamente. Essas atualizações também são incrementais e, portanto, muito mais eficientes. Por último, as agregações contínuas permitem combinar os dados que foram materializados com os dados brutos adicionados e modificados desde a última atualização, garantindo que você obterá apenas resultados atualizados.

Se você estiver executando o PostgreSQL em seu hardware, poderá acessar agregações contínuas instalando a extensão TimescaleDB . Se você estiver na AWS, confira a plataforma Timescale . Os primeiros 30 dias são gratuitos.

Escrito por Carlota Soto e Mat Arye.