Set the default Connection Lifetime to one hour #5662
Conversation
Was this issue raised with postgres maintainers? Unbound cache is the main problem here, since even if we have a one hour limit on a connection lifetime, it can most likely be replicated with sufficient enough load. |
|
It's a very long standing known issue that the maintainers are aware of. But changing to some expiration/LRU based approach hasn't happened yet. Some recent message mentioning this: https://www.postgresql.org/message-id/20230613.165512.2091685398843624399.horikyota.ntt%40gmail.com At the moment it's simply not recommended to have connections open to postgres for an unbounded amount of time. To be clear, I'm not saying that one hour is the perfect limit for everyone. I've seen users have a need to lower it to 10 minutes to keep memory in check. But the current default (no limit at all) is definitely wrong for a significant percentage of users. And the overhead of reconnecting once per hour is very tiny. |
|
OK, Got it. I'm personally OK with changing the default of connection lifetime to one hour. |
There was a problem hiding this comment.
Yeah, this conversation seems to make this point even more forcefully.
So I'm good with this. @NinoFloris any objection?
| /// balancing between a running server and a server just brought online. It can also be useful to prevent | ||
| /// runaway memory growth of connections at the PostgreSQL server side, because in some cases very long lived | ||
| /// connections slowly consume more and more memory over time. | ||
| /// Defaults to 3600 seconds (1 hour). |
There was a problem hiding this comment.
Need to also fix the <value> doc comment just below.
Connection Lifetime can also be used to limit memory growth of PostgreSQL connection. Certain caches only grow over time, the most common example is the cache for table metadata: the "relcache". For systems with many tables (often times due to partitioning) this relcache slowly grows larger and larger. By putting a 1 hour limit on a connection lifetime such excessive growth is limited. This same 1 hour limit is used for PgBouncer its equivalent server_lifetime config option. (I'm the maintainer of PgBouncer)
c8341a0 to
fbc4f3f
Compare
|
Yeah 1 hour sounds fine to me. Bedankt @JelteF :) |
Connection Lifetime can also be used to limit memory growth of
PostgreSQL connection. Certain caches only grow over time, the most
common example is the cache for table metadata: the "relcache". For
systems with many tables (often times due to partitioning) this relcache
slowly grows larger and larger. By putting a 1 hour limit on a
connection lifetime such excessive growth is limited, since this is
especially an issue with very long lived connections (multi-day).
This same 1 hour limit is used for PgBouncer its equivalent
server_lifetime config option. (I'm the maintainer of PgBouncer)