does postgreSQL waits for some time before releasing idle connection?

Viewed 277

I'm using CKAN 2.7.2 (docker) application. We have set:

max_connections = 200 (postgresql.conf)

In my psql's pg_stat_activity table, connection details are as (select * from pg_stat_activity;):

ckandb connections : total 80 connections where active - 51, idle - 20, idle_in_transaction - 09.
ckan_datastore con : total 24 connections where active - 04, idle - 19, idle_in_transaction - 01.
    

We can see many connections are in Idle state.

Does these Idle connections are reusable (waiting to serve new request) or these connections are about to release(waiting for release)?

Does postgreSQL takes some time to release the idle connections?(I mean, If there are some idle connections present then, does postgreSQL waits for some time before releasing those idle connection? )

-----------------------------------------------------Edited

We are using ckan application with 4 ckan_default processes and postgresql database.

In my ckan application sqlalchemy's QueuePool size is 15 (Pool_size - 5, max_overflow - 10), By default pool size is 5 (pool_size=5). So 4 ckan_default processes can hold maximum 20 connections ( 4 processes * 5 pool_size).

When additional connections required it will get max_overflow=10 more connections in the pool(max_overflow=10*4 processes = 40 total connections). But after the use of these additional connections (max_overflow=10), these connections should be released from pool immediately. As given in below sqlalchemy's document: https://docs.sqlalchemy.org/en/14/core/pooling.html#sqlalchemy.pool.QueuePool.params.max_overflow

When those additional connections are returned to the pool, they are disconnected and discarded.`

Please confirm my above understanding?

Currently I have 39 idle connections in my ckan database:

select count(*) from pg_stat_activity where state='idle';
 count
-------
    39
(1 row)

surely some of idle connections(atleast 19-20) belongs to max_overflow. why these idle connections that belongs to max_overflow are not releasing? it should release as per sqlalchemy's official document given above? Does these idle connections will be release after some specific(default) time?

0 Answers
Related