Context manager

In modern Python it is expected that resources are handled by a context manager, so the canonical way to use a pooled connection should be:

with pool.connection() as conn:
    cur = conn.cursor()
    cur.execute("...")
    consume_data(cur.fetchall())

Note that, because there is a Connection.execute() method, the minimal use would be:

with pool.connection() as conn:
    conn.execute(DML_QUERY)

Blocking behaviour

Another important usability point is the behaviour in the face of an exhausted pool: the sane behaviour here is to block: not forever, but until either a connection is available or until a timeout expires, and only then throw an exception, so that in the face of a spike there is a buffer before requests start to get killed. I would expect no crash here:

async def worker(conn, work_for_sec):
    await conn.execute("select pg_sleep(%s)", (work_for_sec,))

pool = psycopg3.AsyncPool(maxconn=4, connection_timeout_sec=1.0):
for i in range(8):
    async with pool.connection():
        create_task(worker(conn, work_for_sec=0.5))

Which also illustrates that, as many other objects in psycopg3, there would be an asyncio version of the object and a sync one (which would work with normal threads and with Eventlet/gevent green threads).

Connection configuration

There should be a way to configure a Python connection, for instance to register adapters, before it is available to the application. Is subclassing the best way to do it? Someone might find more useful to choose dynamically what configuration to use.

# Subclassing:

class MyPool(psycopg3.pool):
    def configure(self, conn):
        MyAdapter.register(conn)
        return conn

pool = MyPool()

# Configuring:

def my_configure(self, conn):
    MyAdapter.register(conn)
    return conn

pool = psycopg3.Pool(configure_func=my_configure)

Exclusive connections?

Should there be a way to create an exclusive connection? Sometimes I need one, in an otherwise pooled application, e.g. to receive notifications:

def listen_notifications():
    with pool.exclusive() as conn:
        conn.autocommit = True
        for notify in conn.cursor().notifies():
            handle_notification(notify)

But I don't think there is such need if the connection parameters are made available on the pool:

pool = Pool(maxconn=config.pool_maxconn, args=(config.db_dsn,))

def listen_notifications():
    with psycopg3.connect(*pool.args, **pool.kwargs):
        conn.autocommit = True
        for notify in conn.cursor().notifies():
            handle_notification(notify)

which leaves to the user the choice between what connection class to use, how to configure it, etc. So that's probably not so useful.

Pool worker

If we desire the pool to be a fast provider of connection, not slowing down the application operations, certain things should happen behind the scene:

• do we have a spike, so we need more connections?
• do we have a moment of calm, so we can do with less connections?
• are the connections in the pool still sane?

A trivial behaviour would be something like "if there are no connections available create a new one":

class Pool:
    def getconn(self):
        conn = self._get_a_connection_from_the_pool()
        if conn:
            return conn
        else:
            conn = psycopg3.connect(*self.args, **self.kwargs)
            self._put_it_in_the_pool_when_done(conn)
            return conn

This interesting article shows that this might not be the best way to do it, especially if the connection time is particularly long compared to the processing time. Growing the pool could be demanded to a background worker, following a strategy like:

class Pool:
    def getconn(self):
        if not self._pool():
            self.worker.create_a_new_connection_and_put_it_in_the_pool()
        return self.wait_for_a_connection_from_the_pool()

If there is a workers infrastructure in place there can me more jobs for them: periodically checking for the state of the unused connections in the pool, for instance, or closing them if they have been alive more than a configured amount of time (30 minutes?) and replacing them with a fresh one, (or not replacing them in case we have more than minconn connections and a previous spike has now passed).

What to do in case of connection failure?

The pool acts as a shield helping the application to reconnect in case the connection with the database is lost. However, when does this behaviour stop being sane? If the database is unreachable - maybe misconfigured, maybe moved - there is a risk that the application doesn't die, because the pool keeps insisting to reconnect, but it doesn't work either, because there is no working database connection.

In my experience with live systems this can be a difficult condition to diagnose: several times I have wished that an application, screaming loud on a dashboard, instead of trying to stay up complaining quietly in some logging stream. On the other hand maybe the state the application holds is precious enough that it can be worth to wait a few minutes before crashing and burning.

A first step towards a sane behaviour could be to die early, on startup, if the connection is not working when the first pool population is done: the program would fail hard and fast if the database - assumed to be a prerequisite - is nowhere to be found.

What if the database is missing in action during the program lifetime? Following connection attempts may be repeated, with an exponential backoff, until dying after a few minutes of fruitless attempts (with a sys.exit(1) or some other termination function which might be subclassed).

Connections usage pattern

In which order should the connections in the pool be used? As a stack (put back a connection on top of the stack, it will be the next one to use)? As a queue (put back the connection at the bottom of the queue, try to use them uniformly)? Randomly (whatever comes out from whatever hash map used to keep the connection)?

Is there a reason to prefer one way or the other? ISTM that a stack behaviour allows a better reuse of prepared statements and an easy implementation of the max_idle_sec parameter (get connections from the top of the stack, evict idle ones from the bottom). I am sure I haven't thought about this well enough though.

Key configuration parameters

These parameter would be passed to the class constructor.

minconn:

Minimum number of connections to keep open in the pool. If some are closed the pool should try to create new ones as quickly as possible to replace them. Proposed default: 4 (very defensive: to enable the pooling behaviour but to avoid to saturate a server unless configured up).

maxconn:

Maximum number of connections to open at any given time. If maxconn == minconn no extra connection is created in case more client requests arrive: they will just wait until a connection is made available again. If maxconn > minconn the pool can create new connections if the demand increases; later the extra connections created may be closed if deemed no more required (default: minconn).

args, kwargs:

Arguments to pass to the connection_factory to create a new connection (default: empty, connect as per PG* env vars, like psycopg3.connect()).

connection_factory:

The connection class to create (default: Connection for Pool, AsyncConnection for AsyncPool).

configure_function:

A function to configure the connection after its creation and before making it available to the pool. Alternative to subclassing the Pool class to configure Pool.configure(). It can be a callable taking a connection as parameter, or the dotted name of such callable, so that e.g. the function name might be passed to the application as an env var.

timeout_sec:

Default timeout before raising an exception if a connection cannot be served. It may be overridden by Pool.connection(timeout_sec=...) (default: 30 sec).

max_lifetime_sec:

Maximum time a connection should be kept in the pool and used. After such time, when the connection is not in use, it can be closed and replaced by a new one (default: 30 minutes - 10% random factor to avoid mass evictions).

max_idle_sec:

Time a connection can sit idle in the pool before being removed. Only connections above minconn are removed, if maxconn allows to create them (default: 10 min + 10% random).

terminate_after_sec:

Number of seconds to wait for a reconnection attempt before giving up and terminate the program, calling Pool.terminate() (default: 5 min, starting from 1sec ± 10%, doubled up until the precise threshold, then kaputt).

num_workers:

Number of background workers to perform maintenance tasks. If set to 0 the dynamic characteristics of the pool might be downgraded (e.g. creating a new connection will happen in the requesting thread, blocking it for the time it takes). Background jobs might be executed by the application calling get_maintenance_task() (default: 3).