Inchaço no Postgres e ajuste do autovacuum para melhor desempenho

O que é inchaço no Postgres e por que o autovacuum importa

Inchaço é o espaço extra e desperdiçado dentro das tabelas e índices do Postgres que se acumula com o tempo. Geralmente vem de uma dieta constante de UPDATEs e DELETEs. O Postgres não sobrescreve linhas no lugar: ele escreve uma nova versão da linha e deixa a antiga até que a limpeza ocorra.

Esse espaço desperdiçado prejudica o desempenho de maneiras previsíveis. Consultas precisam ler mais páginas do disco e da memória para encontrar a mesma quantidade de dados reais. Índices ficam maiores, então buscas e joins exigem mais trabalho. Escritas também podem ficar mais lentas porque o Postgres precisa manter índices maiores e tocar mais páginas. Backups e restores demoram mais simplesmente porque há mais para copiar.

O autovacuum é o trabalhador em segundo plano que mantém isso sob controle. Ele faz basicamente duas tarefas:

• Limpa versões de linhas mortas para que o espaço possa ser reutilizado (VACUUM)
• Atualiza as estatísticas do planner para que o Postgres possa escolher bons planos de consulta (ANALYZE)

Um detalhe importante: o autovacuum normalmente não reduz o arquivo físico no disco. Ele torna o espaço reutilizável dentro da tabela, mas o uso de disco pode não cair. Para devolver espaço ao sistema operacional, normalmente é preciso uma manutenção mais pesada (como VACUUM FULL ou reconstrução da tabela), e essas opções têm trade-offs reais.

Um exemplo simples: um painel administrativo gerado por IA que “atualiza” registros de usuário a cada carregamento de página pode criar um fluxo constante de linhas mortas. O autovacuum consegue acompanhar por um tempo. Depois, um pico de tráfego chega, ele fica para trás, e de repente consultas comuns ficam lentas mesmo que o tamanho do conjunto de dados não tenha mudado muito.

Ajustar o autovacuum não é um interruptor único. As configurações corretas dependem da taxa de escrita, do tamanho das tabelas e de quão tolerante você é ao trabalho de fundo. A abordagem mais segura é iterativa: medir, fazer pequenas mudanças, observar o impacto e então ajustar.

Sinais comuns de que o banco sofre com inchaço

O inchaço frequentemente aparece como “nada mudou, mas tudo ficou mais lento.” Os dados de negócio podem parecer estáveis, mas o Postgres precisa ler e varrer mais páginas para responder às mesmas consultas. Esse trabalho extra vira mais IO, mais CPU e mais espera.

Sinais que apontam para inchaço incluem:

• Consultas que funcionavam bem semana passada agora travam, especialmente as que costumavam usar index scans rápidos.
• Uso de disco continua subindo mesmo que os dados reais não cresçam na mesma proporção.
• Latência de leitura maior e mais faltas de cache (mais leituras do disco em vez da memória).
• O autovacuum roda com frequência, mas você não vê a recuperação esperada depois.
• Backups e restores demoram mais, e aparece lag de replicação em períodos de pico.

Uma pista sutil é quando o plano de consulta ainda parece “correto” (ainda usa o índice esperado), mas o tempo piorou muito. Isso pode acontecer quando o índice está inchado e maior do que o necessário, então o Postgres precisa ler mais páginas para percorrê-lo.

Outro padrão comum: varreduras de tabela ficam mais lentas mesmo que a contagem de linhas pareça similar. UPDATEs e DELETEs deixam tuplas mortas, e quando a limpeza fica para trás, a tabela cresce em páginas. O Postgres então tem mais páginas para buscar e mais verificações de visibilidade para fazer.

Um exemplo prático: um pequeno app SaaS adiciona um campo "update last_seen" em toda requisição. As leituras começam a estourar tempo limite em horários de pico, o uso de disco sobe e o autovacuum fica ocupado o dia todo. A causa não é uma query ruim; é o banco fazendo trabalho extra porque tuplas mortas e índices exagerados se acumularam.

Se você observar dois ou mais desses sinais ao mesmo tempo, meça o inchaço antes de mudar configurações. Ajustes no autovacuum ajudam mais quando você pode confirmar quais tabelas e índices estão realmente crescendo.

Checklist rápido: confirme o inchaço antes de ajustar

Antes de mudar qualquer coisa, confirme que você está realmente lidando com inchaço e não com um plano de consulta ruim, índices faltantes ou uma transação que bloqueia a limpeza. O tuning funciona melhor quando você aponta algumas tabelas e índices específicos.

Comece obtendo números simples que você possa comparar semana a semana. Foque em:

• As maiores tabelas e se elas realmente veem muitas escritas (tabelas frias raramente explicam inchaço).
• Histórico do autovacuum por tabela: quando rodou por último, com que frequência roda e se está levando mais tempo ao longo do tempo.
• Altas contagens de tuplas mortas, especialmente em tabelas atualizadas com frequência.
• Índices grandes em colunas de alto churn (índices podem inflar mesmo quando a tabela parece OK).
• Transações de longa duração, pois podem impedir que o vacuum remova tuplas mortas.

Se quiser uma primeira olhada rápida, essas queries normalmente dão sinal suficiente para decidir o que investigar a seguir:

-- Biggest tables
SELECT relname, pg_total_relation_size(relid) AS bytes
FROM pg_catalog.pg_statio_user_tables
ORDER BY bytes DESC
LIMIT 5;

-- Dead tuples and last vacuum/autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Biggest indexes
SELECT indexrelname, pg_relation_size(indexrelid) AS bytes
FROM pg_stat_user_indexes
ORDER BY bytes DESC
LIMIT 10;

-- Long-running transactions (can block cleanup)
SELECT pid, now() - xact_start AS xact_age, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

Uma forma prática de interpretar os resultados: se uma tabela de eventos é enorme, tem contagem de tuplas mortas crescendo e o autovacuum roda mas nunca alcança, essa tabela é candidata a tuning. Se tuplas mortas estão altas em muitas tabelas e você também vê uma transação rodando por horas, conserte a transação longa primeiro. Autovacuum não consegue vencer uma transação que nunca termina.

Como medir inchaço de tabela e índice (métodos práticos)

Comece com o que o Postgres já monitora. O inchaço costuma correlacionar com muitas tuplas mortas e alto churn (updates e deletes) nas mesmas tabelas.

1) Use estatísticas internas para achar tuplas mortas e churn

Essas views são uma primeira análise rápida e de baixo risco. Procure tabelas onde n_dead_tup é alto e onde n_tup_upd e n_tup_del continuam subindo.

-- Tables with lots of dead tuples
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Churn (writes) that tends to create bloat
SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY (n_tup_upd + n_tup_del) DESC
LIMIT 20;

Uma regra prática: se tuplas mortas são uma grande parcela das tuplas vivas e o autovacuum não roda com frequência (ou demora muito), provavelmente você tem inchaço e deve investigar as configurações do autovacuum e bloqueadores.

2) Estime o inchaço sem matemática pesada

Seja prático. Compare o tamanho da relação em disco com o número de linhas vivas. Se a tabela é enorme mas as linhas vivas não são, a diferença costuma ser inchaço ou linhas muito largas. Você pode confirmar depois com ferramentas mais profundas.

Também confira se a tabela cresceu rápido e nunca encolheu. O Postgres não devolve espaço ao SO depois de deletes e updates normais, por isso inchaço e autovacuum costumam ser discutidos juntos.

3) Verifique tamanho do índice vs utilidade

Índices também incham, e índices não utilizados são um custo oculto. Compare o tamanho com a frequência de scans do índice.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  pg_relation_size(s.indexrelid) AS index_bytes,
  s.idx_scan
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC;

Se um índice é grande e idx_scan fica perto de zero com tráfego real, é candidato a remoção ou redesign. Confirme que não é necessário para constraints, unicidade ou uma consulta rara mas crítica antes de tocar.

4) Quando usar ferramentas de inspeção mais profundas

Se precisar de números reais de inchaço, use pgstattuple (ou pgstattuple_approx para tabelas grandes). Ela pode reportar espaço morto e densidade de tuplas, mas pode ser mais pesada que as views de estatísticas, então rode em horários de baixo tráfego.

5) Anote uma linha de base

Antes de mudar qualquer coisa, grave um snapshot: principais tabelas por n_dead_tup, principais índices por tamanho e alguns tempos de consulta chave. Depois do tuning e manutenção, você saberá o que realmente melhorou.

Por que o autovacuum fica para trás em apps reais

O autovacuum é a equipe de limpeza do Postgres. Ele remove linhas mortas deixadas por updates e deletes e mantém as estatísticas atualizadas para que os planos não degradem. Ele decide quando rodar usando thresholds que são parcialmente fixos e parcialmente baseados no tamanho da tabela.

Para cada tabela, o Postgres espera até que o número de linhas alteradas ultrapasse um gatilho como:

• um número base (autovacuum_vacuum_threshold)
• mais uma porcentagem da tabela (autovacuum_vacuum_scale_factor)

Isso funciona bem para muitas tabelas. Mas tabelas com muitas atualizações (sessions, events, job queues) podem criar linhas mortas mais rápido do que esses gatilhos disparam, especialmente se o scale factor for alto. A tabela cresce, o gatilho cresce com ela e o vacuum chega sempre atrasado.

Bloqueadores reais que impedem a limpeza

A razão mais comum para o vacuum não remover tuplas mortas é uma transação longa. Se alguma transação fica aberta, o Postgres precisa manter versões antigas de linhas, mesmo que todos os outros já tenham terminado. Isso pode acontecer com sessões idle-in-transaction (uma aba aberta), um worker travado ou um job em lote que envolve trabalho demais em uma única transação.

Mesmo quando o vacuum pode rodar, ele às vezes age com muita gentileza. O autovacuum tem limites de custo (quanto IO pode usar antes de dormir). Em sistemas ocupados, esses limites costumam ser conservadores, então o vacuum progride devagar enquanto sua aplicação continua gerando mais linhas mortas.

Causas ocultas que pioram a situação

Alguns padrões empurram o autovacuum para trás: grandes updates/deletes em lote que tocam a maioria das linhas, tabelas quentes com UPDATEs frequentes (mesmo pequenos), conexões idle-in-transaction que seguram snapshots, relatórios longos que mantêm transações abertas e tráfego em rajadas onde o vacuum não consegue recuperar entre picos.

Ao diagnosticar inchaço, comece encontrando duas coisas: a tabela com mais churn e a transação mais longa que nunca termina. Conserte essas primeiro, depois ajuste as configurações.

Passo a passo: ajustar o autovacuum com segurança (comece pequeno)

A maneira mais segura de ajustar o autovacuum é mudar uma coisa só, em uma tabela, e monitorar por um dia inteiro. A maior parte da dor com autovacuum vem de algumas tabelas de alto churn (sessions, events, logs, queues), não do banco todo.

Escolha uma tabela com muitos updates ou deletes e que esteja mostrando lentidão. Um bom candidato tem contagem de tuplas mortas crescendo e gravações frequentes durante o tráfego normal.

Uma sequência pequena e controlada que funciona bem:

1. Escolha uma tabela de alto churn e registre uma linha de base: contagem de linhas, tuplas mortas e frequência do autovacuum.
2. Reduza os scale factors e thresholds por tabela para que a manutenção rode mais cedo (antes da tabela ficar enorme). Mantenha a mudança modesta.
3. Se o vacuum estiver muito lento, ajuste a capacidade com cuidado: aumente autovacuum_max_workers um pouco ou eleve autovacuum_vacuum_cost_limit para que os vacuums façam progresso sem esmagar a aplicação.
4. Confirme que o ANALYZE está mantendo planos precisos observando menos mudanças inesperadas de plano e tempos de consulta mais estáveis.
5. Monitore por um dia inteiro de tráfego real e compare: tuplas mortas, frequência do vacuum, latência de consultas e CPU/IO.

Um ajuste prático por tabela costuma ser assim:

ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 1000
);

Depois valide com as estatísticas que você já tem:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 10;

Exemplo: um app grava 5 milhões de eventos por dia e deleta os antigos a cada hora. Com as configurações padrão, o vacuum começa tarde demais e tuplas mortas se acumulam. Ao reduzir os scale factors só para events, o autovacuum roda mais vezes, o tempo de consulta fica mais estável e você evita uma manutenção pesada durante o pico.

Se você herdou um app gerado por IA com padrões de escrita desordenados (loops de retry, inserts duplicados, tempestades de updates), vale a pena consertar a causa. Do contrário, você acaba ajustando o autovacuum para compensar comportamento quebrado.

Passo a passo: recuperar espaço e velocidade sem quebrar a disponibilidade

Comece com as ferramentas menos disruptivas. Na maior parte das vezes você consegue ganhos notáveis sem bloquear escritas ou tirar a aplicação do ar.

1) Rode um VACUUM normal (geralmente seguro e muitas vezes suficiente)

Um VACUUM normal remove versões de linhas mortas para que o Postgres possa reutilizar esse espaço dentro da tabela. Também atualiza informações de visibilidade para que index scans e sequential scans possam ficar mais rápidos. Ele não reduz o arquivo em disco, mas muitas vezes estanca a sangria e melhora a velocidade das consultas.

Se possível, faça em lotes menores (uma tabela grande por vez) durante tráfego baixo. Exemplo:

VACUUM (ANALYZE) public.big_table;

2) Reconstrua índices inchados (quando consultas continuam lentas)

Se o VACUUM na tabela ajudou pouco, os índices podem ser o problema principal. Índices inchados tornam leituras mais lentas e podem aumentar IO aleatório. Em produção, prefira reconstrução concorrente para que leituras e escritas continuem:

REINDEX INDEX CONCURRENTLY public.big_table_some_idx;

Espere que leve tempo e que use espaço temporário extra enquanto o novo índice é construído.

3) Use VACUUM FULL apenas quando aceitar downtime

VACUUM FULL reescreve a tabela inteira e pode devolver espaço ao SO. É disruptivo porque toma um lock exclusivo na tabela. Isso significa que inserts, updates e muitas vezes leituras vão bloquear.

Use-o somente com janela de manutenção e quando o espaço em disco for realmente urgente.

4) Opções mais seguras para tabelas muito grandes

Se a tabela for enorme e você precisar de espaço sem bloqueios prolongados, planeje uma janela de manutenção e use abordagens como: reindexar índices mais inchados com REINDEX CONCURRENTLY, considerar particionamento ou arquivar dados antigos e dropar partições antigas, ou reescrever a tabela copiando para uma nova e trocando quando puder controlar o cutover.

5) Verifique se realmente melhorou

Não confie em impressões. Compare antes e depois com checagens simples:

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Também reavalie tamanhos e tempos de consulta. Se o inchaço voltar rápido, a causa raiz costuma ser comportamento da aplicação (updates em massa, índices faltando, transações longas).

Um cronograma de manutenção que evita o retorno do inchaço

Um bom plano de manutenção é propositalmente monótono. Ele faz do inchaço uma tarefa rotineira em vez de uma interrupção surpresa. O objetivo não é um ganho único, mas um ritmo que combine com o padrão de escrita do app.

Comece escolhendo janelas de manutenção com base no tráfego real. Evite os 30–60 minutos mais ocupados do dia. Se não tiver gráficos, use pistas simples: horários de checkout, jobs diários, envios de marketing e picos de suporte.

Um ritmo semanal simples que funciona para muitas equipes:

• Revise as 5 maiores tabelas e índices. Confira tuplas mortas e tempos recentes de vacuum.
• Rode VACUUM (ANALYZE) manual nas poucas tabelas mais quentes se o autovacuum estiver sempre atrás.
• Cheque por transações de longa duração que bloqueiam o vacuum.

Uma vez por mês, faça uma passada mais pesada quando o tráfego estiver no mínimo. O autovacuum deve lidar com o churn normal. Trabalho manual é para exceções: tabelas em rajada, grandes deletes e mudanças de esquema.

Para o checklist mensal, mantenha curto e mensurável:

• Compare crescimento de tabelas e índices com o mês anterior.
• Verifique se a duração do vacuum está subindo.
• Decida se alguma tabela precisa de configurações customizadas do autovacuum (thresholds, scale factors, cost limits).
• Agende um REINDEX direcionado (ou rebuild controlado) apenas quando puder tolerar a carga extra.

Acompanhe algumas métricas ao longo do tempo: tuplas mortas (n_dead_tup), tamanho da tabela vs total incluindo toast, tamanhos de índices e timestamps de vacuum/analyze. Ao lançar novas features, planeje com antecedência. Qualquer tabela nova de alto-write deve começar com configurações sensatas do autovacuum e um lugar na revisão semanal.

Erros comuns que pioram inchaço e desempenho

A maioria dos problemas de inchaço piora porque o “conserto” é aplicado de forma ampla e rápida demais. Você terá resultados melhores se mudar uma coisa, medir, depois mudar a próxima.

1) Over-tuning do autovacuum e criar picos de IO

Subir configurações do autovacuum para todo o banco pode disparar leituras e gravações no disco. Isso pode atrasar consultas de usuário, aumentar latência e ainda não resolver o pior inchaço se o problema real for só algumas tabelas.

Uma abordagem mais segura é ajustar por tabela para o punhado de tabelas que realmente churnam (sessions, event logs, queues) e então observar o impacto.

2) Usar VACUUM FULL em tabela ocupada sem plano

VACUUM FULL pode recuperar espaço, mas reescreve a tabela e tipicamente bloqueia o acesso normal enquanto roda. Em uma tabela movimentada em produção, isso pode parecer um outage.

Se precisar recuperar muito espaço, planeje uma janela de manutenção, considere abordagens alternativas e confirme que tem disco livre suficiente para a reescrita.

3) Ignorar transações longas e culpar o autovacuum

Autovacuum não remove tuplas mortas se uma transação longa mantém snapshots antigos abertos. Você verá tuplas mortas se acumulando mesmo que o autovacuum rode constantemente.

Causas comuns: sessões administrativas esquecidas, jobs em background que não fazem commit, ou código que abre uma transação e depois faz trabalho lento.

4) Mudar só configurações globais em vez de corrigir hotspots

Configurações globais do autovacuum são uma ferramenta cega. O inchaço costuma se concentrar em tabelas e índices específicos, que muitas vezes precisam de thresholds e custos próprios.

Se você ajustar globalmente, pode gastar recursos vacuumando tabelas frias enquanto a tabela problemática continua crescendo.

5) Medir uma vez, mudar cinco coisas e perder a linha de base

Se ajustar múltiplos parâmetros ao mesmo tempo, você não saberá o que ajudou. Mantenha uma linha de base simples (tamanhos de tabela, tuplas mortas, timestamps de vacuum/analyze, latência de queries) e mude uma variável por vez.

Exemplo: de consultas lentas a desempenho estável em uma semana

Uma startup lança um feed de atividade: likes, comentários e marcadores de "visto". Da noite para o dia, escritas saltam de algumas dezenas por segundo para alguns milhares. O app continua funcionando, mas páginas que carregavam em 200 ms passam a demorar 2–5 segundos.

Em dois dias, duas coisas ficam claras: índices estão crescendo rápido, uso de disco sobe mesmo depois de deletes de dados antigos, e alerts de pouco espaço aparecem. A latência das queries fica muito irregular: ok por 10 minutos e de repente muito lenta.

O que encontramos

O ponto quente é uma tabela que muda constantemente, como activity_events. Ela recebe updates frequentes (flags de status) e deletes (limpeza por retenção). O autovacuum roda, mas sempre chega atrasado porque a tabela atinge o threshold rapidamente e continua mudando enquanto o vacuum tenta alcançar. Tuplas mortas se acumulam, índices incham e consultas simples passam a ler muito mais páginas do que deveriam.

O que mudamos (sem manutenção arriscada)

Consertamos com passos pequenos e direcionados:

• Definimos thresholds do autovacuum por tabela para que essa tabela de alto churn seja vacuumada mais cedo e com mais frequência.
• Aumentamos o trabalho do autovacuum para essa tabela (cost limit ou workers) para que termine antes do próximo pico.
• Rodamos um reindex direcionado nos índices mais inchados numa janela de baixo tráfego.
• Agendamos uma janela de manutenção semanal para as poucas tabelas que churnam mais.
• Ajustamos jobs de retenção para deletar em lotes menores para que o vacuum consiga acompanhar.

Ao final da semana, o crescimento do disco estabilizou e ficou previsível. A latência parou de oscilar e o feed de atividade voltou estável.

Próximos passos: manter estável e consertar a causa raiz

Quando o inchaço está sob controle, o objetivo muda: evitar que ele volte. A forma mais segura é fazer uma mudança por vez e observar. Isso evita trocar um problema por outro.

Comece pelo pior infrator (uma tabela ou fila que receba muitos updates) e faça uma mudança clara. Por exemplo: reduzir thresholds e scale factors para essa tabela, aumentar o cost limit para o vacuum terminar, reduzir churn na aplicação evitando regravações desnecessárias, ou adicionar alertas para tuplas mortas, atraso do vacuum e transações longas.

Depois de cada mudança, acompanhe alguns números por uma semana: crescimento de tabela, tuplas mortas, frequência do autovacuum e p95 de latência para os endpoints que batem nessa tabela. Se não houver melhora, reverta e tente outra ideia.

Escreva um pequeno runbook que sua equipe possa repetir:

• Qual métrica dispara a ação (ex.: tuplas mortas acima de X% ou atraso do vacuum acima de Y horas)
• O que mudar primeiro (configurações por tabela antes de globais)
• Como verificar sucesso (duas métricas e um check visível para o usuário)
• O que evitar (VACUUM manual em pico, transações longas)
• Quem decide e quando escalar

Se seu app foi gerado por IA, procure padrões que criem churn constante: writes chatos (atualizar linhas a cada requisição), índices faltando em chaves estrangeiras e tratamento ruim de sessão que deixa transações abertas. Um caso comum é um protótipo que grava last_seen em todo carregamento de página.

Quando você continua ajustando e o desempenho ainda deriva, normalmente é problema de comportamento do produto, não do vacuum. Se estiver lidando com um código gerado por IA que está martelando o Postgres de formas estranhas (fluxos de auth quebrados, loops de retry, jobs que nunca param), FixMyMess at fixmymess.ai pode ajudar a diagnosticar e reparar esses caminhos de código para que o banco pare de lutar uma batalha perdida.