How to Monitor PostgreSQL Connections

This article discusses connections to PostgreSQL database servers. It first reviews the possible states for a connection and then shows how to identify and terminate connections that are lying idle and consuming resources.

1. States of a connection
2. Identifying the connection states and duration
3. Identifying the connections that are not required
4. Terminating a connection when necessary

In order to make modifications or read data from a PostgreSQL database, the first thing we need to do is to create connections. However, each connection comes with overhead in terms of both process and memory; hence a system with limited resources (read, hardware)  can only handle a certain number of connections. Once it goes beyond that, it will start throwing errors or refusing connections. PostgreSQL does a good job restricting the connections in postgresql.conf.

In this post we will look at the types of states that exist for connections in PostgreSQL. We will show how to find out if that connection is doing work or has been lying idle for a period of time, in which case it should be terminated to recover the connection and resources. We will go through the following steps:

1. Understanding the states of a connection
2. Identifying the states and duration of the current connections
3. Identifying the connections that are not required
4. Terminating a connection when necessary

1. States of a connection

Once a connection in PostgreSQL is created, it can perform various operations that lead to changes in states. Based on the state and the time the connection has been in that state, an informed decision can be made as to whether the connection is active or has been left idle/abandoned. 

It is worth mentioning that if the connection is not explicitly closed by the application, it will remain available, thereby consuming resources—even when the client has disconnected.

These are the four states a connection can have:

1. active: This indicates that the connection is working. 
2. idle: This indicates that the connection is idle and we need to track these connections based on the time that they have been idle.
3. idle in transaction: This indicates the backend is in a transaction, but it is currently not doing anything and could be waiting for an input from the end user.
4. idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. This also needs to be monitored based on the time since it has been idle.

2. Identifying the connection states and duration

The pg_stat_activity view in the PostgreSQL catalog tables gives you information regarding what a connection is doing and how long it has been in that state. If you run the query to get the values from the view, you get the following output for the state of each connection:

From the above output the one value which we are looking for is the “state”. We can use that to find out which queries are in which state and then we can dig further. So, we can modify the query to show specific records related to just the queries which are idle:

Select * from pg_stat_activity where state=’idle’;

3. Identifying the connections that are not required

We can modify the query a bit more to narrow down the information we are looking for so that we can plan an action on that particular connection. We can do this by selecting just the PIDs and the query states for the PIDs that are idle. We also need to monitor the time since the connection has been idle to check that we do not have any abandoned connections wasting our resources as well. So this would mean:

Select pid, usename, application_name, backend_start, state_change, state from pg_stat_activity where state=’idle’;

4. Terminating a connection when necessary

Once we have narrowed down the query that is either in a hang state or has been idle for a long time, we can use this query to simply kill the backend process without affecting the operations of the server:

SELECT pg_terminate_backend(PID);