Pgbouncer vs npgsql connection pool

[hoerup]

Hi
I'm trying to wrap my head around some of your recommendations regarding operations of pgsql and optionally pgbouncer for VBO365 v8.x

I've read both https://www.veeam.com/kb4728 and https://www.veeam.com/kb4758

Im no pgbouncer expert, but as I see it it can help with 2 different problems when dealing with postgres connections:
1) Multiplex multiple connections into a smaller set of active backend connections, which is especially efficient when setting pgbouncer in transaction mode as you recommend.
This is effective both for burst connections but also very much when dealing with multiple long term/idle connections
2) Reduce the computational overhead of setting up a new postgresql connection.
Which is very relevant when dealing with multiple short lived connections.

From what I have observed, your connections mostly fall into the short-lived category in 2) although i won't deny that the multiplexing in 1) might help during bursts ?

But when reading https://www.veeam.com/kb4760 it's clear no matter whether we connect directly to postgres or via pgbouncer, there is a risk of running out of client ports.

All this makes me wonder, why it seems you go the pgbouncer route as an optimization instead of trying to tune the connection pooling mechanism already present in npgsql?

in ticket #7821146 i asked for a copy of the various default connection strings as seen in a fresh installation of VBO365 v8, and when removing host, db, username and password settings from the supplied constrings,
we are left with "maximum pool size=100;connection idle lifetime=10;command timeout=60;timeout=60;maxpoolsize=20"

Let me first ask why you have maxpoolsize, which afaik is not a valid npgsql parameter ? IMHO this adds confusion when you also provide the more correct "maximum pool size"?

When diving more into this, even though you dont explicitly disable npgsql pooling the effect is very much limited - I assume that this is on purpose and I would like to hear the arguments behind?

And yes, it is limited because you don't specify a 'minimum pool size' so it defaults to zero, and still you specify a short connection idle lifetime as 10 seconds. With these 2 combined, any connection thats released back to the pool will be closed after 10 seconds,
so the possibility of a connection reuse is very little.
Ref: https://www.npgsql.org/doc/connection-s ... ml#pooling


If it was my application where i was dealing with multiple short lived connection usages, as one tend to in a dotnet application where I assume that you acquire a dbContext within a scope via dotnet8's default dependency injection, then I would experiment with keeping a small number
of idling connections around. This would besides the performance improvements, also reduce the number of client connections in TIME_WAIT state and thereby reduce the risk of client port exhaustion.

[Polina]

Hi Torben,

As we already briefly discussed with you in the DM, I'll share your feedback with our RND team so that they could take a closer look.

Thanks!

[Polina]

Hi Torben,

Out RND team asked me to share with you the following update:

We have implemented pgBouncer primarily to reduce the computational overhead associated with establishing new PostgreSQL connections and to limit the number of backend connections. This approach is especially beneficial for workloads characterized by frequent, short-lived connections, as you observed.
The confusion regarding the use of both “maximum pool size” and “maxpoolsize” parameters in connection strings should be addressed in the next product update.

On the simultaneous use of both Npgsql’s built-in pooling and pgBouncer, our plan is to consolidate connection management on one side—most likely relying on pgBouncer for pooling. There're also some longer-term plans on improving our cache databases.

We appreciate your insights and suggestions. Your feedback has been shared with our development team for further consideration.