Handling asyncpg Prepared Statement Errors with PgBouncer

Set statement_cache_size=0 and prepared_statement_cache_size=0 in your engine's connect_args — this disables asyncpg's prepared statement cache and eliminates all PgBouncer transaction-mode incompatibility errors. This page drills into one of the sharpest edges catalogued in the parent dialect-specific gotchas and driver quirks guide.

Quick Answer

The moment you introduce PgBouncer in transaction pooling mode between your application and PostgreSQL, asyncpg's default prepared statement cache becomes a liability. Here is the before/after fix:

# Before — asyncpg with default prepared statement cache
# Raises InvalidSQLStatementNameError behind PgBouncer transaction pooling
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@pgbouncer:6432/mydb",
    pool_size=10,
    max_overflow=20,
)

# After — asyncpg with prepared statement cache disabled
# Works correctly behind PgBouncer in any pooling mode
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@pgbouncer:6432/mydb",
    pool_size=10,
    max_overflow=20,
    connect_args={
        "statement_cache_size": 0,           # disable client-side LRU cache
        "prepared_statement_cache_size": 0,  # asyncpg ≥ 0.28 second cache key
    },
)

Both keys are required. statement_cache_size has existed since asyncpg's early versions; prepared_statement_cache_size was introduced in asyncpg 0.28 as a separate control for a refactored cache layer. Setting only one leaves the other active, which will surface the same errors on asyncpg 0.28 and later.

Execution Context & Async Workflow Integration

Why asyncpg Caches Prepared Statements

When asyncpg executes a query for the first time on a connection, it sends a PostgreSQL wire protocol Parse message. PostgreSQL responds with a ParseComplete and registers the statement under a generated name like __asyncpg_stmt_0__. Subsequent executions of the same query text on the same connection send a Bind message referencing that name, skipping the parse and plan phases entirely.

This is a genuine performance win on a dedicated connection: parsing and planning can account for 10–30% of total query latency on short OLTP queries. asyncpg's cache is a direct application of this optimisation.

Why Transaction Pooling Breaks It

PgBouncer's transaction pooling mode assigns a backend PostgreSQL connection to a client only for the duration of a single transaction. Once your COMMIT or ROLLBACK fires, PgBouncer is free to route your next transaction to a completely different backend server — one that has never seen __asyncpg_stmt_0__.

When your next query arrives and asyncpg sends a Bind message referencing that name, the new backend returns an error:

asyncpg.exceptions.InvalidSQLStatementNameError:
prepared statement "__asyncpg_stmt_0__" does not exist

The sequence is:

1. Connection A → Backend-1: Parse __asyncpg_stmt_0__ (SELECT * FROM orders WHERE id = $1)
2. Transaction commits → PgBouncer releases Backend-1
3. Connection A → Backend-7 (new backend): Bind __asyncpg_stmt_0__ → ERROR

Because asyncpg's cache stores the name but not the query text, it cannot transparently re-issue the Parse on the new backend. SQLAlchemy does not intercept this at the pool level by default.

The Full Fix with Async Engine Configuration

# Full async engine setup for PgBouncer transaction pooling
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://app_user:secret@pgbouncer.internal:6432/production",
    # Pool settings: these are SQLAlchemy pool settings, not PgBouncer settings
    pool_size=10,           # concurrent connections from this process
    max_overflow=20,        # burst connections above pool_size
    pool_timeout=30,        # seconds to wait for a connection from pool
    pool_recycle=1800,      # recycle connections every 30 min (avoid stale TCP)
    pool_pre_ping=True,     # validate connection before checkout
    # Driver-level settings: passed verbatim to asyncpg.connect()
    connect_args={
        "statement_cache_size": 0,
        "prepared_statement_cache_size": 0,
        # Optional but recommended: set application_name for pg_stat_activity
        "server_settings": {
            "application_name": "order_service",
        },
    },
)

AsyncSessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

# Usage — identical whether or not PgBouncer is in front
async def get_pending_orders(tenant_id: int) -> list[Order]:
    async with AsyncSessionLocal() as session:
        result = await session.execute(
            select(Order)
            .where(Order.tenant_id == tenant_id, Order.status == "pending")
            .order_by(Order.created_at.desc())
        )
        return result.scalars().all()

The pool-level knobs above (pool_size, max_overflow, pool_recycle) are the same ones covered in configuring async engines and connection pools; they govern SQLAlchemy's client-side pool and are entirely independent of PgBouncer's own server-side pool. pool_pre_ping=True is especially important behind PgBouncer: it issues a SELECT 1 before returning a connection from the pool, catching connections that have been closed by PgBouncer's server_idle_timeout or server_lifetime settings.

Resolving Warnings, Errors & Common Mistakes

Advanced Prepared Statement Error Mitigation

Session Pooling as an Alternative

If disabling the prepared statement cache causes measurable query latency regression (which it rarely does for OLTP workloads, but may matter for analytical queries), consider switching PgBouncer to session pooling mode instead of transaction pooling mode.

In session pooling, a client gets a dedicated backend connection for the entire session lifetime — prepared statements persist correctly. The trade-off is that PgBouncer provides less multiplexing benefit, requiring more backend connections:

# pgbouncer.ini — session pooling mode (no asyncpg changes needed)
[databases]
mydb = host=postgres.internal dbname=mydb

[pgbouncer]
pool_mode = session        # was: transaction
max_client_conn = 1000
default_pool_size = 50

If you would rather not touch your pooler configuration at all, the other escape hatch is to change drivers: psycopg3's default text protocol does not create server-side prepared statements, so it works behind PgBouncer transaction pooling with no connect_args changes.

Session pooling is appropriate when:

• Your workload issues many queries per session and benefits from the parse cache
• You can afford more PostgreSQL backend connections (each client maps to one backend for session duration)
• Your sessions are short-lived (e.g., HTTP request handlers that use async with AsyncSession() as session)

Transaction pooling is appropriate when:

• You need to multiplex thousands of application connections onto a small PostgreSQL max_connections
• Prepared statement caching is disabled (statement_cache_size=0)

Verifying the Fix with pg_stat_statements

After deploying statement_cache_size=0, confirm that asyncpg no longer sends Parse messages by querying pg_stat_statements:

# Verify no prepared statements are being created
from sqlalchemy import text

async def check_prepared_statements(session: AsyncSession) -> list[dict]:
    result = await session.execute(
        text("""
            SELECT
                name,
                statement,
                prepare_time
            FROM pg_prepared_statements
            ORDER BY prepare_time DESC
            LIMIT 20
        """)
    )
    rows = result.fetchall()
    return [{"name": r.name, "statement": r.statement[:80]} for r in rows]

With statement_cache_size=0, this query should return zero rows from your application's connection. Any remaining rows are from other clients (e.g., admin tools, migration runners) that do not go through asyncpg.

Detecting PgBouncer in the Connection Path

If you are uncertain whether PgBouncer is in your connection path (e.g., in cloud-managed databases like Cloud SQL Proxy or RDS Proxy, both discussed in tuning connection pools for cloud databases):

# Detect PgBouncer by checking for its signature GUC
from sqlalchemy import text

async def is_behind_pgbouncer(session: AsyncSession) -> bool:
    try:
        result = await session.execute(text("SHOW server_version"))
        version = result.scalar()
        # PgBouncer returns its own version string, not PostgreSQL's
        return "PgBouncer" in (version or "")
    except Exception:
        return False

async def check_pool_mode(session: AsyncSession) -> str | None:
    try:
        result = await session.execute(text("SHOW pool_mode"))
        return result.scalar()
    except Exception:
        # SHOW pool_mode is a PgBouncer command; raises on real Postgres
        return None

As a rule: if there is any proxy, pooler, or cloud SQL connector between your application and PostgreSQL, treat it as PgBouncer transaction mode and disable prepared statement caching. The performance cost of disabling the cache is negligible compared to the operational cost of debugging InvalidSQLStatementNameError in production.

Frequently Asked Questions

Related

• Dialect-Specific Gotchas and Driver Quirks — full comparison of asyncpg, psycopg3, aiomysql, and aiosqlite quirks including type codecs, RETURNING support, and isolation defaults.
• Choosing Between asyncpg and psycopg Async Drivers — if the prepared statement cache is a dealbreaker, psycopg3 with text protocol is PgBouncer-compatible by default.
• Configuring Async Engines and Connection Pools — pool_size, max_overflow, pool_pre_ping, and pool_recycle settings that complement PgBouncer configuration.
• Tuning Connection Pools for Cloud Databases — RDS Proxy, Cloud SQL Auth Proxy, and AlloyDB Omni connection tuning with PgBouncer-aware settings.