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

Hi

ne 21. 2. 2021 v 19:05 odesílatel Daniele Varrazzo <
daniele(dot)varrazzo(at)gmail(dot)com> napsal:

> Hello,
>
> I am designing and implementing a connection pool for psycopg3 [1][2].
> Some of the inspiration is coming from HikariCP [3], a Java connection
> pool.
>
> 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?
>
>
I have very strong experience - it is very useful. Long live PostgreSQL
processes can have a lot of allocated memory, and with some unhappy
consequences, the operation system memory can be fragmented, and the
operation system can use swap. Next issue can be bloated catalogue cache
inside processes. Both issues depends on application design, catalog size,
and others factors, and the most simple fix of these issues is setting a
short life of Postgres sessions - 1 hour is usual value.

Regards

Pavel

> Thank you very much,
>
> -- Daniele
>
> [1]: https://www.psycopg.org/articles/2021/01/17/pool-design/
> [2]:
> https://github.com/psycopg/psycopg3/blob/connection-pool/psycopg3/psycopg3/pool.py
> [3]: https://github.com/brettwooldridge/HikariCP
>
>
>