DEV Community

R (RYUSLOG DEV)
R (RYUSLOG DEV)

Posted on Originally published at ryuslog.com

asyncpg DuplicatePreparedStatement on PgBouncer: Why Supabase Breaks Your Pool

A FastAPI service works locally, then fails in production with asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "__asyncpg_stmt_1__" already exists, or with ConnectionFailureError: connection is closed mid-request. The database is Supabase or PgBouncer, the connection string came from the dashboard, and no application code changed.

Neither error is about your SQL. Both come from using a client-side statement cache against a pooler that does not keep a session.

Why the statement collides

asyncpg prepares statements and caches them per connection, keyed by SQL text, up to statement_cache_size (100 by default). Each prepared statement gets a server-side name like __asyncpg_stmt_1__.

Under a direct connection that name is unique per session, so the cache is a pure win. Under transaction pooling, PgBouncer hands every transaction a possibly different server connection: the cache no longer describes anything real, and two clients that both prepared __asyncpg_stmt_1__ can land on the same server session, where the second prepare fails with SQLSTATE 42P05.

import asyncpg

# correct for the Supabase transaction pooler (port 6543) and PgBouncer transaction mode
pool = await asyncpg.create_pool(
    dsn,
    min_size=1,
    max_size=5,              # small: the pooler multiplies, not the app
    statement_cache_size=0,  # no server-side prepared statements
    max_cached_statement_lifetime=0,
    command_timeout=30,
    server_settings={"application_name": "api"},
)
Enter fullscreen mode Exit fullscreen mode

With SQLAlchemy, set it at the dialect level too, because SQLAlchemy builds the asyncpg pool itself:

create_async_engine(
    "postgresql+asyncpg://user:pass@host:6543/postgres",
    poolclass=NullPool,                     # let the pooler pool
    connect_args={
        "statement_cache_size": 0,
        "prepared_statement_cache_size": 0, # SQLAlchemy's own layer
        "server_settings": {"application_name": "api"},
    },
)
Enter fullscreen mode Exit fullscreen mode

Both knobs exist because there are two caches: asyncpg's, and SQLAlchemy's prepared-statement cache on top of the same driver. Setting only one is the most common half-fix.

Which endpoint you are actually on

Endpoint Port Prepared statements App pool size Use for
Direct connection 5432 supported, cache them your real budget migrations, LISTEN/NOTIFY
Session pooler 5432 supported per session small long sessions, ORMs without pooling
Transaction pooler 6543 not supported across transactions 1–5 per worker most web traffic
Transaction pooler + cache 6543 collisions any never

When you see DuplicatePreparedStatementError the cache met a transaction pooler; TooManyConnectionsError means the app pool is larger than the pooler budget; InvalidPasswordError usually means the username is postgres instead of postgres.<project-ref>.

Reproduce it instead of guessing

A single-worker test never reproduces the collision. Run the same query from many coroutines against the pooler — if it passes with statement_cache_size=0 and fails without it, the diagnosis is confirmed. Do not "fix" it by catching DuplicatePreparedStatementError and retrying: the retry prepares the same name on another server connection and the error moves instead of disappearing.

ConnectionFailureError is not a bug in your query

The pooler recycles server connections aggressively — Supabase's transaction pooler closes idle ones long before a typical web worker notices. asyncpg keeps the client object and only learns the socket is gone when it writes. Retry only on ConnectionFailureError and InterfaceError, never on a query error: replaying an INSERT that failed before commit is fine, replaying one that timed out after commit is not.

async def fetch_one(pool, sql, *args):
    for attempt in range(2):
        try:
            async with pool.acquire() as conn:
                return await conn.fetchrow(sql, *args)
        except asyncpg.ConnectionFailureError:
            if attempt:
                raise
Enter fullscreen mode Exit fullscreen mode

pool_pre_ping narrows the window after an idle close but does not remove the need to retry. Keeping min_size=1 and running an occasional SELECT 1 from a background task keeps a warm client, so the first request after a quiet period does not fail.

Transaction pooling also gives one server connection per transaction — exactly what async with conn.transaction() needs, and exactly what a bare SET does not survive. Use SET LOCAL for statement_timeout or search_path, and keep advisory locks, LISTEN, temporary tables, cursors and Alembic migrations on port 5432, where a session exists.

Two traps specific to Supabase

The direct host is IPv6-only. db.<project-ref>.supabase.co resolves to IPv6 unless the IPv4 add-on is enabled, so a container or CI runner without IPv6 gets socket.gaierror: [Errno -9] Address family for the host is not supported. It looks like DNS and is not; use a pooler host, and note the pooler username is postgres.<project-ref>.

Your pool size is multiplied by the pooler. Four uvicorn workers with max_size=20 demand 80 server connections from a pooler that may allow far fewer, and the failure only appears under load. Keep a handful per worker (max_size = 5) and let the pooler queue; NullPool is the simplest correct choice for serverless.

Checklist before you file a bug

  • Both cache switches set to 0.
  • Port 6543 only for traffic that does not need a session.
  • max_size small, per worker.
  • Retry on ConnectionFailureError / InterfaceError only.

Format the failing statement with the SQL formatter before hunting a syntax difference, diff two connection strings character by character with the text diff tool, and split a copied postgres:// URL into its parts with the URL parser.

The longer version, with the pool sizing table and the keepalive snippet, is on my site: asyncpg DuplicatePreparedStatement on PgBouncer and Supabase.

Top comments (0)