Skip to content

Prepared Statements with asyncpg Behind PgBouncer

asyncpg prepares and caches every query it runs, which makes it fast against PostgreSQL directly — and broken against PgBouncer in transaction mode with default settings, because consecutive transactions from one client can land on different server connections, where the prepared statement does not exist. There are two fixes, and they are not equally fast. Measured on Python 3.14 with asyncpg 0.31.0, PgBouncer 1.26.0 in transaction mode with 10 server connections, and PostgreSQL 17, running 200 tasks through a 50-connection asyncpg pool with three parameterized queries: with defaults on both sides, 89.9% of queries failed with prepared statement "__asyncpg_stmt_3__" does not exist. Disabling asyncpg's statement cache with statement_cache_size=0 removed the errors and reached 8,185 queries per second. Setting PgBouncer's max_prepared_statements=200 and keeping asyncpg's cache reached 12,465 per second at 79 µs of client CPU per query, against 122 µs — 52% more throughput than disabling the cache. This guide reproduces the failure and configures both sides.

Prerequisites

1. Reproduce the failure

In transaction pooling mode, PgBouncer assigns a server connection to a client only for the duration of one transaction. asyncpg's fetch prepares the query as a named statement on its first use and reuses the name afterwards — but the next use may run on a different server connection:

pool = await asyncpg.create_pool("postgresql://app:pw@pgbouncer:6432/app",
                                 min_size=20, max_size=20)

async def owner(i):
    async with pool.acquire() as conn:
        return await conn.fetchval("SELECT owner FROM acct WHERE id = $1", i)

results = await asyncio.gather(*(owner(i) for i in range(1, 200)), return_exceptions=True)

Measured: 152 of 199 calls raised InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_3__" does not exist. asyncpg's error hint names the cause and suggests the two fixes below. Under sustained load with three queries, the error rate was 89.9% and only 987 queries per second succeeded. The failure depends on timing — a test with one client may pass, because its transactions keep landing on the same server connection.

Verify: run the service's queries through PgBouncer with many concurrent clients before deploying; a single-client test does not reproduce this.

Why a cached statement goes missing A sequence of 6 messages between 4 participants. Why a cached statement goes missing asyncpg PgBouncer server conn A server conn B Parse __asyncpg_stmt_3__ Parse: prepared on A Bind/Execute stmt 3 (next transaction) routed to B ERROR: stmt 3 does not exist InvalidSQLStatementNameError Transaction pooling moves the client between server connections.

2. Fix it on the client: disable the statement cache

The client-side fix is to stop asyncpg from reusing named statements:

pool = await asyncpg.create_pool(DSN_PGBOUNCER, min_size=50, max_size=50,
                                 statement_cache_size=0)

With the cache disabled, asyncpg re-prepares each query as an unnamed statement instead of reusing a named one, so no statement name has to survive between transactions. Measured: no errors, 8,185 queries per second, and 122 µs of client CPU per query. Against PostgreSQL directly, the same setting cost 27%: 7,024 queries per second against 9,614 with the cache. This fix works with any PgBouncer version and any pooler, which makes it the right first step when the pooler cannot be reconfigured.

Verify: with statement_cache_size=0, a concurrent load test through PgBouncer shows zero InvalidSQLStatementNameError.

3. Fix it on the pooler: let PgBouncer track statements

PgBouncer 1.21 added support for protocol-level prepared statements in transaction mode. With max_prepared_statements above zero, it records each named statement a client prepares and, when the client's next transaction lands on a server connection that lacks it, prepares it there first:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
default_pool_size = 10
max_prepared_statements = 200

Measured with asyncpg's default cache of 100 statements: no errors, 12,465 queries per second, and 79 µs of client CPU per query — 52% more throughput than disabling the cache behind the same PgBouncer, and more than the 9,614 measured directly against PostgreSQL with 50 server connections, because PgBouncer funnelled the load onto 10. Set max_prepared_statements at least as large as asyncpg's statement_cache_size, so statements the client still caches are not evicted at the pooler. This applies to protocol-level statements only; SQL-level PREPARE and EXECUTE are still not tracked.

Verify: SHOW CONFIG on the PgBouncer admin console shows max_prepared_statements above zero and at least asyncpg's cache size.

Successful queries per second, 200 tasks 5 horizontal bars comparing PgBouncer defaults (89.9% errors) with the others. Successful queries per second, 200 tasks PgBouncer defaults (89.9% errors) 987/s direct, statement_cache_size=0 7,024/s PgBouncer, statement_cache_size=0 8,185/s direct, default cache 9,614/s PgBouncer max_prepared_statements=200 12,465/s PgBouncer used 10 server connections; direct runs used 50. asyncpg 0.31.0, PgBouncer 1.26.0, PostgreSQL 17.

4. Keep other session state out of transaction mode

Prepared statements are the most common breakage, but anything that lives on a server session has the same problem under transaction pooling, because the next transaction may run elsewhere:

async with pool.acquire() as conn:
    async with conn.transaction():
        await conn.execute("SET LOCAL statement_timeout = '2s'")   # scoped to this transaction: safe
        await conn.execute("SELECT pg_advisory_xact_lock($1)", key)  # transaction-level lock: safe
        await do_work(conn)

Session-level SET, pg_advisory_lock without _xact, LISTEN, temporary tables and SQL-level PREPARE all leak or vanish between transactions. Use the transaction-scoped variants — SET LOCAL, pg_advisory_xact_lock — inside an explicit transaction, or connect directly to PostgreSQL for the few components that need session state, such as a LISTEN consumer.

Verify: a search of the code for LISTEN, pg_advisory_lock(, session SET and TEMP tables finds none on connections that go through PgBouncer.

5. Choose a configuration

Both fixes are correct; the choice depends on what you control. If you can configure PgBouncer 1.21 or later, set max_prepared_statements and keep asyncpg's cache: it was the fastest configuration measured. If you cannot, or the pooler is another product without statement tracking, set statement_cache_size=0:

def make_pool(dsn: str, behind_tracking_pooler: bool):
    return asyncpg.create_pool(
        dsn,
        min_size=10, max_size=50,
        statement_cache_size=100 if behind_tracking_pooler else 0,
    )

Make the setting explicit in configuration rather than relying on the default, so that moving a service behind a pooler — or upgrading the pooler — is a reviewed change. SQLAlchemy's asyncpg dialect has its own prepared-statement cache, prepared_statement_cache_size, which needs the same decision. For sizing the client and server pools around PgBouncer, see sizing async connection pools for throughput.

Verify: the pooler's version and max_prepared_statements, and the client's statement_cache_size, are set together in configuration and covered by a concurrent integration test.

Configuring asyncpg for a connection pooler A decision on What sits between asyncpg and PostgreSQL with 3 outcomes. Configuring asyncpg for a connection pooler What sits between asyncpg and PostgreSQL? PgBouncer 1.21+, configurable max_prepared_statements >= cache 12,465/s a pooler without tracking statement_cache_size=0 8,185/s nothing, direct defaults 9,614/s at 50 conns Transaction mode in every pooled case.

Verification

asyncpg works correctly behind PgBouncer when:

  • A concurrent load test through PgBouncer shows no InvalidSQLStatementNameError.
  • Either max_prepared_statements is set on PgBouncer at or above asyncpg's cache size, or the cache is disabled.
  • Session state is transaction-scoped: SET LOCAL, pg_advisory_xact_lock, no LISTEN through the pooler.
  • The configuration is explicit on both sides.

Diagnostic Hook: when a service starts failing with prepared statement "__asyncpg_stmt_N__" does not exist after a deployment change, check whether a transaction-mode pooler was added in front of PostgreSQL or its max_prepared_statements changed. The error only appears with concurrency: 152 of 199 parallel calls failed, while sequential calls from one client can pass.

Pitfalls & edge cases

  • Default settings on both sides. Measured: 89.9% of queries failed.
  • Testing with one client. The failure needs transactions spread across server connections.
  • max_prepared_statements smaller than the client cache. Statements are evicted and re-prepared.
  • Session-level state through transaction pooling. LISTEN and session locks do not survive.

Frequently Asked Questions

Why does asyncpg fail with prepared statement does not exist behind PgBouncer?

In transaction mode, consecutive transactions can use different server connections, and asyncpg's cached statement exists only where it was prepared. 89.9% of queries failed.

Should I set statement_cache_size=0 for PgBouncer?

If PgBouncer cannot track prepared statements, yes: errors stopped at 8,185 queries/s. With PgBouncer 1.21+ and max_prepared_statements, keeping the cache reached 12,465.

What does PgBouncer's max_prepared_statements do?

It makes PgBouncer track protocol-level prepared statements per client and prepare them on whichever server connection a transaction lands on. Set it at least to asyncpg's cache size.

Is asyncpg faster through PgBouncer than directly?

In this test it was: 12,465 queries/s through 10 pooled server connections against 9,614 directly with 50, because fewer server connections contended less.