psycopg 3 & asyncpg Connection Pools

This guide is part of Pool Architecture & Algorithm Fundamentals. Python services that talk to PostgreSQL without a heavyweight ORM — data pipelines, async APIs, internal tools, and increasingly Django itself — usually hold connections in one of two pools: psycopg_pool, the pool package that accompanies psycopg 3, or the pool built into asyncpg. They solve the same problem with noticeably different designs. psycopg_pool is a separate, configurable component with background workers, health checks, statistics and both synchronous and asynchronous variants. asyncpg’s pool is small and fast, lives entirely on one event loop, and leaves most policy decisions to the application.

Both are often introduced through a framework — Django 5.1’s "pool" option wraps psycopg_pool, and SQLAlchemy’s asyncpg dialect sits on top of asyncpg connections while using its own pool — but many services use them directly, and the defaults matter more when nothing wraps them. This page explains how each pool works internally, how to size it, how to observe it, and how each behaves behind PgBouncer and other proxies.

Key operational takeaways:

• psycopg_pool.ConnectionPool keeps between min_size and max_size connections, grows on demand using background workers, and fails a request with PoolTimeout after timeout seconds (30 by default). max_waiting bounds the queue; beyond it requests fail immediately with TooManyRequests.
• asyncpg.create_pool defaults to min_size=10 and max_size=10, opens all minimum connections at startup, and waits for a free connection indefinitely unless acquire(timeout=...) is used.
• Both pools reset connections on return — psycopg by rolling back any open transaction, asyncpg by running a reset that releases advisory locks, closes cursors and UNLISTENs — so session state does not leak between borrowers of the same pool.
• Behind PgBouncer in transaction mode before 1.21, disable server-side prepared statements: prepare_threshold=None for psycopg, statement_cache_size=0 for asyncpg.
• Size from concurrent database work, not from thread or task count: an async service with 1,000 concurrent requests may need only 15 connections if each request spends a small fraction of its time in PostgreSQL.

Foundational mechanics

psycopg_pool. A ConnectionPool owns a deque of idle connections, a queue of waiting clients, and a small set of worker threads (num_workers, default 3) that perform slow maintenance off the request path. When a client calls pool.connection(), the pool hands over an idle connection if one exists. If none is idle and the pool is below max_size, the pool schedules a worker to open a new connection and puts the client in the waiting queue; whichever connection becomes available first — the new one or a returned one — goes to the longest-waiting client. That design means a burst of requests does not open a burst of simultaneous connections: growth happens at most num_workers connections at a time, which protects PostgreSQL from connection storms.

When the context manager exits, the pool finishes the transaction — committing on success, rolling back on an exception — and then decides the connection’s fate. A connection in a bad state, or one older than max_lifetime (3,600 seconds by default, with a little jitter so a cohort does not expire together), is closed and replaced by a worker. Otherwise it returns to the idle deque. Connections idle for longer than max_idle (600 seconds) are closed if the pool is above min_size. The optional check callback — ConnectionPool.check_connection is provided — validates a connection before handing it out, and the configure and reset callbacks run once per new connection and on each return respectively.

AsyncConnectionPool mirrors the same design with asyncio tasks in place of threads. NullConnectionPool and its async twin keep no idle connections at all; they open a connection per request but still bound concurrency with max_size and a waiting queue, which suits environments where PgBouncer already pools.

asyncpg. asyncpg.create_pool returns a Pool holding a fixed list of connection holders, max_size of them. On startup the pool connects min_size holders; the rest stay empty. acquire() takes a free holder from an asyncio.LifoQueue, connects it if it is empty, and returns the connection proxy. There is no background worker: connection establishment happens inline in the task that acquired the holder. On release(), the pool calls the connection’s reset — which runs SELECT pg_advisory_unlock_all(); CLOSE ALL; UNLISTEN *; RESET ALL; when the server supports those commands, or a custom reset coroutine if one was supplied — and returns the holder to the queue.

Two asyncpg settings recycle connections: max_queries (50,000) closes a connection after that many queries, and max_inactive_connection_lifetime (300 seconds) closes connections idle longer than that. There is no age-based lifetime, which matters behind load balancers that rebalance only on new connections.

A consequence of the asyncpg design is that a pool is bound to the event loop it was created on. Creating a pool at import time and using it from a different loop — common in test suites and in frameworks that start their own loop — fails with errors about futures attached to a different loop.

Operational Boundary: These pools manage connections within one Python process. Gunicorn workers, Celery processes and multiple containers each hold their own pool; the fleet total is a budgeting question, not a pool setting.

Precision sizing & timeout orchestration

For threaded code, concurrency is bounded by thread count, and a pool larger than the number of threads that can touch the database is wasted. For asyncio code, concurrency is bounded by nothing unless the application bounds it, so the pool size is effectively the concurrency limit for database work.

The sizing formula is the same for both:

max_size = ceil( requests_per_second × queries_per_request × mean_query_seconds ÷ target_utilisation )

A FastAPI-style service handling 600 requests per second per process, with 3 queries per request at 4 ms each, has 600 × 3 × 0.004 = 7.2 queries in flight on average. At 70 % utilisation, max_size = 11. The same process may be serving 300 concurrent requests, because most of each request’s time is spent awaiting other services; a pool sized to request concurrency would open 300 connections for 7 queries’ worth of work.

Order the deadlines from the inside out. The pool wait must expire before the request deadline, and the statement timeout must be shorter still than the pool wait plus the request’s remaining work budget. With a 5-second gateway timeout, a 2-second pool timeout and a 2-second statement timeout leave margin for application work, and a saturated pool fails with an explicit PoolTimeout or asyncio.TimeoutError rather than an upstream 504.

Production configuration examples

psycopg_pool for a threaded service. Open the pool explicitly at startup, wait for the minimum connections, and close it at shutdown:

from psycopg_pool import ConnectionPool
from psycopg.rows import dict_row

def configure(conn):
    # runs once per new physical connection
    conn.execute("SET application_name = 'billing-worker'")
    conn.commit()

pool = ConnectionPool(
    conninfo="host=billing-db.internal dbname=billing user=billing_app",
    min_size=2,
    max_size=12,
    timeout=3.0,            # seconds to wait before PoolTimeout
    max_waiting=48,         # reject beyond this queue length
    max_lifetime=1800,
    max_idle=300,
    check=ConnectionPool.check_connection,
    configure=configure,
    kwargs={"row_factory": dict_row, "prepare_threshold": 5},
    name="billing-primary",
    open=False,
)
pool.open(wait=True, timeout=30)

def unpaid_invoices(customer_id):
    with pool.connection() as conn:            # commits on success, rolls back on error
        return conn.execute(
            "SELECT id, total FROM invoices WHERE customer_id = %s AND paid_at IS NULL",
            (customer_id,),
        ).fetchall()

AsyncConnectionPool in an async application. Use the framework’s lifespan to open and close it on the right event loop:

from contextlib import asynccontextmanager
from psycopg_pool import AsyncConnectionPool

@asynccontextmanager
async def lifespan(app):
    app.state.pool = AsyncConnectionPool(
        "host=orders-db.internal dbname=orders user=orders_app",
        min_size=2, max_size=11, timeout=2.0, max_waiting=100,
        check=AsyncConnectionPool.check_connection, open=False, name="orders-primary",
    )
    await app.state.pool.open(wait=True)
    yield
    await app.state.pool.close()

asyncpg with bounded acquisition. asyncpg’s defaults open ten connections per process at startup and wait forever for a free one; set both explicitly:

import asyncpg

async def init_connection(conn):
    await conn.execute("SET application_name = 'orders-api'")

pool = await asyncpg.create_pool(
    dsn="postgresql://orders_app@orders-db.internal/orders",
    min_size=2,
    max_size=11,
    max_inactive_connection_lifetime=120.0,
    command_timeout=2.0,
    statement_cache_size=100,
    init=init_connection,
)

async def get_order(order_id):
    async with pool.acquire(timeout=2.0) as conn:      # asyncio.TimeoutError after 2 s
        return await conn.fetchrow("SELECT id, status, total FROM orders WHERE id = $1", order_id)

Transactions. In psycopg, with pool.connection() as conn: with conn.transaction(): ... scopes an explicit transaction. In asyncpg, async with conn.transaction(): does the same. In both cases, keep network calls to other services outside the transaction block: the connection is held for the duration of the block whether or not a query is running.

Diagnostics & telemetry

psycopg_pool statistics. pool.get_stats() returns counters and gauges; pool.pop_stats() returns them and resets the counters, which suits periodic export:

stats = pool.pop_stats()
# {'pool_min': 2, 'pool_max': 12, 'pool_size': 12, 'pool_available': 0,
#  'requests_waiting': 9, 'requests_num': 18231, 'requests_queued': 2210,
#  'requests_wait_ms': 511023, 'requests_errors': 14, 'usage_ms': 2031331,
#  'connections_num': 31, 'connections_ms': 1488, 'connections_errors': 0, 'connections_lost': 2}

Derived values matter more than raw ones: requests_queued ÷ requests_num is the share of requests that waited (12 % above), requests_wait_ms ÷ requests_queued the mean wait of those that did (231 ms), and usage_ms ÷ requests_num the mean hold time (111 ms). requests_errors counts PoolTimeout and TooManyRequests; any increase is a user-visible failure.

asyncpg pool state. The pool exposes get_size(), get_idle_size(), get_min_size() and get_max_size(). There is no built-in wait counter, so wrap acquisition to measure it:

import time
from prometheus_client import Histogram, Gauge

ACQUIRE = Histogram("asyncpg_pool_acquire_seconds", "Time to acquire a connection",
                    buckets=(0.001, 0.005, 0.01, 0.05, 0.1, 0.5, 1, 2))
IN_USE = Gauge("asyncpg_pool_in_use", "Connections checked out")

async def acquire(pool, timeout=2.0):
    start = time.perf_counter()
    conn = await pool.acquire(timeout=timeout)
    ACQUIRE.observe(time.perf_counter() - start)
    IN_USE.set(pool.get_size() - pool.get_idle_size())
    return conn

Exhaustion versus starvation. A pool whose connections are all in use and whose PostgreSQL sessions are active with short query times is undersized for the load. A pool whose connections are all in use while PostgreSQL shows them idle or idle in transaction is starved by code that holds connections without using them — usually a transaction block wrapped around network I/O, or an acquire() without a matching release() in an error path. The server-side view comes from pg_stat_activity grouped by application_name, which is why both examples above set it on every new connection.

Integration & proxy compatibility

PgBouncer in transaction mode. Both drivers use server-side prepared statements, which PgBouncer before 1.21 cannot track across server connections. psycopg 3 prepares a statement automatically after it has been executed prepare_threshold times (5 by default); set prepare_threshold=None to disable preparation. asyncpg prepares every query through its statement cache; statement_cache_size=0 disables the cache, though asyncpg still uses unnamed prepared statements internally, which PgBouncer handles. With PgBouncer 1.21 or later and max_prepared_statements above zero, both drivers’ prepared statements work. The details and version matrix are in Using asyncpg Behind PgBouncer Transaction Mode.

Reset queries behind a proxy. asyncpg’s release reset runs RESET ALL and related commands, which is harmless behind PgBouncer but adds a round trip per release. Recent asyncpg releases accept a reset coroutine in create_pool that replaces the default reset; a minimal reset removes the extra round trip, but is safe only if no code ever sets session state. psycopg’s pool, by contrast, only rolls back when a transaction was left open.

RDS Proxy. Prepared statements and session-level SET pin connections behind RDS Proxy. Use SET only inside transactions with SET LOCAL, and prefer the configure/init callbacks for settings that can instead be applied as role defaults with ALTER ROLE ... SET.

Django and SQLAlchemy. Django’s pool option constructs a psycopg_pool.ConnectionPool from the "pool" dictionary, so every parameter above applies — see Enabling Django Connection Pooling with psycopg3. SQLAlchemy’s postgresql+asyncpg dialect uses asyncpg connections inside SQLAlchemy’s own AsyncAdaptedQueuePool, so asyncpg’s pool parameters do not apply there; SQLAlchemy’s pool_size and max_overflow do.

Common failure patterns & remediation

The step-by-step guides for the most common incidents are Configuring psycopg_pool ConnectionPool for Production, Diagnosing psycopg_pool PoolTimeout Errors and Tuning asyncpg Pool Size and Statement Cache.

Frequently Asked Questions

Related

• Pool Architecture & Algorithm Fundamentals — the parent overview of pool topology and lifecycle.
• Configuring psycopg_pool ConnectionPool for Production — every psycopg_pool parameter with production values.
• Tuning asyncpg Pool Size and Statement Cache — sizing and caching for asyncpg.
• FastAPI SQLAlchemy Pool Configuration — when SQLAlchemy’s pool sits on top of asyncpg.
• Node.js Async Connection Limits — the same unbounded-concurrency problem in another async runtime.