Why PostgreSQL connection pooling is not optional at scale
PostgreSQL connection pooling is the single highest-leverage fix when an otherwise healthy database starts buckling under a flood of client connections. The root cause is architectural: PostgreSQL forks a dedicated operating-system process for every connection. Consequently, each backend carries its own memory for the parser, planner, and caches, and the postmaster pays scheduling and context-switch costs as the count climbs. Therefore, a few thousand mostly-idle application connections can consume gigabytes of RAM and starve the server long before any real query work happens.
Application frameworks make this worse. Each service instance typically holds its own client-side pool, so ten replicas with fifty connections apiece demand five hundred backends. Moreover, serverless and autoscaling platforms churn connections constantly. Accordingly, teams place an external pooler between the application and PostgreSQL to multiplex many client sessions onto a small set of real server connections. In this guide we compare three poolers, examine sizing math, and call out the pitfalls that quietly break correctness.
Transaction, session, and statement pooling modes
Before choosing a tool, understand the three pooling modes, because they dictate which application features still work. In session mode, a server connection is assigned to a client for the entire life of that client connection; consequently, it is the safest but least efficient mode, since an idle client still pins a backend. In transaction mode, a server connection is borrowed only for the duration of a single transaction and then returned to the pool. As a result, transaction mode delivers the dramatic multiplexing ratios people expect from pooling.
However, transaction mode breaks anything that spans transactions on the same physical connection. Session-level state such as SET search_path, session GUC variables, LISTEN/NOTIFY, advisory locks held across statements, and unnamed cursors will not behave as the application assumes. Statement mode is stricter still: it returns the connection after every statement and forbids multi-statement transactions entirely, so it suits only autocommit, single-shot workloads. For deeper query-side tuning that pairs well with pooling, see our PostgreSQL 18 asynchronous I/O performance guide.
PgBouncer: the battle-tested default
PgBouncer is the long-standing standard, and for most teams it remains the right first choice. It is a tiny, single-threaded C process built on libevent, so it is light on resources and extremely stable. In practice, teams run it in transaction mode in front of PostgreSQL and immediately collapse thousands of client connections down to a few dozen server connections. The official PgBouncer documentation covers every parameter, but a minimal production configuration is short.
[databases]
appdb = host=10.0.0.10 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
; transaction mode is what gives the big multiplexing win
pool_mode = transaction
; server connections actually opened to PostgreSQL
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; total client connections PgBouncer will accept
max_client_conn = 5000
; recycle backends to avoid long-lived state buildup
server_lifetime = 3600
server_idle_timeout = 600
; required for prepared statements in transaction mode
max_prepared_statements = 200Historically, the biggest knock against PgBouncer in transaction mode was the lack of protocol-level prepared statements, which forced ORMs and drivers into awkward workarounds. That gap is now closed: since version 1.21, PgBouncer supports prepared statements in transaction mode when you set max_prepared_statements to a non-zero value. Nevertheless, PgBouncer is single-threaded, so a single instance can become CPU-bound on a very busy node; teams then run several PgBouncer processes behind a load balancer or use SO_REUSEPORT.
PgCat: Rust-based pooling with sharding and failover
PgCat is a newer pooler written in Rust, designed as a more capable drop-in alternative. Crucially, it is multi-threaded, so it uses all available cores without the multi-process gymnastics PgBouncer requires. Beyond raw throughput, PgCat adds features that previously demanded extra infrastructure: read/write query routing, load balancing across multiple replicas, automatic failover when a replica goes unhealthy, and even built-in sharding. Consequently, PgCat appeals to teams that want pooling and a lightweight routing layer in one process.
Its configuration is TOML rather than INI, and it models pools, primary and replica servers, and per-pool users explicitly. Because PgCat can distribute read traffic across replicas, it pairs naturally with a replication topology; if you are designing that side, our PostgreSQL 17 logical replication patterns are a useful companion. That said, PgCat is younger than PgBouncer, so evaluate its failover behavior carefully under your own failure injection before trusting it as the only thing standing between your app and the database.
Supavisor: pooling built for cloud and multi-tenant scale
Supavisor, written in Elixir on the BEAM runtime, targets a different problem: pooling at cloud scale across many tenants and many nodes. Because the BEAM was built for massive concurrency and distribution, Supavisor can run as a cluster, share connection state, and handle very large numbers of client connections while presenting a small footprint to PostgreSQL. Moreover, it supports both transaction and session modes and adds query load balancing, making it the engine behind large managed-Postgres platforms. For an architectural look at how distributed systems handle this differently, compare with CockroachDB vs YugabyteDB.
Supavisor shines when you are a platform operator multiplexing thousands of databases, or when you need a pooler that itself scales horizontally rather than vertically. For a single application talking to a single PostgreSQL primary, however, that distributed machinery is overkill, and a single PgBouncer process will be simpler to reason about and operate.
Sizing the pool and avoiding correctness pitfalls
Sizing is where teams most often go wrong, usually by setting the pool far too large. The server-side pool should be small, because PostgreSQL does real work on relatively few connections at once. A widely cited starting formula is connections = ((core_count * 2) + effective_spindle_count), which on a typical SSD-backed eight-core box lands near twenty. In practice, teams begin with cores times two to four for the active pool and then measure: if you see queries waiting in the pool while CPU is idle, raise it; if PostgreSQL itself is saturated, a bigger pool only makes contention worse.
-- See where time actually goes inside the database
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'appdb'
GROUP BY state
ORDER BY count(*) DESC;
-- Find idle-in-transaction sessions that hold backends hostage
SELECT pid, usename, state,
now() - state_change AS idle_for,
left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;
-- Confirm the pooler is reusing a small set of backends
SELECT count(*) AS server_backends
FROM pg_stat_activity
WHERE backend_type = 'client backend';The pitfalls almost always trace back to transaction mode hiding session state. Specifically, advisory locks taken with pg_advisory_lock can land on a different backend than the one that releases them, so use transaction-scoped pg_advisory_xact_lock instead. Likewise, never rely on a persistent SET search_path; set it per-transaction or qualify object names. Furthermore, LISTEN/NOTIFY needs session mode, and any driver feature that assumes the same physical connection across calls must be tested explicitly. Finally, partitioned tables interact with planning costs, so review our partitioning strategies guide when query plans look off behind a pooler.
How to choose between the three
Choose PgBouncer when you want the proven default: one app, one primary, and a need for rock-solid stability with minimal moving parts. Choose PgCat when you want a single multi-threaded process that also routes reads to replicas, handles failover, or shards, and you are willing to test a younger project. Choose Supavisor when you operate at platform scale, multiplex many tenants, or need the pooler itself to form a cluster. Importantly, do not reach for a pooler at all if your connection count is naturally low and stable; the indirection adds latency and an operational dependency you may not need.
In conclusion, PostgreSQL connection pooling is mandatory once connection counts outgrow what process-per-connection can absorb, but the right tool depends entirely on scale and topology. Start with PgBouncer in transaction mode and correct sizing, graduate to PgCat when you need routing and failover in the pooler, and reach for Supavisor only when you are operating at multi-tenant cloud scale. Always validate session-state assumptions, keep the server pool small, and let real pg_stat_activity data, not guesswork, drive every tuning decision. Consult the official PostgreSQL documentation as the source of truth for connection and GUC semantics.