PostgreSQL – Measuring Idle Time in Transaction for Django App

postgresql

When looking at pg_stat_activity, how can I tell whether the state IDLE IN TRANSACTION is transient?

Can the table's columns give that information to me (e.g. xact_start or query_start)? Btw, waiting is set to f for all these queries. Maybe that means they're not waiting on any locks, and so everything is fine?

I'm trying to ascertain how bad the IDLE IN TRANSACTION problem is on my app. There are lots of rows in pg_stat_activity with this state, but every time I re-query the table, there's new information. I need a more precise way to measure how long certain queries are IDLE IN TRANSACTION.

Best Answer

A session that is "idle in transaction" can't have waiting = true because otherwise it wouldn't be "idle".

The difference between current_timestamp and the timestamp in state_change should give you the time that the session was in "idle in transaction".

In general, a session that is just "idle" is not a problem. A session in "idle in transaction" might very well be a problem, as it prevents vacuum of cleaning up the rows that the transaction was using.

A short (a few seconds) "idle in transaction" is not a problem, only very long running (minutes, hours) transactions that are not ended properly (and thus stick in "idle in transaction") are a problem.

With 9.6 you can force the server to disconnect long running "idle in transaction" sessions using the property idle_in_transaction_session_timeout

Related Question