Materialized View Refresh Strategies: Concurrent e Agendado

1. Introdução às Materialized Views no PostgreSQL

Materialized views (views materializadas) 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 comum computa os dados a cada acesso, uma materialized view mantém os dados persistidos em disco, permitindo consultas extremamente rápidas sem reexecutar a query subjacente.

Os casos de uso típicos incluem:
- Relatórios agregados pesados (somas, contagens, médias sobre milhões de linhas)
- Dashboards em tempo quase real que exigem performance consistente
- Pipelines de ETL que precisam de snapshots intermediários
- Cenários onde a fonte de dados é externa ou remota (via dblink ou postgres_fdw)

O comando básico para atualizar uma materialized view é:

REFRESH MATERIALIZED VIEW nome_da_view;

Esse comando substitui todo o conteúdo da view pelos resultados atuais da consulta. No entanto, seu comportamento padrão bloqueia a view para leituras durante a operação, o que pode ser problemático em sistemas com alta concorrência.

2. Refresh Completo vs. Concurrent: Entendendo os Modos

O PostgreSQL oferece dois modos de refresh:

Refresh Padrão (REFRESH MATERIALIZED VIEW): Adquire um bloqueio ACCESS EXCLUSIVE na view. Isso significa que nenhuma outra transação pode ler ou escrever na view enquanto o refresh estiver em andamento. Para views pequenas ou operações noturnas, isso é aceitável, mas para sistemas 24/7 pode causar downtime.

Refresh Concurrente (REFRESH MATERIALIZED VIEW CONCURRENTLY): Permite que leitores continuem acessando a view enquanto o refresh ocorre. O bloqueio é apenas ROW EXCLUSIVE, permitindo consultas SELECT simultâneas.

Os trade-offs são claros:

Aspecto Refresh Padrão Refresh Concurrente
Bloqueio de leitura Sim (ACCESS EXCLUSIVE) Não
Performance Mais rápido (menos overhead) Mais lento (usa tabela temporária)
Espaço em disco Mínimo (substitui in-place) Dobro temporário (tabela antiga + nova)
Pré-requisito Nenhum Índice único obrigatório

3. Estratégia de Refresh Concurrent: Funcionamento Interno

O refresh concorrente opera em três fases:

  1. Criação de tabela temporária: O PostgreSQL cria uma tabela temporária com os dados atualizados da consulta.
  2. Swap atômico: Utilizando o índice único, o sistema realiza um INSERT dos dados novos na view original, identificando e removendo linhas obsoletas.
  3. Liberação: A tabela temporária é descartada, e a view fica consistente.

Pré-requisito obrigatório: A view deve ter pelo menos um UNIQUE INDEX. Sem ele, o comando falha com erro:

ERROR: cannot refresh materialized view "minha_view" concurrently
DETAIL: The materialized view must have a unique index with no WHERE clause.

Exemplo de criação de view com índice único:

CREATE MATERIALIZED VIEW vendas_diarias AS
SELECT data_venda, produto_id, SUM(valor) as total
FROM vendas
GROUP BY data_venda, produto_id;

CREATE UNIQUE INDEX idx_vendas_diarias_unique 
ON vendas_diarias (data_venda, produto_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY vendas_diarias;

Atenção: Durante o refresh concorrente, o espaço em disco pode chegar a aproximadamente o dobro do tamanho da view, pois a tabela temporária e a view original coexistem.

4. Refresh Agendado com pg_cron e Job Scheduling

Para automatizar refreshes periódicos, a extensão pg_cron é a solução mais comum no PostgreSQL. Ela permite agendar jobs SQL diretamente no banco.

Instalação:

-- Como superusuário no banco
CREATE EXTENSION pg_cron;

Agendamento básico:

-- Refresh a cada 5 minutos
SELECT cron.schedule('refresh_vendas_5min', '*/5 * * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY vendas_diarias');

-- Refresh diário à meia-noite
SELECT cron.schedule('refresh_vendas_diario', '0 0 * * *', 
    'REFRESH MATERIALIZED VIEW vendas_diarias');

Gerenciamento de jobs:

-- Listar jobs agendados
SELECT * FROM cron.job;

-- Remover um job
SELECT cron.unschedule('refresh_vendas_5min');

Alternativas ao pg_cron incluem:
- pg_timetable (extensão mais avançada com dependências)
- Agendadores externos (cron do Linux, Airflow, Apache NiFi)
- Funções pg_sleep() em loops (não recomendado para produção)

5. Trigger-Based Refresh e Event-Driven Strategies

Para cenários onde o refresh deve ocorrer imediatamente após mudanças significativas, podemos usar triggers combinados com pg_notify:

Exemplo com NOTIFY/LISTEN:

-- Função que dispara refresh
CREATE OR REPLACE FUNCTION notify_refresh_vendas()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('refresh_vendas', 'vendas_diarias');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger na tabela base
CREATE TRIGGER trg_vendas_refresh
AFTER INSERT OR UPDATE OR DELETE ON vendas
FOR EACH STATEMENT
EXECUTE FUNCTION notify_refresh_vendas();

Listener externo (exemplo em Python):

import psycopg2
conn = psycopg2.connect("dbname=meubanco")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN refresh_vendas;")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY %s", (notify.payload,))

Cuidado: Triggers em tabelas base podem causar refreshes excessivos. Uma estratégia mais segura é usar um mecanismo de debounce (acumular notificações por X segundos antes de executar o refresh).

6. Otimização e Monitoramento de Refreshes

Para refreshes em grandes volumes, ajustes de configuração são essenciais:

-- Aumentar memória para operações de manutenção
SET maintenance_work_mem = '2GB';

-- Aumentar work_mem para consultas complexas
SET work_mem = '256MB';

Monitoramento:

-- Verificar estatísticas de varreduras e atualizações
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE '%vendas%';

-- Logs de autovacuum (no postgresql.conf)
log_autovacuum_min_duration = 1000  -- ms

Estratégias de retry:

CREATE OR REPLACE FUNCTION refresh_with_retry(view_name text, max_retries int DEFAULT 3)
RETURNS void AS $$
DECLARE
    attempt int := 0;
BEGIN
    LOOP
        BEGIN
            EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', view_name);
            EXIT;
        EXCEPTION WHEN OTHERS THEN
            attempt := attempt + 1;
            IF attempt >= max_retries THEN RAISE; END IF;
            PERFORM pg_sleep(5);
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

7. Casos Práticos: Escolhendo a Estratégia Ideal

Cenário A: Dashboard em tempo real com alta concorrência
- Estratégia: Refresh concorrente a cada 1-5 minutos via pg_cron
- Índice único obrigatório: (timestamp, id)
- Monitorar bloat com pg_stat_user_tables

Cenário B: Relatório noturno com baixa criticidade
- Estratégia: Refresh padrão agendado para 2h da manhã
- Vantagem: Mais rápido e sem overhead de espaço temporário
- Código: SELECT cron.schedule('relatorio_noturno', '0 2 * * *', 'REFRESH MATERIALIZED VIEW relatorio_mensal');

Cenário C: Dados geoespaciais com PostGIS
- Estratégia: Refresh concorrente com particionamento temporal
- Índice único: (data_ref, gid) onde gid é serial único
- Consideração: Refreshes parciais usando views materializadas particionadas

8. Boas Práticas e Armadilhas Comuns

Erro clássico #1: Tentar refresh concorrente sem índice único.

-- Isso falha!
REFRESH MATERIALIZED VIEW CONCURRENTLY minha_view;
-- ERROR: cannot refresh materialized view ... concurrently

Erro clássico #2: Múltiplos refreshes concorrentes simultâneos podem causar deadlocks. Use locks consultivos ou serialização:

SELECT pg_advisory_lock(12345);
REFRESH MATERIALIZED VIEW CONCURRENTLY minha_view;
SELECT pg_advisory_unlock(12345);

Gerenciamento de bloat: Refreshes concorrentes frequentes podem deixar espaço morto. Agende VACUUM periódico:

SELECT cron.schedule('vacuum_vendas', '0 3 * * *', 'VACUUM ANALYZE vendas_diarias');

Resumo das boas práticas:
1. Sempre crie índice único antes de usar CONCURRENTLY
2. Monitore o espaço em disco durante refreshes concorrentes
3. Use pg_cron para agendamentos previsíveis
4. Implemente retry com backoff para falhas transientes
5. Documente a estratégia de refresh escolhida e os motivos

Referências