Estratégias de vacuum e autovacuum no PostgreSQL em produção

1. Fundamentos do Vacuum no PostgreSQL

O mecanismo MVCC (Multi-Version Concurrency Control) do PostgreSQL é a base para o funcionamento do vacuum. Quando uma linha é atualizada ou excluída, o PostgreSQL não remove fisicamente os dados antigos — em vez disso, marca essas versões como "dead tuples" (tuplas mortas). Com o tempo, o acúmulo dessas tuplas mortas causa degradação de desempenho, aumento do uso de disco e bloat em índices.

O comando VACUUM (comum) realiza as seguintes operações:
- Remove dead tuples, liberando espaço para reutilização
- Atualiza estatísticas para o planejador de consultas
- Previne o wrap-around de XID (Transaction ID)

Já o VACUUM FULL é uma operação bloqueante que reescreve a tabela inteira, liberando espaço para o sistema operacional. Porém, durante sua execução, a tabela fica inacessível para operações de escrita e leitura — o que é proibitivo em ambientes de produção com alta disponibilidade.

O impacto do vacuum no desempenho é diretamente proporcional ao volume de dead tuples. Uma tabela com 30% de dead tuples pode ter consultas 2x mais lentas devido ao escaneamento desnecessário de versões antigas.

-- Verificando dead tuples em uma tabela
SELECT relname, n_dead_tup, n_live_tup, 
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

2. Autovacuum: Configuração e Comportamento Padrão

O autovacuum é um processo em background que executa automaticamente o vacuum quando certos thresholds são atingidos. Os parâmetros principais no postgresql.conf são:

  • autovacuum_vacuum_threshold (padrão: 50) — número mínimo de dead tuples para acionar o vacuum
  • autovacuum_vacuum_scale_factor (padrão: 0.2) — fração da tabela que, quando morta, aciona o vacuum
  • autovacuum_naptime (padrão: 60s) — intervalo entre verificações do autovacuum

O autovacuum é acionado quando: n_dead_tup > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * reltuples)

Em tabelas com 100 milhões de linhas, o threshold padrão significa que o autovacuum só será acionado após 20 milhões de dead tuples — o que pode ser tarde demais para tabelas com alta taxa de atualização.

-- Verificando configurações atuais do autovacuum
SELECT name, setting, unit, short_desc 
FROM pg_settings 
WHERE name LIKE 'autovacuum%' 
ORDER BY name;

3. Monitoramento e Diagnóstico de Bloat e Dead Tuples

Para monitorar efetivamente o estado do vacuum em produção, utilize consultas como:

-- Diagnóstico de bloat em tabelas
SELECT 
    schemaname || '.' || relname AS table_name,
    n_dead_tup,
    n_live_tup,
    last_autovacuum,
    last_vacuum,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 10;

A extensão pgstattuple fornece métricas detalhadas de bloat:

-- Instalar extensão
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Analisar bloat de uma tabela específica
SELECT * FROM pgstattuple('nome_da_tabela');

Métricas críticas para alertas:
- n_dead_tup crescendo rapidamente
- last_autovacuum muito antigo (mais de 24h em tabelas de alta carga)
- Tempo médio de execução do vacuum acima de 30 minutos

4. Estratégias de Tuning para Autovacuum em Produção

Para tabelas específicas com alta taxa de atualização, ajuste os parâmetros individualmente:

-- Ajuste agressivo para tabela de transações
ALTER TABLE transacoes SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_cost_limit = 1000,
    autovacuum_vacuum_cost_delay = 10
);

-- Ajuste conservador para tabela de logs
ALTER TABLE logs_auditoria SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_cost_limit = 200,
    autovacuum_vacuum_cost_delay = 50
);

Os parâmetros vacuum_cost_limit e vacuum_cost_delay controlam o impacto do vacuum em I/O. Valores típicos:
- vacuum_cost_limit = 200 (padrão: 200) — limite de custo antes de pausar
- vacuum_cost_delay = 20ms (padrão: 0) — pausa quando o limite é atingido

Para ambientes com picos de carga, configure janelas de vacuum menos agressivo durante o dia e mais agressivo à noite.

5. Gerenciamento de Freeze e Prevenção de Wrap-Around

O PostgreSQL usa XIDs (Transaction IDs) de 32 bits, que podem sofrer wrap-around. Quando isso ocorre, transações antigas parecem ser do futuro, causando corrupção de dados. O vacuum freeze previne isso marcando tuplas como "frozen".

Parâmetros críticos:
- autovacuum_freeze_max_age (padrão: 200 milhões) — idade máxima antes de forçar vacuum freeze
- vacuum_freeze_table_age (padrão: 150 milhões) — idade para vacuum freeze completo

-- Monitorar idade das transações por tabela
SELECT 
    relname,
    age(relfrozenxid) AS xid_age,
    mxid_age(relminmxid) AS mxid_age,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind IN ('r', 't')
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

Quando a idade do XID se aproxima de 150 milhões, é necessário executar vacuum freeze manual:

-- Vacuum freeze manual preventivo
VACUUM (FREEZE, VERBOSE) nome_da_tabela;

6. Autovacuum em Tabelas Grandes e Particionadas

Tabelas com bilhões de linhas exigem estratégias especiais. Para tabelas particionadas por mês:

-- Configuração por partição
ALTER TABLE vendas_2024_01 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 500
);

-- Script para vacuum em partições antigas (mais de 6 meses)
SELECT 'VACUUM (VERBOSE, INDEX_CLEANUP ON) ' || schemaname || '.' || tablename || ';'
FROM pg_tables
WHERE tablename LIKE 'vendas_%'
  AND tablename < 'vendas_2024_07';

Para tabelas temporárias ou de staging, use VACUUM (TRUNCATE) para liberar espaço rapidamente:

-- Otimizar tabela temporária
VACUUM (TRUNCATE, VERBOSE) tabela_temporaria;

7. Operações Manuais e Ferramentas Avançadas

Quando o autovacuum não é suficiente, utilize pg_repack para recuperar espaço sem bloqueio:

-- Instalar pg_repack (requer extensão)
CREATE EXTENSION IF NOT EXISTS pg_repack;

-- Reorganizar tabela sem downtime
-- pg_repack -d database -t tabela_problema

Para vacuum manual programado em janelas de baixa carga:

-- Script cron para vacuum noturno
-- 0 2 * * * psql -c "VACUUM (VERBOSE, ANALYZE) tabela_grande;"

Integração com Prometheus para métricas de vacuum:

# Métricas exportadas pelo postgres_exporter
# pg_stat_user_tables_n_dead_tup
# pg_stat_user_tables_last_autovacuum
# pg_stat_user_tables_autovacuum_count

Referências