Postgres closing idle connections

[joelsbrown1]

Hello - I am trying out fly.io for the first time and I am seeing some strange Postgres behavior. My app creates a connection pool (size = 10). However, postgres hosted on fly.io seems to close these connections if they are idle for ~1 minute (see screenshot attached). I have seen a few posts mention that haproxy will timeout postgres connections at 30 minutes but this seems different. Anyone know why idle connections are getting closed so quickly? is this behavior documented and/or adjustable?

[jsierles]

Hey, which Postgres client are you using?

This timeout is the default behavior for idle connections on newer Postgres clusters. They send traffic through Flycast, which uses the Fly proxy to route connections. The Fly proxy times out idle connections after 60 seconds.

Right now this behavior isn’t adjustable, but it’s worth checking if you can configure your Postgres client to ‘ping’ Postgres, and/or to handle reconnections.

[joelsbrown1]

I am using r2dbc-postgresql and r2dbc-pool. I appreciate the response. The behavior and timeouts I was seeing make sense in that context. As you suggest most pooling libraries do indeed have some kind of keepalive or idle connection reaper that can be configured. With r2dbc-pool i was able to set maxIdleTime to 60s and that “fixed” the issue

That being said, 60s is quite short. Most libraries have default values much higher than this for handling idle connections. For example, the default maxIdleTime for r2dbc-pool is 30m. The default for the very popular (for JVM) HikariCP is 10m. Might make sense to call this out in the docs/how to guides because 60s is much shorter than most libraries will handle by default. Thank you!

GitHub - pgjdbc/r2dbc-postgresql: Postgresql R2DBC Driver

GitHub - r2dbc/r2dbc-pool: Connection Pooling for Reactive Relational...

[DAlperin]

We’re looking into making this experience better. Thanks for the report. To you and anyone else: keep your pg suggestions/rough edges coming. We can’t get to all of them in the short term but they help us understand what is valuable to you all and how we can keep making things better.