Trabalhar com o autovacuum do PostgreSQL no Amazon RDS for PostgreSQL

É altamente recomendável que você use o recurso de autovacuum para bancos de dados PostgreSQL a fim de manter a integridade de sua instância de banco de dados PostgreSQL. O autovacuum automatiza a execução dos comandos VACUUM e ANALYZE. Ele verifica as tabelas com um grande número de tuplas inseridas, atualizadas ou excluídas. Após essa verificação, ele recupera o armazenamento removendo dados obsoletos ou tuplas do banco de dados PostgreSQL.

Por padrão, o autovacuum é ativado nas instâncias de banco de dados Amazon RDS for PostgreSQL que você cria usando qualquer um dos grupos de parâmetros de banco de dados PostgreSQL padrão. Dentre elas estão a default.postgres10, a default.postgres11 e assim por diante. Todos os grupos de parâmetros de banco de dados PostgreSQL padrão têm um parâmetro rds.adaptive_autovacuum definido como 1, ativando assim o recurso. Outros parâmetros de configuração associados ao recurso autovacuum também são definidos por padrão. Como esses padrões são genéricos, você pode se beneficiar do ajuste de alguns dos parâmetros associados ao recurso autovacuum para seu workload específico.

A seguir, você pode encontrar mais informações sobre o autovacuum e como ajustar alguns dos respectivos parâmetros em sua instância de banco de dados do RDS for PostgreSQL. Para obter informações de alto nível, consulte Práticas recomendadas para trabalhar com PostgreSQL.

Tópicos

• Alocar memória para autovacuum
• Reduzir a probabilidade de conclusão de IDs de transação
• Determinar se as tabelas no seu banco de dados precisam de vacuum
• Determinar quais tabelas são atualmente elegíveis para autovacuum
• Determinar se o autovacuum está em execução e por quanto tempo
• Realização de um congelamento manual de vacuum
• Reindexação de uma tabela quando o autovacuum está em execução
• Gerenciar o autovacuum com grandes índices
• Outros parâmetros que afetam o autovacuum
• Definir parâmetros de autovacuum em nível de tabela
• Registrar atividades do autovacuum e do vacuum em log
• Noções básicas sobre o comportamento do autovacuum com bancos de dados inválidos

Alocar memória para autovacuum

Um dos parâmetros mais importantes que influenciam a performance do autovacuum é o autovacuum_work_mem. No Amazon RDS para PostgreSQL versões 14 e anteriores, o parâmetro autovacuum_work_mem é definido como -1, indicando que a configuração de maintenance_work_mem é usada em vez disso. Para todas as outras versões, autovacuum_work_mem é determinado por GREATEST({DBInstanceClassMemory/32768}, 65536).

As operações vacuum manuais sempre usam a configuração maintenance_work_mem, com uma configuração padrão de GREATEST({DBInstanceClassMemory/63963136*1024}, 65536), e também é possível fazer o ajuste no nível da sessão usando o comando SET para operações VACUUM manuais mais específicas.

O autovacuum_work_mem determina a memória do autovacuum para armazenar identificadores de tuplas mortas (pg_stat_all_tables.n_dead_tup) para índices de vacuum.

Ao executar cálculos para determinar o valor do parâmetro autovacuum_work_mem, esteja ciente do seguinte:

• Se você definir o parâmetro com um valor muito baixo, o processo de vacuum talvez precise verificar a tabela várias vezes para concluir o trabalho. Essas várias verificações podem ter um impacto negativo sobre a performance. Para instâncias maiores, a configuração de maintenance_work_mem ou autovacuum_work_mem para pelo menos 1 GB pode melhorar o desempenho de vaccum nas tabelas com um grande número de tuplas mortas. No entanto, no PostgreSQL versões 16 e anteriores, o uso de memória do vacuum é limitado a 1 GB, o que é suficiente para processar aproximadamente 179 milhões de tuplas mortas em uma única passagem. Se uma tabela tiver mais tuplas mortas do que isso, o vacuum precisará fazer várias passagens pelos índices da tabela, aumentando significativamente o tempo necessário.

  Um identificador de tupla tem 6 bytes de tamanho. Para estimar a memória necessária para limpar um índice de uma tabela, consulte pg_stat_all_tables.n_dead_tup para encontrar o número de tuplas mortas e multiplique esse número por seis para determinar a memória necessária para realizar vacuum do índice em uma única passagem. Você pode usar a seguinte consulta:

  SELECT
      relname AS table_name,
      n_dead_tup,
      pg_size_pretty(n_dead_tup * 6) AS estimated_memory
  FROM
      pg_stat_all_tables
  WHERE
      relname = 'name_of_the_table';

• O parâmetro autovacuum_work_mem funciona em conjunto com o parâmetro autovacuum_max_workers. Cada operador entre autovacuum_max_workers pode usar a memória que você alocar. Se você tiver muitas tabelas pequenas, aloque mais autovacuum_max_workers e menos autovacuum_work_mem. Se você tiver tabelas grandes (com mais de 100 GB), aloque mais memória e menos processos de operadores. Você precisa ter memória suficiente alocada para ter sucesso na sua maior tabela. Portanto, garanta que a combinação de processos de operadores e memória seja igual à memória total que deseja alocar.

Reduzir a probabilidade de conclusão de IDs de transação

Em alguns casos, as configurações de grupos de parâmetros relacionadas ao autovacuum podem não ser agressivas o suficiente para evitar a conclusão de IDs de transação. Para resolver isso, o for PostgreSQL fornece um mecanismo que adapta automaticamente os valores dos parâmetros de autovacuum. O ajuste de parâmetros de autovacuum adaptável é um recurso do RDS for PostgreSQL. Uma explicação detalhada da conclusão de TransactionID encontra-se na documentação do PostgreSQL.

O ajuste de parâmetros de autovacuum adaptável está ativado por padrão para instâncias do RDS for PostgreSQL com o parâmetro dinâmico rds.adaptive_autovacuum definido como ON (ativado). É altamente recomendável manter esse recurso ativado. No entanto, para desabilitar esse recurso, defina o parâmetro rds.adaptive_autovacuum como 0 ou OFF (desativado).

O wraparound de ID de transação ainda é possível mesmo quando o Amazon RDS ajusta os parâmetros de autovacuum. Nós encorajamos que você implemente um alarme do Amazon CloudWatch para a conclusão de IDs de transação. Para ter mais informações, consulte a postagem Implement an early warning system for transaction ID wraparound in RDS for PostgreSQL (Implementar um sistema de alertas antecipados para conclusão de IDs de transação no RDS for PostgreSQL) no Blog de banco de dados da AWS.

Com o ajuste de parâmetros de autovacuum adaptável ativado, o Amazon RDS começa a ajustar parâmetros de autovacuum quando a métrica do CloudWatch MaximumUsedTransactionIDs atingir o valor do parâmetro autovacuum_freeze_max_age ou 500.000.000, o que for maior.

O Amazon RDS continuará a ajustar os parâmetros para o autovacuum se uma tabela continuar a tendência para a conclusão de IDs de transação. Cada um desses ajustes dedica mais recursos ao autovacuum para evitar o envolvimento. O Amazon RDS atualiza os seguintes parâmetros relacionados ao autovacuum:

• autovacuum_vacuum_cost_delay

• autovacuum_vacuum_cost_limit

• autovacuum_work_mem

• autovacuum_naptime

O RDS modificará esses parâmetros somente se o novo valor tornar o autovacuum mais agressivo. Os parâmetros são modificados na memória na instância de banco de dados. Os valores no grupo de parâmetros não são alterados. Para visualizar as configurações atuais na memória, use o comando SQL SHOW PostgreSQL.

Quando o Amazon RDS modifica qualquer um desses parâmetros de autovacuum, ele gera um evento para a instância de banco de dados afetada. Esse evento é visível no AWS Management Console e por meio da API do Amazon RDS. Depois que a métrica MaximumUsedTransactionIDs do CloudWatch volta a ficar abaixo do limite, o Amazon RDS redefine os parâmetros relacionados a autovacuum na memória de volta aos valores especificados no grupo de parâmetros. Em seguida, ele gera outro evento correspondente a essa alteração.