Supabase Connection Pooler pool_mode Prepared Statement Deallocation Behavior

Short answer

In pool_mode = transaction, the effective cache boundary for prepared statements is the backend connection's lifetime, not your transaction. There is no documented per-connection maximum prepared statement count on the Supabase pooler: named statements accumulate on each pooled backend until PostgreSQL runs out of memory or you deallocate them. The only safe cleanup pattern is one you run yourself, inside the same transaction that created the statement, or by avoiding named statements entirely.

1. Is there a configured maximum cache size?

No published one. PgBouncer-style transaction pooling does not impose its own prepared-statement limit; statements live in the PostgreSQL backend, and PostgreSQL caps them only by available memory. Supabase does not document a pool-size or statement-cache ceiling per pricing tier, so you should treat the limit as "unbounded until it hurts." You can sanity-check your own project:

-- connect through the pooler endpoint (port 6543)
SHOW max_prepared_statements;   -- typically 0 (unlimited) or unset

BEGIN;
PREPARE probe_stmt AS SELECT 1;
COMMIT;
SELECT * FROM pg_prepared_statements;  -- probe_stmt is still there

If probe_stmt survives the COMMIT, you have confirmed the cache boundary is the backend session, not the transaction. That is the behavior to design against.

2. Will Supavisor change this?

Supavisor is designed to preserve PgBouncer's transaction-mode semantics: it does not automatically issue DEALLOCATE when a transaction ends, and current behavior matches that. However, this is a compatibility goal, not a contractual guarantee. Because the migration is ongoing and pooling internals can change without notice, do not write code that depends on statements persisting across transactions or on them being cleaned up. Both assumptions are fragile. Treat any cross-transaction prepared-statement behavior as unverified for your project and re-test after pooler upgrades.

3. A cleanup pattern that survives connection handoff

The core hazard: after your transaction ends, the backend goes back to the pool and may serve a different client. DEALLOCATE ALL issued in a later transaction may run on a completely different backend, so it cannot be relied on as cleanup. The patterns that actually work:

• Deallocate in the same transaction. If you must use a named statement, DEALLOCATE it before COMMIT. The statement never outlives the transaction, so handoff is irrelevant.
• Prefer the unnamed statement. Most drivers' simple query protocol uses unnamed statements, which PostgreSQL discards automatically. If your driver caches named statements (e.g., some configurations of node-postgres or JDBC with preparedStatementCacheQueries), disable or bound that cache at the driver level — that is the supported lever, not server-side cleanup.
• Deterministic names, idempotent prepare. If statements are named after their SQL text (a hash), collisions are harmless re-preparations of identical SQL rather than corruption. This bounds growth to the number of distinct queries.

What to verify on your project

Because Supabase does not publish these limits and pooler behavior can shift during the Supavisor migration, confirm empirically:

1. Run the pg_prepared_statements probe above through your pooler endpoint.
2. Under a load test that prepares many unique named statements, watch backend memory and logs for prepared-statement or out-of-memory errors.
3. Check your driver's statement-cache settings first — in practice, driver-side caches cause more exhaustion incidents than the pooler itself.

One caveat worth flagging: if your observed symptom is "prepared statement already exists" errors rather than memory growth, that points to name collisions across pooled clients, and the fix is driver cache tuning or unique naming — not deallocation policy.