Re: Prepared Statements vs. pgbouncer

Josh Berkus wrote:
>> You might also consider if using pgbouncer or similar really is
>> necessary. Perhaps you could get away with a per-client connection pool,
>> with "min pool size" small enough, maybe 0, that the number of
>> simultaneous connections to the server would stay reasonable.
>
> It becomes necessary whenever you have a large number of appservers
> connecting to the same database server.  Setting min pool size downwards
> isn't really effective becuase then you're just adding connection time
> delays ... exactly what you're trying to prevent by using a connection
> pool.

Yeah, I know. I was trying to think of workaround for the OP. With
hundreds of app servers it's likely that a pool of 1-2 connections would
 actually be sufficient. Unless there really is thousands of
simultaneous users running queries at the same time, at which point
Postgres is going to be sweating even if you manage to multiplex the
number of connections to a few hundred.

> I'm working on a scalable project myself which is why I'm concerned
> about this.  The reason why we're planning to rely on pgBouncer is that
> we'll have both java and non-java applications connecting to the same
> database, and we don't want to manage two different connection/failover
> pools.  So it's important to us that PG-JDBC function with independant
> connection pools.
>
> So where is it going to be easier to fix this ... pgBouncer, or pg-JDBC?

It's clearly pgBouncer here that's braking the protocol, so it's
pgBouncer that needs fixing. A workaround in JDBC driver would only help
JDBC apps. If you're having non-java applications in the mix, you'd have
to implement the workaround in *all* the drivers.

BTW: I just stumbled across this Greg Sabine Mullane's blog post, which
describes a workaround for DBD::Pg perl module, so we're not alone:
http://people.planetpostgresql.org/greg/index.php?/archives/110-Using-DBDPg-with-pg_bouncer.html

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com