Tuning de Autovacuum no PostgreSQL: scale_factor, cost_limit, e quando os padrões estão errados

Autovacuum não é uma manutenção opcional que você pode adiar — é o processo que impede o MVCC de lentamente enchar seu disco com linhas mortas. O problema é que o PostgreSQL vem com padrões calibrados para não surpreender ninguém em um banco pequeno, o que significa que em uma tabela de produção movimentada esses mesmos padrões são quase sempre conservadores demais. Este é o guia para fazer o autovacuum realmente acompanhar.

O padrão que pega todo mundo

autovacuum_vacuum_scale_factor tem padrão de 0.2 — o autovacuum só considera vacuumar uma tabela quando 20% das suas linhas estão mortas, além de um pequeno threshold fixo (autovacuum_vacuum_threshold = 50). Em uma tabela com 10 milhões de linhas, isso significa que o autovacuum espera 2 milhões de dead tuples se acumularem antes até de começar. Quando finalmente roda, você já tem bloat significativo e performance de índice degradada.

-- A fórmula que o PostgreSQL realmente usa para decidir quando vacuumar uma tabela:
-- threshold de vacuum = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
SELECT relname, reltuples,
       (50 + 0.2 * reltuples)::bigint AS default_vacuum_threshold
FROM pg_class
WHERE relkind = 'r' AND reltuples > 100000
ORDER BY reltuples DESC
LIMIT 10;

Overrides por tabela são a correção certa

Não abaixe o autovacuum_vacuum_scale_factor global no postgresql.conf como primeiro movimento — isso faz toda tabela, incluindo pequenas raramente atualizadas, vacuumar mais agressivamente, o que desperdiça I/O. Em vez disso, faça override por tabela, focando nas que mais escrevem:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_cost_limit = 2000);
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Com 0.05, a mesma tabela de 10 milhões de linhas dispara um vacuum a cada 500.000 dead tuples em vez de 2 milhões — um loop 4× mais apertado.

Throttling de vacuum baseado em custo

O autovacuum deliberadamente se limita para evitar saturar I/O: ele dorme brevemente depois de acumular "pontos de custo" das páginas que lê e marca como sujas. Os knobs relevantes:

• autovacuum_vacuum_cost_limit (padrão 200) — orçamento total de custo antes do autovacuum dormir.
• autovacuum_vacuum_cost_delay (padrão 2ms no PG 12+) — duração do sono quando o orçamento se esgota.

Em storage SSD/NVMe moderno, o limite de custo padrão é desnecessariamente conservador — foi calibrado para discos giratórios. Aumentar autovacuum_vacuum_cost_limit para 1000-2000 (globalmente ou por tabela) deixa o vacuum trabalhar em uma tabela inchada visivelmente mais rápido sem prejudicar de forma significativa o I/O das queries em primeiro plano em storage rápido.

Como saber que o autovacuum está perdendo

SELECT schemaname || '.' || relname AS table_name,
       n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 2) AS dead_ratio,
       last_autovacuum, autovacuum_count,
       last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Dois sinais indicam que o autovacuum está atrasado: dead_ratio subindo continuamente acima de 15-20%, e um intervalo crescente entre agora e last_autovacuum em uma tabela que claramente ainda recebe escritas. Uma transação de longa duração é a causa raiz mais comum — ela retém o snapshot visível mais antigo, e o vacuum não pode remover nenhuma dead tuple mais nova que esse snapshot, não importa quão agressivamente esteja ajustado.

Workers do autovacuum

autovacuum_max_workers (padrão 3) limita quantas tabelas podem ser vacuumadas concorrentemente. Em um schema com dezenas de tabelas com muita escrita, três workers frequentemente não é suficiente — elas ficam na fila e tabelas grandes esperam atrás de tabelas pequenas. Aumentar isso para 4-6 em um servidor com margem (e maintenance_work_mem suficiente para suportá-los: cada worker usa até autovacuum_work_mem ou maintenance_work_mem independentemente) reduz o enfileiramento.

Um checklist prático de tuning

• Identifique suas top 10-20 tabelas por volume de escrita (n_tup_upd + n_tup_del) — essas são as que precisam de override por tabela, não o banco inteiro.
• Abaixe o autovacuum_vacuum_scale_factor para 0.02-0.05 nessas tabelas específicas.
• Aumente o autovacuum_vacuum_cost_limit globalmente se você está em storage SSD/NVMe.
• Cheque transações de longa duração e slots de replicação inativos bloqueando o horizonte de xmin antes de assumir que é puramente uma questão de configuração.
• Aumente autovacuum_max_workers se tabelas grandes estão visivelmente esperando atrás de tabelas pequenas.
• Recheque o dead_ratio uma semana depois de cada mudança — ajuste iterativamente, não chute uma vez e vá embora.

Por que isso precisa de monitoramento contínuo, não uma correção única

Tuning de autovacuum não é "ajustar uma vez." Padrões de escrita mudam, tamanhos de tabela crescem, e um scale_factor que estava correto em 1 milhão de linhas está errado em 50 milhões. O PG Monitoring rastreia n_dead_tup, dead_ratio, last_autovacuum, e a proporção de HOT updates por tabela em cada ciclo de coleta, e sinaliza tabelas onde a proporção de dead tuples está subindo mais rápido do que o autovacuum está limpando — antes disso aparecer como um sequential scan lento em produção. São os mesmos dados subjacentes que este guia usa, só que observados continuamente em vez de consultados manualmente uma vez por trimestre.