docs/api/pool.rst
!psycopg_pool -- Connection pool implementations.. index:: double: Connection; Pool
.. module:: psycopg_pool
A connection pool is an object used to create and maintain a limited 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
:ref:connection-pools for more details and usage pattern.
This package exposes a few connection pool classes:
ConnectionPool is a synchronous connection pool yielding
~psycopg.Connection objects and can be used by multithread applications.
AsyncConnectionPool has an interface similar to !ConnectionPool, but
with asyncio functions replacing blocking functions, and yields
~psycopg.AsyncConnection instances.
NullConnectionPool is a !ConnectionPool subclass exposing the same
interface of its parent, but not keeping any unused connection in its state.
See :ref:null-pool for details about related use cases.
AsyncNullConnectionPool has the same behaviour of the
!NullConnectionPool, but with the same async interface of the
!AsyncConnectionPool.
.. 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 :ref:pool-installation.
The version numbers indicated in this page refer to the !psycopg_pool
package, not to psycopg.
!ConnectionPool class.. autoclass:: ConnectionPool
This class implements a connection pool serving ~psycopg.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.
:param conninfo: The connection string. See ~psycopg.Connection.connect()
for details. If it is a callable it will be called at
every connection attempt.
:type conninfo: !str or !Callable[[], str]
:param connection_class: The class of the connections to serve. It should
be a !Connection subclass.
:type connection_class: !type, default: ~psycopg.Connection
:param kwargs: Extra arguments to pass to !connect(). Note that this is
one dict argument of the pool constructor, which is
expanded as connect() keyword parameters. If it is a
callable it will be called at every connection attempt.
:type kwargs: !dict[str, Any] or !Callable[[], dict[str, Any]]
:param min_size: 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.
:type min_size: !int, default: 4
:param max_size: 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.
:type max_size: !int, default: !None
:param open: If !True, open the pool, creating the required connections,
on init. If !False, open the pool when !open() is called or
when the pool context is entered. See the open() method
documentation for more details.
:type open: !bool, default: !True
:param configure: 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.
:type configure: Callable[[Connection], None]
:param check: A callback to check that a connection is working correctly
when obtained by the pool. The callback is called at every
getconn() or connection(): the connection is only passed
to the client if the callback doesn't throw an exception.
By default no check is made on the connection. You can
provide the check_connection() pool static method if you
want to perform a simple check.
:type check: Callable[[Connection], None]
:param close_returns: If !True, calling ~psycopg.Connection.close() on
the connection will not actually close it, but it
will return the connection to the pool, like in
~ConnectionPool.putconn(). Use it if you want to
use Psycopg pool with SQLAlchemy. See
:ref:pool-sqlalchemy.
:type close_returns: !bool, default: !False
:param reset: 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.
:type reset: Callable[[Connection], None]
:param name: 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.
:type name: !str
:param timeout: The default maximum time in seconds 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.
:type timeout: !float, default: 30 seconds
:param max_waiting: Maximum number of requests that can be queued to the
pool, after which new requests will fail, raising
TooManyRequests. 0 means no queue limit.
:type max_waiting: !int, default: 0
:param max_lifetime: 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 amount up to 5% to avoid mass eviction.
:type max_lifetime: !float, default: 1 hour
:param max_idle: 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.
:type max_idle: !float, default: 10 minutes
:param reconnect_timeout: 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.
:type reconnect_timeout: !float, default: 5 minutes
:param reconnect_failed: 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).
:type reconnect_failed: Callable[[ConnectionPool], None]
:param num_workers: 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.
:type num_workers: !int, default: 3
.. versionchanged:: 3.1
added !open parameter to the constructor.
.. versionchanged:: 3.2
added !check parameter to the constructor.
.. versionchanged:: 3.2
the class is generic and !connection_class provides types type
variable. See :ref:pool-generic.
.. versionchanged:: 3.3
added !close_returns parameter to the constructor.
.. versionchanged:: 3.3
conninfo and kwargs can be callable.
.. warning::
At the moment, the default value for the `!open` parameter is `!True`;
In a future version, the default will be changed to `!False`.
If you expect the pool to be open on creation even if you don't use
the pool as context manager, you should specify the parameter
`!open=True` explicitly.
Starting from psycopg_pool 3.2, a warning is raised if the pool is
used with the expectation of being implicitly opened in the
constructor and `!open` is not specified.
.. automethod:: connection
.. code:: python
with my_pool.connection() as conn:
conn.execute(...)
# the connection is now back in the pool
.. versionchanged:: 3.2
The connection returned is annotated as defined in `!connection_class`.
See :ref:`pool-generic`.
.. automethod:: open
.. versionadded:: 3.1
.. automethod:: close
.. note::
The pool can be also used as a context manager, in which case it will
be opened (if necessary) on entering the block and closed on exiting it:
.. code:: python
with ConnectionPool(...) as pool:
# code using the pool
.. autoattribute:: closed
.. automethod:: wait
.. attribute:: name :type: str
The name of the pool set on creation, or automatically generated if not
set.
.. autoattribute:: min_size .. autoattribute:: max_size
The current minimum and maximum size of the pool. Use `resize()` to
change them at runtime.
.. automethod:: resize .. automethod:: check .. automethod:: check_connection
.. versionadded:: 3.2
.. automethod:: drain
.. versionadded:: 3.3
.. automethod:: get_stats .. automethod:: pop_stats
See :ref:`pool-stats` for the metrics returned.
.. rubric:: Functionalities you may not need
.. automethod:: getconn .. automethod:: putconn
.. autoclass:: PoolTimeout()
Subclass of ~psycopg.OperationalError
.. autoclass:: PoolClosed()
Subclass of ~psycopg.OperationalError
.. autoclass:: TooManyRequests()
Subclass of ~psycopg.OperationalError
!AsyncConnectionPool class!AsyncConnectionPool has a very similar interface to the ConnectionPool
class but its blocking methods are implemented as !async coroutines. It
returns instances of ~psycopg.AsyncConnection, or of its subclass if
specified so in the !connection_class parameter.
Only the functions and parameters with different signature from
!ConnectionPool are listed here.
.. autoclass:: AsyncConnectionPool
:param conninfo: The connection string. It can be an async function too.
:type conninfo: !str or !Callable[[], str] or async Callable[[], str]
:param connection_class: The class of the connections to serve. It should
be an !AsyncConnection subclass.
:type connection_class: !type, default: ~psycopg.AsyncConnection
:param kwargs: Extra arguments to pass to !connect(). It can be an async
function too.
:type kwargs: !dict[str, Any] or !Callable[[], dict[str, Any]]
or async !Callable[[], dict[str, Any]]
:param check: A callback to check that a connection is working correctly
when obtained by the pool.
:type check: async Callable[[Connection], None]
:param configure: A callback to configure a connection after creation.
:type configure: async Callable[[AsyncConnection], None]
:param reset: A callback to reset a function after it has been returned to
the pool.
:type reset: async Callable[[AsyncConnection], None]
:param reconnect_failed: Callback invoked if an attempt to create a new
connection fails for more than !reconnect_timeout seconds.
:type reconnect_failed: Callable[[AsyncConnectionPool], None] or
async Callable[[AsyncConnectionPool], None]
.. versionchanged:: 3.2
added !check parameter to the constructor.
.. versionchanged:: 3.2
The !reconnect_failed parameter can be !async.
.. versionchanged:: 3.3
added !close_returns parameter to the constructor.
.. versionchanged:: 3.3
conninfo and kwargs can be callable (sync or async).
.. warning::
Opening an async pool in the constructor (using `!open=True` on init)
will become an error in a future pool versions. Please note that,
currently, `!open=True` is the default; in a future version, the
default for the parameter will be changed to `!False`.
In order to make sure that your code will keep working as expected in
future versions, please specify `!open=False` in the constructor and
use an explicit `!await pool.open()`::
pool = AsyncConnectionPool(..., open=False)
await pool.open()
or use the pool context manager::
async with AsyncConnectionPool(..., open=False) as pool:
...
Starting from psycopg_pool 3.2, opening an async pool in the
constructor raises a warning.
.. automethod:: connection
.. code:: python
async with my_pool.connection() as conn:
await conn.execute(...)
# the connection is now back in the pool
.. automethod:: open .. automethod:: close
.. note::
The pool can be also used as an async context manager, in which case it
will be opened (if necessary) on entering the block and closed on
exiting it:
.. code:: python
async with AsyncConnectionPool(...) as pool:
# code using the pool
All the other constructor parameters are the same of !ConnectionPool.
.. automethod:: wait .. automethod:: resize .. automethod:: check .. automethod:: check_connection
.. versionadded:: 3.2
.. automethod:: getconn .. automethod:: putconn
.. versionadded:: 3.1
The NullConnectionPool is a ConnectionPool subclass which doesn't create
connections preemptively and doesn't keep unused connections in its state. See
:ref:null-pool for further details.
The interface of the object is entirely compatible with its parent class. Its behaviour is similar, with the following differences:
.. autoclass:: NullConnectionPool
All the other constructor parameters are the same as in ConnectionPool.
:param min_size: Always 0, cannot be changed.
:type min_size: !int, default: 0
:param max_size: If None or 0, create a new connection at every request,
without a maximum. If greater than 0, don't create more
than !max_size connections and queue the waiting clients.
:type max_size: !int, default: None
:param reset: It is only called when there are waiting clients in the
queue, before giving them a connection already open. If no
client is waiting, the connection is closed and discarded
without a fuss.
:type reset: Callable[[Connection], None]
:param max_idle: Ignored, as null pools don't leave idle connections sitting around.
.. automethod:: wait .. automethod:: resize .. automethod:: check
The AsyncNullConnectionPool is, similarly, an AsyncConnectionPool subclass
with the same behaviour of the NullConnectionPool.
.. autoclass:: AsyncNullConnectionPool
The interface is the same of its parent class `AsyncConnectionPool`. The
behaviour is different in the same way described for `NullConnectionPool`.