Prevenindo o Inchaço: Estratégias Avançadas de Vácuo no PostgreSQL para Desempenho

O PostgreSQL, um banco de dados relacional de código aberto poderoso e versátil, depende de vários mecanismos internos para manter a integridade dos dados e o desempenho. Entre eles, a operação VACUUM desempenha um papel crítico na recuperação de espaço de armazenamento e na prevenção da degradação do desempenho causada por tuplas mortas. Embora o VACUUM seja frequentemente discutido em termos básicos, entender e implementar estratégias avançadas de vácuo pode impactar significativamente a saúde e a velocidade do seu banco de dados PostgreSQL.

O inchaço de tabela, um problema comum em bancos de dados movimentados, ocorre quando linhas excluídas ou atualizadas deixam para trás tuplas mortas que não são removidas imediatamente. Essas tuplas mortas consomem espaço em disco e podem desacelerar a execução de consultas, pois o banco de dados precisa escanear mais dados. O Autovacuum, o processo automatizado em segundo plano do PostgreSQL, visa gerenciar isso, mas suas configurações padrão nem sempre são ideais para cada carga de trabalho. O trabalho útil é saber quais tabelas precisam de uma limpeza mais agressiva, quais podem ser deixadas em paz e quando uma janela de manutenção manual vale a interrupção.

Entendendo o Inchaço de Tabela e seu Impacto

O PostgreSQL usa um sistema de Controle de Concorrência Multiversão (MVCC). Quando uma linha é atualizada, uma nova versão da linha é criada e a versão antiga é marcada como morta. Da mesma forma, quando uma linha é excluída, ela é marcada como morta, mas não removida imediatamente. Essas tuplas mortas permanecem na tabela até que uma operação VACUUM as limpe. Se o VACUUM não for executado com frequência suficiente ou não for agressivo o bastante, as tuplas mortas se acumulam, levando ao inchaço da tabela.

As consequências do inchaço da tabela são significativas:

Aumento do Uso de Disco: Tabelas inchadas consomem mais espaço em disco do que o necessário, o que pode levar a problemas de armazenamento e aumento do tempo de backup.
Desempenho de Consulta Mais Lento: Consultas que escaneiam tabelas inchadas precisam processar mais dados, incluindo tuplas mortas, levando a tempos de execução mais longos. O inchaço do índice pode ter um efeito semelhante e prejudicial.
Eficiência de Cache Reduzida: Tabelas e índices inchados ocupam mais espaço no cache do banco de dados, potencialmente reduzindo a quantidade de dados ativamente usados que podem ser mantidos na memória.
Sobrecarga do Autovacuum: Se o Autovacuum luta para acompanhar a taxa de atualizações e exclusões de tuplas, ele próprio pode se tornar um gargalo de desempenho.

Ajuste do Autovacuum: A Primeira Linha de Defesa

O Autovacuum é um processo em segundo plano projetado para executar automaticamente operações VACUUM e ANALYZE em tabelas que sofreram alterações significativas. Embora esteja habilitado por padrão, sua eficácia depende muito da configuração adequada. Ajustar os parâmetros do Autovacuum é crucial para prevenir o inchaço sem causar carga indevida no sistema.

Principais parâmetros de configuração do Autovacuum encontrados em postgresql.conf:

autovacuum_vacuum_threshold: O número mínimo de tuplas atualizadas ou excluídas antes que um VACUUM seja executado em uma tabela. O padrão é 50.
autovacuum_vacuum_scale_factor: Uma fração do tamanho da tabela antes que um VACUUM seja executado. O padrão é 0,2 (20%).
- Um VACUUM é acionado se (número de tuplas mortas) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (número de tuplas vivas).
autovacuum_analyze_threshold: O número mínimo de tuplas inseridas, atualizadas ou excluídas antes que um ANALYZE seja executado. O padrão é 50.
autovacuum_analyze_scale_factor: Uma fração do tamanho da tabela antes que um ANALYZE seja executado. O padrão é 0,1 (10%).
- Um ANALYZE é acionado se (número de tuplas alteradas) > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * (número de tuplas vivas).
autovacuum_vacuum_cost_delay: O tempo de espera se o limite de custo for excedido (em milissegundos). O padrão é 20ms.
autovacuum_vacuum_cost_limit: A quantidade máxima de custo que o processo de vácuo pode acumular antes de dormir. O padrão é -1 (o que significa que usa vacuum_cost_limit se definido, caso contrário, é efetivamente ilimitado, o que não é ideal).
autovacuum_max_workers: O número máximo de processos de vácuo em segundo plano que podem ser executados simultaneamente. O padrão é 3.
autovacuum_nap_time: O atraso mínimo entre iniciar tarefas de autovacuum. O padrão é 1 minuto.

Cenários Práticos de Ajuste do Autovacuum:

Bancos de Dados com Alta Taxa de Transação: Para tabelas com atualizações e exclusões frequentes, você pode precisar diminuir autovacuum_vacuum_threshold e autovacuum_vacuum_scale_factor para acionar o vácuo com mais frequência. Por exemplo, em uma tabela movimentada, você pode definir:
```
ALTER TABLE sua_tabela SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE sua_tabela SET (autovacuum_analyze_threshold = 200, autovacuum_analyze_scale_factor = 0.02);
```
Isso torna o vácuo mais agressivo nesta tabela específica.
Tabelas Grandes e Estáticas com Atualizações Ocasionalmente: Para tabelas que são principalmente lidas e raramente atualizadas, as configurações padrão podem ser suficientes, ou você pode até aumentar o scale_factor para reduzir a sobrecarga desnecessária do vácuo.
Controlando o Impacto do Autovacuum: Para evitar que o Autovacuum consuma muitos recursos, você pode ajustar autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit. Os valores corretos dependem da velocidade do armazenamento e da carga de trabalho, então teste durante o tráfego normal em vez de copiar um número cegamente.
```
ALTER TABLE sua_tabela SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);
```
session_replication_role não é um controle de ajuste do autovacuum. Ele afeta o comportamento de gatilhos e regras e não deve ser usado como um atalho para gerenciamento de inchaço.

Melhores Práticas de VACUUM Manual

Embora o Autovacuum seja essencial, existem situações em que as operações manuais de VACUUM são necessárias ou benéficas:

Após Grandes Cargas/Exclusões de Dados: Realizar um VACUUM manual após operações em massa significativas pode recuperar espaço imediatamente e evitar que o inchaço se acumule.
Quando o Autovacuum Fica para Trás: Se você observar inchaço significativo apesar do Autovacuum estar em execução, um VACUUM manual pode fornecer uma limpeza imediata.
VACUUM FULL para Inchaço Extremo: Em casos de inchaço severo onde mesmo um VACUUM regular não é suficiente, VACUUM FULL pode ser usado. No entanto, VACUUM FULL reescreve a tabela inteira em um novo arquivo, o que é uma operação de bloqueio (requer um bloqueio exclusivo) e pode levar muito tempo em tabelas grandes. Deve ser usado com extrema cautela e idealmente durante uma janela de manutenção.
VACUUM (FREEZE): Esta opção força um VACUUM a congelar quaisquer tuplas restantes que sejam antigas o suficiente para serem consideradas permanentemente visíveis por todas as transações futuras. Isso pode ajudar a prevenir avisos de VACUUM e reduzir a probabilidade de problemas de wraparound de ID de transação.

Comandos Manuais de VACUUM:

VACUUM Padrão: Recupera espaço e o disponibiliza para reutilização. Não reduz significativamente o tamanho do arquivo em disco, a menos que TRUNCATE seja usado.
```
VACUUM sua_tabela;
VACUUM VERBOSE sua_tabela; -- Fornece mais saída
```
VACUUM ANALYZE: Executa VACUUM e depois atualiza as estatísticas da tabela. Isso é crucial para o planejador de consultas.
```
VACUUM ANALYZE sua_tabela;
```
VACUUM FULL: Reescreve a tabela, recuperando todo o espaço não utilizado e reduzindo o arquivo. Requer um bloqueio exclusivo.
```
VACUUM FULL sua_tabela;
```
VACUUM (FREEZE): Força o congelamento de tuplas antigas.
```
VACUUM (FREEZE) sua_tabela;
```
VACUUM (TRUNCATE): Disponível no PostgreSQL 13+, esta opção pode recuperar espaço do final do arquivo da tabela, semelhante ao TRUNCATE, mas sem um bloqueio exclusivo para toda a operação. Ainda requer um breve bloqueio exclusivo no final.
```
VACUUM (TRUNCATE) sua_tabela;
```

Estratégias e Considerações Avançadas

Além do ajuste básico do Autovacuum e dos comandos manuais de VACUUM, várias técnicas avançadas podem otimizar ainda mais o vácuo:

Monitoramento de Inchaço: Monitore regularmente suas tabelas quanto a inchaço. Você pode usar consultas SQL para estimar o inchaço ou utilizar ferramentas de monitoramento.

-- Consulta para estimar inchaço (requer extensão pgstattuple)
-- CREATE EXTENSION pgstattuple;
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_table_size(oid)) AS index_size,
    CASE WHEN dead_tuples > 0 THEN round(100.0 * dead_tuples / (live_tuples + dead_tuples), 2) ELSE 0 END AS percent_bloat
FROM (
    SELECT
        schemaname,
        relname,
        n_live_tup AS live_tuples,
        n_dead_tup AS dead_tuples,
        c.oid
    FROM pg_stat_user_tables s JOIN pg_class c ON s.relid = c.oid
) AS stats
WHERE live_tuples + dead_tuples > 0
ORDER BY percent_bloat DESC;

-- Consulta alternativa para estimar inchaço sem extensões
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) ELSE 0 END AS percent_bloat
FROM pg_stat_user_tables
ORDER BY percent_bloat DESC;

Manutenção de Índices: Os índices também podem ficar inchados. Use REINDEX para reconstruí-los, se necessário. O REINDEX simples pode bloquear o trabalho normal; REINDEX CONCURRENTLY reduz a interrupção, mas leva mais tempo e ainda precisa de planejamento.
```
REINDEX INDEX CONCURRENTLY nome_do_seu_indice;
```
Prevenção de Wraparound de ID de Transação: O PostgreSQL reutiliza IDs de transação. Quando um ID atinge seu valor máximo, ele dá a volta. Para evitar corrupção de dados, o PostgreSQL congela tuplas antigas. O VACUUM (especialmente com FREEZE) desempenha um papel fundamental. O parâmetro freeze_max_age do Autovacuum determina a idade que um ID de transação pode ter antes que o Autovacuum seja forçado a ser executado, mesmo que outros limites não sejam atingidos.
```
-- Monitorar a idade do ID de transação
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 10;
```
Se você vir idades muito grandes, isso indica problemas potenciais com o vácuo não acompanhando.
Estratégia de Particionamento: Para tabelas muito grandes, considere o particionamento. Vácuo em uma partição menor é muito mais rápido e consome menos recursos do que vácuo em uma única tabela massiva.
Pool de Conexões: Embora não seja diretamente uma estratégia de vácuo, o pool de conexões eficiente (por exemplo, usando PgBouncer) pode reduzir a sobrecarga de estabelecer conexões de banco de dados, o que beneficia indiretamente o desempenho geral do banco de dados e permite que tarefas de manutenção em segundo plano, como o Autovacuum, sejam executadas de forma mais suave.
Controle de Transações Longas: Uma única transação antiga pode impedir a limpeza. Verifique sessões que estão abertas há muito tempo, especialmente sessões idle in transaction, porque elas podem manter versões antigas de linhas visíveis e forçar o inchaço a crescer.
```
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
```

Um Fluxo de Trabalho Prático de Ajuste de Vácuo

Comece pela tabela que dói, não pelo servidor inteiro. Se uma tabela de pedidos de 900 GB está inchada e uma tabela de consulta de 20 MB está limpa, as alterações globais podem fazer barulho sem resolver o problema real. Olhe para pg_stat_user_tables primeiro:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Em seguida, compare isso com a carga de trabalho. Uma tabela do tipo fila que atualiza o status constantemente pode precisar de um autovacuum_vacuum_scale_factor baixo, porque esperar que 20% de uma tabela enorme se torne morta é tarde demais. Uma partição de arquivo mensal pode não precisar de configurações agressivas. As configurações por tabela permitem que você trate esses casos de forma diferente.

Para tabelas com muitas atualizações, um padrão comum é:

ALTER TABLE job_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

Esses números são pontos de partida, não verdades universais. Observe se as tuplas mortas param de crescer entre as execuções do autovacuum, se a latência da consulta melhora e se o autovacuum cria E/S inaceitável durante os horários de pico.

Quando o inchaço já é severo, o VACUUM regular pode parar o sangramento, mas não reduz o arquivo de relação. Isso surpreende muitas equipes. O VACUUM regular torna o espaço reutilizável dentro da tabela; geralmente não retorna a maior parte do espaço para o sistema operacional. Para reduzir fisicamente uma tabela grande, você está escolhendo entre opções disruptivas, como VACUUM FULL, reconstruções de tabela, rotação de partição ou ferramentas como pg_repack onde permitido. Cada opção tem compensações operacionais e de bloqueio e espaço em disco.

Escolhendo a Correção Menos Dolorosa

Se uma tabela está apenas moderadamente inchada, mas ainda recebe gravações constantes, comece com o ajuste do autovacuum e a limpeza de transações antigas. Você quer que o PostgreSQL reutilize o espaço naturalmente em vez de reescrever uma tabela grande durante o horário comercial.

Se uma tabela teve uma limpeza única e agora é muito menor, o VACUUM regular tornará o espaço vazio reutilizável para futuras inserções e atualizações. Se você precisar devolver esse espaço ao sistema operacional, planeje uma opção de reescrita. VACUUM FULL é simples, mas bloqueia. pg_repack pode ser menos disruptivo, mas é uma extensão extra e ainda precisa de espaço livre em disco suficiente para construir estruturas de substituição. Tabelas particionadas oferecem outra opção: descartar ou desanexar partições antigas em vez de excluir milhões de linhas de uma tabela gigante.

Se os índices são o problema, não reconstrua todos os índices por hábito. Verifique quais índices são grandes, não utilizados ou duplicados. pg_stat_user_indexes pode mostrar contagens de varredura de índice, e a revisão do esquema pode revelar índices sobrepostos, como (user_id) e (user_id, created_at) onde apenas um pode ser necessário. Remover um índice verdadeiramente não utilizado pode melhorar o desempenho de gravação e reduzir o trabalho futuro de vácuo.

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Tenha cuidado com índices "não utilizados" após uma reinicialização ou redefinição de estatísticas, porque os contadores começam do zero. Olhe para histórico suficiente antes de descartar qualquer coisa.

Uma boa estratégia de vácuo é chata quando funciona. O Autovacuum é executado com frequência suficiente para que as tuplas mortas não se acumulem, a manutenção manual é reservada para eventos conhecidos e as transações antigas são tratadas como problemas de produção em vez de sessões ociosas inofensivas. O objetivo não é fazer vácuo o máximo possível. O objetivo é manter a limpeza à frente da rotatividade sem roubar a E/S que seu aplicativo precisa.