7

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.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
ddd
  • 4,665
  • 14
  • 69
  • 125

1 Answers1

7

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)

jesal
  • 7,852
  • 6
  • 50
  • 56
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 1
    I set `idle_in_transaction_session_timeout` to 60 seconds. It does not seem to affect any of the sessions that have been idle for 2 months though. Had to manually terminate these legacy sessions. Hopefully, it will apply to new sessions that become idle in the future. – ddd Jan 13 '18 at 02:03
  • you can check it, by opening connection and not running any query for two minutes – Vao Tsun Jan 13 '18 at 09:25
  • I know this is five years old, but `idle_in_transaction_session_timeout` doesn't apply to idle sessions unless they're in an incomplete transaction. – TheTFo Aug 04 '23 at 21:10