Configuring psycopg_pool ConnectionPool for Production

This guide is part of psycopg 3 & asyncpg Connection Pools. A ConnectionPool created with only a connection string works in development and misbehaves in production in predictable ways: max_size silently equals min_size, so the pool never grows beyond four connections; the waiting queue is unbounded, so an overloaded service accumulates thousands of waiting threads; a request waits a full 30 seconds before failing; and nothing validates connections that a firewall closed overnight. The first incident usually looks like this:

psycopg_pool.PoolTimeout: couldn't get a connection after 30.00 sec
psycopg.OperationalError: consuming input failed: server closed the connection unexpectedly
	This probably means the server terminated abnormally before or while processing the request.

The objective is a pool whose every parameter is chosen for the service: bounded size and queue, a timeout inside the request deadline, lifetimes that suit the network and the database, validation where it pays for itself, and an explicit open and close tied to the application lifecycle.

Rapid incident diagnosis

Read the pool’s own view first. get_stats() exposes both configuration and live state, which answers “what is this pool actually configured as?” before any theory about load:

from pprint import pprint
pprint(pool.get_stats())
# {'pool_min': 4, 'pool_max': 4, 'pool_size': 4, 'pool_available': 0,
#  'requests_waiting': 57, 'requests_num': 90211, 'requests_queued': 20344,
#  'requests_wait_ms': 18034112, 'requests_errors': 311, 'usage_ms': 9120331,
#  'connections_num': 6, 'connections_lost': 2}

pool_max equal to pool_min at 4 is the classic default: max_size was never set. requests_waiting at 57 means 57 threads are blocked in pool.connection() right now.

Check whether connections die idle. connections_lost counts connections found broken — by the check callback or when returned in a bad state. A steady increase that correlates with quiet periods points to a network device or the server closing idle connections; a burst of OperationalError: server closed the connection unexpectedly on the first requests after a quiet night is the same problem without a check configured.

Confirm from PostgreSQL. With application_name set in the connection string or the configure callback, the server shows the pool’s sessions and what they are doing:

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

Sessions active and short-lived with a saturated pool mean the pool is too small. Sessions idle in transaction mean code holds connections through non-database work.

Distinguish the two errors. PoolTimeout is raised after waiting timeout seconds. TooManyRequests is raised immediately when max_waiting is set and the queue is full. Seeing neither but long request latencies means the queue is unbounded and the timeout is long.

psycopg_pool defaults compared with production values A grid comparing six parameters: max_size defaults to min_size of 4, max_waiting is unbounded, timeout is 30 seconds, check is not set, max_idle is 600 seconds and the pool opens implicitly, against sized and bounded production values. Parameter Default Production max_size = min_size (4) from in-flight queries max_waiting 0: unbounded queue a few × max_size timeout 30 s below request deadline check none check_connection max_idle 600 s below network idle timeout open implicit, deprecated open=False, then open()
The defaults that matter most are the ones that are easy to miss: max_size silently equal to min_size, an unbounded queue and a 30-second wait.

Mathematical sizing or parameter formula

max_size follows from concurrent database work in one process:

max_size = ceil( requests_per_second × queries_per_request × mean_query_seconds ÷ 0.7 )
           capped at the number of threads that can use the database

A threaded worker with 32 threads processes 250 jobs per second, each running 2 queries of 12 ms: 250 × 2 × 0.012 = 6 in flight, so max_size = 9. The 32-thread cap is not binding.

min_size should cover steady-state demand so the pool does not open connections on the request path during normal traffic, but no more, because every process holds it permanently: min_size = 4 here, roughly the average in-flight count.

timeout must leave room inside the job or request deadline:

timeout ≤ deadline − p99_work_after_acquisition − margin

With a 10-second job timeout and up to 4 seconds of work after acquiring a connection: timeout = 3 seconds leaves 3 seconds of margin.

max_waiting bounds queued requests to those that can plausibly be served within timeout:

max_waiting ≈ max_size × timeout ÷ mean_hold_seconds × 0.5

With 9 connections, a 3-second timeout and a 24 ms mean hold, the pool can serve about 9 × 3 ÷ 0.024 = 1,125 requests in the timeout window; half of that, rounded, is max_waiting = 500. Beyond it, callers get TooManyRequests immediately rather than waiting to time out.

max_idle must be shorter than the shortest idle timeout on the network path — NAT gateways, load balancers, PgBouncer’s client_idle_timeout, or PostgreSQL’s idle_session_timeout:

max_idle < min(network_idle_timeouts) − check_interval_margin

With an AWS NAT gateway’s 350-second idle timeout, max_idle = 300. Connections at min_size are not closed by max_idle, so for those the check callback or TCP keepalives must catch a dead connection.

Queue behaviour with unbounded and bounded max_waiting Under a traffic spike, an unbounded queue grows to thousands of waiting threads, each eventually failing after the 30 second timeout. With max_waiting of 500 and a 3 second timeout, the queue is capped and extra requests are rejected immediately. max_waiting=0, timeout=30 max_waiting=500, timeout=3 capped at 500: TooManyRequests beyond it thousands of blocked threads spike begins +60 s
max_waiting turns a slow queue into an immediate, explicit rejection once the backlog is longer than the pool can drain within its timeout.

Exact remediation & configuration

A complete production configuration for a threaded service:

import logging
import psycopg
from psycopg_pool import ConnectionPool

log = logging.getLogger("db.pool")

def configure(conn: psycopg.Connection) -> None:
    """Runs once for every new physical connection."""
    conn.execute("SET application_name = 'billing-worker'")
    conn.execute("SET statement_timeout = '5s'")
    conn.commit()

def reset(conn: psycopg.Connection) -> None:
    """Runs every time a connection is returned; must leave it clean."""
    if conn.info.transaction_status != psycopg.pq.TransactionStatus.IDLE:
        conn.rollback()

def reconnect_failed(pool: ConnectionPool) -> None:
    log.critical("pool %s could not reconnect within reconnect_timeout", pool.name)

pool = ConnectionPool(
    conninfo="host=billing-db.internal dbname=billing user=billing_app sslmode=require "
             "keepalives=1 keepalives_idle=60 keepalives_interval=10 keepalives_count=3",
    name="billing-primary",
    min_size=4,
    max_size=9,
    timeout=3.0,
    max_waiting=500,
    max_lifetime=1800.0,
    max_idle=300.0,
    reconnect_timeout=120.0,
    num_workers=3,
    check=ConnectionPool.check_connection,
    configure=configure,
    reset=reset,
    reconnect_failed=reconnect_failed,
    kwargs={"prepare_threshold": 5},
    open=False,
)

Open explicitly and fail fast at startup. open(wait=True) blocks until min_size connections exist, so a misconfigured connection string or an unreachable database fails the deploy instead of the first requests:

def start():
    pool.open(wait=True, timeout=30.0)

def stop():
    pool.close(timeout=10.0)       # waits for checked-out connections to return

Wire start and stop into the process lifecycle — Gunicorn’s post_fork and worker_exit hooks, a Celery worker_process_init signal, or the service’s main function. Creating the pool before a fork and using it in children shares sockets between processes and corrupts connections; always create it after the fork.

Use the context manager for every borrow. with pool.connection() as conn: guarantees return and transaction completion even on exceptions. Code that calls pool.getconn() must call pool.putconn(conn) in a finally block; prefer the context manager.

Handle the two errors differently. TooManyRequests means the service is overloaded right now and should shed load quickly; PoolTimeout means a request waited and failed. Both are retryable by the caller, but only after backoff:

from psycopg_pool import PoolTimeout, TooManyRequests

try:
    with pool.connection() as conn:
        process(conn)
except TooManyRequests:
    raise ServiceBusy(retry_after=1)
except PoolTimeout:
    raise ServiceBusy(retry_after=3)

Resize without a restart. pool.resize(min_size, max_size) changes the bounds at runtime, which is useful during an incident when the database has headroom. Persist the change in configuration afterwards so a restart does not revert it.

Operating the pool over time

A pool configured well at launch drifts out of fit as traffic, query mix and infrastructure change. Three recurring reviews keep it matched.

Export statistics continuously. Pop the counters on a timer and publish them as metrics, so trends are visible without attaching a debugger:

import threading
from prometheus_client import Counter, Gauge

WAITING = Gauge("psycopg_pool_requests_waiting", "Requests waiting", ["pool"])
SIZE = Gauge("psycopg_pool_size", "Connections open", ["pool"])
AVAILABLE = Gauge("psycopg_pool_available", "Idle connections", ["pool"])
QUEUED = Counter("psycopg_pool_requests_queued_total", "Requests that waited", ["pool"])
ERRORS = Counter("psycopg_pool_requests_errors_total", "Timeouts and rejections", ["pool"])
LOST = Counter("psycopg_pool_connections_lost_total", "Broken connections found", ["pool"])

def export_stats(interval=15.0):
    s = pool.pop_stats()
    WAITING.labels(pool.name).set(s.get("requests_waiting", 0))
    SIZE.labels(pool.name).set(s.get("pool_size", 0))
    AVAILABLE.labels(pool.name).set(s.get("pool_available", 0))
    QUEUED.labels(pool.name).inc(s.get("requests_queued", 0))
    ERRORS.labels(pool.name).inc(s.get("requests_errors", 0))
    LOST.labels(pool.name).inc(s.get("connections_lost", 0))
    threading.Timer(interval, export_stats, [interval]).start()

Review the share of queued requests monthly. rate(requests_queued) ÷ rate(requests_num) above a few percent at peak means the pool is running close to its limit; zero at all times with pool_size far below max_size means the limit could be lowered to return budget to other services.

Re-derive max_idle when infrastructure changes. Moving behind a new load balancer, a service mesh or PgBouncer changes the shortest idle timeout on the path. The first symptom is connections_lost rising after quiet periods.

Metric Healthy Investigate
Share of requests queued at peak under 2 % over 5 %
requests_errors zero any sustained increase
connections_lost occasional, after deploys or failovers steady trickle during quiet hours
pool_size at peak versus max_size below max_size most of the time pinned at max_size for long periods
psycopg_pool connection lifecycle callbacks A new connection passes through configure once, then cycles through check before each checkout, use by the caller, and reset on return, until max_lifetime or max_idle closes it. New connection opened by worker configure once per connection check before each checkout Caller uses it reset on every return Put session settings in configure, cleanup in reset after reset, the connection returns to the idle deque and the next checkout runs check again until max_lifetime or max_idle closes it
The configure callback runs once per physical connection, check runs before a connection is handed out, and reset runs on every return. Each belongs to a different kind of state.

Validation & verification

Configuration is what you think it is. After deploy, the first stats snapshot must show the intended bounds:

s = pool.get_stats()
assert s["pool_min"] == 4 and s["pool_max"] == 9, s

Bounded waiting. In staging, set max_size=2 and send 100 concurrent jobs that hold a connection for a second. With max_waiting=10, most must fail immediately with TooManyRequests, the rest within about three seconds with PoolTimeout, and none may hang longer than timeout.

Dead connections are replaced, not returned. Kill the pool’s idle backends on the server, then issue requests:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'billing-worker' AND state = 'idle';

Requests must succeed; connections_lost must increase by the number of terminated sessions; and no OperationalError may reach callers, which shows check is doing its job.

Clean shutdown. Stop a worker during load. pool.close() must return within its timeout, and PostgreSQL must show no sessions left from that worker’s process afterwards.

Frequently Asked Questions

Is check_connection expensive?
It sends an empty query before each checkout, which costs one round trip — typically well under a millisecond on the same network. For very high-throughput services, rely on keepalives and max_idle instead and let rare dead connections surface as retried errors.
What does num_workers change?
It sets how many background threads open, close and check connections. Three is enough for most pools. Raising it speeds up growth after a burst but also increases how many connections can be opened simultaneously, which is exactly what protects the database during a storm.
Can the pool be used before open()?
Using a closed pool raises PoolClosed. Older psycopg_pool releases opened the pool in the constructor; that behaviour is deprecated, and explicit open=False plus open() makes startup failures visible and ordering predictable.
Should prepare_threshold be changed?
Leave it at the default when connecting directly to PostgreSQL or to PgBouncer 1.21 and later with max_prepared_statements enabled. Set it to None behind older transaction-mode poolers, where server-side prepared statements break.