How to shut down idle connections to Postgres DB in Amazon RDS

Question

We have a Postgres database set up in the RDS. I wrote some APIs to handle data ingestion to the db using Spring Boot. Recently I discovered that lots of connections kept their sessions alive after calling the APIs. Some of the sessions date back to 3 months ago.

I wonder if there is a way to automatically close these connections after it's been inactive for a while. From How to close idle connections in PostgreSQL automatically?, looks like I can set up a cron job to look for dead connections with a SQL query and terminate them with pg_trminate_backend. Is this the best option? Is there something that can be done in the web application layer? Or maybe some RDS parameters? Need some advice on this.

Answer 1

from 9.6 on, you have:

https://www.postgresql.org/docs/current/static/runtime-config-client.html

idle_in_transaction_session_timeout (integer)

Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

The default value of 0 disables this feature.

(formatting mine)

before 9.6- yes the only native way - cron with SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE now()-state_change > '5 minute'::interval.

also if you use connection pooling with pgbouncer, then:

https://pgbouncer.github.io/config.html

server_idle_timeout If a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. [seconds]

Default: 600.0

(formatting mine)