Sizing pools behind PgBouncer and RDS Proxy

With a transaction-pooling proxy in front of PostgreSQL, the application pool can be sized for concurrency rather than for max_connections — but asyncpg must stop using server-side prepared statements, and session-level state stops working, because a server connection is only yours for the length of a transaction. This guide belongs to tuning connection pools for cloud databases.

Quick Answer

A proxy in transaction mode multiplexes many client connections onto few server connections. Two settings are then mandatory for asyncpg.

Before — the direct-connection configuration, pointed at a pooler:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://shop:secret@pgbouncer.internal:6432/shop",
    pool_size=10,
)
# Works for a while, then:
# asyncpg.exceptions.InvalidSQLStatementNameError:
# prepared statement "__asyncpg_stmt_3__" does not exist

After — prepared statements disabled, pool sized for concurrency:

from uuid import uuid4

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://shop:secret@pgbouncer.internal:6432/shop",
    # The proxy holds the database side small, so this bounds concurrency, not backends.
    pool_size=20,
    max_overflow=10,
    pool_timeout=10,
    # A pooled client connection is not a long-lived server session: recycle often.
    pool_recycle=300,
    pool_pre_ping=True,
    connect_args={
        # asyncpg prepares statements server-side by default; in transaction mode the
        # next statement may land on a different server connection.
        "statement_cache_size": 0,
        "prepared_statement_cache_size": 0,
        # Unique names so a statement prepared on one server connection cannot collide.
        "prepared_statement_name_func": lambda: f"__asyncpg_{uuid4()}__",
        "server_settings": {"application_name": "orders-api"},
    },
)
Session = async_sessionmaker(engine, expire_on_commit=False)

The pool is now bounding how many requests can be in flight per process, not how many backends the database has — which the proxy controls. That is the whole reason to put one there.

Execution Context & Async Workflow Integration

With a proxy there are three pools, and it is worth being explicit about what each bounds.

SQLAlchemy's pool (pool_size + max_overflow) bounds connections held by one application process. Exceeding it makes a request wait pool_timeout and then raise.

The proxy's client-side limit (max_client_conn in PgBouncer) bounds how many application connections it will accept at all. Client connections are cheap — a socket and a small buffer — so this is usually set high.

The proxy's server-side pool (default_pool_size) is what the database actually sees, and in transaction mode a server connection is held only while a transaction is open. That is the multiplexing: a hundred idle client connections can share twenty server connections, because idle clients hold nothing.

PostgreSQL's max_connections is the hard ceiling, shared by every client of the database, and a backend costs memory whether or not it is busy.

The sizing question becomes: which layer should be the bottleneck? Usually the proxy's server pool, because it is the layer whose limit maps to database capacity — roughly a few times the number of cores for a CPU-bound workload. The application pool should then be wider than the concurrency you expect per process, so that queueing happens at the proxy rather than inside the application, where it is harder to observe.

The transaction-mode caveats all follow from one fact: your session does not own a server connection between transactions. Four consequences matter.

Server-side prepared statements break, because a statement prepared on one server connection is not visible on another. asyncpg prepares everything by default, which is why the two cache settings and the name function above are mandatory rather than optional — the failure and its details are in handling asyncpg prepared statement errors with PgBouncer.

Session-level SET does not persist. Anything that must apply to a statement has to be set inside the same transaction with SET LOCAL, or at connect time via server_settings — which is why the row-level-security pattern in enforcing tenant isolation with Postgres row-level security uses set_config(..., true).

LISTEN/NOTIFY is session-scoped and cannot work through transaction pooling at all; a listener needs a direct connection.

Session-level advisory locks persist beyond the transaction and therefore leak onto a shared server connection. Use the _xact variants, which release at commit.

RDS Proxy behaves similarly, with one extra concept: pinning. When a client uses session-level state the proxy cannot multiplex safely, it pins that client to one server connection for the rest of the session, silently removing the benefit. The DatabaseConnectionsCurrentlySessionPinned metric is the one to watch after rollout.

Resolving Warnings, Errors & Common Mistakes

Distinguishing "queueing in my pool" from "queueing at the proxy" is the main diagnostic skill here, and each layer reports it differently. SQLAlchemy raises TimeoutError naming the pool; PgBouncer reports waiting clients in its own statistics:

from sqlalchemy import text

# Against PgBouncer's admin database, not your application database.
POOL_STATS = text("SHOW POOLS")

cl_waiting greater than zero in that output means clients are waiting for a server connection, which says the proxy's server pool is the constraint. SQLAlchemy's own gauge answers the other half:

pool = engine.sync_engine.pool
print(pool.checkedout(), pool.size(), pool.overflow())

If checkedout() is at pool_size + max_overflow while the proxy reports no waiting clients, the application pool is too small — and unlike the database side, widening it costs almost nothing behind a proxy.

One more configuration detail worth getting right: pool_recycle. A client connection to a proxy is not a long-lived database session, and proxies have their own idle timeouts (client_idle_timeout, or RDS Proxy's IdleClientTimeout). Recycling well below the smallest of them avoids handing out a connection the proxy has already closed — the reasoning in configuring pool_pre_ping to handle stale connections.

Advanced: Choosing the Numbers

The numbers follow from three quantities you can measure, and the arithmetic is worth doing explicitly rather than copying defaults.

Database capacity. The number of concurrently executing statements a PostgreSQL instance handles well is closer to its core count than to its max_connections. A common starting point for default_pool_size on the proxy is two to four times the number of cores; more than that and the backends compete rather than progress.

Application concurrency. For an async service, the number of requests in flight per process, each needing at most one connection at a time — plus any fan-out, which multiplies it. That is the number the SQLAlchemy pool should accommodate:

# Worked example: 20 replicas, ~40 concurrent requests each, no fan-out.
#
#   SQLAlchemy per process:  pool_size=20, max_overflow=20   → up to 40
#   Proxy client limit:      max_client_conn >= 20 × 40       = 800  (cheap)
#   Proxy server pool:       default_pool_size = 4 × cores    = 32   (the bottleneck)
#   PostgreSQL:              max_connections   >= 32 + admin  = 100  (headroom)

The intent is visible in that block: the database sees 32 backends whatever the traffic, the application never queues internally, and the proxy is where excess concurrency waits. Without the proxy, the same 20 replicas would need pool_size of about 4 to stay under max_connections, and any burst would queue inside the application.

Transaction duration. This is the quantity that decides whether multiplexing works at all. A server connection is held for the length of a transaction, so short transactions multiplex well and long ones do not. A service whose transactions last 5 ms can share 32 server connections among hundreds of concurrent requests; one whose transactions last 2 seconds — because they span an HTTP call — cannot, and the proxy provides almost no benefit. That is a further reason to keep transactions around database work only, as detecting idle-in-transaction sessions from async code argues.

Two further refinements are worth knowing. A separate pool per workload — a small one for exports and background jobs, a larger one for requests — keeps a slow job from consuming the request pool, and with a proxy the extra client connections are nearly free. And per-role proxy pools in PgBouncer let a reporting user have its own pool_size, so an analytical query storm cannot starve the API.

Finally, decide whether the proxy is needed at all. For a single application with a handful of replicas and short transactions, a modest SQLAlchemy pool talking directly to PostgreSQL is simpler and one fewer component in the path. Proxies earn their place when connection count is the constraint: many replicas, serverless functions, or a database whose max_connections cannot be raised — the case made in using NullPool for serverless and AWS Lambda.

Verifying the Configuration End to End

Three checks confirm that the layers are configured as intended, and each has caught a real misconfiguration.

Prepared statements are genuinely off. The settings above are easy to apply to one engine and forget on another. A startup check proves it for the engine actually in use:

from sqlalchemy import text


async def assert_no_server_prepared_statements(engine) -> None:
    async with engine.connect() as conn:
        for _ in range(10):
            await conn.execute(text("SELECT 1"))
        count = await conn.scalar(text("SELECT count(*) FROM pg_prepared_statements"))
    if count:
        raise RuntimeError(
            f"{count} prepared statement(s) on this connection — "
            "statement_cache_size is not 0"
        )

Running the same statement repeatedly is what would normally trigger caching, so a zero count is meaningful.

Session state does not survive. A short test asserts the behaviour the application must not rely on, which documents the constraint far better than a comment:

import pytest
from sqlalchemy import text


@pytest.mark.asyncio
async def test_session_level_set_does_not_persist(engine):
    """Through a transaction pooler, a SET in one transaction is gone in the next."""
    async with engine.connect() as conn:
        await conn.execute(text("SET application_name = 'probe'"))
        await conn.commit()
        name = await conn.scalar(text("SHOW application_name"))
    assert name != "probe", "session state persisted — is this really transaction mode?"

That test passes against a transaction pooler and fails against a direct connection, so it also documents which topology the suite is running against.

The bottleneck is where you intended. Under a load test, read all three numbers at once: SQLAlchemy's checkedout(), the proxy's waiting clients, and the database's backend count. Exactly one should be saturated, and it should be the layer you chose. If SQLAlchemy's pool is full while the proxy has idle server connections, the application pool is too small. If the proxy is queueing while the database is at low CPU, its server pool can grow. If the database is saturated, no amount of pool tuning helps and the work is in the queries.

Finally, set application_name per service and role. pg_stat_activity then shows backends grouped by who is using them, which through a proxy is otherwise almost impossible to attribute — and it is the same recommendation as in configuring connect_args and server_settings for asyncpg.

Frequently Asked Questions

Related

• Tuning Connection Pools for Cloud Databases — The parent guide: pool sizing for managed databases.
• Handling asyncpg prepared statement errors with PgBouncer — The error transaction pooling produces, in detail.
• Setting pool_size and max_overflow for AWS RDS — The direct-connection arithmetic this replaces.
• Using NullPool for serverless and AWS Lambda — The case where a proxy is effectively mandatory.