Every PostgreSQL backend process is a full Unix process with its own megabytes of memory. That design choice buys you isolation and simplicity, but it puts a hard ceiling on how many simultaneous connections your database can absorb. At a few hundred connections, lock contention and scheduling overhead start eating your throughput; at a few thousand, your database spends more time context-switching than answering queries. Meanwhile, the application tier scales horizontally — thirty pods each with a default pool of ten connections is three hundred connections before you’ve done anything interesting. Connection pooling is the bridge between those two scaling models, and doing it well requires understanding what actually happens inside Postgres when a connection exists.
This post covers how Postgres handles connections and why the process-per-connection model runs out of headroom, what actually gets cleaned up when a connection ends, the difference between session and transaction pooling, the PgBouncer features that make transaction pooling viable (including the prepared statements support that changed the calculus in 1.21), and the sizing and configuration mistakes that cause most pooling incidents in production.
Why Postgres connections are expensive
MySQL keeps a thread per connection. PostgreSQL forks an entire operating system process for every client connection. The backend process holds a private working memory for sorts and hash joins (work_mem), catalog caches, prepared statement state, and a file descriptor table. Establishing one involves process creation, authentication, and catalog lookups — tens of milliseconds on a loaded system.
The bigger cost is not memory, it’s contention. Every backend takes shared locks while reading system catalogs, and the lock manager’s shared lock table itself becomes a contention point. Active backends compete for CPU and I/O, and snapshots must hold back vacuum across the entire cluster: one long-running transaction anywhere can prevent cleanup of dead tuples everywhere. Past a few hundred active connections, throughput actually degrades — the database does less total work with more connections, not more.
Yet modern application deployments routinely want thousands of connections. Kubernetes replicas, serverless functions, batch workers, dashboards — each brings its own pool. The math is adversarial: app tier scales 100x, database tier tolerates maybe 2-3x. A pooler breaks the coupling. Many client connections share a small set of server connections, and the multiplexing ratio can be enormous because a typical client is idle most of the time — it’s thinking, rendering, waiting on other services. The pooler parks idle clients and puts the server connection to work for someone else.
What a connection leaks between uses
Connection pooling in PostgreSQL is trickier than in most databases because a Postgres connection is stateful far beyond “logged in as X.” A session accumulates state that must be handled when the connection moves to a different client:
- Session variables —
SET search_path,SET timezone,SET statement_timeout, application-specific GUCs. The next client would silently inherit your settings. - Prepared statements — named via
PREPAREor the extended query protocol. Names are session-scoped; the next client can’t see them and may collide with its own. - Advisory locks — session-level locks held until explicitly unlocked or the session ends. In shared use, a “released” connection can carry another application’s locks.
- Open transactions — an abandoned
BEGINwithoutCOMMITwould hold locks and a snapshot open across every future client. - Listened channels —
LISTEN/NOTIFYregistrations that would deliver other clients’ messages. - Temporary tables — tied to the session that created them.
Anything you leave behind becomes a landmine for the next user of that connection. The pooler’s job is to ensure the state boundary matches the pooling boundary. PostgreSQL gives you the tool for this: DISCARD ALL, which resets session state — variables, prepared statements, advisory locks (session-level), LISTEN registrations, and open transaction state.
-- What DISCARD ALL covers
SET search_path TO app, public;
SET statement_timeout = '5s';
LISTEN events;
SELECT pg_advisory_lock(42);
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
DISCARD ALL;
-- search_path, statement_timeout, LISTEN, advisory lock,
-- and the prepared statement are all gone.There are narrower variants — DEALLOCATE ALL (prepared statements only), UNLISTEN *, pg_advisory_unlock_all() — but DISCARD ALL is the catch-all, and it’s what poolers use as the default server_reset_query. The cost is a round trip to the server per connection handoff, so when that handoff happens after every transaction, reset cost matters.
Session pooling: safe but barely pooling
In session pooling, a client checks out a server connection and owns it until it disconnects. This is the safest mode because the state boundary matches the client’s lifetime — everything the client does, including prepared statements, advisory locks, LISTEN, and open transactions, works exactly as it would with a direct connection. The only saving is connection establishment cost and a modest reduction in idle server processes; the multiplexing ratio is essentially 1:1 for active clients.
Session pooling still helps in specific shapes: serverless functions where connection setup dominates request time, or deployments with huge client counts and extremely low per-client activity. But if your goal is to feed 500 concurrent queries into 50 server connections, session pooling won’t do it. That requires decoupling at a finer granularity.
Transaction pooling: real multiplexing, real constraints
In transaction pooling, the server connection attaches to the client for the duration of a transaction and returns to the pool as soon as the transaction commits or rolls back. Your 50 server connections now serve all 500 clients. This is the mode that actually decouples application scaling from database scaling — and it’s the mode with all the sharp edges, because the state boundary is now the transaction, not the session.
;; pgbouncer.ini
[databases]
appdb = host=10.0.0.5 dbname=app
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 40
max_client_conn = 2000
; 1.21+ tracks prepared statements across server connections
max_prepared_statements = 200The constraints of transaction pooling
Anything session-scoped becomes unreliable, because your next statement may run on a different server connection:
- Named prepared statements — historically the biggest blocker. In transaction mode, the
PREPAREand theEXECUTEmay land on different server connections, and the statement doesn’t exist on the second one. PgBouncer 1.21 added protocol-level tracking that makes most drivers work transparently: it remembers the mapping of statement name to SQL text per client and re-issues the parse on whichever server connection handles the next use, up tomax_prepared_statementsper client. Since PgBouncer 1.24.0 this is enabled by default (max_prepared_statements = 200). Drivers must use the extended query protocol for this to work; anything that does manualPREPARE/EXECUTESQL should switch to the driver-level API. - Advisory locks — the classic incident story is a queue worker that grabs a session-level advisory lock, commits, and loses the lock mid-job because the connection was handed to another client. Use transaction-level advisory locks (
pg_advisory_xact_lock) which are automatically released at commit, or pin the worker to session pooling. - LISTEN/NOTIFY — a
LISTENissued outside a transaction may be registered on a connection that gets handed off; you’ll miss notifications sent while “your” connection served someone else. Dedicated listener connections must bypass the pooler or use session pooling. - SET/RESET games —
SEToutside a transaction applies to a server connection you don’t own. PgBouncer tracks a fixed set of parameters (client_encoding,datestyle,timezone,standard_conforming_strings,application_name, plusIntervalStyleand anything you add viatrack_extra_parameters) and restores them on assignment; anything else will leak between clients. - Cursors —
DECLARE ... WITHOUT HOLDcursors live for the transaction; WITH HOLD materializes and survives, but this is a pattern to review under transaction pooling. - Large objects — session-scoped handles; avoid them under transaction pooling.
The rule of thumb: transaction pooling works well for request/response HTTP services and microservices where each request is one or a few transactions. It is hostile to anything that treats a connection as a workspace — interactive psql sessions, migrations that use session state, connection-as-workspace patterns.
What PgBouncer does between transactions
When a transaction ends and a connection returns to the pool, PgBouncer does light-touch cleanup. In transaction mode, the connection must be left in a clean state for the next client, and PgBouncer relies on the client driver and server to have committed or rolled back cleanly. Crucially, the default server_reset_query (DISCARD ALL) is not run in transaction mode — running it after every transaction would cost an extra round trip per transaction. Instead, PgBouncer assumes the driver behaves: it trusts the extended protocol’s implicit cleanup and the tracking mechanisms above.
Session mode runs server_reset_query when a client disconnects — one DISCARD ALL per client lifetime, negligible cost, full safety. Transaction mode deliberately skips it, which is precisely why the constraints list exists. If you’ve ever seen a transaction-mode deployment where search_path or a GUC mysteriously changed between statements of the same request, this is the mechanism — or the absence of one — behind it.
One more piece of protocol context: queries over the simple query protocol with an implicit transaction can actually straddle connections badly — a multi-statement string like BEGIN; INSERT ...; COMMIT; sent as one simple-query packet works, but a multi-statement string with no explicit transaction can split across server connections mid-string. Drivers that batch unrelated statements in one round trip may occasionally bite you here. This is one more reason the transaction boundary should be explicit and driver-managed.
Sizing the pool: the counterintuitive part
The most common sizing mistake is “connections = concurrency, so more is better.” For PostgreSQL, past the CPU core count, additional active connections mostly add context switching and lock manager pressure. The influential HikariCP pool sizing analysis demonstrates this for connection pools generally: a pool of connections equal to a small multiple of cores, plus a modest factor for disk seek time, outperforms much larger pools. The formula commonly cited — connections = ((core_count * 2) + effective_spindle_count) — looks absurdly small to teams used to pool sizes of 100+.
The reasoning transfers directly to Postgres. Your ceiling for active server connections is roughly (cores × 2) for CPU-bound work, and your storage type matters: NVMe with many queues sustains more parallelism than a network volume, but “more than 4x cores” is rarely justified. If you need to serve 200 queries concurrently, the answer is not 200 connections — it’s 50 connections with a queue, and the queue is nearly free compared to what 200 active backends do to each other.
PgBouncer gives you knobs for exactly this shape. default_pool_size caps server connections per user/database pool; max_client_conn is the client-side ceiling and can be huge (thousands); max_db_connections is a global cap across all pools for a database — useful when multiple pooler instances target the same primary. reserve_pool_size with reserve_pool_timeout lets one busy pool borrow headroom if a client waits longer than the timeout, so a chatty service can’t starve a quiet one… and can’t exhaust the database either, because the reserve is capped.
-- On the Postgres side, sanity-check your budget:
SHOW max_connections; -- e.g. 200
SELECT count(*) FROM pg_stat_activity; -- actual backends
-- With HA: every pooler × every pool counts against max_connections.
-- Budget: (poolers × (default_pool_size + reserve_pool_size))
-- + superuser_reserved_connections + replication + headroomSet max_connections on the Postgres side comfortably above your total pooler budget but not absurdly high — every slot costs memory in the postmaster’s shared memory structures. And keep superuser_reserved_connections as your break-glass: when the pooler saturates the database, you still need one connection to log in and run KILL/RELOAD on the pooler console.
Queueing: what happens when the pool is exhausted
When all server connections are busy, clients queue inside PgBouncer. This is a feature — queueing at the pooler is far cheaper than queueing at the database — but only if you bound it. Without timeouts, a slow transaction (usually a function that does an HTTP call mid-transaction, or a migration) can hold a connection for minutes, the queue grows, upstream thread pools fill, and the failure spreads into a cascade. The correct defensive config:
query_wait_timeout = 30 ; client waited > 30s for a server conn: fail fast
query_timeout = 0 ; per-query hard limit if you want one (0 = off)
idle_transaction_timeout = 60 ; kill idle-in-transaction sessions (server side)
server_idle_timeout = 300 ; recycle idle server conns after 5 minquery_wait_timeout is the pressure valve: it converts “everything is slow” into “this request fails with a clear error, right now,” which is the correct behavior for overload. Pair it with server-side idle_in_transaction_session_timeout in postgresql.conf so that a client that opens a transaction and stalls (the classic “opened a transaction, then called a third-party API” bug) can’t pin a server connection indefinitely. PgBouncer 1.25 added transaction_timeout to bound the total duration of a transaction from the pooler side, giving you a third layer of defense.
Monitor the queue, not just the pool. SHOW POOLS in the PgBouncer admin console exposes cl_waiting (clients waiting) and wait_time statistics. A growing cl_waiting with a rising average wait time means your pool is undersized for your peak — or, more often, some transaction is holding connections too long, which no pool size can fix.
High availability and the single-instance pooler problem
PgBouncer is single-threaded and usually deployed as one instance — which makes it a single point of failure. The standard patterns: run it as a systemd service on each application host (localhost, sidecar style, no extra network hop and no shared failure domain), or run two pooler instances behind your existing service discovery and failover at the client level. Multiple poolers are fine, but remember the arithmetic: each pooler maintains its own pools, so total server connections = poolers × pool size. That total is what max_db_connections and the server’s max_connections budget must absorb.
For failover, PgBouncer doesn’t automatically reroute server connections when the primary changes — reconfiguration is a RELOAD (or DNS change if your [databases] entry uses a hostname). Applications that pin to a single pooler address should use a virtual IP or DNS with short TTL. There is no multi-master aware routing here; the pooler is deliberately dumb about topology.
Application-side pools still matter
Adding PgBouncer doesn’t remove the pool inside your application. The two layers do different jobs: the app pool amortizes connection establishment over requests and caps per-instance concurrency; the server-side pooler multiplexes instances onto the database. A common anti-pattern is a large app-side pool aimed at a transaction-mode PgBouncer: 30 replicas × 20 app connections = 600 client connections competing for 40 server connections, with queueing inside PgBouncer adding latency for all of them. Right-size the app pool to expected per-instance concurrency (often surprisingly small — check p99 usage before assuming 20), and let PgBouncer’s queue absorb the rest.
Also verify your driver’s pooling mode. Some ORMs and drivers default to session-affinity behavior (serverless function runtimes are notorious) and will break quietly under transaction pooling. And check your stack’s specifics before assuming one layer is doing all the work — Postgres 18’s async I/O improvements, for example, change some of the capacity arithmetic but not the fundamentals.
A production checklist
- Prefer transaction pooling for HTTP services; verify your driver uses the extended query protocol so prepared statement tracking works.
- Replace session-level advisory locks with
pg_advisory_xact_lock, or route those workloads to session pooling. - Keep LISTEN/NOTIFY on dedicated connections outside the pooler.
- Size server connections at roughly cores × 2, queue the rest, and let
max_client_connbe generous. - Bound the queue:
query_wait_timeout, plus server-sideidle_in_transaction_session_timeoutand (1.25+)transaction_timeout. - Budget total connections across all pooler instances against the server’s
max_connections, and preserve a superuser reservation. - Run the pooler close to the app (sidecar or per-host), failover via VIP/DNS, and remember RELOAD doesn’t fail over server connections for you.
- Monitor
SHOW POOLS:cl_waitingand wait times are your leading indicators, well before users notice.
Connection pooling is one of those components that looks like configuration but is really architecture: where does the state boundary live, who queues, and what fails fast. Get the boundaries right — transaction pooling with transaction-scoped state, a small connection budget, and bounded queues — and Postgres scales with your application tier far beyond what its process-per-connection model suggests.