Re: Prepared Statements vs. pgbouncer

Paul Lindner wrote:

> * 100s of application servers using Torque and DBCP
> * Dozens of databases.
> * All app servers can connect to all databases.
> * Each application server may need many connections to an individual
> database.
> * App code as written will spawn multiple concurrent SELECTs to
> a single database to speed up queries on partitioned tables.

I'm assuming there is some reason why you can't run the same apps on
fewer appservers and share the pools. The basic problem here is that you
can't get a good global view of which connections are idle because you
have a lot of separate appservers each doing their own thing.

> 3. Use pgbouncer to allow 6k connections while actually
> lowering the number of DB backends, plus giving us some very cool
> maintenance features like redirecting connections to other hosts
> and more.
>
> So we we're trying to implement #3.

That is a reasonable approach to take, that's essentially giving you a
global connection pool. However..

> The only thing holding us back from deploying the pgbouncer solution
> is this issue with the server-side prepared statements.

This is a really an issue with pgbouncer's incomplete implementation of
the protocol.

If you were going to change the driver the best place to do it is in the
V3 protocol code itself -- tell it not to use named statements at all.
The higher layers in the driver are working to a different API that
doesn't know anything about named statements at all, it just provides
hints about whether a query is likely to be re-used or not. So trying to
repair particular instances of that so they don't used named statements
is going to be fairly error-prone.

I don't see that sort of change going into the official driver though ..
as Tom says, why would we deliberately cripple use of the protocol
because a 3rd party piece of software can't handle the full protocol? So
I think you are going to be stuck with either deploying a customized
JDBC driver for your particular environment, or fixing pgbouncer so that
it properly implements the protocol.

-O