Diagnosing psycopg_pool PoolTimeout Errors

This guide is part of psycopg 3 & asyncpg Connection Pools. PoolTimeout means one thing precisely: a caller asked the pool for a connection and did not get one within timeout seconds. It does not say why, and the four possible causes need opposite fixes — a pool that is too small, connections held by code that is not using them, queries that got slower, or a pool that cannot open new connections at all:

Traceback (most recent call last):
  File "/app/billing/invoices.py", line 88, in charge_due_invoices
    with pool.connection() as conn:
  File "/usr/local/lib/python3.12/site-packages/psycopg_pool/pool.py", line 200, in connection
    conn = self.getconn(timeout=timeout)
psycopg_pool.PoolTimeout: couldn't get a connection after 3.00 sec

The objective is to identify which of the four it is from the pool’s own statistics and the database’s view, then apply the fix that matches — and to leave behind the metrics that make the next occurrence obvious.

Rapid incident diagnosis

Take a statistics snapshot while it is happening. get_stats() is cheap and safe to call from a signal handler, an admin endpoint or a debug thread:

s = pool.get_stats()
print({k: s.get(k) for k in (
    "pool_min", "pool_max", "pool_size", "pool_available", "requests_waiting",
    "requests_num", "requests_queued", "requests_wait_ms", "requests_errors",
    "usage_ms", "connections_num", "connections_ms", "connections_errors", "connections_lost")})

Three derived numbers decide the diagnosis:

mean_hold_ms    = usage_ms ÷ requests_num
mean_wait_ms    = requests_wait_ms ÷ max(requests_queued, 1)
connect_ms      = connections_ms ÷ max(connections_num, 1)

Then look at the same moment from PostgreSQL:

SELECT state,
       count(*)                                        AS sessions,
       max(now() - state_change)                       AS longest_in_state,
       max(now() - query_start) FILTER (WHERE state = 'active') AS longest_query
FROM pg_stat_activity
WHERE application_name = 'billing-worker'
GROUP BY state;

Match the pattern.

Check for a stalled reconnect. If connections_errors climbs and pool_size falls towards zero, the pool is failing to reconnect. psycopg_pool retries with backoff until reconnect_timeout, then calls reconnect_failed. Without that callback wired to an alert, the service looks healthy while holding no connections at all.

Mathematical sizing or parameter formula

Once the cause is capacity, size the pool from measured demand rather than guesswork. The pool’s counters contain everything needed.

in_flight_average = usage_ms ÷ elapsed_ms          (connections busy on average)
max_size          = ceil( in_flight_average ÷ target_utilisation )

Worked example. Over a 60-second window, requests_num rose by 24,000 and usage_ms by 270,000. Average in-flight work is 270000 ÷ 60000 = 4.5 connections. At a 70 % target, max_size = ceil(4.5 ÷ 0.7) = 7. The pool was configured at 4, which is below the average demand — the queue could never drain, which is why waits grew until they hit the timeout.

The same numbers say what the timeout should be. Mean wait among queued requests was requests_wait_ms ÷ requests_queued; if that is already close to timeout, the queue is not draining and raising the timeout only lengthens failure. If it is far below, the timeout is doing its job.

For the held-connection case, the useful figure is hold time against query time:

hold_overhead = mean_hold_ms − mean_query_ms

With a mean hold of 220 ms and mean query time of 12 ms from pg_stat_statements, 208 ms of every checkout is application work. Cutting that — by moving an HTTP call out of the block — reduces demand by the same factor as multiplying the pool size, without any extra load on the database.

Check the fleet before raising anything:

processes × max_size ≤ usable_connections_for_this_service

Sixteen worker processes at max_size = 7 claim 112 connections. If the service’s share of the database is 80, the pool cannot grow to 7 everywhere; either reduce process count, cut hold time, or put PgBouncer in front.

Exact remediation & configuration

If the pool is too small, raise it at runtime to stop the errors, then persist the change:

pool.resize(min_size=4, max_size=7)      # takes effect immediately

Check the fleet arithmetic before doing this on every process, and confirm PostgreSQL has free slots:

SELECT current_setting('max_connections')::int - count(*) AS free_slots
FROM pg_stat_activity;

If connections are held, shorten the block. The pattern that causes it is a network call inside with pool.connection():

# Before: the connection is held for the whole HTTP round trip
with pool.connection() as conn:
    invoice = conn.execute("SELECT * FROM invoices WHERE id = %s", (invoice_id,)).fetchone()
    receipt = gateway.charge(invoice["total"])          # 180 ms, no database work
    conn.execute("UPDATE invoices SET paid_at = now(), receipt = %s WHERE id = %s",
                 (receipt.id, invoice_id))

# After: two short checkouts, nothing held during the call
with pool.connection() as conn:
    invoice = conn.execute("SELECT * FROM invoices WHERE id = %s", (invoice_id,)).fetchone()

receipt = gateway.charge(invoice["total"], idempotency_key=str(invoice_id))

with pool.connection() as conn:
    conn.execute("UPDATE invoices SET paid_at = now(), receipt = %s WHERE id = %s AND paid_at IS NULL",
                 (receipt.id, invoice_id))

The idempotency key makes the split safe: if the process dies between the call and the update, retrying the charge does not double-bill.

If queries got slower, find them and fix the query rather than the pool:

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE userid = (SELECT oid FROM pg_roles WHERE rolname = 'billing_app')
ORDER BY mean_exec_time * calls DESC
LIMIT 10;

If the pool cannot open connections, the fix is outside the pool: credentials, TLS, DNS, security groups or the server’s connection ceiling. Make the failure loud so it is never mistaken for saturation:

def reconnect_failed(pool):
    log.critical("pool %s has no connections; reconnect failed for %ss", pool.name, pool.reconnect_timeout)
    metrics.pool_reconnect_failed.labels(pool.name).inc()

Make the timeout explicit per call site where it differs. A background job can afford to wait longer than a request handler:

with pool.connection(timeout=10.0) as conn:    # nightly reconciliation
    reconcile(conn)

Preventing recurrence

Two habits stop the same incident repeating: alert on the leading indicator, and keep hold time visible.

Alert on queued share and errors, not on pool_size. A pool at its maximum is working, not failing; a pool whose callers wait is heading for timeouts:

groups:
  - name: psycopg-pool
    rules:
      - alert: PsycopgPoolTimeouts
        expr: rate(psycopg_pool_requests_errors_total[5m]) > 0
        for: 2m
        labels: { severity: critical }
        annotations:
          summary: "{{ $labels.pool }}: PoolTimeout or TooManyRequests raised"
      - alert: PsycopgPoolQueueingHigh
        expr: rate(psycopg_pool_requests_queued_total[5m])
              / rate(psycopg_pool_requests_total[5m]) > 0.05
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.pool }}: more than 5 % of requests wait for a connection"

Track mean hold time as a first-class series. A rise in usage_ms ÷ requests_num without a matching rise in database query time is application work creeping inside checkouts — the cause that no amount of pool sizing fixes. Reviewing it after each release catches it while the change is still fresh.

Keep one canary assertion in the load test. Run the service’s peak load in staging with the production pool settings and assert that requests_errors stays at zero and the queued share stays under a few percent. A change that breaks either — a new query in a hot path, a slower dependency inside a checkout — then fails before release.

Validation & verification

Reproduce the diagnosis. In staging, force each cause and confirm the statistics look as the table predicts: cap max_size at 1 under load (pool too small); add a time.sleep(0.5) inside a checkout (connections held); run a deliberately slow query (query time); point the pool at a wrong password (cannot connect). This builds confidence that the next production snapshot can be read correctly.

Confirm the fix under the original load. Replay the traffic that caused the incident at the same rate. requests_errors must stay at zero, pool_size must peak below max_size, and mean wait must stay well under the timeout:

s = pool.pop_stats()
assert s.get("requests_errors", 0) == 0, s
assert s.get("requests_wait_ms", 0) / max(s.get("requests_queued", 1), 1) < 200, s

Verify the database side did not simply move the queue. After raising max_size, PostgreSQL’s Threads-equivalent view should show more sessions but similar query times:

SELECT count(*) AS sessions,
       count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity WHERE application_name = 'billing-worker';

If active sessions rose and query times rose with them, the database — not the pool — is now the constraint, and the extra connections should be given back.

Frequently Asked Questions

Related

• psycopg 3 & asyncpg Connection Pools — the parent guide to both Python pools.
• Configuring psycopg_pool ConnectionPool for Production — the parameters this page adjusts.
• Applying Little’s Law to Connection Pool Sizing — the queueing arithmetic behind the sizing formula.
• Detecting Connection Pool Saturation — the language-independent version of this diagnosis.