Autovacuum tuning: ajustando para sua carga de trabalho

1. Fundamentos do Autovacuum no PostgreSQL

O autovacuum é um dos mecanismos mais críticos para a saúde de bancos PostgreSQL. Ele gerencia automaticamente a limpeza de linhas mortas (dead tuples) geradas por operações de UPDATE e DELETE, evitando o crescimento descontrolado do tamanho das tabelas (bloat) e garantindo que o banco não pare por wrap-around de IDs de transação.

A diferença entre os comandos é essencial:
- VACUUM: remove linhas mortas e libera espaço para reuso, mas não retorna espaço ao sistema operacional
- VACUUM FULL: reescreve a tabela, eliminando todo o bloat, mas requer lock exclusivo e dobra o espaço em disco durante a operação
- ANALYZE: atualiza estatísticas do planner, melhorando planos de execução de consultas

O ciclo de vida típico: o autovacuum executa VACUUM quando o número de linhas mortas ultrapassa um limite, enquanto o autoanalyze dispara ANALYZE baseado em alterações na tabela. Ambos rodam como processos background.

2. Parâmetros Essenciais de Configuração

Os principais parâmetros que controlam o comportamento do autovacuum são:

-- Parâmetros globais (postgresql.conf)
autovacuum_max_workers = 3          # Máximo de processos simultâneos
autovacuum_naptime = 60             # Segundos entre verificações
autovacuum_vacuum_threshold = 50    # Número mínimo de linhas mortas
autovacuum_vacuum_scale_factor = 0.2 # Fração da tabela para gatilho
autovacuum_analyze_threshold = 50   # Mínimo de alterações para ANALYZE
autovacuum_analyze_scale_factor = 0.1 # Fração para gatilho de ANALYZE

O gatilho para VACUUM é calculado como:

Gatilho = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * número_de_linhas)

Para uma tabela com 1 milhão de linhas e scale_factor=0.2, o VACUUM dispara quando há 200.050 linhas mortas. Para tabelas grandes, isso pode ser tarde demais.

3. Identificando Sinais de Subconfiguração

Sinais comuns de que o autovacuum precisa de ajuste:

Bloat excessivo: tabelas que crescem desproporcionalmente ao volume de dados ativos.

-- Consulta para identificar tabelas com bloat potencial
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC
LIMIT 10;

Queda de performance: consultas que antes eram rápidas começam a degradar. Estatísticas desatualizadas fazem o planner escolher planos ruins.

Atraso na remoção: n_dead_tup persistentemente alto mesmo após VACUUM indica que o autovacuum não está acompanhando a carga.

-- Verificando progresso do autovacuum
SELECT * FROM pg_stat_progress_vacuum;

4. Ajuste Fino por Carga de Trabalho

Cargas OLTP (alta concorrência, transações curtas)

Em sistemas OLTP, tabelas sofrem muitas pequenas alterações. O autovacuum precisa ser mais agressivo:

-- Configuração para OLTP
autovacuum_vacuum_scale_factor = 0.05  # 5% da tabela
autovacuum_vacuum_threshold = 100
autovacuum_naptime = 30                # Verificar a cada 30s
autovacuum_max_workers = 4

Cargas OLAP (grandes lotes)

Para cargas noturnas com milhões de linhas alteradas de uma vez:

-- Configuração para OLAP
autovacuum_vacuum_scale_factor = 0.01  # 1% da tabela
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_scale_factor = 0.05

Tabelas de alta volatilidade (logs, eventos)

Tabelas que recebem muitas inserções e exclusões merecem configuração especial por tabela:

ALTER TABLE eventos SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

5. Estratégias para Tabelas Problemáticas

Tabela de pedidos com alta taxa de UPDATE

Uma tabela pedidos com 50 milhões de linhas e 500.000 updates/dia requer ajuste específico:

-- Configuração por tabela para pedidos
ALTER TABLE pedidos SET (
    autovacuum_vacuum_scale_factor = 0.02,  -- Dispara com 1 milhão de mortas
    autovacuum_vacuum_threshold = 100000,
    autovacuum_vacuum_cost_limit = 2000,    -- Mais agressivo
    autovacuum_vacuum_cost_delay = 10       -- Pausa de 10ms entre iterações
);

Prevenção de wrap-around

O parâmetro autovacuum_freeze_max_age (default 200 milhões) força VACUUM para evitar que IDs de transação se esgotem:

-- Reduzir para tabelas muito ativas
ALTER TABLE pedidos SET (
    autovacuum_freeze_max_age = 100000000
);

-- Monitorando idade das transações
SELECT relname, age(relfrozenxid) AS idade_tx
FROM pg_class
WHERE relkind = 'r'
ORDER BY idade_tx DESC
LIMIT 10;

6. Monitoramento e Troubleshooting

Verificando workers ativos e fila

-- Workers de autovacuum em execução
SELECT 
    pid,
    datname,
    relid::regclass AS tabela,
    phase,
    heap_blks_total,
    heap_blks_scanned
FROM pg_stat_progress_vacuum;

-- Tabelas que precisam de VACUUM urgente
SELECT 
    relname,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
  AND (last_autovacuum IS NULL 
       OR last_autovacuum < NOW() - INTERVAL '1 hour')
ORDER BY n_dead_tup DESC;

Identificando autovacuum bloqueado

-- Consultas bloqueando autovacuum
SELECT 
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.query ILIKE '%autovacuum%';

Correlação com I/O

-- Métricas de I/O por tabela
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_upd + n_tup_del DESC
LIMIT 10;

7. Boas Práticas e Recomendações Finais

  1. Comece com defaults e ajuste incrementalmente: Altere um parâmetro por vez e monitore por 24-48h antes de novas mudanças.

  2. Documente todas as alterações: Mantenha um registro das configurações por tabela e seus impactos observados.

  3. Evite VACUUM FULL em produção: Use pg_repack para reorganizar tabelas sem locks prolongados.

  4. Integre com manutenção programada: Combine autovacuum com jobs de manutenção em janelas de baixa carga:

-- Exemplo de job de manutenção (cron)
0 3 * * 0 psql -c "VACUUM ANALYZE tabela_pesada;"
  1. Monitore métricas-chave: Crie alertas para:
  2. n_dead_tup > 10% da tabela
  3. last_autovacuum > 24h em tabelas ativas

  4. Idade de transação > 100 milhões

  5. Ajuste o custo do autovacuum para minimizar impacto em produção:

autovacuum_vacuum_cost_limit = 200   # Default: 200
autovacuum_vacuum_cost_delay = 20    # Default: 20ms

Para tabelas críticas, valores mais altos de cost_limit (ex: 2000) com cost_delay baixo (ex: 10ms) tornam o autovacuum mais agressivo sem comprometer totalmente a performance.

O tuning de autovacuum é um processo contínuo. Comece monitorando, ajuste baseado em dados reais e repita o ciclo até encontrar o equilíbrio entre limpeza eficiente e baixo impacto na carga de trabalho.

Referências