Handling Session-Level Features Under Transaction Pooling

This guide is part of Transaction vs Statement Pooling Tradeoffs. Transaction pooling is what makes a proxy valuable: a server connection is assigned to a client only for the duration of a transaction, so a few dozen backends can serve thousands of clients. Everything that breaks under it breaks for one reason — anything whose state lives in the session rather than the transaction is no longer reliably yours.

The failures are nasty because they are intermittent. Under light load a client often gets the same server connection back and the code appears to work; under load it gets a different one and the state has vanished. Code that passes every test can fail in production at peak, which is exactly the profile of a bug that survives for months.

What has session scope

The rows marked as leaking are the more serious half. It is not only that your state disappears — it is that your state arrives at somebody else’s transaction. A SET ROLE left on a server connection is inherited by the next client that receives it, which is a privilege escalation, not merely a bug.

Replacements, feature by feature

Session parameters

Replace SET with SET LOCAL inside the transaction:

BEGIN;
SET LOCAL statement_timeout = '60s';
SET LOCAL work_mem = '128MB';
SELECT /* the expensive report query */ ...;
COMMIT;   -- both settings revert here

For settings that must apply to every connection rather than to one transaction, put them on the role or the database instead — the proxy’s server connections will inherit them at connect time and they never need to be set per transaction:

ALTER ROLE reporting SET work_mem = '128MB';
ALTER ROLE app_web  SET statement_timeout = '8s';

This is the better answer generally: role-level defaults are declarative, survive restarts, and cannot leak between clients.

Advisory locks

Replace pg_advisory_lock with pg_advisory_xact_lock. The transaction-scoped variant is released automatically at commit or rollback, which is exactly the semantics transaction pooling can support:

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('nightly-invoice-run'));
-- protected work
COMMIT;   -- lock released

If the lock genuinely needs to outlive a transaction — a long-running singleton job, for instance — an advisory lock is the wrong mechanism under transaction pooling. Use a row in a table with an owner and an expiry, renewed by the holder, which is inspectable and recoverable in a way an orphaned advisory lock is not.

LISTEN and NOTIFY

There is no transaction-scoped equivalent; LISTEN is inherently session state. Two workable options:

Bypass the proxy for the listener. Open a direct connection to PostgreSQL for the notification listener only, and route all normal traffic through the proxy. One extra backend for a listener process is affordable, and it keeps the multiplexing benefit for everything else.

Move to an external queue. If you are already running Redis, Kafka, or SQS, the notification path belongs there. LISTEN/NOTIFY has no durability and no delivery guarantee across a reconnect, so a queue is usually an upgrade rather than a compromise.

Note that NOTIFY itself is fine — it is transactional and delivered at commit. Only the listening side needs a session.

Temporary tables

A temp table created in one transaction is invisible in the next and leaks onto a shared backend. Replacements, in order of preference:

• A CTE, if the data is only needed within one query.
• An unlogged table with a session or request identifier column, cleaned up at the end of the transaction. Unlogged tables skip WAL, so they are fast, and they are visible across transactions by design.
• A temp table created and dropped inside a single transaction, which is safe because it never outlives the borrow — but be aware it still writes catalog entries on the shared backend, and heavy use causes catalog bloat.

Prepared statements

Server-side prepared statements are named objects in a session. Under transaction pooling a PREPARE on one backend is not found on another, giving prepared statement "S_1" does not exist.

Modern PgBouncer (1.21 and later) supports max_prepared_statements, which tracks named statements and re-prepares them on whichever server connection is assigned. Enable it if your driver relies on server-side prepares:

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

Otherwise, disable server-side prepares in the driver — prepareThreshold=0 for the PostgreSQL JDBC driver, statement_cache_size=0 for asyncpg, prepare_threshold=None for psycopg 3, binary_parameters=yes and simple protocol for some Go drivers. The specifics are in PgBouncer Transaction vs Statement Pooling.

Cursors

DECLARE ... WITH HOLD needs the cursor to survive the transaction, which it cannot. Cursors declared without WITH HOLD are fine — they live inside the transaction.

Django’s QuerySet.iterator() uses server-side cursors by default on PostgreSQL, which is why DISABLE_SERVER_SIDE_CURSORS = True is required behind a transaction-mode proxy. SQLAlchemy’s stream_results has the same shape. When you need to iterate a large result set without a holdable cursor, keyset pagination in a loop of short transactions is the reliable alternative.

Detecting the problem before production does

Two techniques catch these before they become an intermittent incident.

Set a small pool and shuffle it. In a staging environment, configure the proxy with default_pool_size = 2 and server_lifetime = 30. Server connections then churn constantly and any session-state dependency fails quickly and reproducibly rather than once a week at peak.

Audit the server-side view. Query for state that should not exist between transactions:

-- advisory locks held by backends not currently in a transaction
SELECT a.pid, a.state, l.objid, l.classid
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory'
  AND a.state <> 'active';

-- temporary schemas left behind on shared backends
SELECT nspname FROM pg_namespace WHERE nspname LIKE 'pg_temp_%';

-- non-default settings sitting on idle backends
SELECT pid, application_name, setting
FROM pg_settings, pg_stat_activity
WHERE name = 'statement_timeout' AND state = 'idle';

Anything the first two queries return under transaction pooling is a bug. The technique for reading these views in general is in PostgreSQL Server-Side Connection Diagnostics.

Common failure patterns and remediation

When to use session pooling instead

Sometimes the honest answer is that a workload does not fit transaction pooling. A migration tool that holds advisory locks across statements, a job runner that depends on LISTEN, or a legacy application that sets session parameters everywhere will not be worth rewriting.

Run those on a separate proxy pool in session mode, pointed at the same database:

[databases]
orders       = host=db.internal dbname=orders pool_mode=transaction pool_size=25
orders_admin = host=db.internal dbname=orders pool_mode=session      pool_size=5

The session-mode pool gives up multiplexing — one server connection per client for the client’s whole session — which is why it must be small and reserved for the workloads that need it. Everything else keeps the transaction-mode pool and its ratio. Sizing both against the same ceiling is covered in configuring PgBouncer pool_size and max_client_conn.

Operational boundary

Transaction pooling is a trade, and the trade is not negotiable: you get a large client-to-server ratio and you give up session state. No configuration recovers both. What you can do is confine the cost — route session-dependent workloads to their own small session-mode pool, replace session-scoped constructs with transaction-scoped ones everywhere else, and make the substitution a review checklist item rather than something rediscovered per incident.

The one thing not to do is set pool_mode = session globally to make an error go away. That eliminates the multiplexing you deployed the proxy for, and the fleet’s connection count reverts to what it was before — usually while everyone believes the proxy is still protecting the database.

Frequently Asked Questions

Related

• Transaction vs Statement Pooling Tradeoffs — the parent guide comparing the modes.
• PgBouncer Transaction vs Statement Pooling — the proxy-side configuration.
• Configuring PgBouncer pool_size and max_client_conn — sizing a mixed-mode deployment.
• PostgreSQL Server-Side Connection Diagnostics — auditing for leaked session state.
• Enabling Django Connection Pooling with psycopg3 — a framework that needs a specific setting behind a proxy.