How to Kill Idle Connections in PostgreSQL

The first step in making changes or reading some information from a PostgreSQL databank is to establish connections. On the other hand, each link generated overhead utilizing procedure and storage. That’s why a device with minimal resources (read, storage, hardware) can support the limited aggregate of connections. Once the limited aggregate has gone far beyond a point, It should continue hurling errors or denying connections. Within PostgreSQL.conf, PostgreSQL is doing a decent job of limiting links. In this tutorial, we’ll look at the different forms of states that PostgreSQL links can have. We’ll show you how to determine whether the link is active or has been inactive for a long duration, wherein case it can be disconnected to free up the links and resources.

Connect to Server:

At the start, make sure you have pgAdmin4 have been fully functional installed on your computer system. Open it from your applications. You have to connect it with the localhost by providing a password.

After the connectivity with root localhost, connect it with the PostgreSQL server. Type the password for the PostgreSQL 13 user ‘Postgres to connect. Tap on the OK button to proceed.

Now you have been connected to the PostgreSQL 13 server. You can see a list of databases residing in the server as presented in the picture appended below. Postgres’s database is the default database’ created at the time of PostgreSQL installation, while the ‘test’ database has been created by a user afterward the installation.

Connection States:

If a PostgreSQL link is established, it may perform various actions that result in state transitions. A rational decision should be taken about whether the link is working or it has been left idle/unused depending on the state and the duration it has been in each state. It is important to note that until the application deliberately closes the connection, it will keep operating, wasting resources long after the client is detached. There are the 4 potential states for a connection:

Active: This means that the link is up and running.
Idle: This means that the link is inactive, so we must keep a record of them depending on how long they’ve been idle.
Idle (in transaction): This means that the backend is engaged in a query, although it is actually idle and maybe expecting input from the end client.
Idle in transaction (aborted): This condition is equivalent to idle in process. However, one of the declarations culminated in an error. It can be tracked depending on how long it has been idle.

Identify the Connection States:

The PostgreSQL catalog tables provide a built-in view ‘pg_stat_activity’ to check statistics about what a link does or how much time it’s been in this condition. To check all the statistics regarding every database and every connection state, open the query tool and execute the below query:

>> SELECT * FROM pg_stat_activity;

The query has been implemented fruitfully, and the accomplishment note has been shown.

When you check its data Output side, you will find a table with several columns, as shown below. You can check the states of connections by checking the values of the field’ state’.

To simplify the output and have a clear idea of connections, their states, the users, and servers on those states, you have to execute the below-modified query in the query tool. This query is only showing the 5 fields of records for connections and particular data regarding them. The column ‘pid’ stands for process id. The column’ state’ holds the states of processes. The column’ usename’ identifies the user that has been working on the particular process. The column ‘datname’ specified the database name on which the transaction has been executing. The column ‘datid’ stands for database id.

>> SELECT pid, state, usename datname, datid, from pg_stat_activity;

The output has a total of 8 processes recorded. The ‘state’ column shows that there are only 3 processes working right now. One is held by default database ‘Postgres and the other two are held by database ‘test’. At the same time, the ‘Postgres user has been performing these processes.

Identify the Idle Connections:

The “state” seems to be the sole value we’re searching for within the results mentioned above. We will use this information to determine which processes or queries are in which states and afterward dig deeper. We may slim down the details we’re searching for by refining the query, allowing us to prepare an intervention on that specific connection. We could do this by choosing only the idle PIDs using the WHERE clause and the states for those PIDs. We should also keep track of how long the link has been inactive and ensure that we don’t have any neglected links squandering our resources. As a result, we will be using the below-rephrased command to only display records relevant to the processes that are currently idle:

>> SELECT pid, usename, usesysid, datid, datname, application_name, backend_start, state_change, state FROM pg_stat_activity WHERE state = ‘idle’;

The query has fetched only 2 records of data where the state was ‘idle’ using the WHERE clause. The result is showing the 2 idle processes with certain information regarding them.

Kill an Idle Connection:

After the identification of idle connections, now a time to kill them. Once we have whittled down the process either in a hold state or inactive for a lot longer, we could use the simple command to easily terminate the back-end mechanism without disrupting the server’s activities. We have to provide the process ‘id’ within the query in a terminate function.

>> SELECT pg_terminate_backend(7408);