Using asyncpg Behind PgBouncer Transaction Mode

This guide is part of psycopg 3 & asyncpg Connection Pools. Putting PgBouncer in front of an asyncpg service is the standard answer to too many connections, and it usually breaks the service within seconds of the first deploy. asyncpg prepares and caches every statement it executes; transaction-mode PgBouncer hands each transaction to whichever server connection is free; and a statement prepared on one server connection does not exist on another:

asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "__asyncpg_stmt_3__" already exists
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_7__" does not exist

Both errors appear intermittently and only under concurrency, which makes them easy to misread as a database problem. The objective is a configuration where asyncpg and PgBouncer agree about prepared statements, where session state never leaks across transactions, and where the two pools in the path are sized as a pair rather than independently.

Rapid incident diagnosis

Confirm the pooling mode. From the PgBouncer admin console:

SHOW CONFIG;      -- look for pool_mode and max_prepared_statements
SHOW VERSION;     -- prepared statement tracking needs 1.21 or later

pool_mode = transaction with max_prepared_statements = 0 is the combination that breaks asyncpg’s cache.

Confirm the client settings. In the service, print what the pool was created with:

print(pool.get_max_size(), pool.get_min_size())
# statement_cache_size is per connection; check the value you passed to create_pool

Match the error to the mechanism.

Error What happened
DuplicatePreparedStatementError asyncpg believed a statement was not prepared on this connection and prepared it again, but the server connection already had it — the same server connection served a different client earlier
InvalidSQLStatementNameError asyncpg executed a statement it had prepared earlier, but this transaction landed on a different server connection that never saw it
InvalidCachedStatementError The result type of a cached statement changed, usually after a migration
prepared statement "S_1" already exists in PgBouncer logs PgBouncer’s own tracking saw a name collision, typically with max_prepared_statements set too low

Check for pinning as well. Session-level state prevents PgBouncer from returning a server connection to the pool for other clients. Anything asyncpg runs in init — SET application_name, set_type_codec does not touch the server, but SET statements do — applies to the server connection it happens to be on at that moment, and is neither reliable nor free. Look for unexpected values:

-- Through the proxy, in autocommit, run repeatedly: the value should be the same every time
SELECT current_setting('application_name');

Different answers on different calls mean session settings are being applied to server connections and then handed to other clients.

Prepared statement collision under transaction pooling Client A prepares statement __asyncpg_stmt_3__ on server connection 7 and commits. PgBouncer gives server connection 7 to client B, whose own statement 3 is unknown to asyncpg's cache on that connection, producing a duplicate or missing statement error. Client A prepares stmt_3 Client B executes stmt_3 PgBouncer transaction mode server conn 7 reused Server connection 7 already has stmt_3 from A B's prepare: duplicate B's execute: unknown name the cache is per client connection; the server connection is shared
asyncpg names its prepared statements per connection. Under transaction pooling those connections are shared, so a name prepared for one client can be absent — or already present — for the next.

Mathematical sizing or parameter formula

Two pools sit in the path, and they are sized from different quantities.

PgBouncer’s backend pool is sized from concurrent transactions, because a server connection is held only for the duration of a transaction:

default_pool_size = ceil( transactions_per_second × mean_transaction_seconds ÷ 0.7 )

At 1,500 transactions per second with a mean transaction time of 5 ms: 1500 × 0.005 = 7.5 busy, so default_pool_size = 11.

asyncpg’s pool is sized from concurrent queries in one process, exactly as it would be without a proxy, and it is now cheap to hold client connections because they cost PgBouncer little:

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

The client ceiling must accommodate every process:

max_client_conn ≥ processes × max_size × 1.5

With 40 processes at max_size = 11, that is 440 client connections; max_client_conn = 1000 leaves room for deploy overlap and ad-hoc sessions.

Prepared statements, if tracking is enabled. PgBouncer 1.21 and later track protocol-level prepared statements per server connection, up to max_prepared_statements each:

max_prepared_statements ≥ distinct_query_texts_in_the_busiest_service
server_side_memory ≈ default_pool_size × max_prepared_statements × plan_size

With 60 distinct statements, max_prepared_statements = 100 gives headroom; each server connection then keeps up to 100 plans, which is modest.

The decision between the two strategies is a trade:

Strategy Requires Cost When to choose
statement_cache_size=0 Nothing PostgreSQL parses and plans each execution PgBouncer older than 1.21, or mixed proxy fleet
PgBouncer max_prepared_statements PgBouncer 1.21+ Memory per server connection; PgBouncer does the bookkeeping Modern PgBouncer, hot repeated queries
PgBouncer prepared statement tracking With max_prepared_statements set, PgBouncer records which statements each client prepared and replays the prepare on any server connection that does not yet have it, so both errors disappear. Client prepares P1 PgBouncer 1.21+ tracks P1 per client re-prepares as needed server conn 4 · has P1 server conn 7 · prepared on use server conn 9 · prepared on use One client statement, many server connections memory cost: default_pool_size × max_prepared_statements plans
With tracking enabled, PgBouncer re-prepares a client's statement on whichever server connection it uses, so the client's cache stays valid.

Exact remediation & configuration

Option A — disable the client-side cache. Works with every PgBouncer version and with other transaction poolers:

pool = await asyncpg.create_pool(
    dsn="postgresql://orders_app@pgbouncer.internal:6432/orders",
    min_size=2,
    max_size=11,
    statement_cache_size=0,          # no named prepared statements
    max_inactive_connection_lifetime=120.0,
    command_timeout=2.0,
)

asyncpg still uses the extended query protocol with unnamed statements, which PgBouncer passes through safely, so parameter binding and type safety are unchanged.

Option B — enable tracking in PgBouncer 1.21 or later and keep the cache:

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 100
default_pool_size = 11
max_client_conn = 1000
server_reset_query = DISCARD ALL
server_reset_query_always = 0        ; transaction mode: PgBouncer resets between transactions itself

Reload with RELOAD; on the admin console and confirm with SHOW CONFIG;. Keep the client cache no larger than max_prepared_statements, so the client never expects more statements than PgBouncer will track.

Keep sessions stateless either way. Replace anything that sets session state on connect with role defaults or transaction-scoped settings:

-- Instead of SET application_name / SET statement_timeout in asyncpg's init callback:
ALTER ROLE orders_app SET application_name = 'orders-api';
ALTER ROLE orders_app SET statement_timeout = '2s';

Role settings are applied by PostgreSQL when a server connection is established, so every backend behind the proxy has them, and nothing pins a session. For per-transaction values, use SET LOCAL inside the transaction, as described in Setting Tenant Context for Row-Level Security Under Transaction Pooling.

Drop the features transaction pooling cannot support. LISTEN/NOTIFY, session-level advisory locks, WITH HOLD cursors and temporary tables outside a transaction do not work behind a transaction pooler. asyncpg’s add_listener in particular fails silently in the sense that notifications simply never arrive, because the server connection that ran LISTEN moves to another client. Route those code paths to a direct connection:

# Notifications bypass the proxy entirely
listener_conn = await asyncpg.connect(dsn=DIRECT_DSN)
await listener_conn.add_listener("orders_events", on_event)

Run migrations directly. Point Alembic or the migration runner at the direct DSN on port 5432; advisory locks used for migration serialisation require a session.

Verifying the whole path under concurrency

A single request through the proxy proves nothing: both failure modes need two clients sharing a server connection. Reproduce that deliberately.

import asyncio, asyncpg

DSN = "postgresql://orders_app@pgbouncer.internal:6432/orders"

async def worker(pool, n):
    for _ in range(500):
        async with pool.acquire(timeout=2) as conn:
            await conn.fetchval("SELECT count(*) FROM orders WHERE status = $1", "open")
            await conn.fetchval("SELECT max(total) FROM orders WHERE customer_id = $1", n)

async def main():
    # a client pool much larger than PgBouncer's backend pool forces sharing
    pool = await asyncpg.create_pool(DSN, min_size=20, max_size=20, statement_cache_size=0)
    await asyncio.gather(*(worker(pool, i) for i in range(20)))
    await pool.close()

asyncio.run(main())

Run it with default_pool_size set to a small value — 3 is enough — so that twenty client connections certainly share three server connections. With the cache disabled, or with tracking enabled, it must complete with no errors; with neither, it fails within a second, which also makes it a good regression test.

Watch both sides while it runs. On PgBouncer, SHOW POOLS should show cl_active at 20 and sv_active at or below 3, demonstrating multiplexing. On PostgreSQL, the session count from the proxy should equal the backend pool size, not the client count:

SELECT count(*) FROM pg_stat_activity WHERE usename = 'orders_app';

Check statement counts if tracking is on. Each server connection should hold at most max_prepared_statements:

SHOW SERVERS;   -- on the PgBouncer console, confirm the server connections in use

Keep the test in CI against a PgBouncer container with pool_mode = transaction and a tiny default_pool_size. The cost is seconds, and it catches the two regressions that matter: someone re-enabling the statement cache, and someone adding session state to the connection path.

Check Command Expected
Multiplexing is happening SHOW POOLS; cl_active ≫ sv_active
No statement errors Application logs during the concurrency test Zero DuplicatePreparedStatement, InvalidSQLStatementName
No pinning SHOW POOLS; over time sv_used returns to the pool between transactions
Server connections bounded pg_stat_activity count ≈ default_pool_size, not client count
Client and server connections during the concurrency test Twenty client connections from the test drive traffic while PgBouncer keeps three server connections to PostgreSQL, showing multiplexing is active during the test that would otherwise expose prepared statement errors. During the concurrency test cl_active (clients) 20 sv_active (servers) 3 with statement_cache_size=0 or PgBouncer tracking, this runs cleanly without either, it fails within the first second
Client connections stay high while server connections stay at the backend pool size. That gap is the whole reason for the proxy, and the concurrency test is what proves it exists.

Frequently Asked Questions

Does statement_cache_size=0 disable parameter binding?
No. asyncpg keeps using the extended protocol with unnamed prepared statements, so parameters are still sent separately from the SQL text, with full type handling. Only the reuse of named server-side statements is lost.
Can I use session pooling instead?
Session pooling avoids every prepared-statement problem, because each client keeps one server connection for its whole session — but it also removes almost all the multiplexing benefit, so the connection count barely improves. Use it only when an application genuinely needs session state, as discussed in How to Choose Between Transaction and Statement Pooling in PostgreSQL.
What about SQLAlchemy with the asyncpg dialect?
SQLAlchemy drives asyncpg through its own pool and constructs the connection with the driver arguments you pass. Set connect_args={"statement_cache_size": 0} — and, for SQLAlchemy’s own caching, prepared_statement_cache_size=0 in create_async_engine — when connecting through an older transaction pooler.
Do these errors ever appear without a proxy?
Rarely. Connecting directly to PostgreSQL, each asyncpg connection owns its session, so names never collide. The same errors against a direct connection usually mean two asyncpg connections were accidentally sharing one socket, or a connection object was used from two tasks at once.