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

On Mon, Feb 22, 2021 at 7:52 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> 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.

+1, I multiple times had to rely on similar cleanup in other poolers.

> 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.

And in some case negative cache entries can be a problem too.