Sessions longues et inactives-en-transaction dans PostgreSQL : ce qu'elles bloquent et comment les borner

Ce que c'est

Une transaction commencée depuis longtemps conserve deux choses : tous les verrous qu'elle a acquis, libérés seulement au commit ou au rollback, et un instantané, qui fixe son backend_xmin. La documentation de idle_in_transaction_session_timeout indique que même sans verrous significatifs, une transaction ouverte empêche le vacuum de supprimer des tuples récemment morts qui pourraient n'être visibles que pour elle, si bien que rester inactive longtemps peut contribuer au gonflement des tables. pg_stat_activity montre, par session, le state (active, idle, idle in transaction, idle in transaction (aborted)), xact_start et backend_xmin.

Pourquoi c'est important

Une session en idle in transaction est la cause courante de trois symptômes qui semblent sans rapport : des tables qui continuent de grossir bien qu'autovacuum tourne, une migration qui se bloque sur ALTER TABLE et bloque ensuite toute requête derrière elle, et des avertissements de wraparound d'identifiant de transaction, dont le chapitre sur le vacuum courant liste, parmi les remèdes, la fin des transactions ouvertes de longue durée repérées via age(backend_xmin). Le client ignore généralement qu'il retient quelque chose : un framework a ouvert une transaction à la première requête, puis le code a effectué un appel HTTP ou attendu une personne utilisatrice.

Comment l'appliquer

• Les repérer : 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 liste d'abord les transactions les plus anciennes.
• Définir idle_in_transaction_session_timeout par rôle applicatif (ALTER ROLE app SET idle_in_transaction_session_timeout = '30s') ; la session est terminée et le client voit une erreur de connexion, ce qui est le bon résultat pour une transaction oubliée.
• Ajouter transaction_timeout (PostgreSQL 17 et versions ultérieures) pour les rôles dont les transactions doivent être courtes, et statement_timeout pour les instructions individuelles ; la documentation note qu'un transaction_timeout inférieur ou égal aux deux autres rend les plus longs sans effet.
• Conserver les valeurs par défaut pour les rôles de reporting et de migration qui tournent légitimement longtemps, et leur donner leurs propres connexions et leur propre pool.
• Définir ces paramètres par rôle ou par session ; la documentation déconseille de définir statement_timeout et transaction_timeout dans postgresql.conf, car cela affecte toutes les sessions, y compris la maintenance.
• Alerter sur le xact_start le plus ancien et sur age(backend_xmin) depuis la supervision, pas seulement sur le gonflement.

Pièges

Les slots de réplication et les transactions préparées retiennent l'horizon xmin de la même façon et ne sont pas couverts par ces délais ; vérifier aussi pg_replication_slots et pg_prepared_xacts. Un standby avec hot_standby_feedback exporte ses requêtes longues vers le primaire. Un pooler en mode transaction peut faire apparaître une connexion serveur terminée comme une erreur déroutante sur un client sans rapport. La terminaison annule le travail ; un job par lots a besoin de commits par tranches, pas d'un délai plus long.