psycopg_pool – Connection pool implementations#

A connection pool is an object to create and maintain a specified amount of PostgreSQL connections, reducing the time requested by the program to obtain a working connection and allowing an arbitrary large number of concurrent threads or tasks to use a controlled amount of resources on the server. See Connection pools for more details and usage pattern.

This module implement two connection pools: ConnectionPool is a synchronous connection pool yielding Connection objects and can be used by multithread applications. AsyncConnectionPool has a similar interface, but with asyncio functions replacing blocking functions, and yields AsyncConnection instances.

The intended (but not mandatory) use is to create a single connection pool, as a global object exposed by a module in your application, and use the same instance from the rest of the code (especially the ConnectionPool.connection() method).

Note

The psycopg_pool package is distributed separately from the main psycopg package: use pip install psycopg[pool] or pip install psycopg_pool to make it available. See Installing the connection pool.

The ConnectionPool class#

class psycopg_pool.ConnectionPool(conninfo='', *, connection_class=<class 'psycopg.Connection'>, configure=None, reset=None, **kwargs)#

This class implements a connection pool serving Connection instances (or subclasses). The constructor has alot of arguments, but only conninfo and min_size are the fundamental ones, all the other arguments have meaningful defaults and can probably be tweaked later, if required.

Parameters:

  • conninfo (str) – The connection string. See connect() for details.

  • min_size (int, default: 4) – The minimum number of connection the pool will hold. The pool will actively try to create new connections if some are lost (closed, broken) and will try to never go below min_size

  • max_size (int, default: None) – The maximum number of connections the pool will hold. If None, or equal to min_size, the pool will not grow or shrink. If larger than min_size, the pool can grow if more than min_size connections are requested at the same time and will shrink back after the extra connections have been unused for more than max_idle seconds.

  • kwargs (dict) – Extra arguments to pass to connect(). Note that this is one dict argument of the pool constructor, which is expanded as connect() keyword parameters.

  • connection_class (type, default: Connection) – The class of the connections to serve. It should be a Connection subclass.

  • configure (Callable[[Connection], None]) – A callback to configure a connection after creation. Useful, for instance, to configure its adapters. If the connection is used to run internal queries (to inspect the database) make sure to close an eventual transaction before leaving the function.

  • reset (Callable[[Connection], None]) – A callback to reset a function after it has been returned to the pool. The connection is guaranteed to be passed to the reset() function in “idle” state (no transaction). When leaving the reset() function the connection must be left in idle state, otherwise it is discarded.

  • name (str) – An optional name to give to the pool, useful, for instance, to identify it in the logs if more than one pool is used. if not specified pick a sequential name such as pool-1, pool-2, etc.

  • timeout (float, default: 30 seconds) – The default maximum time in seconts that a client can wait to receive a connection from the pool (using connection() or getconn()). Note that these methods allow to override the timeout default.

  • max_waiting (int, default: 0) – Maximum number of requests that can be queued to the pool, after which new requests will fail, raising TooManyRequests. 0 means no queue limit.

  • max_lifetime (float, default: 1 hour) – The maximum lifetime of a connection in the pool, in seconds. Connections used for longer get closed and replaced by a new one. The amount is reduced by a random 10% to avoid mass eviction.

  • max_idle (float, default: 10 minutes) – Maximum time, in seconds, that a connection can stay unused in the pool before being closed, and the pool shrunk. This only happens to connections more than min_size, if max_size allowed the pool to grow.

  • reconnect_timeout (float, default: 5 minutes) – Maximum time, in seconds, the pool will try to create a connection. If a connection attempt fails, the pool will try to reconnect a few times, using an exponential backoff and some random factor to avoid mass attempts. If repeated attempts fail, after reconnect_timeout second the connection attempt is aborted and the reconnect_failed callback invoked.

  • reconnect_failed (Callable[[ConnectionPool], None]) – Callback invoked if an attempt to create a new connection fails for more than reconnect_timeout seconds. The user may decide, for instance, to terminate the program (executing sys.exit()). By default don’t do anything: restart a new connection attempt (if the number of connection fell below min_size).

  • num_workers (int, default: 3) – Number of background worker threads used to maintain the pool state. Background workers are used for example to create new connections and to clean up connections when they are returned to the pool.

wait(timeout=30.0)#

Wait for the pool to be full (with min_size connections) after creation.

Raise PoolTimeout if not ready within timeout sec.

Calling this method is not mandatory: you can try and use the pool immediately after its creation. The first client will be served as soon as a connection is ready. You can use this method if you prefer your program to terminate in case the environment is not configured properly, rather than trying to stay up the hardest it can.

connection(timeout=None)#

Context manager to obtain a connection from the pool.

Return the connection immediately if available, otherwise wait up to timeout or self.timeout seconds and throw PoolTimeout if a connection is not available in time.

Upon context exit, return the connection to the pool. Apply the normal connection context behaviour (commit/rollback the transaction in case of success/error). If the connection is no more in working state replace it with a new one.

Return type:

Iterator[Connection[Any]]

with my_pool.connection() as conn:
    conn.execute(...)

# the connection is now back in the pool
close(timeout=5.0)#

Close the pool and make it unavailable to new clients.

All the waiting and future client will fail to acquire a connection with a PoolClosed exception. Currently used connections will not be closed until returned to the pool.

Wait timeout seconds for threads to terminate their job, if positive. If the timeout expires the pool is closed anyway, although it may raise some warnings on exit.

Note

The pool can be used as context manager too, in which case it will be closed at the end of the block:

with ConnectionPool(...) as pool:
    # code using the pool
name: str#

The name of the pool set on creation, or automatically generated if not set.

min_size#
max_size#

The current minimum and maximum size of the pool. Use resize() to change them at runtime.

resize(min_size, max_size=None)#

Change the size of the pool during runtime.

check()#

Verify the state of the connections currently in the pool.

Test each connection: if it works return it to the pool, otherwise dispose of it and create a new one.

get_stats()#

Return current stats about the pool usage.

Return type:

Dict[str, int]

pop_stats()#

Return current stats about the pool usage.

After the call, all the counters are reset to zero.

Return type:

Dict[str, int]

See Pool stats for the metrics returned.

Functionalities you may not need

getconn(timeout=None)#

Obtain a contection from the pool.

You should preferrably use connection(). Use this function only if it is not possible to use the connection as context manager.

After using this function you must call a corresponding putconn(): failing to do so will deplete the pool. A depleted pool is a sad pool: you don’t want a depleted pool.

Return type:

Connection[Any]

putconn(conn)#

Return a connection to the loving hands of its pool.

Use this function only paired with a getconn(). You don’t need to use it if you use the much more comfortable connection() context manager.

Pool exceptions#

class psycopg_pool.PoolTimeout(*args, info=None, encoding='utf-8')#

The pool couldn’t provide a connection in acceptable time.

Subclass of ~psycopg.OperationalError

class psycopg_pool.PoolClosed(*args, info=None, encoding='utf-8')#

Attempt to get a connection from a closed pool.

Subclass of ~psycopg.OperationalError

class psycopg_pool.TooManyRequests(*args, info=None, encoding='utf-8')#

Too many requests in the queue waiting for a connection from the pool.

Subclass of ~psycopg.OperationalError

The !AsyncConnectionPool class#

!AsyncConnectionPool has a very similar interface to the ConnectionPool class but its blocking method are implemented as async coroutines. It returns ~psycopg.AsyncConnection instances, or its subclasses if specified so in the connection_class parameter.

Only the function with different signature from !ConnectionPool are listed here.

class psycopg_pool.AsyncConnectionPool(conninfo='', *, connection_class=<class 'psycopg.AsyncConnection'>, configure=None, reset=None, **kwargs)#

All the other parameters are the same.

Parameters:

  • connection_class (!type, default: ~psycopg.AsyncConnection) – The class of the connections to serve. It should be an !AsyncConnection subclass.

  • configure (async Callable[[AsyncConnection], None]) – A callback to configure a connection after creation.

  • reset (async Callable[[AsyncConnection], None]) – A callback to reset a function after it has been returned to the pool.

async wait(timeout=30.0)#
connection(timeout=None)#
Return type:

AsyncIterator[AsyncConnection[Any]]

async with my_pool.connection() as conn:
    await conn.execute(...)

# the connection is now back in the pool
async close(timeout=5.0)#

Note

The pool can be used as context manager too, in which case it will be closed at the end of the block:

async with AsyncConnectionPool(...) as pool:
    # code using the pool
async resize(min_size, max_size=None)#
async check()#
async getconn(timeout=None)#
Return type:

AsyncConnection[Any]

async putconn(conn)#