Pg_stat_activity is a useful view to help troubleshoot problems with pg (such as too many connections). Each line of pg_stat_activity displays information about a “process,” which can be understood as a DB connection.
: The pg_stat_activity view will have one row per server process, showing information related to The current activity of that process.
Three useful fields, translated as follows:
Query_start: the query start time for the active state, or if the state is not active, the last query start time
State: the running state, which can be of several values. Active: executing a query; Idle: waiting for a new command; Idle in transaction: the back end is a transaction, but the query has not been executed; Idle in transaction(aborted): similar to idle in transaction with the exception of transaction execution errors.
Query: the query text executed (that is, SQL). If the state is active, the SQL is executing. If it is in any other state, the last executed SQL is shown.
So you can use:
Select count(*) from pg_stat_activity where state=’idle’;
Query the number of idle connections. If the number is too large, it can be considered problematic (such as forgetting to close the connection).
If you want to locate the problematic SQL further, you can query it as follows: