Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> That's why there's both transaction and session pooling. The
> benefit of session pooling is to avoid forking backends, reusing
> them instead, and you still get the pooling control.
So the application would need to open and close a pgbouncer
connection for each database transaction in order to share the
backend properly?
>> (4) Other factors than active connection count could be applied,
>> like expected memory consumption, or more esoteric metrics.
>
> All you can put in connection strings or per-role setting can be
> used to trick a virtual database and have it pre-set, but that
> means different pools (they accumulate, now) and different
> connection strings for the application.
Well, I don't know that you can very accurately predict a plan or
what its memory usage would be. Trying to work out all permutations
in advance and send each query to the right pool doesn't seem
workable on a large scale.
If we had a pooler bundled into the backend and defaulted to a
halfway reasonable configuration, it's possible that implementing an
active connection limit the second tier ACP would be covering close
enough to the same ground as to be redundant. I'm not quite
convinced, however, that your proposed use of pgbouncer for this,
given the multiple pools which would need to be configured and the
possible application awareness and cooperation with policy would be
better than a fairly simple ACP. It seems a bit like driving nails
with a wrench. I like wrenches, I use them to turn things, but I
don't like using them to drive nails when I can help it. :-)
-Kevin