Limpeza e análise automática de tabelas

O Autovacuum é um daemon (ou seja, ele é executado em segundo plano) que aspira (limpa) automaticamente as tuplas inativas, recupera o armazenamento e reúne estatísticas. Ele verifica se há tabelas inchadas no banco de dados e limpa o inchaço para reutilizar o espaço. Ele monitora tabelas e índices do banco de dados e os adiciona a uma tarefa de vacuum após atingirem um limite específico de operações de atualização ou exclusão.

O Autovacuum gerencia a limpeza automatizando os comandos VACUUM e ANALYZE do PostgreSQL. O VACUUM remove o inchaço das tabelas e recupera o espaço, enquanto o ANALYZE atualiza as estatísticas que permitem que o otimizador gere planos eficientes. O VACUUM também executa uma tarefa importante chamada congelamento de vacuum para evitar problemas de wraparound de IDs de transação no banco de dados. Cada linha atualizada no banco de dados recebe um ID de transação do mecanismo de controle de transações do PostgreSQL. Esses IDs controlam a visibilidade da linha em relação a outras transações simultâneas. O ID de transação é um número de 32 bits. Dois bilhões de IDs são sempre mantidos visíveis no hstórico de transações. Os IDs restantes (cerca de 2,2 bilhões) são preservados para transações que ocorrerão no futuro e estão ocultos da transação atual. O PostgreSQL exige uma limpeza e um congelamento ocasionais de linhas antigas para evitar que as transações sejam agrupadas e tornem as linhas antigas e existentes invisíveis quando novas transações forem criadas. Para obter mais informações, consulte Evitar falhas de conclusão do ID da transação na documentação do PostgreSQL.

O autovacuum é recomendado e habilitado por padrão. Seus parâmetros incluem o seguinte:

O Autovacuum faz uma lista de tabelas para processar com base em fórmulas de limite específicas, conforme a seguir.

Para tabelas de pequeno a médio porte, os valores padrão podem ser suficientes. No entanto, uma tabela grande com modificações de dados frequentes terá um número maior de tuplas inativas. Nesse caso, o autovacuum pode processar a tabela com frequência para fins de manutenção, e a manutenção de outras mesas pode ser atrasada ou ignorada até que a tabela grande seja concluída. Para evitar isso, você pode ajustar os parâmetros de autovacuum descritos na seção a seguir.

Parâmetros relacionados à memória do autovacuum

autovacuum_max_workers

Especifica o número máximo de processos de autovacuum (exceto o inicializador de autovacuum) que podem ser executados ao mesmo tempo. Esse parâmetro só pode ser definido quando você inicia o servidor. Se o processo de autovacuum estiver ocupado com uma tabela grande, esse parâmetro ajudará a executar a limpeza de outras tabelas.

maintenance_work_mem

Ele especifica a quantidade máxima de memória a ser utilizada por operações de manutenção, como VACUUM, CREATE INDEX e ALTER. No Amazon RDS e no Aurora, a memória é alocada com base na classe da instância usando a fórmula GREATEST({DBInstanceClassMemory/63963136*1024},65536). Quando o autovacuum é executado, até autovacuum_max_workers vezes esse valor calculado pode ser alocado, portanto, tenha cuidado para não definir o valor muito alto. Para controlar isso, você pode definir autovacuum_work_mem separadamente.

autovacuum_work_mem

Especifica a memória máxima a ser usada por cada processo de operador de autovacuum. Esse parâmetro é padronizado para -1, o que indica que você deve usar o valor de maintenance_work_mem.

Para obter mais informações sobre parâmetros de memória de autovacuum, consulte Alocar memória para autovacuum na documentação do Amazon RDS.

Ajuste dos parâmetros de autovacuum

Os usuários talvez precisem ajustar os parâmetros de autovacuum, dependendo das operações de atualização e exclusão. As configurações dos parâmetros a seguir podem ser definidas no nível de tabela, instância ou cluster.

Nível de cluster ou instância

Como exemplo, vejamos um banco de dados do setor financeiro em que se espera um volume contínuo de operações de Linguagem de Manipulação de Dados (DML). Para manter a integridade do banco de dados, você deve ajustar os parâmetros de autovacuum no nível de cluster para o Aurora e no nível de instância para o Amazon RDS, além de aplicar o mesmo grupo de parâmetros ao leitor. No caso de um failover, os mesmos parâmetros devem ser aplicados ao novo gravador.

Nível de tabela

Por exemplo, em um banco de dados para entrega de alimentos em que se espera operações contínuas de DML em uma única tabela chamada orders, você deve considerar o ajuste do parâmetro autovacuum_analyze_threshold no nível de tabela usando o seguinte comando:

ALTER TABLE <table_name> SET (autovacuum_analyze_threshold = <threshold rows>)

Uso de configurações agressivas de autovacuum no nível de tabela

A tabela orders de exemplo que tem operações contínuas de atualização e exclusão torna-se candidata à limpeza devido às configurações padrão de autovacuum. Isso leva à geração incorreta de planos e à lentidão nas consultas. Limpar o inchaço e atualizar as estatísticas requer configurações agressivas de autovacum em nível de tabela.

Para determinar as configurações, acompanhe a duração das consultas em execução nessa tabela e identifique a porcentagem de operações de DML que resultam em alterações no plano. A visuazliação de pg_stat_user_tables ajuda a monitorar as operações de inserção, atualização e exclusão.

Exemplo:

Vamos supor que o otimizador gere planos ruins sempre que 5% da tabela orders muda. Nesse caso, você deve alterar o limite do fator de escala para 2% da seguinte forma:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02)

Para obter mais informações, consulte o seguinte:

Para garantir que o autovacuum funcione de forma eficaz, monitore as linhas inativas, o uso do disco e a última vez em que o autovacuum ou o ANALYZE foi executado regularmente. A visualização pg_stat_all_tables fornece informações sobre cada tabela (relname) e quantas tuplas inativas (n_dead_tup) estão na tabela.

O monitoramento do número de tuplas inativas em cada tabela, especialmente em tabelas atualizadas com frequência, ajuda a determinar se os processos de autovacuum estão removendo periodicamente as tuplas inativas para que seu espaço em disco possa ser reutilizado para uma melhor performance. É possível usar a consulta abaixo para conferir o número de tuplas inativas e quando o último autovacuum foi executado nas tabelas:

SELECT
relname AS TableName,n_live_tup AS LiveTuples,n_dead_tup AS DeadTuples,
last_autovacuum AS Autovacuum,last_autoanalyze AS Autoanalyze_FROM 
pg_stat_user_tables;

Vantagens e limitações

O autovacuum oferece as seguintes vantagens:

Limitações: