Autovacuum e bloat no PostgreSQL: como diagnosticar e corrigir tabelas inchadas

Entenda o autovacuum do PostgreSQL, descubra tabelas com bloat e dead tuples, ajuste os parâmetros e recupere espaço com pg_repack sem parar o banco.

Para que serve

No PostgreSQL, um UPDATE ou DELETE não apaga a linha antiga na hora: ela vira uma "tupla morta" que só é reaproveitada depois do VACUUM. O autovacuum do PostgreSQL faz essa limpeza em segundo plano. Quando ele não acompanha o ritmo de escrita, as tabelas e índices incham (bloat), as consultas ficam mais lentas e o disco enche. Este artigo mostra como diagnosticar, ajustar o autovacuum e recuperar o espaço já perdido.

Pré-requisitos

• Acesso ao psql como postgres ou superusuário.
• PostgreSQL 13 ou mais novo (exemplos testados no 16 a 18).
• Espaço livre em disco igual ao tamanho da maior tabela que você pretende reorganizar.

1. Veja se o autovacuum está acompanhando

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup*100.0/nullif(n_live_tup+n_dead_tup,0),1) AS pct_morta,
       last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 15;

Sinais de problema: pct_morta acima de 20% em tabelas grandes, last_autovacuum vazio ou antigo em tabelas muito atualizadas. Para ver o que o autovacuum está fazendo agora:

SELECT p.pid, p.relid::regclass AS tabela, p.phase,
       p.heap_blks_scanned, p.heap_blks_total, a.query_start
FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a USING (pid);

2. Procure o que impede a limpeza

O VACUUM só remove tuplas que nenhuma transação aberta ainda possa enxergar. Uma única transação esquecida trava a limpeza do banco inteiro. Verifique três suspeitos:

-- Transações abertas há muito tempo (inclusive "idle in transaction")
SELECT pid, usename, state, now() - xact_start AS duracao, left(query,80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

-- Slots de replicação parados segurando dados antigos
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

-- Transações preparadas esquecidas
SELECT gid, prepared, owner FROM pg_prepared_xacts;

Encerre a sessão culpada com SELECT pg_terminate_backend(pid); (avise o responsável pela aplicação). Para evitar reincidência, defina um limite para sessões paradas no meio de transação:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

Réplicas com hot_standby_feedback = on também seguram a limpeza no primário enquanto executam consultas longas. Veja o artigo sobre replicação em streaming no PostgreSQL.

3. Ajuste o autovacuum do PostgreSQL

Por padrão, uma tabela é limpa quando as tuplas mortas passam de 50 + 20% das linhas. Em uma tabela de 100 milhões de linhas, isso significa esperar 20 milhões de linhas mortas. Os padrões também limitam o ritmo do autovacuum para não pesar no I/O, o que é conservador demais para NVMe.

cat > /etc/postgresql/17/main/conf.d/91-autovacuum.conf <<'EOF'
autovacuum_max_workers = 6
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 2000
log_autovacuum_min_duration = 10s
EOF
# autovacuum_max_workers exige restart até a versão 17; os demais valem com reload
systemctl restart postgresql@17-main

• autovacuum_vacuum_cost_limit: quanto trabalho cada rodada faz antes de pausar. Subir de 200 (padrão efetivo) para 1000–2000 é comum em SSD/NVMe. O limite é dividido entre os workers ativos.
• autovacuum_max_workers: até a versão 17 exige restart. No PostgreSQL 18 pode ser alterado com reload, até o valor de autovacuum_worker_slots.
• No PostgreSQL 18 existe também autovacuum_vacuum_max_threshold, um teto absoluto de tuplas mortas que dispara o vacuum mesmo em tabelas gigantes.
• log_autovacuum_min_duration registra no log cada execução demorada, útil para acompanhar.

Para tabelas grandes e muito atualizadas, ajuste por tabela, usando um número absoluto de linhas:

ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 100000,
  autovacuum_analyze_scale_factor = 0.02
);

4. Meça o bloat de verdade

As estatísticas acima são estimativas. Para números exatos em uma tabela específica, use a extensão pgstattuple (lê a tabela inteira, rode fora do pico):

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT pg_size_pretty(table_len) AS tamanho, dead_tuple_percent, free_percent
FROM pgstattuple('public.pedidos');

-- Versão mais leve, por amostragem
SELECT * FROM pgstattuple_approx('public.pedidos');

Somando dead_tuple_percent e free_percent, você tem a parte do arquivo que não guarda dados úteis. Algum espaço livre é normal e até desejável; acima de 30–40% em tabela grande, vale reorganizar.

5. Recupere o espaço sem parar a aplicação

O VACUUM comum libera espaço para reuso, mas quase nunca devolve ao sistema operacional. Há três formas de encolher:

Instalação e uso do pg_repack (pacote do repositório do Debian/Ubuntu ou do PGDG, na mesma versão do servidor):

apt install postgresql-17-repack
sudo -u postgres psql -d meubanco -c "CREATE EXTENSION pg_repack;"
sudo -u postgres pg_repack -d meubanco -t public.pedidos --no-order

Como saber se funcionou

• n_dead_tup cai e last_autovacuum passa a ter datas recentes nas tabelas mais movimentadas.
• SELECT pg_size_pretty(pg_total_relation_size('public.pedidos')); mostra a tabela menor após o pg_repack.
• O log mostra entradas automatic vacuum of table ... com frequência.

Problemas comuns

Aviso "database must be vacuumed within ... transactions"

É o alerta de wraparound do contador de transações. Confira a idade com SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;. Valores perto de 2 bilhões são emergência: remova o que bloqueia o vacuum (passo 2) e rode VACUUM (VERBOSE) nas tabelas mais antigas. Não desligue o autovacuum.

O autovacuum consome muito I/O

Diminua autovacuum_vacuum_cost_limit aos poucos. Em NVMe isso raramente é necessário; muitas vezes o vacuum pesado é sinal de que ele estava atrasado.

Tabela de fila (insere e apaga sem parar) sempre inchada

Use ajuste por tabela com threshold baixo e, se possível, particionamento com DROP de partições antigas em vez de DELETE.

Perguntas frequentes

Posso desligar o autovacuum?

Não. Além do bloat, ele previne o wraparound, que pode forçar o banco a parar. Ajuste em vez de desligar.

Preciso agendar VACUUM manual no cron?

Normalmente não. Com o autovacuum bem configurado, o manual fica para casos pontuais, como depois de cargas ou exclusões massivas (VACUUM ANALYZE tabela;).

VACUUM FULL e pg_repack fazem a mesma coisa?

O resultado é parecido (tabela reescrita e compacta), mas o VACUUM FULL bloqueia a tabela o tempo todo e o pg_repack só por instantes.

Leitura complementar

• PostgreSQL: Routine Vacuuming
• PostgreSQL: Automatic Vacuuming (parâmetros)
• pg_repack: documentação

Precisa de ajuda?

Se o disco do servidor estiver perto de encher por causa do bloat e você precisar de espaço emergencial, abra um ticket com a saída de df -h e zpool list (se usar ZFS).