PgBouncer Transaction vs Session Pooling: What Breaks
Port 6543 hands out a server connection per transaction, so prepared statements, SET, LISTEN and cursors stop behaving. Driver-level settings for each stack.

Introduction & Quick Summary
Symptom. The application works against port 5432 and produces ERROR: prepared statement "S_1" does not exist, prepared statement "__asyncpg_stmt_3__" already exists, or current transaction is aborted on port 6543. Or a SET search_path that ran once stops applying, a Django query dies with cursor "c_1" does not exist, and LISTEN/NOTIFY silently never fires.
Cause. PgBouncer in transaction pooling mode assigns a server connection per transaction and returns it immediately afterwards, then runs a reset query. Anything that outlives a transaction — prepared statements, SET parameters, LISTEN registrations, session-level advisory locks, WITH HOLD cursors, temporary tables — either disappears between statements or, worse, belongs to the next client who got that server connection. PgBouncer in session pooling mode trades concurrency for exactly these guarantees and behaves like a direct connection from the client's point of view.
Fix. Choose the pool mode by what the workload needs. For transaction pooling, disable driver-level prepared statements and server-side cursors, scope everything with SET LOCAL inside a transaction, and keep LISTEN, advisory locks, and migration DDL on a session or direct connection. PgBouncer 1.21 and later can also track prepared statements itself with max_prepared_statements, which is off by default.
Core Mechanics, Signatures & Reproducible Setup
The difference is a single sentence: in session mode one client owns one server connection until it disconnects; in transaction mode it owns it only for the duration of a transaction.
; pgbouncer.ini — transaction mode (Supabase port 6543, PgBouncer default in many stacks)
[pgbouncer]
pool_mode = transaction
max_client_conn = 400
default_pool_size = 20
server_reset_query = DISCARD ALL
max_prepared_statements = 0 ; 0 disables protocol-level prepared statements; session mode (Supabase port 5432, classic PgBouncer setup)
[pgbouncer]
pool_mode = sessionPort numbers are the reliable signal with hosted providers: Supabase's transaction pooler listens on 6543, its session pooler on 5432, and the direct database host also uses 5432 but requires IPv6 or the IPv4 add-on. A connection string that was copied from a different environment is the most common cause of "it worked yesterday".
Reproduce a leaked setting with two psql sessions through the pooler:
-- session A
BEGIN;
SET search_path TO tenant_42; -- not scoped, survives the transaction
COMMIT;
-- session B, a different client, seconds later
SHOW search_path; -- tenant_42, or the default, depending on timingSET LOCAL inside the same transaction is the correct form, and it is the one statement that behaves identically on all three endpoints.
Key Comparison Table
| Property | Direct (5432) | Session pooler (5432) | Transaction pooler (6543) |
|---|---|---|---|
| Server connection lifetime | client session | client session | one transaction |
| Prepared statements | yes | yes | no, unless max_prepared_statements |
SET persists | yes | yes | no; use SET LOCAL |
LISTEN / NOTIFY | yes | yes | no |
| Session advisory locks | yes | yes | no |
Temp tables, WITH HOLD cursors | yes | yes | no |
| Server-side cursors (Django) | yes | yes | disable them |
| Concurrent clients per server conn | 1 | 1 | many |
| Driver | Setting for transaction pooling |
|---|---|
| asyncpg | statement_cache_size=0 |
| SQLAlchemy + asyncpg | prepared_statement_cache_size=0 |
| psycopg 3 | prepare_threshold=None |
| node-postgres | avoid the name option on query() |
| Django ORM | DISABLE_SERVER_SIDE_CURSORS = True |
| Rails ActiveRecord | prepared_statements: false |
| JDBC | prepareThreshold=0 |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. prepared statement "S_1" does not exist
This is the reverse of the duplicate error: the client believes it prepared a statement earlier, the server connection it lands on never did, and PostgreSQL reports a missing name. It appears on the second execution of any query, which is why single-request tests pass.
# psycopg 3: stop the driver from preparing at all
conn = psycopg.connect(dsn, prepare_threshold=None)Disabling the cache costs one extra round trip per statement and removes a class of intermittent production errors. Measure before assuming it matters.
2. Django's server-side cursors
Django opens a named cursor for large querysets when the database supports it. Under transaction pooling the cursor dies with the transaction that created it, and the next fetch raises cursor "c_1" does not exist.
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "postgres",
"HOST": "aws-0-us-east-1.pooler.supabase.com",
"PORT": "6543",
"OPTIONS": {"options": "-c statement_timeout=30000"},
"DISABLE_SERVER_SIDE_CURSORS": True,
}
}Without that flag the failure only shows on querysets over DISABLE_SERVER_SIDE_CURSORS's implicit threshold — large pages, exports, admin lists — which is why it reaches production before a test suite sees it.
3. SET versus SET LOCAL
Session parameters set through the pooler leak into unrelated clients and are reset at unpredictable times. The scoped form is the only portable one:
BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL search_path TO tenant_42, public;
SELECT ...;
COMMIT;ALTER ROLE ... SET is the correct place for values that must always apply, since it is applied at connection establishment by the server rather than by the client.
4. LISTEN and realtime features
PgBouncer rejects LISTEN in transaction mode (LISTEN is not allowed), and Supabase Realtime does not use LISTEN through the pooler anyway — it holds its own replication connection. A worker that polls with LISTEN must connect directly or through the session pooler.
# keep long-lived listeners off the transaction pooler
LISTEN_DSN = os.environ["DATABASE_URL_SESSION"] # port 54325. PgBouncer 1.21 and max_prepared_statements
PgBouncer 1.21 added protocol-level tracking: set max_prepared_statements above zero and the pooler rewrites statement names per client, so drivers can prepare again. It is per-server-connection memory and refused above 1000 in current releases.
max_prepared_statements = 100Hosted providers decide this for you; on Supabase's transaction pooler it is disabled, which is why every driver-level fix above stays necessary.
6. Migrations, LOCK, and DDL
Alembic, Prisma Migrate, and Rails migrations hold advisory locks and long transactions. Through the transaction pooler the advisory lock is lost between statements and two deploys can migrate concurrently. Run migrations on port 5432 and keep the pool mode out of that path entirely.
Common Compilation/Runtime Mistakes & Version Gotchas
- Raising
default_pool_sizeto fix latency. A bigger server pool hides a slow query and multiplies connections past the database's own limit. server_reset_query = DISCARD ALLin session mode. It runs after every transaction instead of every session, adding a round trip and clearing state the client expected.pgbouncerin transaction mode withLISTEN-based caching. Cache invalidation silently stops firing.application_nameper request. It is a session setting and will attach to whichever server connection you get next.max_prepared_statementsset high on a small server. Memory grows per server connection, not per client.statement_timeoutset throughPGOPTIONSand expected to persist. Same class of bug; useSET LOCALorALTER ROLE.- Two pool layers. An ORM pool of 20 inside 8 workers, behind a pooler, is 160 client connections queueing for 20 server connections.
Related tools. Check a failing statement's shape with the SQL formatter, diff the two connection strings to confirm which port each environment uses with the text diff, and pull the host, port, and user out of a copied DSN with the URL parser.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres RLS 42P17: infinite recursion detected in policy
A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.
Zero-Downtime Postgres Migrations: lock_timeout and NOT VALID
One ALTER TABLE waiting behind a slow SELECT blocks every query that arrives after it. The timeout-retry loop, NOT VALID constraints and CONCURRENTLY index rules.
Postgres JSONB Indexing: GIN vs Expression B-Tree
GIN answers containment, not equality, and an expression B-tree is the reverse. Choose by operator, match the expression exactly, and prove it with EXPLAIN.
Postgres Deadlock Detected 40P01: Reading pg_locks and Lock Order
The executor locks rows in plan order, not the order you listed them. A consistent lock order, the pg_locks query that proves the cycle, and a retry that works.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.