Re: connection pooling

On Fri, Mar 20, 2009 at 3:31 AM, David <[email protected]> wrote:
> What specific benefit does an within-process connection pool provide
> to a system?
>
> Psycopg2 is threadsafe to the cursor level, (threadsafety=2) so a
> process can create a global connection that is shared by all threads;
> each thread can get a new cursor for its own purposes, and all threads
> in the process can benefit from one persistant connection.  That would
> seem to provide all the benefits of connection pooling, without the
> overhead of checking connections into and out-of  a pool.

While you can use cursors for a single connection from one thread,
there are a few reasons you probably want multiple connections:

1. Each connection can only execute one query at a time.  So using
only a single connection will limit the maximum amount of concurrency
for your application.

2. Each connection can only manage a single transaction at a time.  So
sharing a connection between multiple threads means all those threads
are participating in a single transaction.

3. If you have multiple threads accessing the same connection
essentially means you'll be issuing queries in a random order.
Depending on what you're doing, this could be a problem.

The thread-safe nature of psycopg2 does not prevent the connection
from acting as a serialisation point.  And if you're doing anything
with transactions, you certainly want separate connections for
separate parallel transactions.


> If there were multiple databases, needing different connections with
> different authentication, then a connection pool would make sense, but
> the Psycopg2 pool implementation (for example) does not seem to track
> the connections by user, nor does it accomodate different
> authentication data for different connections.
>
> Am I overlooking something important in terms of what benefit
> connection pooling provides, as opposed to simply sharing connections?
>
> Is there a bandwidth issue, in that multiple connections create more
> bandwidth to the pg server?

Multiple connections means you can run multiple concurrent
transactions.  If you're not using transactions, it means you can
issue multiple concurrent queries.


> Is connection pooling one of those tools where you just know you need
> it when you need it?

It isn't always necessary to deal with connection pools directly.  For
a number of web applications I've worked with, requests are handled by
a thread pool.  Rather than grabbing and releasing connections from a
connection pool, we just created one connection per thread and stored
that in thread local storage.  This also works pretty well for e.g.
running database transactions within Twisted's reactor thread pool.

James.