Detecção e Eliminação de Inchaço de Banco de Dados no PostgreSQL Usando VACUUM

O inchaço de banco de dados no PostgreSQL ocorre quando versões antigas de linhas e índices superdimensionados ocupam mais espaço do que seus dados ativos precisam. Você pode notar backups maiores, varreduras mais lentas, contas de armazenamento crescentes ou o autovacuum trabalhando constantemente sem que a tabela diminua de tamanho no disco.

A correção depende do tipo de espaço que você precisa recuperar. O VACUUM padrão torna o espaço de tuplas mortas reutilizável dentro do PostgreSQL. VACUUM FULL, REINDEX e ferramentas como pg_repack podem reduzir fisicamente os objetos, mas eles vêm com diferentes custos operacionais e de bloqueio.

Entendendo o MVCC do PostgreSQL e o Inchaço

Para combater efetivamente o inchaço, devemos primeiro entender sua causa raiz. A arquitetura MVCC do PostgreSQL garante que os leitores nunca bloqueiem os escritores e vice-versa. Quando uma linha é atualizada, o PostgreSQL não sobrescreve a linha antiga; ele insere uma nova versão e marca a versão antiga como morta. Da mesma forma, linhas excluídas deixam tuplas mortas.

O inchaço ocorre quando essas tuplas mortas se acumulam mais rápido do que os processos de manutenção (Autovacuum ou VACUUM manual) podem limpá-las ou reutilizar o espaço.

Consequências do Inchaço de Banco de Dados

O inchaço impacta o desempenho em várias áreas principais:

1. Aumento do Uso de Espaço em Disco: Tuplas mortas ocupam espaço físico, forçando tabelas e índices a consumir mais armazenamento do que o necessário.
2. Varreduras Sequenciais Mais Lentas: O mecanismo do banco de dados deve ler através de tuplas mortas durante as varreduras de tabela, aumentando a carga de E/S.
3. Indexação Ineficiente: Índices inchados são maiores, levando a mais leituras de disco para percorrer a estrutura do índice.
4. Esforços de Autovacuum Desperdiçados: O Autovacuum tem que trabalhar mais e por mais tempo para limpar tabelas, potencialmente atrasando a manutenção crítica em outras tabelas.

Detectando Inchaço de Banco de Dados

A detecção começa com estatísticas do catálogo e tamanhos de objetos. Trate consultas simples como triagem, não como medição exata de inchaço, porque o layout da tabela, fator de preenchimento, dados TOAST e índices afetam o número real.

1. Identificando Tabelas Inchadas usando pg_stat_user_tables

A visão pg_stat_user_tables fornece estatísticas sobre tabelas definidas pelo usuário. Podemos calcular o inchaço aproximado comparando o tamanho total alocado para a tabela versus o tamanho dos dados vivos.

Métricas Chave para Monitorar:

• n_dead_tup: Número de tuplas mortas.
• last_autovacuum, last_vacuum: Quando a manutenção foi executada pela última vez.

Contagens simples são úteis porque mostram onde a pressão do vácuo está se acumulando. Um grande n_dead_tup comparado com n_live_tup é uma boa razão para inspecionar uma tabela mais de perto.

Exemplo de Consulta (Encontrando Candidatos a Vácuo):

Esta consulta destaca tabelas com muitas tuplas mortas e mostra quando o vácuo foi executado pela última vez:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. Avaliando Índices Inchados

O inchaço geralmente afeta significativamente os índices. Comece procurando por índices excepcionalmente grandes em tabelas de alta rotatividade:

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

Apenas o tamanho grande não prova inchaço, mas indica quais índices valem a pena verificar com ferramentas mais profundas, como a extensão pgstattuple ou a consulta de inchaço da sua plataforma de monitoramento.

Gerenciando o Inchaço: O Papel do VACUUM

VACUUM é a principal ferramenta do PostgreSQL para recuperar espaço de tuplas mortas e atualizar mapas de visibilidade.

Autovacuum: A Primeira Linha de Defesa

Por padrão, o PostgreSQL executa processos autovacuum automaticamente. O Autovacuum executa um VACUUM padrão (que marca o espaço como reutilizável internamente, mas não o libera de volta ao SO) quando um limite é atingido. Este limite é definido por autovacuum_vacuum_scale_factor (padrão 0.2 ou 20% do tamanho da tabela) mais autovacuum_vacuum_threshold (padrão 50 tuplas).

Dica de Configuração: Para tabelas de alta rotatividade, considere diminuir o scale_factor para acionar a manutenção mais cedo, evitando grande acúmulo de inchaço.

-- Exemplo: Definindo parâmetros agressivos de autovacuum para uma tabela crítica 'orders'
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

VACUUM Padrão vs. VACUUM FULL

Existem dois modos principais de limpeza:

VACUUM Padrão

Um VACUUM padrão marca tuplas mortas para reutilização dentro do arquivo físico existente. Ele não reduz o tamanho do arquivo da tabela no disco. Isso não é bloqueante e é seguro para tabelas de alto tráfego.

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- Mostra estatísticas sobre tuplas limpas

VACUUM FULL (A Ferramenta de Recuperação de Espaço)

VACUUM FULL reescreve todo o arquivo da tabela para remover fisicamente tuplas mortas e recuperar espaço de volta para o sistema operacional.

Aviso: VACUUM FULL requer um bloqueio ACCESS EXCLUSIVE na tabela durante sua duração. Isso significa que todas as operações de leitura e escrita nessa tabela serão bloqueadas até que VACUUM FULL seja concluído. Use este comando com moderação em tabelas grandes e muito usadas.

VACUUM FULL table_name;

Melhor Prática: Use VACUUM FULL apenas quando o inchaço for severo e você puder arcar com o tempo de inatividade, ou durante janelas de manutenção programadas.

Estratégias Avançadas Anti-Inchaço

Quando VACUUM FULL é muito disruptivo, existem métodos alternativos para recuperar espaço com menos tempo de inatividade.

1. Reconstruindo Índices

Índices individuais podem ser reconstruídos quando o inchaço do índice é o principal problema. Para sistemas ocupados, prefira a forma concorrente para que leituras e escritas possam continuar durante a maior parte da operação:

REINDEX INDEX CONCURRENTLY index_name;

REINDEX INDEX index_name; simples é mais rápido, mas usa bloqueios mais fortes, então use-o durante uma janela de manutenção.

2. Usando pg_repack para Reescrever Tabelas Online

O utilitário pg_repack é o método preferido para eliminar o inchaço da tabela com tempo de inatividade mínimo. Ele funciona criando uma nova cópia limpa da estrutura e dos dados da tabela ao lado da tabela antiga, aplicando alterações de forma síncrona e, em seguida, trocando as tabelas atomicamente.

Como pg_repack funciona:

1. Ele cria uma tabela temporária (_new) espelhando a original.
2. Ele monitora continuamente as alterações na tabela original usando gatilhos.
3. Ele executa uma cópia e troca final sincronizada.

Instalação e Uso (Fluxo Típico):

Instale a extensão e o pacote CLI para sua versão do PostgreSQL, habilite a extensão no banco de dados e execute o comando pg_repack a partir de um shell:

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

Nota sobre pg_repack: Embora reduza significativamente o bloqueio em comparação com VACUUM FULL, ainda requer a criação de gatilhos e cópia de dados, o que consome E/S e armazenamento extra temporariamente.

Conclusão

O inchaço do banco de dados é gerenciável quando você o monitora antes que se torne uma interrupção. A prevenção através de autovacuum ajustado é melhor do que reescritas de emergência. Quando o inchaço ocorrer, siga esta hierarquia:

1. Monitore: Verifique regularmente pg_stat_user_tables para altas contagens de n_dead_tup.
2. Ajuste o Autovacuum: Para tabelas ativas, diminua o fator de escala para garantir que o VACUUM padrão seja executado com mais frequência.
3. Repare: Se o inchaço for menor, um VACUUM table_name padrão pode ser suficiente se a atividade da tabela diminuir.
4. Reparo Agressivo (Baixo Tempo de Inatividade): Use pg_repack para reescrever a estrutura da tabela online.
5. Reparo de Emergência (Alto Tempo de Inatividade): Use VACUUM FULL apenas como último recurso quando o tempo de inatividade for aceitável, pois ele mantém bloqueios exclusivos.

Antes de qualquer reescrita, verifique transações de longa duração e certifique-se de ter espaço livre em disco suficiente para a operação.