Trabalhar com o autovacuum do PostgreSQL no Amazon RDS for PostgreSQL - Amazon Relational Database Service
Alocar memória para autovacuumReduzir a probabilidade de conclusão de IDs de transaçãoDeterminar se as tabelas no seu banco de dados precisam de vacuumDeterminar quais tabelas são atualmente elegíveis para autovacuumDeterminar 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çãoGerenciar o autovacuum com grandes índices Outros parâmetros que afetam o autovacuumDefinir parâmetros de autovacuum em nível de tabelaRegistrar atividades do autovacuum e do vacuum em log

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.

Alocar memória para autovacuum

Um dos parâmetros mais importantes que influenciam a performance do autovacuum é o maintenance_work_mem. Esse parâmetro determina quanta memória é alocada para uso pelo autovacuum a fim de examinar uma tabela de banco de dados e manter todos os IDs de linha que serão evacuados. Se você definir um valor muito baixo para o parâmetro maintenance_work_mem, o processo de evacuação talvez precise varrer a tabela várias vezes para completar seu trabalho. Essas várias verificações podem ter um impacto negativo sobre a performance.

Ao fazer cálculos para determinar o valor do parâmetro maintenance_work_mem, lembre-se de duas coisas:

  • A unidade padrão é kilobytes (KB) para este parâmetro.

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

Em termos gerais, para hosts grandes, defina o parâmetro maintenance_work_mem como um valor entre um e dois gigabytes (entre 1.048.576 e 2.097.152 KB). Para hosts extremamente grandes, defina o parâmetro como um valor entre dois e quatro gigabytes (entre 2.097.152 e 4.194.304 KB). O valor definido para esse parâmetro depende da workload. O Amazon RDS atualizou seu padrão para esse parâmetro como quilobytes, calculados da maneira a seguir.

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

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:

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.

Determinar se as tabelas no seu banco de dados precisam de vacuum

Você pode usar a consulta a seguir para mostrar o número de transações vacuum em um banco de dados. A coluna datfrozenxid de uma linha pg_database do banco de dados é um limite inferior nos IDs de transação normais que aparecem nesse banco de dados. Essa coluna é o mínimo dos valores de relfrozenxid por tabela no banco de dados. 

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

Por exemplo, os resultados da execução da consulta anterior podem ser os seguintes.

datname    | age
mydb       | 1771757888
template0  | 1721757888
template1  | 1721757888
rdsadmin   | 1694008527
postgres   | 1693881061
(5 rows)

Quando a idade de um banco de dados atingir 2 bilhões de IDs de transação, ocorrerá a conclusão de IDs de transação (XID), e o banco de dados se tornará somente leitura. Use essa consulta para produzir uma métrica e execute-a algumas vezes por dia. Por padrão, o autovacuum é definido para manter a idade das transações como um máximo de 200,000,000 (autovacuum_freeze_max_age).

Uma estratégia de monitoramento de exemplo pode ser assim:

  • Defina o valor autovacuum_freeze_max_age como 200 milhões de transações.

  • Se uma tabela atingir 500 milhões de transações sem vacuum, um alarme de baixa gravidade será disparado. Este não é um valor não razoável, mas pode indicar que o autovacuum não está dando conta.

  • Se uma tabela chegar a 1 bilhão, ela deverá ser tratada como um alarme que requer medidas. Em geral, convém manter as idades mais próximas de autovacuum_freeze_max_age por motivos de performance. Recomendamos que você investigue usando as recomendações a seguir.

  • Se uma tabela atingir 1,5 bilhões de transações sem vacuum, um alarme de alta gravidade será disparado. Dependendo da rapidez com que seu banco de dados usa IDs de transação, esse alarme pode indicar que o sistema está ficando sem tempo para executar o autovacuum. Nesse caso, recomendamos que você resolva isso imediatamente.

Se uma tabela estiver ultrapassando constantemente esses limiares, modifique ainda mais seus parâmetros de autovacuum. Por padrão, usar VACUUM manualmente (que tem atrasos baseados em custos desabilitados) é mais agressivo do que usar o autovacuum padrão, mas também é mais intrusivo para o sistema como um todo.

Recomendamos o seguinte:

  • Esteja ciente e ative um mecanismo de monitoramento para que você tenha conhecimento da idade das transações mais antigas.

    Para obter informações sobre como criar um processo que avisa sobre a conclusão de IDs de transação, consulte a publicação do blog de banco de dados da AWS Implement an early warning system for transaction ID wraparound no Amazon RDS for PostgreSQL (Implementar um sistema de alerta antecipado para conclusão de IDs de transação no Amazon RDS for PostgreSQL).

  • Para tabelas mais ocupadas, execute um congelamento de vacuum manual regularmente durante uma janela de manutenção além de depender do autovacuum. Para obter informações sobre a realização de um congelamento manual de vacuum, consulte Realização de um congelamento manual de vacuum.

Determinar quais tabelas são atualmente elegíveis para autovacuum

Muitas vezes, uma ou duas tabelas precisam de vacuum. Tabelas cujo valor de relfrozenxid é maior que o número de transações em autovacuum_freeze_max_age são sempre visadas pelo autovacuum. Caso contrário, se o número de tuplas obsoletas desde o último VACUUM exceder o limite de vacuum, um vacuum será realizado na tabela.

O limite de autovacuum é definido como:

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

em que o vacuum base threshold é autovacuum_vacuum_threshold, o vacuum scale factor é autovacuum_vacuum_scale_factor e o number of tuples é pg_class.reltuples.

Enquanto você estiver conectado ao seu banco de dados, execute a consulta a seguir para ver uma lista de tabelas que o autovacuum considera elegíveis para o vacuum.

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM 
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM 
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), 
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace 
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid 
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;

Determinar se o autovacuum está em execução e por quanto tempo

Se você precisa realizar vacuum em uma tabela manualmente, determine se o autovacuum está sendo executado. Se estiver, talvez seja necessário ajustar parâmetros para fazê-lo funcionar mais eficientemente ou desativar o autovacuum temporariamente para poder executar VACUUM manualmente.

Use a seguinte consulta para determinar se o autovacuum está sendo executado e por quanto tempo, e se ele está esperando por outra sessão. 

SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity 
WHERE upper(query) LIKE '%VACUUM%' 
ORDER BY xact_start;

Depois de executar a consulta, você deverá ver uma saída semelhante à seguinte:


 datname | usename  |  pid  | state  | wait_event |      xact_runtime       | query  
 --------+----------+-------+--------+------------+-------------------------+--------------------------------------------------------------------------------------------------------
 mydb    | rdsadmin | 16473 | active |            | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)
 mydb    | rdsadmin | 22553 | active |            | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)
 mydb    | rdsadmin | 41909 | active |            | 3 days 02:43:54.203349  | autovacuum: VACUUM ANALYZE public.mytable3
 mydb    | rdsadmin |   618 | active |            | 00:00:00                | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+
         |          |       |        |            |                         | FROM pg_stat_activity                                                                                 +
         |          |       |        |            |                         | WHERE query like '%VACUUM%'                                                                           +
         |          |       |        |            |                         | ORDER BY xact_start;                                                                                  +

Vários problemas podem causar uma sessão de autovacuum de longa execução (ou seja, vários dias de duração). O problema mais comum é que o valor do seu parâmetro maintenance_work_mem está muito baixo para o tamanho da tabela ou a taxa de atualizações.

Recomendamos o uso da fórmula a seguir para definir o valor do parâmetro maintenance_work_mem.

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

Sessões de autovacuum em execução por pouco tempo também podem indicar problemas:

  • Isso pode indicar que não há autovacuum_max_workers suficientes para sua workload. Neste caso, você precisa indicar o número de operadores.

  • Pode indicar que existe uma corrupção de índice (o autovacuum trava e reinicia na mesma relação, mas não faz nenhum progresso). Nesse caso, execute um vacuum freeze verbose table manual para ver a causa exata.

Realização de um congelamento manual de vacuum

Você pode querer realizar um vacuum manual em uma tabela que tenha um processo de vacuum já em execução. Isso é útil se você identificou uma tabela com uma idade XID próxima de 2 bilhões de transações (ou acima de qualquer limite que você esteja monitorando).

As etapas a seguir são uma orientação com diversas variações no processo. Por exemplo, durante testes, suponha que você perceba que o valor do parâmetro maintenance_work_mem está muito baixo e que precisa tomar medidas imediatas em uma tabela. No entanto, talvez você não queira devolver a instância no momento. Usando as consultas nas seções anteriores, você determina qual tabela é o problema e observa uma sessão de autovacuum de longa execução. Você sabe que precisa alterar a configuração do parâmetro maintenance_work_mem, mas também precisa tomar medidas imediatas e evacuar a tabela em questão. O procedimento a seguir mostra o que fazer nessa situação.

Para executar manualmente um congelamento de vacuum

  1. Abra duas sessões no banco de dados que contém a tabela em que você deseja realizar vacuum. Para a segunda sessão, use "screen" ou outro utilitário que mantenha a sessão se a conexão for encerrada.

  2. Na sessão um, obtenha o “Process ID” (PID – ID de processo) da sessão de autovacuum em execução na tabela.

    Execute a seguinte consulta para obter o PID da sessão de autovacuum.

    SELECT datname, usename, pid, current_timestamp - xact_start 
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY 
xact_start;

  3. Na sessão dois, calcule a quantidade de memória necessária para essa operação. Neste exemplo, determinamos que podemos usar até 2 GB de memória para essa operação e, portanto, definimos maintenance_work_mem da sessão atual como 2 GB.

    SET maintenance_work_mem='2 GB';
SET

  4. Na sessão dois, emita um comando vacuum freeze verbose para a tabela. A configuração detalhada é útil porque, embora não haja um relatório de andamento para isso no PostgreSQL, você pode ver as atividades.

    \timing on
Timing is on.
vacuum freeze verbose pgbench_branches;
    INFO:  vacuuming "public.pgbench_branches"
INFO:  index "pgbench_branches_pkey" now contains 50 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pgbench_branches_test_index" now contains 50 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pgbench_branches": found 0 removable, 50 nonremovable row versions 
     in 43 out of 43 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 9347 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 2.765 ms

  5. Na sessão um, se o autovacuum estava bloqueando a sessão de vacuum, você verá em pg_stat_activity que a espera é "T" para a sua sessão de vacuum. Nesse caso, você precisa encerrar o processo de autovacuum da maneira a seguir.

    SELECT pg_terminate_backend('the_pid');

    Nesse ponto, sua sessão começa. É importante observar que o autovacuum é reiniciado imediatamente, pois essa tabela é provavelmente a mais alta em sua lista de trabalho.

  6. Inicie seu comando vacuum freeze verbose na sessão dois e depois termine o processo de autovacuum na sessão um.

Reindexação de uma tabela quando o autovacuum está em execução

Se um índice se tornar corrompido, o autovacuum continuará processando a tabela e falhará. Se você tentar um vacuum manual nessa situação, receberá uma mensagem de erro semelhante à mostrada a seguir.

postgres=>  vacuum freeze pgbench_branches;
ERROR: index "pgbench_branches_test_index" contains unexpected 
   zero page at block 30521
HINT: Please REINDEX it.

Quando o índice está corrompido e o autovacuum está tentando ser executado na tabela, você lutará com uma sessão de autovacuum já em execução. Ao emitir um comando REINDEX, você remove um bloqueio exclusivo na tabela. As operações de gravação são bloqueadas, bem como as operações de leitura que usam esse índice específico.

Para reindexar uma tabela quando o autovacuum está em execução nela

  1. Abra duas sessões no banco de dados que contém a tabela em que você deseja realizar vacuum. Para a segunda sessão, use "screen" ou outro utilitário que mantenha a sessão se a conexão for encerrada.

  2. Na sessão um, obtenha o PID da sessão de autovacuum em execução na tabela.

    Execute a seguinte consulta para obter o PID da sessão de autovacuum.

    SELECT datname, usename, pid, current_timestamp - xact_start 
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY 
xact_start;

  3. Na segunda sessão, emita o comando reindex.

    \timing on
Timing is on.
reindex index pgbench_branches_test_index;
REINDEX
  Time: 9.966 ms

  4. Na sessão um, se o autovacuum estava bloqueando o processo, você verá em pg_stat_activity que a espera é "T" para a sua sessão de vacuum. Nesse caso, você encerra o processo de autovacuum. 

    SELECT pg_terminate_backend('the_pid');

    Nesse ponto, sua sessão começa. É importante observar que o autovacuum é reiniciado imediatamente, pois essa tabela é provavelmente a mais alta em sua lista de trabalho.

  5. Inicie seu comando na sessão dois e depois termine o processo de autovacuum na sessão um.

Gerenciar o autovacuum com grandes índices

Como parte de sua operação, o autovacuum executa várias fases de aspiração ao trabalhar em uma tabela. Antes de a tabela ser limpa, todos os seus índices são aspirados primeiro. Ao remover vários índices grandes, essa fase consome uma quantidade significativa de tempo e recursos. Portanto, como prática recomendada, controle o número de índices em uma tabela e elimine os índices não usados.

Para esse processo, primeiro confira o tamanho geral do índice. Depois, determine se há índices possivelmente não usados que podem ser removidos conforme mostrado nos exemplos a seguir.

Como conferir o tamanho da tabela e os respectivos índices

postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)

Neste exemplo, o tamanho dos índices é maior do que a tabela. Essa diferença pode causar problemas de performance, pois os índices estão sobrecarregados ou não são usados, o que afeta as operações de autovacuum e de inserção.

Como conferir índices não usados

Usando a visualização pg_stat_user_indexes, você pode conferir com que frequência um índice é usado com a coluna idx_scan. No exemplo a seguir, os índices não usados têm o valor idx_scan de 0.

postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
relid  | indexrelid | schemaname | relname          | indexrelname          | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433  | 16454      | public     | pgbench_accounts | index_f               | 6        | 6            | 0
16433  | 16450      | public     | pgbench_accounts | index_b               | 3        | 199999       | 0
16433  | 16447      | public     | pgbench_accounts | pgbench_accounts_pkey | 0        | 0            | 0
16433  | 16452      | public     | pgbench_accounts | index_d               | 0        | 0            | 0
16433  | 16453      | public     | pgbench_accounts | index_e               | 0        | 0            | 0
16433  | 16451      | public     | pgbench_accounts | index_c               | 0        | 0            | 0
16433  | 16449      | public     | pgbench_accounts | index_a               | 0        | 0            | 0
(7 rows)        
    
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
schemaname  | relname          | indexrelname          | idx_scan
------------+------------------+-----------------------+----------
public      | pgbench_accounts | index_f               | 6
public      | pgbench_accounts | index_b               | 3
public      | pgbench_accounts | pgbench_accounts_pkey | 0
public      | pgbench_accounts | index_d               | 0
public      | pgbench_accounts | index_e               | 0
public      | pgbench_accounts | index_c               | 0
public      | pgbench_accounts | index_a               | 0
(7 rows)
nota

Essas estatísticas são incrementais a partir do momento em que as estatísticas são redefinidas. Suponha que você tenha um índice usado apenas no final de um trimestre comercial ou apenas para um relatório específico. É possível que esse índice não tenha sido usado desde que as estatísticas foram redefinidas. Para ter mais informações, consulte Funções de estatística. Os índices usados para impor a exclusividade não terão verificações realizadas e não devem ser identificados como índices não usados. Para identificar os índices não usados, você deve ter um conhecimento profundo da aplicação e das respectivas consultas.

Para conferir quando as estatísticas foram redefinidas pela última vez em um banco de dados, use pg_stat_database

postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
    
datname   | stats_reset
----------+-------------------------------
postgres  | 2022-11-17 08:58:11.427224+00
(1 row)

Aspirar uma tabela o mais rápido possível

RDS para PostgreSQL 12 e posterior

Se você tiver muitos índices em uma tabela grande, a instância de banco de dados poderá estar se aproximando do encapsulamento de ID (XID), que é quando o contador XID chega a zero. Se não for conferida, essa situação poderá ocasionar perda de dados. No entanto, você pode aspirar rapidamente a tabela sem limpar os índices. No RDS para PostgreSQL 12 e posterior, você pode usar VACUUM com a cláusula INDEX_CLEANUP.

postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
        
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.

Se uma sessão de autovacuum já estiver em execução, você deverá encerrá-la para iniciar a aspiração manual. Para ter informações sobre a realização de um congelamento manual de aspiração, consulte Realização de um congelamento manual de vacuum.

nota

Ignorar a limpeza do índice regularmente pode causar sobrecarga no índice, o que afeta a performance geral da verificação. Como prática recomendada, use o procedimento anterior somente para evitar o encapsulamento de ID.

RDS para PostgreSQL 11 e versões mais antigas

No entanto, no RDS para PostgreSQL 11 e versões anteriores, a única maneira de permitir que a aspiração seja concluída mais rapidamente é reduzir o número de índices em uma tabela. A eliminação de um índice pode afetar os planos de consulta. Recomendamos que você elimine primeiro os índices não usados e, depois, descarte os índices quando o encapsulamento XID estiver muito próximo. Depois que o processo de aspiração for concluído, você poderá recriar esses índices.

Outros parâmetros que afetam o autovacuum

A consulta a seguir mostra os valores de alguns dos parâmetros que afetam diretamente o autovacuum e seu comportamento. Os parâmetros de autovacuum são descritos inteiramente na documentação do PostgreSQL.

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'autovacuum_max_workers',
'autovacuum_analyze_scale_factor',
'autovacuum_naptime',
'autovacuum_analyze_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_cost_delay',
'autovacuum_vacuum_cost_limit',
'vacuum_cost_limit',
'autovacuum_freeze_max_age',
'maintenance_work_mem',
'vacuum_freeze_min_age');

Enquanto todos estes afetem o autovacuum, alguns dos mais importantes são:

Definir parâmetros de autovacuum em nível de tabela

Você pode definir parâmetros de armazenamento relacionados ao autovacuum em nível de tabela, o que pode ser melhor do que alterar o comportamento do banco de dados inteiro. Para tabelas grandes, talvez você precise definir configurações agressivas e talvez você não queira que o autovacuum se comporte dessa maneira para todas as tabelas.

A consulta a seguir mostra quais tabelas atualmente têm opções em nível de tabela.

SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT null;

Um exemplo em que isso pode ser útil é em tabelas que são muito maiores do que o resto das suas tabelas. Suponha que você tenha uma tabela de 300 GB e outras 30 tabelas com menos de 1 GB. Nesse caso, você pode definir alguns parâmetros específicos para a sua tabela grande, para não alterar o comportamento de todo o sistema.

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);

Fazer isso desativará o atraso de autovacuum baseado em custos para essa tabela, mas aumentará o uso de recursos no seu sistema. Normalmente, o autovacuum faz uma pausa durante autovacuum_vacuum_cost_delay sempre que autovacuum_cost_limit é alcançado. Para obtert mais detalhes, consulte a documentação do PostgreSQL sobre vacuum baseado no custo.

Registrar atividades do autovacuum e do vacuum em log

Informações sobre atividades de autovacuum são enviadas para postgresql.log com base no nível especificado no parâmetro rds.force_autovacuum_logging_level. A seguir estão os valores permitidos para esse parâmetro e as versões do PostgreSQL para as quais esse valor é a configuração padrão:

  • disabled (PostgreSQL 10, PostgreSQL 9.6)

  • debug5, debug4, debug3, debug2, debug1

  • info (PostgreSQL 12, PostgreSQL 11)

  • notice

  • warning (PostgreSQL 13 e superior)

  • error, log, fatal, panic

A configuração rds.force_autovacuum_logging_level funciona com o parâmetro log_autovacuum_min_duration. O valor do parâmetro log_autovacuum_min_duration é o limite (em milissegundos) acima do qual as ações autovacuum são registradas em log. Uma configuração -1 não registra nada em log, enquanto uma configuração 0 registra todas as ações em log. Como ocorre com rds.force_autovacuum_logging_level, os valores padrão de log_autovacuum_min_duration são dependentes da versão, da seguinte maneira:

  • 10000 ms: PostgreSQL 14, PostgreSQL 13, PostgreSQL 12 e PostgreSQL 11

  • (empty): nenhum valor padrão para PostgreSQL 10 e PostgreSQL 9.6

Recomendamos que você defina rds.force_autovacuum_logging_level como WARNING. Também recomendamos definir log_autovacuum_min_duration para um valor entre 1000 e 5000. Uma configuração 5000 registra em log a atividade que leva mais de 5.000 milissegundos. Qualquer configuração diferente de -1 também registrará mensagens se a ação autovacuum for ignorada devido a um bloqueio conflitante ou relações descartadas simultaneamente. Para ter mais informações, consulte Automatic Vacuuming (Aplicação automática do vacuum) na documentação do PostgreSQL.

Para solucionar problemas, você pode alterar o parâmetro rds.force_autovacuum_logging_level para um dos níveis de depuração, de debug1 a debug5, para obter informações mais detalhadas. Recomendamos que você use as configurações de depuração por curtos períodos e apenas para fins de solução de problemas. Para saber mais, consulte When to log (Quando registrar em log) na documentação do PostgreSQL.

nota

O PostgreSQL permite que a conta rds_superuser visualize sessões de autovacuum em pg_stat_activity. Por exemplo, você pode identificar e encerrar uma sessão de autovacuum que esteja bloqueando a execução de um comando, ou executando de forma mais lenta do que um comando de vacuum emitido manualmente.