Idle connextions in PostgreSQL

[jdediegoa]

Dear all

Have you even noticed a high number of "idle" connections in PostgreSQL related to martin? We have deployed martin in an Azure containerapp and, when running martin, the number of idle connections grows until it reaches the maximum number of connections allowed per instance...It can take 4 hours or 1 day. We have tried with the "keep_alive" parameter, but there is no changes in this behaviour apparently....

Any idea about what can be happening?

Thanks in advance

Jesús de Diego

[nyurik]

Thanks for reporting. I looked at the deadpool-postgres crate, which uses tokio_postgres crate, which supports keepalives - a boolean flag to send keepalive signals to postgres connections to make sure they are still connected. This makes me think that the default behavior is to grow the pool up until its maximum, and never shrink the number of connections.

Try to use keepalives=0 in your postgres connection string to disable it - I think that will force database connections to be auto-dropped when not in use -- see supported keys in https://docs.rs/tokio-postgres/0.7.10/tokio_postgres/config/struct.Config.html#keys

[jdediegoa]

Hi Yuri

Thank you so much for the info. We will try with those parameter in the connection string and will let you know about the results.

Thanls

Jesús de Diego

[jdediegoa]

Hi Yuri
We have tried adding these two keys to the connection string:

?keepalives=0&keepalives_idle=15

But nothing happens... Again the number of connections grows until it reaches the maximum of the DB.

We will check another options (basically, modifying the idle_session_timeout database parameter... but this is a bit dangerous as this parameter will apply to all DATABASE connections, not only those used by martin....

Any other idea?

Best regards and thanks for your support

Jesús de Diego

[nyurik]

@jdediegoa thx for testing, interesting indeed. I created a ticket bikeshedder/deadpool#299 - might be an upstream issue?

One thing to check: in the martin output, check a line at the beginning that looks like this:

INFO  martin::pg::pool] Connecting with SSL support: Config { ...

It should have this in it: keepalives: false, keepalives_idle: 15s -- this means your parameter string was correctly parsed. Note though that according to the docs, keepalives_idle will be ignored if keepalives is disabled. So not sure why you were setting that.

[nyurik]

P.S. Please use "Write a reply" box, not the "suggest an answer" box at the bottom.

[nyurik]

@jdediegoa the author of the Deadpool did a thorough reply on how to get this done: bikeshedder/deadpool#299 (comment) -- would you be interested in submitting a PR for this? We would need to think on how to add this functionality and make it configurable.

[jdediegoa]

Hi Yuri
First of all... sorry for the late response... as we splited the data in different databases/servers this problem disspeared. In any case, we are going to implement a scheduled task that kill all these idle connections.
Thanks in any case for your help and support.