Find Idle in Transaction Sessions in PostgreSQL

calendar Mar 28, 2026 / · 4 min read · postgresql administration monitoring troubleshooting database  ·
Share on: linkedin copy

How to Find Idle in Transaction Sessions in PostgreSQL

An idle-in-transaction session is a database connection that has started a transaction but is not currently running a query. It is just sitting there, holding the transaction open. This is different from a regular idle connection, which has no open transaction.

Idle-in-transaction sessions are a common problem. They hold locks, block autovacuum, and prevent rows from being cleaned up. If they stay open long enough, they can cause serious performance problems — including transaction ID wraparound.

These queries find all sessions in this state and flag the ones that have been open for too long.

Sample Code

Find all idle-in-transaction sessions:

1SELECT *
2FROM pg_stat_activity
3WHERE state = 'idle in transaction'
4  AND xact_start IS NOT NULL;

Find sessions idle in transaction for more than 5 minutes:

 1SELECT
 2    pid,
 3    usename,
 4    state,
 5    query,
 6    age(clock_timestamp(), query_start) AS idle_duration,
 7    client_addr
 8FROM
 9    pg_stat_activity
10WHERE
11    state = 'idle in transaction'
12    AND age(clock_timestamp(), query_start) > interval '5 minutes';

Notes: Works on all supported PostgreSQL versions. Adjust the interval '5 minutes' threshold to match what is acceptable for your workload. The first query gives a full picture; the second filters to sessions that have been idle long enough to be a problem.

Code Breakdown

  • pg_stat_activity — A built-in view that shows all current database sessions and their state.
  • state = 'idle in transaction' — Filters to sessions that are inside an open transaction but not actively running a query. This is the state that causes problems.
  • xact_start IS NOT NULL — Confirms the session has an active transaction. This field is set when the transaction started and cleared when it ends.
  • age(clock_timestamp(), query_start) — Calculates how long the session has been in this state. clock_timestamp() returns the real current time, not the transaction time.
  • pid — The process ID of the session. Use this to cancel or terminate it.
  • usename — The database user running the session.
  • query — The last query that ran in this session. This can help you identify where in your application the transaction was opened.
  • client_addr — The IP address of the client that opened this connection. Useful for identifying the source application or server.
  • interval '5 minutes' — The time threshold. Sessions older than this are returned. Adjust based on your application's normal transaction duration.

Key Points

  • Any session with state = 'idle in transaction' has an open transaction. It is holding all the locks that transaction acquired.
  • The longer a transaction stays open, the more it blocks autovacuum and potentially blocks other queries waiting for the same rows.
  • A session that shows the same query over and over but is always idle in transaction is likely a bug in your application — a transaction was opened but never committed or rolled back.
  • client_addr helps you trace the problem back to the application or service that created the connection.

Insights and Explanations

To understand what locks an idle-in-transaction session is holding, join to pg_locks:

 1SELECT
 2    a.pid,
 3    a.usename,
 4    a.state,
 5    age(clock_timestamp(), a.xact_start) AS duration,
 6    l.relation::regclass AS locked_table,
 7    l.mode,
 8    l.granted
 9FROM pg_stat_activity a
10JOIN pg_locks l ON l.pid = a.pid
11WHERE a.state = 'idle in transaction'
12ORDER BY duration DESC;

To cancel the query in a session (the session stays connected but the transaction is rolled back):

1SELECT pg_cancel_backend(pid);

To terminate the session entirely (disconnects the client):

1SELECT pg_terminate_backend(pid);

Use pg_cancel_backend first — it is less disruptive. If the session does not respond, use pg_terminate_backend.

To prevent idle-in-transaction sessions from piling up, set a timeout at the database or role level:

1-- Set a 10-minute limit for all new connections
2ALTER DATABASE your_database SET idle_in_transaction_session_timeout = '10min';
3
4-- Or for a specific role
5ALTER ROLE your_role SET idle_in_transaction_session_timeout = '10min';

When the timeout is reached, PostgreSQL automatically terminates the idle transaction and rolls it back.

Additional Considerations

  • Permissions: Superusers can see all sessions in pg_stat_activity. Regular users can only see their own sessions unless granted the pg_monitor role.
  • Application fix: The root cause of idle-in-transaction sessions is usually application code that opens a transaction and then waits (for user input, an API call, etc.) before committing. Fix the application rather than relying on timeouts alone.
  • Connection poolers: PgBouncer and similar tools can hold connections open in this state if not configured correctly. Check your pooler's server_idle_timeout and pool_mode settings.

References

Posts in this series