Polling pg_stat_activity vs pg_stat_statements for Idle-in-Transaction Alerting: Which Minimizes Noise?

The Recommended Approach

Polling pg_stat_activity is the only reliable method of the two for detecting idle-in-transaction sessions. pg_stat_statements is designed to aggregate execution statistics per query ID across all sessions; it does not track the current state of a backend or the duration a specific session has remained idle. Using pg_stat_statements for this purpose would result in false negatives and an inability to identify the specific PID holding locks.

Behavior of pg_stat_activity and Timeouts

In PostgreSQL (9.6+), pg_stat_activity reflects only live backends. When idle_in_transaction_session_timeout is triggered, the backend is terminated, and its corresponding row is removed from pg_stat_activity. The state 'idle in transaction' is not reported after the termination process is complete.

The Risk of Redundant Notifications

While the timeout removes the session, a race condition exists. If a polling script samples pg_stat_activity milliseconds before the timeout fires, it will detect the idle session and trigger an alert. Immediately following this, the PostgreSQL server will write a termination entry to the logs. If you have independent alerting systems monitoring both the poll results and the server logs, you will receive duplicate notifications for the same event.

Implementation for Minimal Noise

To minimize noise and avoid redundant alerts, use a stateful polling mechanism rather than a stateless trigger. Follow these steps:

1. Filter by State and Age: Poll pg_stat_activity using a query that filters for state = 'idle in transaction' and calculates the duration since xact_start.
2. Establish an Incident Key: Use a combination of (pid, xact_start) as a unique identifier for the incident.
3. Deduplicate: Only fire an alert the first time a specific incident key exceeds your threshold. Suppress further notifications for that key until the session either commits, rolls back, or disappears from the view.
4. Consolidate Sources: Choose either polling-based alerts or log-based alerts, but not both. Polling is preferred for proactive detection before the timeout kills the session.

   Verification Command

   You can verify current idle sessions and their durations with the following scoped query:

   SELECT pid, state, xact_start, now() - xact_start AS idle_duration 
   FROM pg_stat_activity 
   WHERE state = 'idle in transaction';

   Missing Diagnostic Detail: To further refine the recommendation, please specify if your alerting pipeline supports stateful deduplication (e.g., via Prometheus Alertmanager or a custom database) or if it is a stateless "fire-and-forget" webhook.