Vacuum e analyze: manutenção automática do PostgreSQL

1. Entendendo o Problema: Por que o PostgreSQL Precisa de Manutenção?

O PostgreSQL implementa o controle de concorrência multiversão (MVCC) para permitir que múltiplas transações acessem dados simultaneamente sem bloqueios excessivos. Sempre que uma linha é atualizada ou excluída, o banco não remove fisicamente o registro antigo — ele cria uma nova versão e marca a anterior como "morta" (dead tuple). Essas versões obsoletas se acumulam ao longo do tempo.

O acúmulo de dead tuples gera dois problemas graves:

  • Bloat de tabelas: o espaço ocupado por versões antigas nunca é liberado para o sistema operacional, inflando tabelas e índices
  • Degradação de consultas: o planner precisa examinar mais linhas durante varreduras sequenciais, aumentando o custo de execução

O comando VACUUM resolve esse problema recuperando espaço e atualizando estatísticas. O comando ANALYZE alimenta o planejador de consultas com dados atualizados sobre distribuição de valores.

2. Vacuum a Fundo: Mecanismo e Modos de Operação

Vacuum Padrão

O VACUUM padrão não bloqueia leituras nem escritas concorrentes. Ele percorre a tabela, identifica dead tuples e marca o espaço como reutilizável para futuras inserções. Exemplo prático:

-- Verificar estado atual da tabela
SELECT n_dead_tup, n_live_tup, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'pedidos';

-- Executar vacuum manual
VACUUM pedidos;

-- Vacuum em todas as tabelas do banco
VACUUM;

VACUUM FULL

O VACUUM FULL é mais agressivo: ele recria a tabela do zero, compactando o espaço e liberando páginas para o sistema operacional. Porém, exige bloqueio exclusivo (ACCESS EXCLUSIVE), impedindo qualquer operação concorrente.

-- Libera espaço físico, mas bloqueia a tabela
VACUUM FULL pedidos;

Processo Interno

O vacuum consulta o mapa de visibilidade (visibility map) para determinar quais páginas contêm dead tuples. Ele então:

  1. Percorre páginas com dead tuples
  2. Remove referências a versões obsoletas
  3. Atualiza o mapa de visibilidade
  4. Registra o progresso no catálogo pg_stat_progress_vacuum

Thresholds de Ativação

O autovacuum é acionado quando o número de dead tuples ultrapassa:

dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * total_tuples)

Valores padrão: threshold=50, scale_factor=0.2 (20% das linhas).

3. Analyze: Coleta de Estatísticas para o Planejador de Consultas

O ANALYZE examina uma amostra das linhas da tabela e gera:

  • Histogramas de distribuição de valores
  • Correlações entre colunas
  • Nulos e valores mais comuns

Sem estatísticas atualizadas, o planejador pode escolher planos catastróficos:

-- Exemplo: consulta que deveria usar índice, mas faz scan sequencial
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE status = 'cancelado';

-- Atualizar estatísticas
ANALYZE pedidos;

-- Após analyze, o plano muda para index scan
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE status = 'cancelado';

Granularidade das Estatísticas

O parâmetro default_statistics_target controla a amostragem (padrão: 100). Valores maiores melhoram a precisão para colunas com distribuições irregulares:

-- Aumentar amostragem para coluna específica
ALTER TABLE pedidos ALTER COLUMN status SET STATISTICS 500;

-- Verificar configuração atual
SHOW default_statistics_target;

4. Autovacuum Daemon: Configuração e Comportamento Automático

O autovacuum é um processo em segundo plano que gerencia vacuum e analyze automaticamente. Parâmetros essenciais no postgresql.conf:

# Ativar/desativar (padrão: on)
autovacuum = on

# Threshold mínimo de dead tuples
autovacuum_vacuum_threshold = 50

# Fator de escala (percentual de linhas mortas)
autovacuum_vacuum_scale_factor = 0.2

# Threshold para analyze
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

# Número máximo de workers simultâneos
autovacuum_max_workers = 3

# Atraso entre rodadas de vacuum (ms)
autovacuum_naptime = 60

Logs e Monitoramento

Para identificar se o autovacuum está rodando:

-- Ver processos ativos de autovacuum
SELECT pid, datname, relname, phase, heap_blks_total,
       heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

-- Última execução por tabela
SELECT relname, last_autovacuum, last_autoanalyze,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

5. Tuning do Autovacuum para Diferentes Cargas de Trabalho

Cargas OLTP (alta taxa de UPDATE/DELETE)

Para tabelas com muitas atualizações, reduza o scale_factor e aumente workers:

-- Configuração no postgresql.conf
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
autovacuum_max_workers = 6

Cargas OLAP/Data Warehouse

Tabelas grandes com janelas de manutenção noturna podem usar configurações mais conservadoras:

-- Tabela específica: desativar autovacuum automático
ALTER TABLE vendas_historico SET (autovacuum_enabled = false);

-- Manutenção manual programada
VACUUM ANALYZE vendas_historico;

Sobrescrita por Tabela

É possível personalizar parâmetros por tabela:

-- Tabela de logs com alta taxa de inserção
ALTER TABLE logs_acesso SET (
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_scale_factor = 0.05
);

6. Monitoramento e Diagnóstico Proativo

Detectando Bloat

Use a extensão pgstattuple para medir desperdício real:

-- Instalar extensão
CREATE EXTENSION pgstattuple;

-- Analisar bloat da tabela
SELECT * FROM pgstattuple('pedidos');
-- Campo 'dead_tuple_percent' indica desperdício

Identificando Tabelas Negligenciadas

-- Tabelas sem vacuum há mais de 24h
SELECT relname, n_dead_tup, n_live_tup,
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE (last_autovacuum IS NULL
       OR last_autovacuum < now() - interval '1 day')
  AND n_dead_tup > 0
ORDER BY dead_pct DESC;

Alerta de Wraparound

O PostgreSQL usa IDs de transação de 32 bits. Quando o limite se aproxima, o banco força vacuum para evitar corrupção:

-- Verificar idade de freeze
SELECT datname, age(datfrozenxid) AS idade_transacao,
       datfrozenxid
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Aviso: se idade > autovacuum_freeze_max_age (200 milhões)
-- o banco entra em modo de emergência

7. Boas Práticas e Armadilhas Comuns

Evitando VACUUM FULL em Produção

O VACUUM FULL bloqueia a tabela por minutos ou horas. Alternativa segura: pg_repack:

-- Instalar extensão
CREATE EXTENSION pg_repack;

-- Reorganizar tabela sem bloqueio longo
SELECT pg_repack('pedidos');

Cuidados com Freeze e Wraparound

Configure corretamente os limites de freeze:

-- Configurações seguras
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
autovacuum_freeze_max_age = 200000000

Sinergia com Manutenção de Índices

Após grandes operações de DELETE, reconstrua índices:

-- Reconstruir índice após vacuum
REINDEX INDEX idx_pedidos_status;

-- Reindexar tabela inteira
REINDEX TABLE pedidos;

Resumo de Comandos Essenciais

-- Rotina de manutenção recomendada
VACUUM ANALYZE;                    -- Todas as tabelas
REINDEX DATABASE meu_banco;        -- Reconstruir índices
ANALYZE;                           -- Atualizar estatísticas

Referências