PostgreSQL – What Does IDLE State Mean in pg_stat_activity?

postgresql

Can someone resolve my below doubts about pg_stat_activity in terms of postgres 9.5?

  1. Can some let me know what does the state IDLE denotes in the row of pg_stat_activity? Is it like it checks all the open connections to postgres and if the connection is not executing any query then IDLE denotes the last query executed by the connection?
  2. In the output of select * from pg_stat_activity I am seeing rows with state IDLE and the query_start_date more than 2 days old? What does this signify ? Should I be worried about this?
  3. Can someone let me know how pg_stat_activity is populated? If I am executing a query like Select * from pg_stat_activity does it produce the details from some snapshot maintained by postgres or it checks the current connections open in postgres to provide the details?

Best Answer

This has been answered in postgres mailing list

Each row in pg_stat_activity represents an established connection to the server from a client. "idle" means the client is not currently executing a query nor in a transaction.

If query_start_date is 2 days old, that just means the last query to be executed on that connection was two days ago.

Not really a reason for worry, unless you have so many connections open that it's consuming more RAM than you can afford. It's generally desirable for a connection pool to have a few idle connections so queries don't suffer the latency of establishing a new connection.

Related Question