Why pooling exists, and the three modes

PostgreSQL forks a process per connection. Modern application tiers scale by adding replicas, each with its own connection pool. Those two facts are in direct tension: twelve services at eight replicas with twenty connections each is 1,920 database connections, for a workload whose measured concurrency might be sixty.

A pooler resolves the tension by sitting between them. Applications connect to it freely; it maintains a much smaller set of connections to PostgreSQL and multiplexes the work across them.

What it actually buys

Demonstrated against PgBouncer 1.25.2 in front of PostgreSQL 18.6, with pool_mode = transaction and default_pool_size = 3:

$ psql -U postgres -tAc "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"

-- twelve concurrent clients connected through PgBouncer
-- PostgreSQL backends actually serving them:
3

Twelve to three. The database is carrying a quarter of the processes, a quarter of the private memory, and a quarter of the contention, for the same workload.

Two further benefits follow from the same mechanism.

Establishment cost disappears. The pooler’s server connections are long-lived, so the fork and initialisation cost measured in the first lesson of this part is paid once rather than per client connection.

The database sees a bounded concurrency. However badly the application tier behaves — a storm, an autoscaler adding replicas — the database receives at most default_pool_size concurrent transactions per pool. Work waits at the pooler instead of arriving as backends. That queue is the load-shedding mechanism PostgreSQL itself does not have.

The three modes

Session pooling returns the server connection only when the client disconnects. It is fully compatible and buys very little: a client that holds a connection for hours holds a server connection for hours. Its genuine use is limiting the total number of connections and reusing them across short-lived clients.

Transaction pooling returns the server connection at each COMMIT or ROLLBACK. This is the mode that produces the twelve-to-three result above, because most applications spend most of their time between transactions rather than inside them. It is the mode worth having, and the one with a compatibility cost.

Statement pooling returns the connection after every statement, which makes a multi-statement transaction impossible. It exists for specific autocommit-only workloads and is rarely the right answer.

What transaction pooling costs

Because a client is not guaranteed the same server connection between transactions, anything scoped to a session rather than a transaction becomes unreliable.

The features to check against an application before adopting transaction pooling:

• SET and RESET at session scope. Including SET ROLE and SET search_path, which some frameworks rely on for multi-tenancy.
• Session-level advisory locks. Transaction-scoped advisory locks are fine; session-scoped ones are not.
• LISTEN and NOTIFY. The listener is a session-scoped registration.
• Temporary tables, which live for the session that created them.
• WITH HOLD cursors, which outlive their transaction.
• Protocol-level prepared statements, depending on the pooler version and configuration.

The pooler’s own documentation is the authority for the current list, and it changes between releases — PgBouncer’s handling of prepared statements in particular has improved over time.

Sizing a pool

The pool size is the concurrency the database will see, so it is sized from what the database can serve, not from how many clients exist.

# Measured peak concurrent ACTIVE sessions is the input
psql -U postgres -c \
  "SELECT count(*) FILTER (WHERE state = 'active') AS active,
          count(*) AS total
     FROM pg_stat_activity WHERE backend_type = 'client backend'"

Sample that at peak over a week. The pool should be near the measured peak active count, with headroom, and well below the concurrency plateau from the first lesson of this part. A pool far larger than the plateau reintroduces the contention the pooler was meant to remove.

max_client_conn — how many clients the pooler will accept — is a different number entirely and can be large, because a client connection at the pooler is cheap. That asymmetry is the whole design: accept generously at the front, meter strictly at the back.

Production discipline

1. Prefer transaction pooling, and check the application against the unsupported feature list before adopting it.
2. Treat the session-scoped constraint as hard. It is load-dependent, so testing under light load proves nothing.
3. Size default_pool_size from measured peak active concurrency, not from client count, and keep it below the plateau.
4. Set max_client_conn generously. Accepting at the front is cheap; the metering belongs at the back.
5. Monitor the pooler’s queue. A growing wait there is the earliest signal that the database is saturating.
6. Lower the pool during a storm, never raise it. Queuing outside the database is the mechanism that breaks the loop.

Cross-course references

• Kubernetes for Production Sysadmins — Part LIV (Stateful workloads) and Part XXXVIII (Services) cover running a pooler as a cluster service and routing to it.
• Observability for Production Sysadmins — Part IX (Exporters) covers exporting pooler statistics, which are a separate source from PostgreSQL’s own.
• Secrets, PKI & Certificate Management — Part VII (TLS for operators) covers terminating TLS at a pooler, which changes what the database sees as the client.

Quiz