Postgresql – Controlling number of idle sessions in postgresql 11.5

postgresql

We recently moved from PostgreSQL 9.0 to PostgreSQL 11.5.
We are now having problems with hundreds of idle sessions consuming all the database connections.

We have tried defining idle_in_transaction_session_timeout=30000. That only managed to abruptly terminate legitimate processes while they were awaiting results from queries

We have tried defining tcp_keepalives_idle=300, tcp_keepalives_interval=30, and tcp_keepalives_count=3 Still, the number of idle sessions are in the hundreds.

When we kill idle sessions, or restart the database, the idle sessions reconnect, and stay idle.

Best Answer

You have to make a distinction between idle and idle in transaction. The database parameter idle_in_transaction_session_timeout limits the duration of the latter state, but there is nothing in PostgreSQL that will terminate idle connections. The reason is that they are not a problem, and in the case of connection pooling they are even something desirable.

What is odd about your question is that idle_in_transaction_session_timeout will never terminate a query that is active – being executed while the client waits for the result. The parameter for that is statement_timeout.

If you are running foul of max_connections because your application leaves connections open in idle state, you have a connection leak on the application side that you have to fix there.

If the connections are idle in transaction for a long time, that is also an application problem and should be fixed there. idle_in_transaction_session_timeout is just a brutal way to ascertain the database's health in the face of a buggy application that cannot be fixed.

Related Question