Autovacuum que não acompanha: quando a tabela incha e a consulta fica lenta sem mudar nada

O PostgreSQL não altera uma linha no lugar. Um UPDATE grava uma versão nova da linha e marca a antiga como encerrada por aquela transação, e um DELETE apenas marca a versão como encerrada. A versão antiga continua ocupando espaço na página, porque outras transações abertas podem precisar dela para enxergar os dados como estavam quando começaram. Quando nenhuma transação ativa pode mais vê-la, ela vira uma tupla morta, e só o VACUUM a remove, marcando o espaço como reutilizável para novas versões. Ele não devolve esse espaço ao sistema operacional: a tabela não encolhe, ela para de crescer.

Tabelas de status são o caso mais sensível. Cada mensagem enviada gera uma linha em entregas e, em seguida, três ou quatro atualizações: enviada, entregue, lida, às vezes falhou. Como a coluna status é indexada para a consulta de pendentes, nenhuma dessas atualizações é HOT, e cada uma deixa uma versão morta na tabela e uma entrada morta em cada índice. Se o VACUUM não acompanha esse ritmo, as páginas passam a conter mais versões mortas que vivas, e o sintoma aparece no plano como o mesmo nó de índice lendo muito mais páginas para devolver as mesmas linhas.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, mensagem_id, tentativas
FROM entregas
WHERE status = 'pendente'
ORDER BY criado_em
LIMIT 200;

-- Tres semanas antes: 15 ms
--   Index Scan using entregas_status_criado_idx on entregas
--     (actual time=0.031..14.870 rows=200 loops=1)
--     Buffers: shared hit=1204 read=87
--
-- Hoje, mesmo plano, mesmas 200 linhas: 2,3 s
--   Index Scan using entregas_status_criado_idx on entregas
--     (actual time=0.044..2291.502 rows=200 loops=1)
--     Buffers: shared hit=48210 read=91022

Nenhum ajuste no plano resolve isso, porque o plano está certo. O índice aponta para centenas de milhares de versões com status pendente que já foram atualizadas para entregue ou lida, e o executor precisa visitar cada uma na tabela para descobrir que ela não é mais visível. O custo da consulta passou a depender do lixo acumulado, e não dos dados.

O autovacuum acorda a cada minuto, por padrão, e escolhe as tabelas cujo número de tuplas mortas ultrapassou um gatilho calculado por uma fórmula simples: um limite fixo mais uma fração do número de linhas da tabela. Com os valores padrão, isso é 50 mais vinte por cento das linhas. Numa tabela de dez mil linhas, o gatilho é 2.050 tuplas mortas, o que é razoável. Numa tabela de noventa milhões, são dezoito milhões de tuplas mortas antes da primeira limpeza, e a essa altura as páginas quentes já estão cheias de versões que ninguém enxerga.

Os dois últimos parâmetros explicam o segundo problema: mesmo quando dispara, o autovacuum trabalha devagar de propósito, para não competir com as consultas. Ele gasta um orçamento de custo por rodada, em que cada página que precisa sujar custa bem mais que uma página lida da memória, e dorme ao atingir o limite. Esse orçamento é dividido entre os workers ativos, então três tabelas grandes sendo limpas ao mesmo tempo andam cada uma a um terço da velocidade. Numa tabela que recebe milhões de atualizações por hora, o VACUUM pode simplesmente nunca alcançar a taxa de produção de lixo. A consulta abaixo mostra, para cada tabela, quantas tuplas mortas existem e a partir de quantas o autovacuum vai agir.

-- Tuplas mortas por tabela e o gatilho do autovacuum com os valores globais.
-- Tabelas com ajuste proprio (ALTER TABLE ... SET) aparecem em c.reloptions.
SELECT s.relname,
       s.n_live_tup,
       s.n_dead_tup,
       round(current_setting('autovacuum_vacuum_threshold')::numeric
             + current_setting('autovacuum_vacuum_scale_factor')::numeric
               * greatest(c.reltuples, 0)::numeric) AS gatilho,
       s.last_autovacuum,
       s.autovacuum_count,
       c.reloptions
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 15;

Se n_dead_tup está muito acima do gatilho e last_autovacuum é recente, o autovacuum está rodando, mas não está conseguindo remover o que encontra. Esse é o caso mais comum e o mais mal diagnosticado, porque a reação natural é deixá-lo mais agressivo, e isso não muda nada enquanto a causa real continua ativa.

O VACUUM só pode remover versões que morreram antes do snapshot mais antigo ainda em uso em todo o cluster. Esse limite é o horizonte de xmin, e basta uma única coisa segurando esse horizonte para que nenhuma versão morta depois dele seja removida, em nenhuma tabela, por mais vezes que o autovacuum rode. No incidente, a limpeza parou dezenove dias antes, no minuto em que a ferramenta de BI abriu a transação.

dia 0   BI abre transacao (xmin = 8.201.334) e fica "idle in transaction"
dia 1   autovacuum em entregas: 2,1 mi mortas encontradas, 0 removidas
dia 7   autovacuum em entregas: 14 mi mortas encontradas, 0 removidas
dia 19  autovacuum em entregas: 41 mi mortas encontradas, 0 removidas
        horizonte continua em 8.201.334: tudo que morreu depois dele e irremovivel
dia 19  sessao do BI encerrada -> proximo autovacuum remove 41 mi em 38 min

A sessão ociosa dentro de uma transação é o culpado mais frequente, mas não o único. Um slot de replicação lógica abandonado, de um consumidor de CDC que foi desligado sem remover o slot, segura o horizonte do catálogo e acumula WAL. Uma réplica com hot_standby_feedback ligado, rodando relatórios de horas, empresta o snapshot dela para o primário. E uma transação preparada esquecida, de um gerenciador de transações distribuídas que falhou, fica aberta até alguém executar COMMIT PREPARED ou ROLLBACK PREPARED. A consulta abaixo lista todas essas fontes, ordenadas pela idade.

-- Quem esta segurando o horizonte de xmin: sessoes, replicas, slots e transacoes preparadas.
SELECT 'sessao' AS origem, pid::text AS id, state,
       greatest(age(backend_xmin), age(backend_xid)) AS idade_xmin,
       now() - xact_start AS duracao,
       left(query, 60) AS detalhe
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
UNION ALL
SELECT 'replica', application_name, state, age(backend_xmin), NULL, NULL
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name::text, CASE WHEN active THEN 'ativo' ELSE 'inativo' END,
       greatest(age(xmin), age(catalog_xmin)), NULL, slot_type
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
UNION ALL
SELECT 'preparada', gid, NULL, age(transaction), now() - prepared, NULL
FROM pg_prepared_xacts
ORDER BY idade_xmin DESC NULLS LAST;

O log do autovacuum confirma o diagnóstico sem ambiguidade. Com log_autovacuum_min_duration configurado, cada execução registra quantas tuplas removeu e quantas encontrou mortas, mas ainda não removíveis, e as versões mais recentes do PostgreSQL também informam a idade do limite de remoção. Uma linha com zero removidas e milhões ainda não removíveis é a assinatura do horizonte preso.

A correção imediata é encerrar a fonte: pg_terminate_backend na sessão, pg_drop_replication_slot no slot abandonado, ROLLBACK PREPARED na transação esquecida. A correção permanente são limites que impedem a repetição: idle_in_transaction_session_timeout para toda a instância, statement_timeout e, a partir do PostgreSQL 17, transaction_timeout nos papéis usados por ferramentas de análise, max_slot_wal_keep_size para que um slot abandonado seja invalidado em vez de segurar tudo indefinidamente, e relatórios longos numa réplica sem hot_standby_feedback, aceitando que eles possam ser cancelados por conflito de replicação.

Com o horizonte liberado, o próximo passo é fazer o autovacuum chegar antes. Mudar o fator de escala para toda a instância faz com que milhares de tabelas pequenas sejam limpas sem necessidade. O ajuste certo é por tabela, nas poucas que concentram atualizações, trocando a fração por um limite absoluto que faça sentido para o volume delas.

-- Tabela de status com milhoes de atualizacoes por hora:
-- dispara a cada ~200 mil tuplas mortas, independentemente do tamanho,
-- e com orcamento de I/O proprio, maior que o padrao.
ALTER TABLE entregas SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold    = 200000,
  autovacuum_vacuum_cost_limit   = 2000,
  autovacuum_vacuum_cost_delay   = 1
);

-- Deixa espaco livre em cada pagina para que atualizacoes sem coluna
-- indexada caibam na mesma pagina (HOT). Vale para paginas novas ou reescritas.
ALTER TABLE entregas SET (fillfactor = 85);

-- Na instancia: mais workers e mais orcamento total, porque o limite e
-- dividido entre os workers ativos. Workers exigem reinicio.
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1200;
ALTER SYSTEM SET autovacuum_work_mem = '1GB';
SELECT pg_reload_conf();

Três observações evitam surpresas. A primeira é que uma tabela com orçamento próprio de custo sai da divisão com os outros workers, então vale reservar isso para as tabelas que realmente precisam. A segunda é que autovacuum_work_mem define quanta memória cada worker usa para guardar os identificadores das tuplas mortas: com pouca memória, a mesma execução precisa varrer todos os índices da tabela várias vezes, e em tabelas com muitos índices isso domina o tempo total. A terceira é revisar se todos os índices que impedem atualizações HOT são necessários: um índice sobre atualizado_em que ninguém consulta transforma toda atualização em escrita em todos os índices.

Quando o horizonte é liberado, o VACUUM remove as versões mortas e a consulta volta a ficar rápida, porque o espaço passa a ser reutilizado e as páginas quentes voltam a ter linhas vivas. Mas o arquivo continua com cento e setenta gigabytes, e isso tem custo: backups maiores, réplicas novas mais lentas para sincronizar, varreduras sequenciais lendo espaço vazio e índices com páginas quase vazias. Antes de decidir reescrever, vale medir o quanto da tabela é realmente espaço livre.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Estimativa rapida: le apenas as paginas que o mapa de visibilidade nao garante.
SELECT pg_size_pretty(table_len) AS tamanho,
       approx_tuple_percent       AS pct_vivo,
       dead_tuple_percent         AS pct_morto,
       approx_free_percent        AS pct_livre
FROM pgstattuple_approx('entregas');

-- Densidade das folhas de um indice: abaixo de ~50% indica reconstrucao.
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('entregas_status_criado_idx');

Se a tabela tem mais de metade de espaço livre e ele não vai ser reaproveitado tão cedo, reescrever compensa. As opções diferem no bloqueio que exigem, e escolher a errada transforma uma manutenção em indisponibilidade.

A ordem importa. Reescrever a tabela antes de liberar o horizonte e ajustar o autovacuum é desperdício, porque ela volta a inchar no mesmo ritmo. E o pg_repack também precisa de cuidado com o horizonte: ele roda por horas em tabelas grandes, e enquanto roda também segura o xmin, então o ideal é executá-lo num período de menor volume de atualizações e acompanhar as tuplas mortas das outras tabelas enquanto ele trabalha.

O inchaço é um problema que cresce devagar e aparece de repente, porque a consulta só fica lenta quando as versões mortas passam a dominar as páginas que ela lê. Isso significa que existe uma janela de dias ou semanas em que o problema é visível nas métricas e ainda invisível para o usuário. Os sinais abaixo cobrem essa janela.

-- Registra toda execucao do autovacuum acima de 10 segundos,
-- incluindo tuplas removidas e mortas ainda nao removiveis.
ALTER SYSTEM SET log_autovacuum_min_duration = '10s';
SELECT pg_reload_conf();

-- Distancia de cada banco ate o vacuum agressivo contra wraparound.
SELECT datname,
       age(datfrozenxid) AS idade_xid,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::int, 1) AS pct_do_gatilho
FROM pg_database
ORDER BY idade_xid DESC;

O primeiro sinal da tabela é o mais valioso, porque é o único que aponta para a causa em vez de medir a consequência, e teria disparado no primeiro dia do incidente, dezoito dias antes da primeira reclamação. Ele também é barato: a consulta da terceira seção roda em milissegundos e pode alimentar um alerta a cada minuto.