Psycopg2 pool clarification

[Israel Brewster]

I've been using the psycopg2 pool class for a while now, using code similar to the following:

>>> pool=ThreadedConnectionPool(0,5,<connection_args>)
>>> conn1=pool.getconn()
>>> <do whatever with conn1>
>>> pool.putconn(conn1)
.... repeat later, or perhaps "simultaneously" in a different thread.

and my understanding was that the pool logic was something like the following:

- 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
- perhaps (or perhaps not), after some time, unused connections would be closed and purged from the pool to prevent large numbers of only used once connections from laying around.

However, in some testing I just did, this doesn't appear to be the case, at least based on the postgresql logs. Running the following code:

>>> pool=ThreadedConnectionPool(0,5,<connection_args>)
>>> conn1=pool.getconn()
>>> conn2=pool.getconn()
>>> pool.putconn(conn1)
>>> pool.putconn(conn2)
>>> conn3=pool.getconn()
>>> pool.putconn(conn3)

produced the following output in the postgresql log:

2017-06-02 14:30:26 AKDT LOG:  connection received: host=::1 port=64786
2017-06-02 14:30:26 AKDT LOG:  connection authorized: user=logger database=flightlogs
2017-06-02 14:30:35 AKDT LOG:  connection received: host=::1 port=64788
2017-06-02 14:30:35 AKDT LOG:  connection authorized: user=logger database=flightlogs
2017-06-02 14:30:46 AKDT LOG:  disconnection: session time: 0:00:19.293 user=logger database=flightlogs host=::1 port=64786
2017-06-02 14:30:53 AKDT LOG:  disconnection: session time: 0:00:17.822 user=logger database=flightlogs host=::1 port=64788
2017-06-02 14:31:15 AKDT LOG:  connection received: host=::1 port=64790
2017-06-02 14:31:15 AKDT LOG:  connection authorized: user=logger database=flightlogs
2017-06-02 14:31:20 AKDT LOG:  disconnection: session time: 0:00:05.078 user=logger database=flightlogs host=::1 port=64790

Since I set the maxconn parameter to 5, and only used 3 connections, I wasn't expecting to see any disconnects - and yet as soon as I do putconn, I *do* see a disconnection. Additionally, I would have thought that when I pulled connection 3, there would have been two connections available, and so it wouldn't have needed to connect again, yet it did. Even if I explicitly say close=False in the putconn call, it still closes the connection and has to open

What am I missing? From this testing, it looks like I get no benefit at all from having the connection pool, unless you consider an upper limit to the number of simultaneous connections a benefit? :-) Maybe a little code savings from not having to manually call connect and close after each connection, but that's easily gained by simply writing a context manager. I could get *some* limited benefit by raising the minconn value, but then I risk having connections that are *never* used, yet still taking resources on the DB server.

Ideally, it would open as many connections as are needed, and then leave them open for future requests, perhaps with an "idle" timeout. Is there any way to achieve this behavior?

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------