A connection pool lets many application requests borrow a smaller number of PostgreSQL sessions over time. Reuse amortizes connection setup and limits simultaneous leases, but does not make an overloaded database faster by itself. The pool creates another queue whose capacity, waiting time, and cancellation behavior belong to the request budget. Treat that queue as part of the service, not as an invisible implementation detail.

When it breaks

A PostgreSQL backend waiting on ClientRead is waiting for its client to send data. It is not reporting that an application request is queued for a pool lease. Pool waiters may have no backend connection at all. Measure acquisition waiting in the pool and database execution or lock waiting at the server; combining those into one supposed PostgreSQL pool event hides the actual bottleneck.

Returning a connection also does not universally erase session state. Psycopg’s connection context commits on normal exit and rolls back when an exception leaves the context. Additional reset behavior depends on configuration. A committed session-level setting can remain for the next borrower, even though no transaction was leaked. Temporary objects and other session resources likewise require a deliberate policy rather than an assumption that every pool resets everything.

Check it quickly

Use Python 3.10 or later, a disposable PostgreSQL 18 database, and a role allowed to create the fixture schema. Install the tested dependencies with python3 -m pip install "psycopg[binary]==3.3.5" "psycopg-pool==3.3.1". Set PG_DSN to that database’s connection string using your existing authentication configuration. Save the program as pool-demo.py and run it with the same Python environment.

import os
import time
from concurrent.futures import ThreadPoolExecutor

import psycopg
from psycopg_pool import ConnectionPool, PoolTimeout, TooManyRequests

dsn = os.environ['PG_DSN']
with psycopg.connect(dsn, autocommit=True) as admin:
    admin.execute('CREATE SCHEMA pool_demo')
    try:
        admin.execute('CREATE TABLE pool_demo.counter (n integer)')
        admin.execute('INSERT INTO pool_demo.counter VALUES (0)')
        with ConnectionPool(
            dsn, min_size=1, max_size=1, max_waiting=1,
            timeout=3, open=True,
            kwargs={'application_name': 'pool_example'},
        ) as pool:
            pool.wait(timeout=3)

            def queued_request():
                try:
                    with pool.connection(timeout=1):
                        raise AssertionError('holder should still own the only connection')
                except PoolTimeout:
                    return 'PoolTimeout'

            with ThreadPoolExecutor(max_workers=1) as workers:
                with pool.connection() as held:
                    original_pid = held.info.backend_pid
                    waiting = workers.submit(queued_request)
                    deadline = time.monotonic() + 0.5
                    while pool.get_stats()['requests_waiting'] != 1:
                        if time.monotonic() >= deadline:
                            raise TimeoutError('request did not enter the pool queue')
                        time.sleep(0.005)
                    state = admin.execute(
                        'SELECT state,wait_event_type,wait_event '
                        'FROM pg_stat_activity WHERE pid=%s', (original_pid,)
                    ).fetchone()
                    assert state == ('idle', 'Client', 'ClientRead'), state
                    print('waiting_in_pool', 1, 'server', state)
                    try:
                        with pool.connection(timeout=0.1):
                            raise AssertionError('queue limit was not applied')
                    except TooManyRequests:
                        print('queue_limit', 'TooManyRequests')
                    assert waiting.result(timeout=2) == 'PoolTimeout'
                    print('queued_request', 'PoolTimeout')

            with pool.connection() as conn:
                assert conn.info.backend_pid == original_pid
                conn.execute("SET application_name='previous_borrower'")
            try:
                with pool.connection() as conn:
                    conn.execute('UPDATE pool_demo.counter SET n=99')
                    raise ValueError('simulated request failure')
            except ValueError as error:
                assert str(error) == 'simulated request failure'

            with pool.connection() as conn:
                value = conn.execute('SELECT n FROM pool_demo.counter').fetchone()[0]
                setting = conn.execute('SHOW application_name').fetchone()[0]
                assert value == 0 and setting == 'previous_borrower'
                assert conn.info.backend_pid == original_pid
                print('same_backend', True, 'rollback_value', value, 'retained_setting', setting)
                conn.execute('RESET application_name')
    finally:
        admin.execute('DROP SCHEMA pool_demo CASCADE')

The pool contains exactly one connection and allows one waiting request. The main thread holds the connection without running a query, while a worker tries to borrow it. Once the pool reports one waiter, the independent observer sees the held backend idle on ClientRead. Those states coexist: the queue is in the application even though the database session has no query to execute.

A third acquisition is rejected with TooManyRequests because the queue is already full. The queued worker then reaches its one-second acquisition budget and reports PoolTimeout. The holder does not release early, so the test distinguishes queue overflow from expiration while waiting. Neither error is a PostgreSQL row-lock timeout, and neither indicates that an application SQL operation was executed.

After release, another borrower receives the same backend identifier. It sets application_name at session scope and exits normally. A later borrower updates the counter to 99, then raises an exception outside the successful path. The connection context rolls back that update. The final borrower sees counter zero and the earlier session setting still present, while the backend identifier remains unchanged.

The final output therefore reports same_backend True, rollback_value 0, and retained_setting previous_borrower. Transaction cleanup worked, while session state was retained. The program explicitly resets its setting and drops its own schema. An existing schema of that name causes setup to fail before cleanup ownership is assumed.

Size the complete deployment

Pool size applies to one pool instance, not the whole service fleet. Four processes with eight connections each can consume thirty-two server slots before counting other applications, migrations, monitoring, and administrative headroom. Compare that aggregate with the server’s connection budget and useful execution concurrency. Increasing every pool can move waiting into the database while increasing memory pressure.

Borrow connections for the shortest complete database operation that preserves its transaction boundary, and return them on every exit path. Avoid holding a lease during unrelated network calls or user interaction. Use transaction-local settings where appropriate, or an explicit reset callback for state that must be cleared. Observe acquisition latency, queue rejection, lease duration, and database execution together before changing capacity.

Sources