PostgreSQL idle-in-transaction sessions

A session is idle in transaction after it begins a transaction, finishes its current command, and waits for the client's next command without committing or rolling back. The aborted form means a statement failed and the transaction is still awaiting rollback.

This read-only query groups sessions by ownership clues and reports the oldest transaction timestamp and age. It deliberately omits query text, client addresses, and process identifiers.

SELECT
  datname,
  usename AS role_name,
  COALESCE(NULLIF(application_name, ''), 'unlabeled') AS application_name,
  state,
  wait_event_type,
  count(*) AS sessions,
  min(xact_start) AS oldest_transaction_start,
  max(clock_timestamp() - xact_start) AS oldest_transaction_age
FROM pg_stat_activity
WHERE state IN (
  'idle in transaction',
  'idle in transaction (aborted)'
)
GROUP BY datname, usename, application_name, state, wait_event_type
ORDER BY oldest_transaction_start
LIMIT 50;

• Locks acquired earlier in the transaction can remain held and block other work.
• An old snapshot can prevent vacuum from removing row versions that might still be visible to that transaction.
• Connection slots remain occupied, which can amplify pressure when many application instances behave similarly.
• Transaction-pooled applications can hold a server connection until the transaction ends, reducing effective pool capacity.

Short pauses can be legitimate—for example, an application may briefly wait between related commands. Age, repetition, blockers, and user impact matter more than the state label alone.

This query reads relevant timeout values and their configuration source. A zero idle-in-transaction timeout means that timeout is disabled.

SELECT
  name,
  setting,
  unit,
  source
FROM pg_settings
WHERE name IN (
  'idle_in_transaction_session_timeout',
  'lock_timeout',
  'statement_timeout'
)
ORDER BY name;

1. Identify the owning role, application, deployment, and connection mode.
2. Confirm transaction age and whether it holds locks or an old snapshot.
3. Review application paths for missing commit, rollback, exception cleanup, or requests that wait on external work inside a transaction.
4. Coordinate with the owner before cancellation or termination; understand retry and rollback behavior.
5. Compare repeated observations to confirm whether the pattern persists after the code path is fixed.

• An idle connection is not the same as an idle connection inside a transaction.
• One short-lived idle transaction is not automatically a production incident.
• The last query text alone may not explain why the application left the transaction open.
• Terminating a session removes the symptom but does not correct the application transaction boundary.
• A global timeout chosen from one snapshot can disrupt legitimate long workflows.

The PostgreSQL health check counts idle-in-transaction sessions and reports them as an attention signal with an explicit instruction to inspect ownership and age before intervening. It also reports long transaction counts without collecting SQL text.

Connected telemetry preserves bounded activity groups by role, application, and state. DatabaseMeter does not currently terminate sessions, change timeouts, or determine whether a particular transaction is safe to interrupt.