0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Transaction vs Session Pooling Modes

Pick the right PgBouncer mode and know which features break under transaction pooling.

Transaction vs Session Pooling Modes is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Pooling Modes Matter

Each PostgreSQL backend process costs memory (work_mem, catalog caches, plan caches) and CPU. A few thousand idle client connections can exhaust a server even when nothing is running.

PgBouncer sits between your app and PostgreSQL, multiplexing many client connections onto a small set of real server connections. The pool_mode setting decides when a server connection is handed back to the pool.

  • session — server held for the client's whole session
  • transaction — server held only for one transaction
  • statement — server released after every statement

Session Pooling Mode

In session pooling, a server connection is assigned to a client when it connects and is only returned to the pool when the client disconnects.

This is the safest mode: the client gets a dedicated backend for its entire lifetime, so every PostgreSQL feature behaves exactly as if it connected directly. The cost is poor reuse — an idle but connected client still pins a server connection.

; PgBouncer config: pgbouncer.ini
[pgbouncer]
pool_mode = session
max_client_conn = 10000
default_pool_size = 20

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

Transaction Pooling Mode

In transaction pooling, the server connection is assigned only for the duration of a single transaction. The instant the transaction commits or rolls back, the backend goes back to the pool and may serve a different client next.

This gives dramatically better reuse: thousands of mostly-idle clients can share a tiny pool, because a server connection is only borrowed during active work. It is the recommended mode for web apps with many short-lived requests.

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20

; 10000 clients multiplexed onto only 20 backends

The Core Tradeoff

The decision is reuse versus feature compatibility:

  • Session: full compatibility, low connection reuse.
  • Transaction: high reuse, but anything that relies on state outside a transaction can break.

The key insight: in transaction mode, consecutive transactions from the same client may land on different backends. Anything that lives on the connection between transactions is unsafe.

What Breaks: Session-Level State

Because a backend is shared across clients between transactions, any session-scoped state set in one transaction can leak to another client or be lost.

These commonly break under transaction pooling:

  • SET / SET SESSION session GUCs (e.g. SET statement_timeout, SET search_path outside a transaction)
  • Session-level advisory locks (pg_advisory_lock)
  • LISTEN / NOTIFY subscriptions
  • Unparameterized session variables and WITH HOLD cursors
-- Unsafe in transaction pooling: runs in its own tx,
-- the GUC is reset before your next query reuses a backend
SET statement_timeout = '5s';

-- Session advisory lock may be acquired on one backend
-- and never matched by the unlock on another
SELECT pg_advisory_lock(42);

What Breaks: Prepared Statements

Named prepared statements are stored on a specific backend. In transaction mode, your next execution may hit a different backend that has never seen that prepared statement, causing errors like prepared statement "sN" does not exist.

Mitigations:

  • Disable client-side prepared statements, or use simple/unnamed protocol.
  • PgBouncer 1.21+ supports max_prepared_statements to track and re-prepare named statements per backend automatically.
; PgBouncer 1.21+ : safely allow named prepared statements
; in transaction mode by tracking them per server connection
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

Keep Settings Inside the Transaction

If you need a GUC like statement_timeout or search_path under transaction pooling, scope it to the transaction with SET LOCAL. It applies only until the transaction ends, so it can never leak to the next client on that backend.

Use this pattern instead of a bare SET.

BEGIN;
  SET LOCAL statement_timeout = '5s';
  SET LOCAL search_path = analytics, public;

  SELECT count(*) FROM orders WHERE created_at >= now() - interval '1 day';
COMMIT;

Long Transactions Pin the Pool

Transaction pooling only reuses backends between transactions. A long-running or idle-in-transaction query holds its backend the entire time, just like session mode would.

If many clients hold open transactions, the small pool drains and new requests queue. Guard against this:

  • Set a low idle_in_transaction_session_timeout on the server.
  • Keep transactions short; never BEGIN then wait on app-side I/O.
-- Server-side safety net (postgresql.conf or ALTER ROLE)
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10s';

-- Now an app that BEGINs and stalls gets its backend
-- reclaimed instead of starving the PgBouncer pool

Sizing default_pool_size

Under transaction pooling, default_pool_size is the number of real backends per (database, user) pair. Because work is interleaved, you need far fewer backends than clients.

A common starting point is roughly the number of CPU cores available for queries, not the number of clients. Oversizing the pool just recreates the connection-storm problem you used PgBouncer to avoid.

[pgbouncer]
pool_mode = transaction
default_pool_size = 20      ; ~ matches Postgres CPU capacity
min_pool_size = 5           ; keep warm backends ready
reserve_pool_size = 5       ; burst headroom
max_client_conn = 10000     ; how many apps can attach

Inspecting Pool Behavior

PgBouncer exposes a virtual admin database. Connect to it and run SHOW POOLS; to see, per pool, how many clients are active/waiting and how many server connections are active/idle.

If cl_waiting is consistently above zero, clients are queuing for a backend — either raise default_pool_size or shorten transactions.

-- psql -p 6432 pgbouncer
SHOW POOLS;
-- columns: database | user | cl_active | cl_waiting
--          sv_active | sv_idle | sv_used | pool_mode

SHOW STATS;   -- query/transaction throughput per database

Per-Database Mode Overrides

You do not have to pick one mode globally. Set a default pool_mode and override it per database. A typical split:

  • Main OLTP app database in transaction mode for maximum reuse.
  • A legacy or admin database that uses LISTEN/NOTIFY, advisory locks, or temp tables in session mode for correctness.
[databases]
; high-concurrency web traffic -> transaction reuse
appdb  = host=127.0.0.1 dbname=appdb pool_mode=transaction

; uses LISTEN/NOTIFY + session advisory locks -> keep session
jobsdb = host=127.0.0.1 dbname=jobsdb pool_mode=session

Quick Check

Choose the correct behavior under PgBouncer transaction pooling.

Recap

Session vs transaction pooling, distilled:

  • Session mode: backend held until client disconnects. Full feature compatibility, low reuse. Use it for databases needing LISTEN/NOTIFY, session advisory locks, or persistent prepared statements.
  • Transaction mode: backend released per transaction. High reuse for many short requests, but session-level state can leak or vanish.
  • What breaks in transaction mode: bare SET GUCs, named prepared statements, session advisory locks, LISTEN/NOTIFY, WITH HOLD cursors.
  • Fixes: SET LOCAL inside a transaction, max_prepared_statements (1.21+), short transactions, idle_in_transaction_session_timeout, and per-database pool_mode overrides.

Frequently asked questions

Is the “Transaction vs Session Pooling Modes” lesson free?

Yes — the full text of “Transaction vs Session Pooling Modes” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Transaction vs Session Pooling Modes”?

Pick the right PgBouncer mode and know which features break under transaction pooling. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Transaction vs Session Pooling Modes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Connections Are Expensive in PostgreSQL
  2. Transaction vs Session Pooling Modes
  3. Sizing Pools Against Core Count
  4. Diagnosing Pool Saturation and Queueing
← Back to PostgreSQL Performance & Query Optimization