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"},
)
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"},
},
)
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
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_sizesmall, per worker. - Retry on
ConnectionFailureError/InterfaceErroronly.
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)