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

Re: Connection Pooling, a year later

From: Michael Owens <owensmk(at)earthlink(dot)net>
To: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Connection Pooling, a year later
Date: 2001-12-19 18:22:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
As long as each client's call is composed of a standalone transaction, there 
is no problem with external connection pools. But what about when a client's 
transactions spans two or more calls, such as SELECT FOR UPDATE? Then pooling 
is not safe: it offers no assurance of what may be interjected into an open 
transaction between calls. For example, each is a separate call to a shared 

Client A:  BEGIN WORK; SELECT last_name from customer for update where <X>;

Client B:  BEGIN WORK; SELECT street from customer for update where <Y>;

Client A:  update customer set lastname=<modified value> where <X>; COMMIT 

Now, isn't Client B's write lock gone with Client A's commit? Yet Client A's 
lock is still hanging around. While Client B's commit will close it, Client B 
has lost the assurance of its lock, defeating the purpose of SELECT FOR 

If this is corrent, then external connection pools limit what you can do with 
the database to a single call. Any transaction spanning more than one call is 
unsafe, because it is not isolated from other clients sharing the same 

On Tuesday 18 December 2001 11:30 pm, Andrew McMillan wrote:
> On Tue, 2001-12-18 at 13:46, Michael Owens wrote:
> > By having the postmaster map multiple clients to a fixed number of
> > backends, you achieve the happy medium: You never exceed the ideal number
> > of active backends, and at the same time you are not limited to only
> > accepting a fixed number of connections. Accepting connections can now be
> > based on load (however you wish to define it), not number.  You now make
> > decisions based on utlization.
> >
> > If it were shown that even half of a backend's life consisted of idle
> > time, leasing out that idle time to another active connection would
> > potentially double the average number of simultaneous requests without
> > (theoretically) incurring any significant degradation in performance.
> Have you looked at the client-side connection pooling solutions out
> there?
> DBBalancer ( ) tries to sit very
> transparently between your application and PostgreSQL, letting you
> implement connection pooling with almost no application changes.
> There was another one I came across too, but that one requires you to
> make more wide-reaching changes to the application.
> In my applications I have found DBBalancer to be roughly the same level
> of performance as PHP persistent connections, but a lot fewer
> connections are needed in the pool because they are only needed when
> Apache is delivering dynamic content - not the associated static
> stylesheets and images.
> Regards,
> 					Andrew.

In response to


pgsql-hackers by date

Next:From: Don BaccusDate: 2001-12-19 18:26:27
Subject: Re: Concerns about this release
Previous:From: Jason EarlDate: 2001-12-19 18:05:09
Subject: Re: Concerns about this release

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