PgBouncer Modes

PgBouncer pool_mode decides when a server connection returns to the pool. Transaction pooling maximizes density; session pooling maximizes compatibility on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
 
[pgbouncer]
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000

When to reach for this: Choosing pool mode for a new service or debugging prepared statement already exists errors.

Working Example

-- Session mode: SET and temp tables persist for client connection lifetime
SET search_path = app, public;
CREATE TEMP TABLE staging (id int);
-- OK in session pooling until disconnect
 
-- Transaction mode: server connection may switch after COMMIT
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT count(*) FROM orders;
COMMIT;
-- SET LOCAL is safe; session-level SET without LOCAL is risky

Verify mode with PgBouncer admin: SHOW CONFIG; -> pool_mode.

What this demonstrates:

Transaction-scoped SET LOCAL vs session SET
Temp tables require session mode (or avoid)
Why ORMs default assumptions matter

Deep Dive

How It Works

session - Server backend pinned to client until disconnect. Highest compatibility, lowest multiplexing.
transaction - Backend assigned per transaction; returned at COMMIT/ROLLBACK. Default for web OLTP.
statement - Backend per statement (rare); breaks multi-statement transactions and most ORMs.

Mode Comparison

Mode	Multiplexing	Temp tables	Prepared stmts
session	Low	Yes	Yes
transaction	High	No	Tricky
statement	Highest	No	Broken

SQL Notes

-- Safe pattern in transaction pooling
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
COMMIT;

Gotchas

LISTEN/NOTIFY in transaction mode - Unreliable across backend swaps. Fix: Session pool or dedicated listener connection.
Advisory locks across requests - Must use session mode. Fix: Separate pool for job workers needing locks.
Schema migrations via pooler - DDL may need session stickiness. Fix: Direct admin connection bypassing pooler.
statement mode with Hibernate - Broken transactions. Fix: Never use statement mode for ORMs.
Forgotten DEALLOCATE ALL on checkout - Some drivers need server_reset_query. Fix: Configure server_reset_query = DISCARD ALL where appropriate.

Alternatives

Alternative	Use When	Don't Use When
session pooling	Heavy temp table ETL in app	Thousands of idle clients
RDS Proxy	AWS managed multiplexing	On-prem K8s
Direct connect (no pool)	Local dev only	Production fleet

FAQs

Default mode for web APIs?

transaction with SET LOCAL and no temp tables.

Does transaction mode break RLS SET?

Use SET LOCAL inside transaction or connection init via startup params.

server_reset_query?

DISCARD ALL clears session state between transactions in transaction mode.

prepared statements?

See dedicated page; often disable in ORM for transaction mode.

multiple databases?

PgBouncer database stanza per target; pools isolated per db/user.

TLS to Postgres?

Pooler connects upstream TLS; clients TLS to pooler.

auth query?

userlist.txt or auth_query for SCRAM with PostgreSQL 18.

transaction pooling and COPY?

COPY in transaction usually OK within single transaction block.

failover?

Pooler config update or DNS to new primary; apps reconnect to pooler.

Next?

Prepared statements pitfalls in transaction mode.

Prepared Statements & Pooling - ORM fixes
Pooling Basics - Why pool
Role & Timeout per Pool - Pool separation
Pooling Best Practices - Operations

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Quick-reference recipe card - copy-paste ready.

; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
 
[pgbouncer]
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000

When to reach for this: Choosing pool mode for a new service or debugging prepared statement already exists errors.

Working Example

-- Session mode: SET and temp tables persist for client connection lifetime
SET search_path = app, public;
CREATE TEMP TABLE staging (id int);
-- OK in session pooling until disconnect
 
-- Transaction mode: server connection may switch after COMMIT
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT count(*) FROM orders;
COMMIT;
-- SET LOCAL is safe; session-level SET without LOCAL is risky

Verify mode with PgBouncer admin: SHOW CONFIG; -> pool_mode.

What this demonstrates:

Transaction-scoped SET LOCAL vs session SET
Temp tables require session mode (or avoid)
Why ORMs default assumptions matter

Deep Dive

How It Works

session - Server backend pinned to client until disconnect. Highest compatibility, lowest multiplexing.
transaction - Backend assigned per transaction; returned at COMMIT/ROLLBACK. Default for web OLTP.
statement - Backend per statement (rare); breaks multi-statement transactions and most ORMs.

Mode Comparison

Mode	Multiplexing	Temp tables	Prepared stmts
session	Low	Yes	Yes
transaction	High	No	Tricky
statement	Highest	No	Broken

SQL Notes

-- Safe pattern in transaction pooling
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
COMMIT;

Gotchas

LISTEN/NOTIFY in transaction mode - Unreliable across backend swaps. Fix: Session pool or dedicated listener connection.
Advisory locks across requests - Must use session mode. Fix: Separate pool for job workers needing locks.
Schema migrations via pooler - DDL may need session stickiness. Fix: Direct admin connection bypassing pooler.
statement mode with Hibernate - Broken transactions. Fix: Never use statement mode for ORMs.
Forgotten DEALLOCATE ALL on checkout - Some drivers need server_reset_query. Fix: Configure server_reset_query = DISCARD ALL where appropriate.

Alternatives

Alternative	Use When	Don't Use When
session pooling	Heavy temp table ETL in app	Thousands of idle clients
RDS Proxy	AWS managed multiplexing	On-prem K8s
Direct connect (no pool)	Local dev only	Production fleet

FAQs

Default mode for web APIs?

transaction with SET LOCAL and no temp tables.

Does transaction mode break RLS SET?

Use SET LOCAL inside transaction or connection init via startup params.

server_reset_query?

DISCARD ALL clears session state between transactions in transaction mode.

prepared statements?

See dedicated page; often disable in ORM for transaction mode.

multiple databases?

PgBouncer database stanza per target; pools isolated per db/user.

TLS to Postgres?

Pooler connects upstream TLS; clients TLS to pooler.

auth query?

userlist.txt or auth_query for SCRAM with PostgreSQL 18.

transaction pooling and COPY?

COPY in transaction usually OK within single transaction block.

failover?

Pooler config update or DNS to new primary; apps reconnect to pooler.

Next?

Prepared statements pitfalls in transaction mode.

Prepared Statements & Pooling - ORM fixes
Pooling Basics - Why pool
Role & Timeout per Pool - Pool separation
Pooling Best Practices - Operations

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

PgBouncer Modes

Search Documentation

PgBouncer Modes