[psycopg] Caching connection pool? - Mailing list psycopg

• create a "pool" of connections, with an initial number of connections equal to the "minconn" argument

• When getconn is called, see if there is an available connection. If so, return it. If not, open a new connection and return that (up to "maxconn" total connections)

• When putconn is called, return the connection to the pool for re-use, but do not close it (unless the close argument is specified as True, documentation says default is False)

• On the next request to getconn, this connection is now available and so no new connection will be made

• After some time, old connections would be closed and purged from the pool to prevent large numbers of no-longer-needed connections from laying around.

I have since been corrected about this impression, and now know that the pool simply keeps minconn connections open for the lifetime of the object, and if additional connections are needed, it opens them (up to maxconn), then immediately closes them again upon being returned to the pool. So my assumptions went wrong basically from point 3 onward. This means that for optimal usage of a pool object, some tweaking (probably ongoing as use changes) of minconn is needed, and if usage varies widely, there will be periods of having to open/close many connections and/or having many connections sitting unused, depending on how minconn is tuned.

To resolve this, I went ahead and wrote up a AbstractConnectionPool subclass that implements the caching logic I laid out above (https://github.com/psycopg/psycopg2/pull/565). At the moment I would consider it to be a fairly basic implementation - pruning of expired connections occurs only on a call to putconn (as opposed to running periodically), and connections time out at a (user-specified) period of time from when they were created, rather than last used, for example. It's arguable that both those elements (and perhaps others) could be improved on, but in my initial testing it appears to work. What do you guys think? Is this worth my time, or am I re-creating the wheel or engaging in premature optimization or something? Is the logic as I laid it out above good, or would there be some improvements that could be suggested for it? One thing I was thinking that could be helpful would be that if it saw the pool of available connections getting low, it could open one or more additional connections "in the background" so they are ready when needed. But maybe that's going a bit overboard?

Thanks!