Establishing connections is the initial stage in making changes or reading data from a PostgreSQL.
Each link, on the other hand, generated overhead through the use of method and storage. That's why a device with few resources (memory, storage, and hardware) can handle a small number of connections. It should continue throwing errors or rejecting connections once the limited aggregate has gone far beyond a point.
PostgreSQL does a good job of limiting links within PostgreSQL.conf. We'll look at the many states that PostgreSQL links can have in this tutorial. We'll show you how to identify whether a link is active or has been dormant for an extended period of time, in which case it can be severed to free up resources and links.
What are the different Connection States in PostgreSQL:
When a PostgreSQL connection is formed, it can conduct a variety of actions that cause state transitions. Depending on the state and the length of time it has been in each condition, a sensible conclusion should be made about whether the link is functional or has been left idle/unused. It's worth noting that the application will continue to run until the connection is intentionally closed, squandering resources long after the client has been disconnected.
A link can exist in one of four states:
- Active: This indicates that the link is operational.
- Idle: This indicates that the link is inactive, and we must keep track of how long it has been idle.
- Idle (in transaction): This indicates that the backend is working on a query, despite the fact that it is actually idle and waiting for input from the end user.
- Idle in transaction (aborted): This is the same as idle in process. One of the declarations, however, resulted in an error. Depending on how long it has been idle, it can be tracked.
How to see the active connections in PostgreSQL
The built-in view 'pg stat activity' in the PostgreSQL catalog tables can be used to check statistics on what a link does or how long it's been in this state. Open the query tool and run the following query to see all the statistics for each database and connection state:
select * from pg_stat_activity
When you look at the data output side, you'll see a table with numerous columns. The values of the field' state' can be used to examine the states of connections.
SELECT state, pid, usename, usesysid, datid, datname, application_name, backend_start, state_change, state FROM pg_stat_activity WHERE state = 'idle';
How to identify the Idle Connections in PostgreSQL
Within the above-mentioned results, the "state" appears to be the only value we're looking for.
We'll utilize this data to figure out which processes or queries are in which statuses, and then investigate further. By refining the query, we may narrow down the details we're looking for, allowing us to plan an intervention on that precise link. We may do this by utilizing the WHERE clause to select only the idle PIDs and the statuses for those PIDs.
We should additionally keep track of how long the link has been dormant to ensure that we aren't wasting resources on inactive links. Use the command below to only show idle records:
SELECT pid, usename, usesysid, datid, datname, application_name, backend_start, state_change, state FROM pg_stat_activity WHERE state = ‘idle’;
How to Kill the idle connections in PostgreSQL
Now that you've identified any idle connections, it's time to terminate them. We could utilize the simple command to simply terminate the back-end mechanism without affecting the server's activity once we've whittled down the process either in a hold state or inactive for a long time.
In a terminate function, we must specify the Process ID within the query.
SELECT pg_terminate_backend(7540);
, where 7540 is the PID.
For better configuration of PostgreSQL database, optimizing the configurations mentioned in the postgresql.conf file is mandatory.
You can refer here for memory configurations.
Tunning PostgreSQL for better performance