Como usar materialized views para acelerar queries analíticas

1. O que são materialized views e por que são importantes para análises

Materialized views são objetos de banco de dados que armazenam fisicamente o resultado de uma consulta, diferentemente das views comuns, que são apenas definições virtuais executadas sob demanda. Enquanto uma view tradicional calcula os dados toda vez que é acessada, uma materialized view mantém os dados pré-computados em disco, permitindo acesso instantâneo a resultados agregados complexos.

Para queries analíticas, essa diferença é crucial. Dashboards de vendas, relatórios mensais de performance e agregações pesadas que envolvem milhões de linhas podem ser acelerados em ordens de grandeza. Em vez de executar joins e agregações em tabelas enormes a cada requisição, a materialized view entrega dados já processados, reduzindo drasticamente o tempo de resposta.

2. Criando e gerenciando materialized views no PostgreSQL

A sintaxe básica para criar uma materialized view no PostgreSQL é direta:

CREATE MATERIALIZED VIEW mv_vendas_mensais AS
SELECT 
    DATE_TRUNC('month', data_venda) AS mes,
    categoria,
    COUNT(*) AS total_vendas,
    SUM(valor) AS receita_total
FROM vendas
GROUP BY 1, 2
WITH DATA;

A cláusula WITH DATA popula a view imediatamente. Para melhorar ainda mais a performance, podemos adicionar índices:

CREATE INDEX idx_mv_vendas_mensais_mes ON mv_vendas_mensais (mes);
CREATE INDEX idx_mv_vendas_mensais_categoria ON mv_vendas_mensais (categoria);

Outro exemplo prático: uma view para totais por cliente ativo:

CREATE MATERIALIZED VIEW mv_total_por_cliente AS
SELECT 
    cliente_id,
    COUNT(*) AS pedidos,
    SUM(valor_total) AS gasto_total,
    AVG(valor_total) AS ticket_medio
FROM pedidos
WHERE status = 'concluido'
GROUP BY cliente_id;

3. Atualização de dados: refresh completo vs. concorrente

Materialized views não são atualizadas automaticamente. Para atualizar os dados, usamos:

REFRESH MATERIALIZED VIEW mv_vendas_mensais;

Esse comando bloqueia a tabela durante a execução, impedindo leituras. Para evitar downtime, o PostgreSQL oferece:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_vendas_mensais;

Para usar CONCURRENTLY, a view precisa ter uma chave primária ou índice único. A atualização ocorre sem bloquear leituras, mas consome mais recursos e é mais lenta.

Estratégias de agendamento comuns incluem:

  • pg_cron: agendar refresh noturno
  • Triggers: atualizar após inserções em lote
  • Job externo: cron job do sistema operacional

Exemplo com pg_cron:

SELECT cron.schedule('refresh-mv', '0 3 * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_vendas_mensais');

4. Quando usar (e quando evitar) materialized views

Cenários ideais:
- Dados históricos que mudam com pouca frequência
- Agregações que demoram mais de 5 segundos para executar
- Consultas repetitivas em dashboards e relatórios
- Redução de carga em joins complexos entre tabelas grandes

Limitações:
- Dados não estão em tempo real (latência entre refreshes)
- Custo de armazenamento duplicado
- Complexidade adicional de manutenção

Alternativas a considerar:
- Views comuns para consultas leves
- Tabelas temporárias para análises ad-hoc
- Índices parciais para filtros específicos
- Cache externo com Redis para dados de alta frequência

5. Otimização avançada e boas práticas

Índices específicos podem transformar materialized views em ferramentas ainda mais poderosas:

-- Índice BRIN para dados ordenados temporalmente
CREATE INDEX idx_brin_mes ON mv_vendas_mensais USING BRIN (mes);

-- Índice B-tree composto para consultas comuns
CREATE INDEX idx_composto ON mv_total_por_cliente (categoria, mes);

Para atualizações incrementais, ferramentas como pg_ivm permitem refreshes parciais:

-- Exemplo conceitual com pg_ivm (extensão)
CREATE INCREMENTAL MATERIALIZED VIEW mv_vendas_diarias AS
SELECT data_venda, SUM(valor) FROM vendas GROUP BY 1;

Monitoramento constante é essencial:

SELECT 
    schemaname || '.' || matviewname AS mv_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || matviewname)) AS size,
    last_refresh
FROM pg_matviews;

6. Integração com Redis Streams e outras ferramentas do ecossistema

Materialized views podem ser combinadas com Redis Streams para criar pipelines de dados quase em tempo real. Por exemplo, uma view de resumo diário pode ser atualizada a cada hora via stream:

-- Pseudocódigo para integração
-- 1. Redis Stream recebe eventos de vendas
-- 2. Processo consome stream a cada hora
-- 3. Acumula dados e dispara REFRESH CONCURRENTLY

Comparativamente, o Redis é excelente para cache de queries rápidas (milissegundos), enquanto materialized views no PostgreSQL são melhores para agregações complexas que exigem SQL completo e consistência transacional.

7. Troubleshooting e manutenção em produção

Para identificar queries que se beneficiariam de materialized views:

-- Encontrar queries lentas no log
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE query LIKE '%GROUP BY%' OR query LIKE '%SUM(%'
ORDER BY total_time DESC
LIMIT 10;

Gerenciamento de dependências: se uma materialized view depende de outra, o refresh precisa ser em ordem:

REFRESH MATERIALIZED VIEW mv_base_vendas;
REFRESH MATERIALIZED VIEW mv_agregado_vendas; -- depende da anterior

Para backup de views grandes, utilize pg_dump com opção específica:

pg_dump --table=mv_vendas_mensais --data-only banco > mv_backup.sql

Referências