Sessões longas e idle in transaction no PostgreSQL: o que bloqueiam e como limitá-las

O que é

Uma transação iniciada há muito tempo mantém duas coisas: todos os locks que adquiriu, liberados apenas no commit ou no rollback, e um snapshot, que fixa o seu backend_xmin. A documentação de idle_in_transaction_session_timeout afirma que, mesmo sem locks significativos, uma transação aberta impede que o vacuum elimine tuplas recentemente mortas que podem ser visíveis apenas para ela; por isso, permanecer ociosa por muito tempo pode contribuir para o bloat das tabelas. A view pg_stat_activity mostra, por sessão, o state (active, idle, idle in transaction, idle in transaction (aborted)), o xact_start e o backend_xmin.

Por que importa

Uma única sessão em idle in transaction é a causa comum de três sintomas que parecem não ter relação: tabelas que continuam crescendo apesar de o autovacuum estar rodando, uma migration que trava em ALTER TABLE e passa a bloquear todas as consultas que vêm depois dela, e avisos de wraparound do ID de transação; para este último, a lista de remédios do capítulo sobre vacuum de rotina inclui encerrar as transações abertas de longa duração identificadas por age(backend_xmin). Em geral o cliente não sabe que está segurando algo: um framework abriu uma transação na primeira consulta e, em seguida, o código fez uma chamada HTTP ou ficou esperando um usuário.

Como aplicar

• Encontre-as: SELECT pid, usename, state, now() - xact_start AS age, age(backend_xmin), left(query, 80) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start lista primeiro as transações mais antigas.
• Defina idle_in_transaction_session_timeout por role da aplicação (ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'); a sessão é encerrada e o cliente recebe um erro de conexão, que é o resultado correto para uma transação esquecida.
• Adicione transaction_timeout (PostgreSQL 17 e posteriores) para roles cujas transações devem ser curtas, e statement_timeout para comandos individuais; a documentação observa que um transaction_timeout menor ou igual aos outros dois torna os mais longos irrelevantes.
• Mantenha os valores padrão para roles de relatórios e de migration que legitimamente rodam por muito tempo, e dê a elas conexões e pool próprios.
• Configure esses parâmetros por role ou por sessão; a documentação desaconselha definir statement_timeout e transaction_timeout no postgresql.conf, porque isso afeta todas as sessões, inclusive as de manutenção.
• Configure alertas no monitoramento para o xact_start mais antigo e para age(backend_xmin), e não apenas para o bloat.

Armadilhas

Replication slots e prepared transactions seguram o horizonte de xmin da mesma forma e não são cobertos por esses timeouts; verifique também pg_replication_slots e pg_prepared_xacts. Um standby com hot_standby_feedback repassa suas consultas longas para o primário. Um pooler em modo transaction pode fazer o encerramento de uma conexão com o servidor aparecer como um erro desconcertante em um cliente sem relação com o problema. O encerramento desfaz o trabalho feito; um job em lote precisa de commits em blocos, e não de um timeout maior.