Re: Is a connection max lifetime useful in a connection pool?

Hi,

On 2021-02-21 19:05:03 +0100, Daniele Varrazzo wrote:
> One of the HikariCP configuration parameters is "maxLifetime", whose
> description is: "This property controls the maximum lifetime of a
> connection in the pool. [...] **We strongly recommend setting this
> value, and it should be several seconds shorter than any database or
> infrastructure imposed connection time limit.**" (bold is theirs,
> default value is 30 mins).
>
> When discussing the pool features in the psycopg mailing list someone
> pointed out "what is the utility of this parameter? connections don't
> rot, do they?"
>
> Hikari is a generic connection pool, not one specific for Postgres. So
> I'm wondering: is there any value in periodically deleting and
> recreating connections for a Postgres-specific connection pool? Is a
> MaxLifetime parameter useful?

It's extremely useful. If your pooler is used in a large application
with different "parts" or your application uses schema based
multi-tenancy or such, you can end up with the various per-connection
caches getting very large without providing much benefit. Unfortunately
we do not yet have effective "pressure" against that. Similarly, if you
have an application using prepared statements you can end up with enough
prepared statements for that to be a memory usage issue.

Additionally, if there's ever a problem with memory leakage, be it in
core PG or some extension, being able to limit the harm of that can be a
life saver.

Greetings,

Andres Freund