Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#PostgreSQL#PgBouncer#Supabase#Performance
3D illustration of a pooler valve switching one server cylinder between several client cubes, with lost setting tokens floating away

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.

ini
; 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
ini
; session mode (Supabase port 5432, classic PgBouncer setup) [pgbouncer] pool_mode = session

Port 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:

sql
-- 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 timing

SET 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

PropertyDirect (5432)Session pooler (5432)Transaction pooler (6543)
Server connection lifetimeclient sessionclient sessionone transaction
Prepared statementsyesyesno, unless max_prepared_statements
SET persistsyesyesno; use SET LOCAL
LISTEN / NOTIFYyesyesno
Session advisory locksyesyesno
Temp tables, WITH HOLD cursorsyesyesno
Server-side cursors (Django)yesyesdisable them
Concurrent clients per server conn11many
DriverSetting for transaction pooling
asyncpgstatement_cache_size=0
SQLAlchemy + asyncpgprepared_statement_cache_size=0
psycopg 3prepare_threshold=None
node-postgresavoid the name option on query()
Django ORMDISABLE_SERVER_SIDE_CURSORS = True
Rails ActiveRecordprepared_statements: false
JDBCprepareThreshold=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.

python
# 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.

python
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:

sql
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.

python
# keep long-lived listeners off the transaction pooler LISTEN_DSN = os.environ["DATABASE_URL_SESSION"] # port 5432

5. 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.

ini
max_prepared_statements = 100

Hosted 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_size to fix latency. A bigger server pool hides a slow query and multiplies connections past the database's own limit.
  • server_reset_query = DISCARD ALL in session mode. It runs after every transaction instead of every session, adding a round trip and clearing state the client expected.
  • pgbouncer in transaction mode with LISTEN-based caching. Cache invalidation silently stops firing.
  • application_name per request. It is a session setting and will attach to whichever server connection you get next.
  • max_prepared_statements set high on a small server. Memory grows per server connection, not per client.
  • statement_timeout set through PGOPTIONS and expected to persist. Same class of bug; use SET LOCAL or ALTER 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.