How does PgBouncer transaction mode work?
PgBouncer is a lightweight proxy that sits between your application and Postgres. Clients connect to PgBouncer, and PgBouncer multiplexes those client connections onto a smaller set of real server connections. This is connection pooling in the proxy itself rather than in each application process.
Teams add it because Postgres connections are expensive. Each connection is a separate backend process with its own memory, and a large number of mostly idle connections still costs resources and adds contention. When dozens of app instances each run their own in-process pool, the total connection count grows with every deploy and autoscaling event. PgBouncer caps what Postgres actually sees.
The pool mode decides when a server connection goes back to the pool:
| Mode | Server connection returns to pool | What the client can rely on |
|---|---|---|
| Session | When the client disconnects | Full session semantics |
| Transaction | When a transaction ends | Only state scoped to the current transaction |
| Statement | After each statement | No multi-statement transactions |
PgBouncer transaction mode gives the best connection reuse while still allowing real transactions, which is why it is the common choice. It also changes a basic assumption. Two consecutive transactions from the same client can run on different backends, so your application no longer owns a session.
How it works, step by step
- The client connects to PgBouncer, not Postgres. PgBouncer authenticates the client itself. Holding the client connection open does not reserve a server connection.
- The client starts work. When the client sends
BEGIN, or a single statement in autocommit mode, PgBouncer takes an idle server connection from the pool for that database/user pair. If none is idle and the pool is already at its size limit, the client waits in a queue. - PgBouncer forwards traffic. Every statement in that transaction goes to the same server connection.
- The transaction ends. Postgres reports to the client after each command whether a transaction is still open. When it reports that the connection is idle, meaning after
COMMIT,ROLLBACK, or an autocommit statement, PgBouncer detaches the server connection from the client and returns it to the pool. - The next transaction may go anywhere. The client's next transaction gets whichever server connection is free at that moment.
The result is that server connections are held only while a transaction is in progress. The number of busy server connections tracks concurrent transactions, not connected clients.
The cleanup step makes this worse. server_reset_query (typically DISCARD ALL) runs when a server connection is released in session mode. In transaction mode it is skipped by default. Any session-level state left behind, such as a SET, a temp table, or an advisory lock, stays on that backend and is inherited by whichever client gets it next. That is a correctness bug, and when the leaked state is something like a role or a security setting, it is a security problem.
App instances: many client connections
+------------+ +------------+ +------------+
| Client A | | Client B | | Client C |
+------------+ +------------+ +------------+
| | | |
| tx1 | tx2 | tx3 | tx4
v v v v
+------------------------------------------------------------+
| PgBouncer (pool_mode = transaction) |
| - server conn goes back to pool when each tx ends |
| - server_reset_query skipped between tx by default |
+------------------------------------------------------------+
| | |
| A.tx1, then B.tx3 | A.tx2 | C.tx4
v v v
+-------------------+ +-------------------+ +-------------------+
| Backend 1 | | Backend 2 | | Backend 3 |
| t1 A.tx1: | | t2 A.tx2: | | t4 C.tx4 |
| SET search_path | | A's SET is not | | |
| (session-level) | | present here | | |
| t3 B.tx3: | | | | |
| inherits A's SET | | | | |
+-------------------+ +-------------------+ +-------------------+
Postgres: small fixed set of backend processes (one per server conn)
In transaction mode, PgBouncer can send Client A's consecutive transactions to different backends. Because no reset runs between transactions by default, a session-level SET from A stays on Backend 1 and Client B inherits it there.
Who should use PgBouncer transaction mode?
Transaction mode fits when the number of client connections is much larger than the number of transactions running at the same moment. Typical cases:
- Horizontally scaled services. Many app instances or worker processes, each with its own in-process pool, so the total client connection count is far above what Postgres should handle.
- Autoscaling and short-lived processes. Deploys, scale-out events, and serverless or job-style workers open new connections in bursts. PgBouncer absorbs the burst, and Postgres sees a stable number of backends.
- Several services sharing one database. Each service gets its own pool, and the total is capped in one place instead of being negotiated per team.
- OLTP traffic with short transactions. Request handlers that run a few quick queries and commit benefit most, because each server connection serves many clients in turn.
It is a poor fit when the application depends on session state that you cannot scope to a transaction, or when most work is long-running transactions. In that case pooling gives little reuse. If you run a few instances whose pools stay comfortably under max_connections, you may not need a pooler at all. The section on when to avoid transaction mode covers these cases in detail.
Which session state breaks in transaction mode, and what replaces it?
Every transaction may run on a different server connection, so anything scoped to the session either disappears or leaks into another client's work. The fix is to scope the state to the transaction, or to move durable defaults into the server's configuration.
| Session feature | Failure in transaction mode | Safe replacement |
|---|---|---|
SET search_path, SET timezone |
Applies to whichever backend ran it, then leaks to the next client | SET LOCAL or set_config(name, value, true) inside the transaction |
| Per-role or per-database defaults set at connect | Lost when the client lands on another backend | ALTER ROLE ... SET or ALTER DATABASE ... SET |
pg_advisory_lock |
Held by a backend the client no longer owns; unlock may hit a different backend | pg_advisory_xact_lock, released at commit or rollback |
LISTEN |
Notifications go to a backend that is no longer yours | A dedicated direct or session-mode connection |
| Temp tables | Remain visible on the backend after commit | CREATE TEMP TABLE ... ON COMMIT DROP |
DECLARE ... WITH HOLD cursors |
The cursor outlives the transaction on a shared backend | A non-holdable cursor inside one transaction, or keyset pagination |
SQL PREPARE |
The statement exists on only one backend | See the next section |
Auditing your hand-written SQL does not catch all of these. Drivers and ORMs often run their own setup statements right after connecting, such as setting search_path, the time zone, or an initial SQL hook defined in pool configuration. PgBouncer tracks and re-applies a small set of startup-packet parameters, including TimeZone and application_name. A SET sent as an ordinary query is not tracked, so it behaves like any other statement. Check driver connection options and ORM initialization callbacks, and log statements on a staging database to see what the client actually sends.
-- Session-scoped (unsafe behind PgBouncer transaction mode):
-- SET search_path = tenant_42, public; -- leaks to the next client on this backend
-- SELECT pg_advisory_lock(42); -- held by a backend you no longer own
-- Transaction-scoped replacements: state ends at COMMIT or ROLLBACK.
BEGIN;
-- Equivalent to SET LOCAL; the third argument 'true' means "local to this transaction".
SET LOCAL search_path = tenant_42, public;
SELECT set_config('timezone', 'UTC', true);
-- Lock is released automatically at end of transaction; no unlock call needed.
SELECT pg_advisory_xact_lock(42);
-- Temp table is dropped at commit, so it cannot linger on a shared backend.
CREATE TEMP TABLE import_batch (id bigint, payload jsonb) ON COMMIT DROP;
INSERT INTO import_batch VALUES (1, '{"sku": "A-100"}');
INSERT INTO orders_staging SELECT id, payload FROM import_batch;
COMMIT;
-- Durable defaults belong in server configuration, not in per-connection SET.
-- Every backend applies these at session start, regardless of which client lands on it.
ALTER ROLE app_user SET search_path = app, public;
ALTER DATABASE appdb SET timezone = 'UTC';
-- Verify what a role/database will get by default.
SELECT setdatabase::regdatabase, setrole::regrole, setconfig
FROM pg_db_role_setting;
Replacing session-scoped SET, advisory locks and temp tables with transaction-scoped equivalents, and moving durable defaults into ALTER ROLE / ALTER DATABASE settings.
Why do prepared statements fail behind PgBouncer?
There are two kinds of prepared statements, and only one is obvious from your code. SQL-level PREPARE and EXECUTE are explicit statements you write. Protocol-level prepared statements come from the extended query protocol: the driver sends Parse, Bind, and Execute messages and often gives the parsed statement a name so it can reuse it. Many drivers and ORMs do this automatically for queries they run often, so an application can depend on prepared statements without ever writing PREPARE.
Both kinds are stored on a single server backend. In transaction mode, PgBouncer can assign each transaction to a different backend. Suppose the driver prepares S_1 on backend A, and its next transaction lands on backend B. The Bind for S_1 fails with "prepared statement does not exist." The opposite also happens. A client tries to prepare S_1 on a backend where another client already created a statement with that name, and the server returns "already exists." Unnamed statements sent in one round trip usually work, because nothing has to survive between transactions.
You have two fixes:
- Turn off or limit server-side statement caching in the driver. Most drivers have a setting for this, such as a prepare threshold or a statement cache size.
- Let PgBouncer track protocol-level statements. Newer releases can do this through
max_prepared_statements. PgBouncer re-prepares statements on whichever backend it assigns. SQL-levelPREPAREis still not tracked. Confirm that your version supports this setting in its own documentation.
Each fix costs something. Turning off caching makes hot queries go through parse and plan on every execution. Tracking in the pooler keeps that savings, but it uses PgBouncer memory, adds state the pooler has to manage, and ties correctness to the PgBouncer version you run.
;; pgbouncer.ini: transaction pooling with protocol-level prepared statement tracking
[databases]
; Clients connect to "appdb" on PgBouncer and are routed to this Postgres server
appdb = host=10.0.0.12 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Each transaction may run on a different server backend
pool_mode = transaction
default_pool_size = 20
; Fix B: PgBouncer tracks named Parse messages from the extended query protocol
; and re-prepares them on whichever backend it assigns to the next transaction.
; 0 turns tracking off. Higher values use more pooler memory per connection.
; SQL-level PREPARE/EXECUTE is still NOT tracked.
; Older releases do not support this setting. Check the docs for your version.
max_prepared_statements = 200
;; Fix A (use this instead if your PgBouncer cannot track statements):
;; stop the driver from creating named server-side statements.
;; pgJDBC: jdbc:postgresql://pgbouncer:6432/appdb?prepareThreshold=0
;; asyncpg: await asyncpg.connect(dsn, statement_cache_size=0)
;; psycopg 3: conn.prepare_threshold = None
;; Cost: hot queries go through parse and plan on every execution.
A transaction-mode pgbouncer.ini that enables max_prepared_statements, with driver settings in comments that turn off server-side statement caching when the pooler cannot track prepared statements.
When should you avoid transaction mode or route work around it?
Some workloads depend on a session staying attached to one server connection. Behind a transaction-mode pool they fail in ways that are hard to diagnose.
- Schema migrations. Many migration tools take a session-level advisory lock to stop concurrent runs and issue
SET lock_timeoutbefore running DDL. If the lock and the DDL land on different backends, both protections disappear. LISTENconsumers. Notifications go to the backend that ranLISTEN. Once that connection returns to the pool, the consumer stops receiving events and nothing reports an error.- Long-lived advisory-lock holders, such as leader election or singleton workers. They need the lock to last as long as the process, not one transaction.
- Admin and interactive tools.
psqlsessions, BI clients and debuggers set variables, create temp tables and leave transactions open. - Batch jobs. Long transactions hold a server connection for their full duration, which starves the pool. They can also trip query or idle-transaction timeouts that you tuned for short OLTP traffic.
Route these through a different connection path:
| Option | Use when | Trade-off |
|---|---|---|
| Direct Postgres connection | Few clients, such as migrations, admin work and LISTEN |
Uses max_connections directly |
Separate PgBouncer database entry with pool_mode=session |
Many session-bound clients that still need a connection cap | Each client holds a server connection for its whole session |
Sometimes you may not need PgBouncer at all. If you run a handful of app instances whose combined app-side pools stay well under max_connections, or your managed Postgres service already includes pooling, adding PgBouncer gives you little. It is another network hop, and you have to operate, monitor, make highly available and upgrade it.
; /etc/pgbouncer/pgbouncer.ini
; Two entries point at the same Postgres database with different pool modes.
; Clients choose the behaviour by the database name in their connection string.
[databases]
; Default path for OLTP application traffic: short transactions, no session state.
appdb = host=10.0.0.5 port=5432 dbname=appdb pool_mode=transaction pool_size=40
; Session-mode path for clients that need a stable backend, such as LISTEN
; consumers, leader-election advisory locks and BI tools.
; Each connected client holds one server connection until it disconnects,
; so pool_size caps how many of these clients can be connected at once.
appdb_session = host=10.0.0.5 port=5432 dbname=appdb pool_mode=session pool_size=10
; Migrations and ad-hoc DBA work skip PgBouncer and connect to 10.0.0.5:5432
; directly. Leave headroom in max_connections for them.
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; This applies only to entries that do not set their own pool_mode.
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
; Example client connection strings (placeholders):
; app: postgresql://app_user@pgbouncer.internal:6432/appdb
; listener: postgresql://app_user@pgbouncer.internal:6432/appdb_session
; migration: postgresql://migrator@10.0.0.5:5432/appdb
A PgBouncer config that serves the same database through a transaction-mode entry for application traffic and a separate session-mode entry for session-bound clients, while migrations connect to Postgres directly.
What are the alternatives to PgBouncer transaction mode?
Transaction mode is one way to keep the server-side connection count under control. Before you adopt it, compare it with these options:
- Application-side pooling only. Keep the connection pool inside each application process and size it so that instances × pool size, plus admin headroom, stays below
max_connections. This keeps full session semantics and adds no extra network hop. It stops working when instance counts grow or change quickly, because every new instance adds connections. - PgBouncer in session mode. You keep session semantics and get a central cap on connections, plus fast reconnects for clients that connect and disconnect often. Each connected client still holds a server connection, so this mode does not help when many clients stay connected but sit idle.
- PgBouncer in statement mode. It gives even more aggressive reuse, but it does not allow multi-statement transactions. It suits only workloads that are strictly autocommit.
- Pooling built into a managed Postgres service. If your provider offers a pooler, you skip running one yourself. The same transaction-scoping rules usually apply, so check which pool modes it supports and how it handles prepared statements.
- Other standalone Postgres poolers. Several other connection poolers exist. They differ in multi-threading, prepared statement handling, and routing features. The session-state rules in this article apply to any pooler that reassigns server connections between transactions.
- Reducing connection demand. Fewer, larger app instances, shorter transactions, and moving long-running work to a small number of dedicated workers all lower the number of connections you need. Sometimes that removes the need for a pooler entirely.
Choose transaction mode when the client count is far above the number of concurrent transactions and you can scope session state to transactions. Choose one of the alternatives when session semantics matter more than connection reuse.
How do you size a PgBouncer pool?
Three settings interact. max_client_conn caps how many client connections PgBouncer accepts. default_pool_size caps server connections per database/user pair, not globally. Postgres max_connections is the hard ceiling. Add up the pool sizes across every database/user pair, plus any reserve pools and direct admin or migration connections, and keep that total below max_connections. If you add a new service user, you add another pool.
A starting estimate
Little's law says the number of busy connections equals arrival rate × time each transaction holds a connection. Illustrative estimate, with assumed inputs: 1,500 transactions per second, each holding a server connection for 8 ms on average, gives 1,500 × 0.008 = 12 busy connections. Adding headroom for variance puts the starting point at around 16 to 20. Validate that figure under real load, and measure duration at the server, not at the client.
Why the extremes hurt
If the pool is too small, any slowdown in Postgres, such as a lock wait or a cold cache, lengthens hold time. PgBouncer then queues clients. A small latency spike becomes a large one at the application edge.
If the pool is too large, you lose the backpressure that made pooling useful. Postgres absorbs the surge itself, and contention for CPU, locks, and memory slows every query.
Hold time matters more than request count. A pool of 20 with 20 clients sitting idle in transaction serves no one else. A few slow batch jobs or leaked transactions can starve the whole application.
What guardrails and monitoring keep transaction mode safe?
Timeouts on both sides
Set idle_in_transaction_session_timeout on Postgres so a transaction left open by a crashed worker or forgotten BEGIN gets terminated instead of pinning a server connection indefinitely. Pair it with statement_timeout to cap runaway queries. On the pooler, idle_transaction_timeout does the same job one layer up. query_wait_timeout bounds how long a client queues for a connection, so callers fail fast instead of piling up. Treat query_timeout as a backstop set above statement_timeout.
Global defaults that suit request traffic will kill migrations and batch jobs. Give those workloads their own roles and override per role with ALTER ROLE ... SET statement_timeout. Route them through a separate pool or a direct connection so long transactions don't consume web capacity.
The most common leak is application code making an HTTP call, publishing to a queue, or waiting on a lock while a transaction is open. Do network I/O before BEGIN or after COMMIT. Every second spent waiting on a remote service is a second the server connection serves no one.
Reading the admin console
In SHOW POOLS, watch cl_waiting and maxwait. Any sustained nonzero cl_waiting means clients are queuing for a server connection. A climbing maxwait is the earliest reliable sign of exhaustion, often visible before latency alerts fire. Compare sv_active against the pool size to confirm saturation. Use SHOW SERVERS to find long-held connections and SHOW CLIENTS to trace which application addresses hold them.
How do you migrate an existing app to PgBouncer transaction mode?
Pre-migration checklist
Treat the migration as an audit first and a config change second.
- Audit session state in code and at connect time. Grep for
SET(withoutLOCAL), advisory locks,LISTEN, temporary tables, andWITH HOLDcursors. Then check ORM and driver hooks that run on connect, such assearch_path,statement_timeout, or time zone settings. Under transaction mode those hooks run on one server connection, and later transactions can land on a different one that never received them. - Pick a prepared statement strategy. You can disable server-side prepares in the driver. If your PgBouncer version supports protocol-level prepared statement tracking, you can enable that instead. Decide per driver rather than per service, because defaults vary across drivers.
- Add a session-mode path. Define a separate pool entry with
pool_mode = sessionfor migrations,LISTENconsumers, and advisory-lock jobs. Point only those workloads at it. - Test with a deliberately tiny pool. Set the pool size to one or two server connections and run concurrent load. Staging traffic is usually too light to move a client between server connections, so state leaks stay hidden there. A tiny pool forces that reuse on almost every transaction.
Roll out one service at a time. Watch cl_waiting, error rates, and any "prepared statement does not exist" errors before moving to the next service.
| Upfront cost | Ongoing payoff |
|---|---|
| Auditing code and driver settings | Fewer connection-exhaustion incidents |
| Redesigning session-dependent features | Fewer emergency max_connections changes |
| Maintaining a second pool path | Capacity planning based on pool size instead of client count |
How can transaction mode save engineering time?
Most of the time saved comes from removing recurring coordination and incident work, not from making queries faster.
- Scaling no longer requires connection math. Without a central pooler, every change to instance count, worker concurrency, or in-process pool size has to be checked against
max_connections. With transaction mode, Postgres sees the pool size, and app teams can scale out without negotiating connection budgets with whoever owns the database. - Fewer "too many connections" incidents. Deploys and autoscaling events open connection bursts. These often trigger connection-limit errors that need someone to investigate and then raise limits or restart services. PgBouncer queues those bursts instead of passing them to Postgres.
- One place to tune and observe. Pool size, timeouts, and wait metrics live in one config and one admin console. You don't have to collect them from every service's pool settings.
- Fewer database restarts for capacity. Raising
max_connectionsrequires a Postgres restart. Changing pool sizes in PgBouncer is a pooler config reload, which is less disruptive. - Session-state bugs show up early. The audit and the tiny-pool test find hidden reliance on session state. That reliance would also break failover, connection recycling, or any later change in poolers.
These savings are real only after the upfront work is done. If you skip the session-state audit, transaction mode creates its own class of intermittent bugs that are slow to debug: wrong search_path, lost locks, missing prepared statements. Those bugs can cost more time than the pooler saves. Weigh the size of the audit against how often your team handles connection-limit problems today, and use the table above to decide whether the switch is worth it.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to pgbouncer transaction mode, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.