Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statements vs. pgbouncer

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Paul Lindner <lindner(at)inuus(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Prepared Statements vs. pgbouncer
Date: 2007-09-29 08:43:33
Message-ID: 46FE1035.8050801@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Gregory StarkDate: 2007-09-29 09:36:33
Subject: Re: Prepared Statements vs. pgbouncer
Previous:From: Paul LindnerDate: 2007-09-29 07:21:59
Subject: Re: Prepared Statements vs. pgbouncer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group