Solucionar bloqueadores de limpeza identificáveis no RDS para PostgreSQL

O Autovacuum realiza limpezas agressivas e reduz a idade dos IDs de transação para abaixo do limite especificado pelo parâmetro autovacuum_freeze_max_age de sua instância do RDS. É possível rastrear essa idade usando a métrica MaximumUsedTransactionIDs do Amazon CloudWatch.

Para encontrar a configuração de autovacuum_freeze_max_age (que tem um padrão de 200 milhões de IDs de transação) da sua instância do Amazon RDS, use a seguinte consulta:

SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

Observe que postgres_get_av_diag() só verifica se há bloqueadores de limpeza agressiva quando a idade excede o limite de 500 milhões de IDs de transação do autovacuum adaptativo do Amazon RDS. Para que postgres_get_av_diag() detecte bloqueadores, o bloqueador deve ter pelo menos 500 milhões de transações de idade.

A função postgres_get_av_diag() identifica os seguintes tipos de bloqueadores:

Instrução ativa

No PostgreSQL, uma instrução ativa é uma instrução SQL que está sendo executada atualmente pelo banco de dados. Isso inclui consultas, transações ou quaisquer operações em andamento. Ao monitorar por meio do pg_stat_activity, a coluna de estado indica que o processo com o PID correspondente está ativo.

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando identifica uma instrução que é uma instrução ativa.

blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

Ação sugerida

Seguindo as orientações na coluna suggestion, o usuário pode se conectar ao banco de dados em que a instrução ativa está presente e, conforme especificado na coluna suggested_action, é recomendável analisar cuidadosamente a opção de encerrar a sessão. Se o encerramento for seguro, use a função pg_terminate_backend() para encerrar a sessão. Essa ação pode ser executada por um administrador (como a conta mestra do RDS) ou por um usuário com o privilégio pg_terminate_backend() necessário.

Ocioso na transação

Uma instrução ociosa na transação se refere a qualquer sessão que abriu uma transação explícita (como emitir uma instrução BEGIN), realizou algum trabalho e agora está esperando que o cliente passe mais trabalho ou sinalize o final da transação emitindo um COMMIT, ROLLBACK ou END (o que resultaria em um COMMIT implícito).

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando identifica uma instrução idle in transaction como um bloqueador.

blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

Ação sugerida

Conforme indicado na coluna suggestion, conecte-se ao banco de dados em que a sessão ociosa na transação está presente e encerre a sessão usando a função pg_terminate_backend(). O usuário pode ser seu usuário administrador (conta mestra do RDS) ou um usuário com o privilégio pg_terminate_backend().

Transação preparada

O PostgreSQL permite transações que fazem parte de uma estratégia de confirmação bifásica chamada transações preparadas. Elas são habilitadas ao definir o parâmetro max_prepared_transactions como um valor diferente de zero. As transações preparadas são projetadas para garantir que uma transação seja durável e permaneça disponível mesmo após falhas no banco de dados, reinicializações ou desconexões do cliente. Como as transações regulares, elas recebem um ID de transação e podem afetar o autovacuum. Se deixado em um estado preparado, o autovacuum não realiza o congelamento e isso pode levar à conclusão do ID da transação.

Quando as transações são deixadas no estado de preparação indefinidamente, sem serem resolvidas por um gerenciador de transações, elas se tornam transações preparadas órfãs. A única maneira de corrigir isso é confirmar ou reverter a transação usando os comandos COMMIT PREPARED ou ROLLBACK PREPARED, respectivamente.

A função postgres_get_av_diag() exibe a saída a seguir quando identifica um bloqueador que é uma transação preparada.

blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

Ação sugerida

Conforme mencionado na coluna de sugestões, conecte-se ao banco de dados em que a transação preparada está localizada. Com base na coluna suggested_action, analise cuidadosamente se deseja executar COMMIT ou ROLLBACK, e então execute a ação apropriada.

Para monitorar transações preparadas em geral, o PostgreSQL oferece uma visualização de catálogo chamada pg_prepared_xacts. Use a consulta a seguir para encontrar transações preparadas.

SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

Slot de replicação lógica

O objetivo de um slot de replicação é manter as alterações não consumidas até que elas sejam replicadas em um servidor de destino. Consulte a página sobre replicação lógica do PostgreSQL para obter mais informações.

Existem dois tipos de slots de replicação lógica.

Slots de replicação lógica inativa

Quando a replicação é encerrada, os logs de transações não consumidos não podem ser removidos e o slot de replicação fica inativo. Embora um slot de replicação lógica inativo não seja usado atualmente por um assinante, ele permanece no servidor, levando à retenção de arquivos WAL e impedindo a remoção de logs de transações antigos. Isso pode aumentar o uso do disco e, especificamente, impedir que o autovacuum limpe as tabelas internas do catálogo, pois o sistema deve evitar que as informações do LSN sejam sobrescritas. Se não for resolvido, esse problema pode resultar em inchaço do catálogo, degradação do desempenho e aumento do risco de conclusão da limpeza, potencialmente causando tempo de inatividade das transações.

Slots de replicação lógica ativos, mas lentos

Às vezes, a remoção de tuplas inativas do catálogo é adiada devido à degradação do desempenho da replicação lógica. Esse atraso na replicação retarda a atualização do catalog_xmin e pode causar inchaço do catálogo e conclusão da limpeza.

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando encontra um slot de replicação lógica como um bloqueador.

blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

Ação sugerida

Para resolver esse problema, verifique se há problemas com o esquema ou os dados de destino na configuração de replicação que possam estar encerrando o processo de aplicação. Os motivos mais comuns são:

Se o problema estiver relacionado a questões de infraestrutura:

Se sua instância estiver fora da rede da AWS ou no AWS EC2, consulte seu administrador sobre como resolver os problemas de disponibilidade ou problemas relacionados à infraestrutura.

Descartar o slot inativo

Depois de confirmar que o slot de replicação não é mais necessário, descarte-o para permitir que o autovacuum continue. A condição active = 'f' garante que somente um slot inativo seja descartado.

SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

Réplicas de leitura

Quando a configuração hot_standby_feedback está habilitada para réplicas de leitura do Amazon RDS, ela impede que o autovacuum no banco de dados primário remova linhas inativas que ainda podem ser necessárias para consultas executadas na réplica de leitura. Isso afeta todos os tipos de réplicas de leitura físicas, incluindo as que são gerenciadas com ou sem slots de replicação. Esse comportamento é necessário porque as consultas executadas na réplica em espera exigem que essas linhas permaneçam disponíveis no primário, evitando conflitos de consultas e cancelamentos.

Réplica de leitura com slot de replicação física

As réplicas de leitura com slots de replicação física aumentam significativamente a confiabilidade e a estabilidade da replicação no RDS para PostgreSQL. Esses slots garantem que o banco de dados primário retenha os arquivos essenciais do log de gravação antecipada até que a réplica os processe, mantendo a consistência de dados mesmo durante interrupções na rede.

A partir do RDS para PostgreSQL versão 14, todas as réplicas utilizam slots de replicação. Nas versões anteriores, somente réplicas entre regiões usavam slots de replicação.

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando encontra uma réplica de leitura com slot de replicação física como um bloqueador.

blocker               | Read replica with physical replication slot
database              |
blocker_identifier    | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event            | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion            | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:                           
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;                                                       
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                 +                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;","                                                                                 +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                   +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Delete the read replica if not needed"}

Réplica de leitura com replicação de streaming

O Amazon RDS permite configurar réplicas de leitura sem um slot de replicação física em versões mais antigas, até a versão 13. Essa abordagem reduz a sobrecarga ao permitir que o primário recicle arquivos WAL de forma mais agressiva, o que é vantajoso em ambientes com espaço em disco limitado e que podem tolerar ReplicaLag ocasional. No entanto, sem um slot, o standby deve permanecer sincronizado para evitar a perda de arquivos WAL. O Amazon RDS usa arquivos WAL arquivados para ajudar a réplica a se recuperar caso ela fique atrasada, mas esse processo exige monitoramento cuidadoso e pode ser lento.

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando encontra uma réplica de leitura de streaming como um bloqueador.

blocker               | Read replica with streaming replication slot
database              | Not applicable
blocker_identifier    | xx.x.x.xxx/xx
wait_event            | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion            | Run the following query on the replica "xx.x.x.xxx" to find the long running query:                                                                                                                                                         +
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;                                                                                                                                                     +
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                       +
                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;","                                                                                                                        +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                                                          +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Delete the read replica if not needed"}

Ação sugerida

Conforme recomendado na coluna suggested_action, analise cuidadosamente essas opções para desbloquear o autovacuum.

Tabelas temporárias

Tabelas temporárias, criadas usando a palavra-chave TEMPORARY, residem no esquema temporário, por exemplo pg_temp_xxx, e só podem ser acessadas pela sessão que as criou. As tabelas temporárias são removidas quando a sessão termina. No entanto, essas tabelas são invisíveis para o processo de autovacuum do PostgreSQL e devem ser limpas manualmente pela sessão que as criou. Tentar limpar a tabela temporária de outra sessão não tem efeito.

Em circunstâncias incomuns, uma tabela temporária pode existir sem uma sessão ativa que a possua. Se a sessão proprietária terminar inesperadamente devido a uma falha fatal, problemas de rede ou eventos do tipo, a tabela temporária pode não ser limpa, deixando-a como uma tabela "órfã". Quando o processo de autovacuum do PostgreSQL detecta uma tabela temporária órfã, ele registra em log a seguinte mensagem:

LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"

A função postgres_get_av_diag() exibe uma saída semelhante à saída a seguir quando identifica uma tabela temporária como um bloqueador. Para que a função exiba corretamente a saída relacionada às tabelas temporárias, ela precisa ser executada no mesmo banco de dados em que essas tabelas existem.

blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

Ação sugerida

Siga as instruções fornecidas na coluna suggestion da saída para identificar e remover a tabela temporária que está impedindo a execução do autovacuum. Use o comando a seguir para eliminar a tabela temporária reportada por postgres_get_av_diag(). Substitua o nome da tabela com base na saída fornecida pela função postgres_get_av_diag().

DROP TABLE my_temp_schema.my_temp_table;

A seguinte consulta pode ser usada para identificar tabelas temporárias:

SELECT
    oid,
    relname,
    relnamespace::regnamespace,
    age(relfrozenxid)
FROM
    pg_class
WHERE
relpersistence = 't'
ORDER BY
    age(relfrozenxid) DESC;