Migrating from Session to Transaction Pooling

This guide is part of Transaction vs Statement Pooling Trade-offs. Session pooling is where most PgBouncer deployments start, because it is compatible with everything: each client keeps one server connection for its whole session, so nothing about the application has to change. It also provides almost no multiplexing — the backend connection count still tracks the client connection count — so the first time the fleet grows, the proxy stops helping:

# session mode: 400 clients, 400 backend connections
SHOW POOLS;
 database | user       | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
 orders   | orders_app |       400 |         37 |       398 |       0 | session
FATAL:  sorry, too many clients already

Transaction pooling collapses those 400 clients onto a few dozen backends, at the cost of breaking anything that assumes a session persists between transactions. The objective is a migration that finds every such assumption before the switch, changes the application where needed, and cuts over with a fast way back.

Rapid incident diagnosis

Confirm the current mode and its ratio. The console shows both:

SHOW POOLS;       -- pool_mode per database
SHOW DATABASES;   -- pool_size, max_connections

A cl_active close to sv_active is session mode’s signature: one server connection per client.

Audit the application for session-scoped behaviour. Five categories break under transaction pooling, and each has a searchable shape:

# 1. session-level SET (not SET LOCAL)
grep -rnE "\bSET +(SESSION +)?[a-z_]+ *(=|TO)" --include=*.py --include=*.rb --include=*.java --include=*.ts . | grep -v "SET LOCAL"
# 2. session advisory locks
grep -rn "pg_advisory_lock\b" --include=* . | grep -v xact
# 3. LISTEN / NOTIFY
grep -rn "LISTEN \|add_listener\|on_notify" --include=* .
# 4. temporary tables and WITH HOLD cursors
grep -rniE "create +temp(orary)? +table|with hold" --include=* .
# 5. server-side prepared statements
grep -rn "prepareThreshold\|statement_cache_size\|prepare_threshold\|max_prepared_statements" --include=* .

Check the database for evidence rather than trusting the grep. Session state in use shows up in the server’s own views:

-- advisory locks held outside a transaction
SELECT l.pid, a.application_name, a.state
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory' AND a.state <> 'active';

-- sessions listening for notifications
SELECT pid, application_name FROM pg_stat_activity WHERE wait_event = 'ClientRead' AND query ILIKE 'listen%';

-- temporary tables in use
SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'pg_temp%';

Measure what the migration would buy. The ratio transaction pooling can reach depends on how long transactions are:

backend_needed = transactions_per_second × mean_transaction_seconds ÷ 0.7

At 1,200 transactions per second averaging 4 ms: 1200 × 0.004 ÷ 0.7 ≈ 7 backends for 400 clients — a ratio of about 57:1.

Mathematical sizing or parameter formula

Size the backend pool from transaction concurrency:

default_pool_size = ceil( transactions_per_second × mean_transaction_seconds ÷ 0.7 )

Round up for burstiness and add reserve_pool_size for spikes. The measured mean transaction time must include application time inside explicit transactions, which is often much larger than the query time.

Client limits stay generous:

max_client_conn ≥ processes × pool_max × 1.5

Transaction duration is the lever. Because backends scale with transaction time, shortening transactions multiplies the ratio:

ratio = clients ÷ (transactions_per_second × mean_transaction_seconds ÷ 0.7)

Moving a 150 ms HTTP call out of a transaction that otherwise takes 4 ms changes the required backends by a factor of nearly 40.

Application pools can shrink after the migration. With the proxy owning reuse, the local pool needs only one process’s concurrency:

app_pool_max ≈ per-process concurrent queries, often 5–10 rather than 20–50

Plan the rollback capacity. During the migration both ports exist, and in the worst case both are in use:

worst_case_backends = session_port_clients + transaction_port_backends

The database must accept that sum for the duration of the cutover.

Exact remediation & configuration

Fix the five categories before switching.

-- SET → SET LOCAL, inside a transaction
BEGIN;
SET LOCAL statement_timeout = '2s';
SELECT set_config('app.tenant_id', $1, true);
-- ...
COMMIT;

-- session advisory lock → transaction-scoped
SELECT pg_advisory_xact_lock(hashtext('nightly-invoice-run'));

Constant settings move to role defaults, where they cost nothing and survive any pooling mode:

ALTER ROLE orders_app SET application_name = 'orders-api';
ALTER ROLE orders_app SET statement_timeout = '2s';

LISTEN/NOTIFY, temporary tables outside a transaction and WITH HOLD cursors have no transaction-scoped equivalent; those code paths need a direct connection that bypasses the proxy.

Run both modes side by side. A second database entry pointing at the same server, in transaction mode, lets services migrate one at a time:

[databases]
orders          = host=orders-db.internal port=5432 dbname=orders pool_mode=session      pool_size=40
orders_txn      = host=orders-db.internal port=5432 dbname=orders pool_mode=transaction  pool_size=12

[pgbouncer]
listen_port = 6432
max_client_conn = 2000
reserve_pool_size = 4
max_prepared_statements = 200        ; PgBouncer 1.21+: keeps prepared statements working
server_reset_query = DISCARD ALL     ; applies to session mode

A service migrates by changing its database name in the connection string from orders to orders_txn — a configuration change, deployable and revertible per service.

Handle prepared statements. On PgBouncer 1.21 and later, max_prepared_statements above zero lets drivers keep using them. On older versions, disable them per driver: prepareThreshold=0 for PostgreSQL JDBC, statement_cache_size=0 for asyncpg, prepare_threshold=None for psycopg, ?pgbouncer=true for Prisma’s engine.

Point migrations and admin tools at the direct port. Advisory locks, CREATE INDEX CONCURRENTLY and session-level settings need a session:

DATABASE_URL="postgres://app@pgbouncer.internal:6432/orders_txn"   # runtime
DIRECT_URL="postgres://migrator@orders-db.internal:5432/orders"    # migrations

Shrink the application pools after the switch, not before: the proxy now owns connection reuse, and a large local pool simply inflates the client count.

Staging the cutover

A migration that flips every service at once has no useful rollback. Doing it service by service, with a rehearsal, takes longer and fails smaller.

Stage one: audit and fix. Work through the five categories with the greps and the server-side queries. Fix what has a transaction-scoped equivalent; list what does not.

Stage two: rehearse in staging. Point one service at the transaction-mode entry under production-like load and watch for the failure signatures: prepared statement "S_1" already exists, advisory locks that never release, LISTEN notifications that never arrive, or settings that appear to apply to the wrong request.

Stage three: migrate the least risky service first. A read-heavy service with no session state proves the configuration end to end with little exposure. Watch its ratio: cl_active ÷ sv_active should rise immediately.

Stage four: migrate the rest, one at a time. After each, check three numbers: the multiplexing ratio, cl_waiting, and the application’s error rate. A service whose ratio stays near 1:1 has session state that the audit missed — usually a driver behaviour rather than application code.

Stage five: retire the session entry. Once no service uses it, remove the orders entry and reclaim its pool_size for the transaction pool.

Keep the rollback trivial. Because the switch is a database name in a connection string, reverting one service is a deploy of one environment variable — which is what makes it safe to migrate during business hours.

Validation & verification

The ratio improved. After each service migrates, compare client and server connections for its pool:

SELECT database, cl_active, sv_active, round(cl_active::numeric / nullif(sv_active,0), 1) AS ratio
FROM (SELECT * FROM pgbouncer.pools) p WHERE database LIKE 'orders%';

Run it from the admin console; a ratio that stays near 1 means session state is still pinning.

No session-state errors. Search application logs for the signatures over the first day: prepared statement, no such savepoint, there is no transaction in progress, or unexplained empty results from a query that follows a SET.

Advisory locks release. The query from the diagnosis section should return no rows outside active transactions.

Backends stay within the pool. On PostgreSQL, sessions from the proxy should equal roughly default_pool_size + reserve_pool_size, not the client count.

Rollback works. Before migrating the second service, actually revert the first one in staging and confirm it returns to session mode cleanly. A rollback that has never been executed is a plan, not a capability.

Frequently Asked Questions

Related

• Transaction vs Statement Pooling Trade-offs — the parent guide to what each mode costs.
• Handling Session-Level Features Under Transaction Pooling — the replacement for each session feature.
• Prepared Statement Caching Under Transaction Pooling — the driver settings the migration needs.
• Configuring PgBouncer Pool Size and max_client_conn — sizing the new transaction pool.