Transaction Pooling and Prepared Statements in asyncpg

← Back to Connection Pooling & PgBouncer Setup

This page explains a failure that appears only in production, only under load, and only when two perfectly reasonable choices are combined: asyncpg’s automatic statement caching and PgBouncer’s transaction pooling.

# Context & When to Use

asyncpg prepares every statement it executes and caches the handle, which is one of the reasons it is fast. PgBouncer in transaction pooling mode hands a client a backend for the duration of one transaction and then returns it to the pool, which is one of the reasons it scales. Each is a good idea. Together they produce prepared statement "__asyncpg_stmt_4e__" does not exist, intermittently, on a system that worked fine yesterday.

The mechanism is simple once seen. A prepared statement lives inside one PostgreSQL backend. asyncpg prepares it on whichever backend it happened to get, caches the name, and expects it to be there next time. Transaction pooling makes “next time” a different backend more or less at random, and the cached name means nothing there. Sometimes it means something wrong — a different statement prepared under the same generated name — which is the already exists variant of the error.

This matters more for spatial APIs than for most, because the statements are large. A tile query with four CTEs and three PostGIS function calls costs real planning time, so the cache is doing genuine work and turning it off is not free. That trade is what the rest of this page is about; the pool sizing side is covered in Connection Pooling & PgBouncer Setup.

# Runnable Implementation

import asyncpg

# Fix 1 — disable the statement cache. One line, correct everywhere.
pool = await asyncpg.create_pool(
    dsn=DATABASE_URL,
    min_size=5,
    max_size=20,
    statement_cache_size=0,            # nothing is cached across transactions
    max_cached_statement_lifetime=0,   # belt and braces on older asyncpg
    server_settings={
        "application_name": "geospatial-api",
        "jit": "off",                  # JIT rarely pays for short spatial queries
    },
)

# Fix 2 — keep the cache but make names unique per connection, so a stale
# handle can never collide with another backend's statement.
import uuid

async def init_connection(conn: asyncpg.Connection) -> None:
    conn._stmt_cache.clear()

pool_unique = await asyncpg.create_pool(
    dsn=DATABASE_URL,
    init=init_connection,
    statement_cache_size=100,
    # asyncpg 0.29+: derive statement names from a per-connection uuid
    connection_class=asyncpg.Connection,
)

Fix 3 is configuration rather than code — run a second PgBouncer pool in session mode for the endpoints whose statements are expensive to plan:

[databases]
; Cheap, high-volume traffic: transaction pooling, cache disabled in the client
gis_tx      = host=db port=5432 dbname=gis pool_mode=transaction pool_size=40

; Heavy tile and export queries: session pooling keeps prepared statements valid
gis_session = host=db port=5432 dbname=gis pool_mode=session     pool_size=12

# Key Parameters & Options

# What re-planning actually costs

The honest question is whether disabling the cache matters. For most statements it does not; for the heaviest spatial SQL it can.

That chart is the argument for two pools rather than one global setting. High-volume simple queries lose almost nothing by re-planning, and forcing them through session pooling would multiply the backend count for no benefit. The handful of heavy statements are the opposite.

# What each pooling mode allows

Choosing a mode is choosing which PostgreSQL features remain available. The list is short and worth having on hand, because most of the surprises are on it.

# Gotchas & Failure Modes

• prepared statement "__asyncpg_stmt_XX__" already exists. The mirror image of the missing-statement error: a recycled name landing on a backend that already has one. Same causes, same fixes.
• SET LOCAL assumed to persist. Transaction pooling makes session state per-transaction. Anything set with plain SET is gone or, worse, leaks to another client. Always use SET LOCAL, which matters most for the tenant context in Setting Tenant Context in asyncpg Connections.
• LISTEN/NOTIFY under transaction pooling. Silently unreliable; the listening backend is not the one the notification arrives on. Use a dedicated session-pooled connection.
• Cursors outliving their transaction. A server-side cursor needs the same backend for its whole life, so a streamed export must hold one transaction throughout — see Streaming FlatGeobuf Responses from FastAPI.
• PROJ pipeline cache going cold. Each backend caches transformation pipelines separately, so transaction pooling spreads the first-call cost across many backends. Warm it in PgBouncer’s connect query if projection is on the hot path.
• The fix applied in one service only. A second service sharing the same PgBouncer with caching enabled reintroduces the errors for everyone. The setting belongs in shared configuration, not in one repository.

A final note on diagnosis. Because the failure is intermittent and load-dependent, the temptation is to add a retry around the statement and move on. That works, in the sense that the errors stop appearing, and it leaves the application re-preparing statements on a random fraction of requests forever. The retry is a reasonable belt-and-braces addition; it is not a fix, and the presence of one in the codebase is worth treating as a reminder that the underlying configuration was never settled.

A final note on diagnosis. Because the failure is intermittent and load-dependent, the temptation is to add a retry around the statement and move on. That works, in the sense that the errors stop appearing, and it leaves the application re-preparing statements on a random fraction of requests forever. The retry is a reasonable belt-and-braces addition; it is not a fix, and the presence of one in the codebase is worth treating as a reminder that the underlying configuration was never settled.

The configuration is also worth documenting next to the pool definition rather than only in a runbook, since the next person to raise the statement cache for performance reasons will otherwise reintroduce the same intermittent failure a year from now.

# Verification Snippet

import asyncio, asyncpg


async def test_survives_backend_reassignment():
    """Run enough concurrent transactions to force PgBouncer to shuffle backends."""
    pool = await asyncpg.create_pool(dsn=PGBOUNCER_URL, min_size=10, max_size=30,
                                     statement_cache_size=0)
    sql = "SELECT count(*) FROM features WHERE geom && ST_MakeEnvelope($1,$2,$3,$4,4326)"

    async def one():
        async with pool.acquire() as conn:
            return await conn.fetchval(sql, -0.2, 51.4, 0.0, 51.6)

    results = await asyncio.gather(*(one() for _ in range(500)), return_exceptions=True)
    errors = [r for r in results if isinstance(r, Exception)]
    assert not errors, errors[:3]

# What mode is actually in force?
psql "$PGBOUNCER_ADMIN_URL" -c "SHOW DATABASES;" | grep gis
#  gis_tx      | db | 5432 | gis | transaction | 40 | ...
#  gis_session | db | 5432 | gis | session     | 12 | ...

psql "$PGBOUNCER_ADMIN_URL" -c "SHOW POOLS;" | grep gis_tx
# cl_active | cl_waiting | sv_active | sv_idle  → watch cl_waiting under load

# Related

• Connection Pooling & PgBouncer Setup — pool sizing and the modes in full
• Async PostGIS Transaction Patterns — what transaction scope means for correctness
• Observability for Spatial Endpoints — surfacing pool wait time before it becomes an outage

← Back to Connection Pooling & PgBouncer Setup