Controlling number of idle sessions in postgresql 11.5

Question

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.

• Schwern: What is making those hundreds of idle connections? It may be simpler to address them.
• Jeremy: idle_in_transaction_session_timeout will not kill queries that are waiting for results. statement_timeout would do that, though.
• user1822: I am quite sure that it Postgres 11 does not handle connections/sessions differently from 9.0 - if you have many idle connections now (without changing your application), you should have had them before as well. Your application connects to the database, Postgres itself does not "reconnect" in any way. You have to search for the culprit outside of Postgres
• Colin 't Hart: Install pgpool?

Answer 1

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.

• Mark Dyer: We tried changing max_connections to 1200, but found the OS has PostgreSQL limited to 1024 connections. I have shared these responses with my team. Thanks for the quick responses.
• Laurenz Albe: You can change the ulimit on the operating system, but 1200 connections is never the correct answer. Perhaps you need a connection pool.

Answer 2

Postgres 14 will introduce a new setting idle_session_timeout which will close idle (not "idle in transaction") session after the specified amount of time.

Answer 3

With PostgreSQL 11.5, you can try my extension pg_timeout: https://www.pgxn.org/dist/pg_timeout/