Tuning asyncpg Pool Size and Statement Cache

This guide is part of psycopg 3 & asyncpg Connection Pools. asyncpg’s pool has two defaults that shape a service’s behaviour more than any other setting: min_size and max_size are both 10, so every process opens exactly ten connections at startup and never more, and acquire() waits indefinitely when all ten are busy. The result is either too many connections at idle across a large fleet, or requests that hang with no error under load:

# 40 worker processes × 10 connections, mostly idle
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

# or, under load, no error at all — just requests that never complete
asyncio.TimeoutError          # only if acquire(timeout=...) was used

A third default matters once a proxy is involved: statement_cache_size is 100, so asyncpg prepares and caches statements on the server, which transaction-mode PgBouncer before 1.21 cannot track. The objective is a pool sized from real demand, an acquisition that always fails rather than hangs, and a statement cache matched to the query mix and the connection path.

Rapid incident diagnosis

Read the pool’s own numbers. asyncpg exposes size and idleness, from which in-use follows:

print({
    "min": pool.get_min_size(),
    "max": pool.get_max_size(),
    "size": pool.get_size(),          # holders with a live connection
    "idle": pool.get_idle_size(),
    "in_use": pool.get_size() - pool.get_idle_size(),
})

in_use equal to max means every connection is checked out and further acquire() calls are queued — indefinitely, unless they passed a timeout.

Confirm from the server side. With application_name set in the pool’s init callback, group the service’s sessions:

SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE application_name = 'orders-api'
GROUP BY state;

• Sessions active with short queries and in_use = max: the pool is too small.
• Sessions idle with in_use = max: connections are held by tasks doing non-database work, typically inside async with conn.transaction(): around an HTTP call.
• Sessions well below max at all times: the pool is larger than needed, and min_size may be holding idle connections for no reason.

Look for statement cache errors. Behind a transaction-mode pooler, cached prepared statements produce distinctive failures:

asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "__asyncpg_stmt_4__" already exists
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_9__" does not exist

Both mean the cache is on while connections are being multiplexed underneath asyncpg.

Check for schema-change fallout. asyncpg caches the plan and the result types of a prepared statement. After a migration changes a column type, cached statements can fail with InvalidCachedStatementError; asyncpg handles this by invalidating its cache, but a burst of these right after a deploy is a signal that migrations and long-lived connections overlapped.

Mathematical sizing or parameter formula

Pool maximum. asyncio makes request concurrency large and query concurrency small; size for the latter:

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

A service handling 600 requests per second per process with 3 queries of 4 ms each: 600 × 3 × 0.004 = 7.2 in flight, so max_size = 11.

Pool minimum. min_size connections are opened at pool creation and never closed by the idle reaper, so the fleet holds processes × min_size permanently:

min_size ≈ ceil( average_in_flight_at_trough )
fleet_idle_cost = processes × min_size

If the trough is one connection in flight, min_size = 2 is enough to avoid opening connections on the request path during normal traffic, and 40 processes then hold 80 connections at idle instead of 400.

Acquisition timeout. Choose it from the request deadline, not from patience:

acquire_timeout ≤ request_deadline − p99_query_time − application_work − margin

With a 3-second deadline, 40 ms of p99 query time and 200 ms of other work, a 2-second acquire timeout leaves ample margin and still fails long before the client gives up.

Statement cache. Each cached statement costs memory on both sides — the plan on the server, metadata in the client — per connection:

server_prepared_statements ≈ processes × max_size × distinct_statements_cached

With 40 processes, max_size = 11 and 100 distinct statements, the fleet can hold 44,000 prepared statements on the server. That is usually acceptable on PostgreSQL, which has no global limit equivalent to MySQL’s, but it is memory in each backend. Size the cache to the number of distinct query texts the service actually issues:

statement_cache_size ≈ distinct_query_texts × 1.2

A service with 40 distinct queries gains nothing from a cache of 100; one that generates SQL with variable IN lists creates unbounded distinct texts and should either normalise them (= ANY($1)) or reduce the cache.

Exact remediation & configuration

Create the pool with explicit sizes and callbacks, inside the running loop:

import asyncpg
from contextlib import asynccontextmanager
from fastapi import FastAPI

async def init_connection(conn: asyncpg.Connection) -> None:
    await conn.execute("SET application_name = 'orders-api'")
    await conn.set_type_codec(
        "jsonb", encoder=json.dumps, decoder=json.loads, schema="pg_catalog")

@asynccontextmanager
async def lifespan(app: FastAPI):
    app.state.pool = await asyncpg.create_pool(
        dsn=os.environ["DATABASE_URL"],
        min_size=2,
        max_size=11,
        max_inactive_connection_lifetime=120.0,   # close idle connections above min_size
        max_queries=50_000,
        command_timeout=2.0,                      # cancel a query that overruns
        statement_cache_size=60,                  # ≈ distinct query texts × 1.2
        init=init_connection,
    )
    yield
    await app.state.pool.close()                  # waits for in-flight work

app = FastAPI(lifespan=lifespan)

Always bound acquisition. Wrap it once so no call site can forget:

from asyncio import TimeoutError as AsyncTimeoutError

class PoolBusy(Exception):
    pass

@asynccontextmanager
async def db(pool: asyncpg.Pool, timeout: float = 2.0):
    try:
        conn = await pool.acquire(timeout=timeout)
    except AsyncTimeoutError as exc:
        raise PoolBusy("no database connection available") from exc
    try:
        yield conn
    finally:
        await pool.release(conn)

Map PoolBusy to HTTP 503 with a short Retry-After so callers back off instead of retrying immediately into a saturated pool.

Keep transactions free of network calls. The connection is held for the whole async with conn.transaction(): block:

# Before
async with db(pool) as conn:
    async with conn.transaction():
        row = await conn.fetchrow("SELECT total FROM orders WHERE id = $1", order_id)
        receipt = await gateway.charge(row["total"])            # holds the connection
        await conn.execute("UPDATE orders SET receipt = $1 WHERE id = $2", receipt.id, order_id)

# After
async with db(pool) as conn:
    row = await conn.fetchrow("SELECT total FROM orders WHERE id = $1", order_id)

receipt = await gateway.charge(row["total"], idempotency_key=str(order_id))

async with db(pool) as conn:
    await conn.execute(
        "UPDATE orders SET receipt = $1 WHERE id = $2 AND receipt IS NULL", receipt.id, order_id)

Set the statement cache for the connection path. Direct to PostgreSQL, or through PgBouncer 1.21+ with max_prepared_statements, keep it enabled. Through older transaction-mode poolers, disable it:

pool = await asyncpg.create_pool(dsn=DSN, min_size=2, max_size=11, statement_cache_size=0)

Normalise list parameters so the cache stays bounded:

# Instead of building "... WHERE id IN ($1, $2, $3)" for every list length:
rows = await conn.fetch("SELECT id, status FROM orders WHERE id = ANY($1::bigint[])", order_ids)

Connection churn and long-lived pools

Two asyncpg settings recycle connections, and both interact with infrastructure in ways worth checking.

max_inactive_connection_lifetime closes connections that have been idle longer than its value, down to min_size. It must be shorter than every idle timeout on the path — NAT gateways, load balancers, PgBouncer’s client_idle_timeout, PostgreSQL’s idle_session_timeout — or the remote end closes first and the next use of that connection fails. A value of 120 seconds is comfortably inside a typical 350-second NAT timeout.

max_queries closes a connection after 50,000 queries by default. On a busy process that is minutes, not hours, which quietly provides the lifetime recycling asyncpg otherwise lacks: it lets connections rebalance after a replica is added, and bounds the lifetime of any server-side state. If your service issues few but long queries, connections can live for days; in that case add explicit recycling by closing connections in the init path on age, or accept that rebalancing will not happen.

There is no keepalive setting on the pool, but the DSN accepts libpq keepalive parameters, which keep NAT translations alive and detect dead peers:

dsn = ("postgresql://orders_app@orders-db.internal/orders"
       "?keepalives=1&keepalives_idle=30&keepalives_interval=10&keepalives_count=3")

Failover behaviour. When PostgreSQL restarts or fails over, checked-out connections raise ConnectionDoesNotExistError or InterfaceError on next use; asyncpg discards them and the pool opens replacements on demand. Because connection establishment happens inline in the acquiring task, a failover can produce a burst of simultaneous connection attempts — one per waiting task, up to max_size. Keeping max_size modest is therefore also a failover safety measure.

Validation & verification

Connection count matches the configuration. At idle, the server must show processes × min_size sessions for the service; at peak, no more than processes × max_size:

SELECT count(*) FROM pg_stat_activity WHERE application_name = 'orders-api';

Acquisition fails instead of hanging. In staging, set max_size=1, hold a connection in one task, and issue a second request. It must fail within the acquire timeout with a 503, not hang:

hey -n 20 -c 20 https://staging-orders.internal/orders/42 | grep -E "\[503\]|\[200\]|Slowest"

Statement cache behaves. Through a transaction-mode pooler with the cache disabled, run the full endpoint suite and confirm no DuplicatePreparedStatementError or InvalidSQLStatementNameError appears in logs. Directly against PostgreSQL with the cache enabled, check that the number of prepared statements per backend stabilises rather than growing:

SELECT count(*) FROM pg_prepared_statements;   -- run on a session; per-backend view

Idle reaping works. Drive load, stop it, and watch the session count fall to min_size within about max_inactive_connection_lifetime plus a few seconds.

Frequently Asked Questions

Related

• psycopg 3 & asyncpg Connection Pools — the parent guide comparing both pools.
• Using asyncpg Behind PgBouncer Transaction Mode — the proxy-specific settings in detail.
• Scoping Async SQLAlchemy Sessions in FastAPI — when SQLAlchemy owns the pool above asyncpg.
• Tuning SQLAlchemy pool_size and max_overflow — the equivalent sizing for SQLAlchemy engines.