Sesiones de larga duración e inactivas en transacción en PostgreSQL: qué bloquean y cómo acotarlas

Qué es

Una transacción que empezó hace mucho conserva dos cosas: todos los bloqueos que adquirió, que solo se liberan con un commit o un rollback, y una instantánea, que fija su backend_xmin. La documentación de idle_in_transaction_session_timeout indica que, incluso sin bloqueos importantes, una transacción abierta impide eliminar mediante vacuum las tuplas recién muertas que solo ella puede ver, de modo que permanecer inactiva durante mucho tiempo puede contribuir a la hinchazón (bloat) de las tablas. pg_stat_activity muestra, por sesión, el state (active, idle, idle in transaction, idle in transaction (aborted)), xact_start y backend_xmin.

Por qué importa

Una sola sesión en idle in transaction es la causa habitual de tres síntomas que parecen no estar relacionados: tablas que no dejan de crecer aunque autovacuum se ejecuta, una migración que se queda colgada en ALTER TABLE y bloquea después toda consulta que va detrás, y avisos de reciclaje (wraparound) del identificador de transacción, para los que el capítulo sobre el vacuum rutinario incluye entre sus remedios terminar las transacciones abiertas de larga duración localizadas mediante age(backend_xmin). Por lo general, el cliente no sabe que está reteniendo nada: un framework abrió una transacción en la primera consulta, y después el código hizo una llamada HTTP o esperó a una persona usuaria.

Cómo aplicarlo

• Localizarlas: 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 primero las transacciones más antiguas.
• Fijar idle_in_transaction_session_timeout por rol de aplicación (ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'); la sesión se termina y el cliente ve un error de conexión, que es el resultado correcto para una transacción olvidada.
• Añadir transaction_timeout (PostgreSQL 17 en adelante) para los roles cuyas transacciones deban ser breves, y statement_timeout para sentencias individuales; la documentación señala que un transaction_timeout más corto o igual que los otros dos hace irrelevantes a los más largos.
• Mantener los valores por defecto para los roles de generación de informes y de migración que legítimamente se ejecutan durante mucho tiempo, y darles sus propias conexiones y su propio pool.
• Fijar estos parámetros por rol o por sesión; la documentación desaconseja fijar statement_timeout y transaction_timeout en postgresql.conf, porque eso afecta a todas las sesiones, incluidas las de mantenimiento.
• Generar alertas sobre el xact_start más antiguo y sobre age(backend_xmin) desde la monitorización, no solo sobre la hinchazón.

Trampas

Los slots de replicación y las transacciones preparadas retienen el horizonte xmin de la misma manera y estos tiempos de espera no los cubren; conviene revisar también pg_replication_slots y pg_prepared_xacts. Un standby con hot_standby_feedback exporta sus consultas largas al primario. Un pooler en modo transacción puede hacer que la terminación de una conexión del servidor aparezca como un error desconcertante en un cliente sin relación con ella. La terminación deshace el trabajo; un trabajo por lotes necesita hacer commits en tramos, no un tiempo de espera más largo.