Connection Overhead: Why Pooling Is Mandatory at Scale

The one thing to understand first

PostgreSQL uses a process-per-connection model: the postmaster fork()s a dedicated backend for every client connection (src/backend/postmaster/postmaster.c). That backend lives until the client disconnects. This design is robust and simple, but it makes connections relatively expensive — unlike thread-per-connection databases, each PostgreSQL connection carries OS-process weight.

The right number of active connections is small — a few times your core count — but applications want to hold far more than they use at any instant. A pooler exists to bridge exactly that mismatch. "Just raise max_connections" makes the problem worse, not better.

The real costs of a connection

• Memory. Each backend has private memory for caches (catalog, plan), plus its share of work for sorts/hashes. Thousands of mostly-idle backends consume gigabytes doing nothing.
• Snapshot cost. Taking an MVCC snapshot scans the ProcArray of all backends (see the snapshots article). More backends → more expensive GetSnapshotData() → contention on every transaction, even idle ones add to the array.
• Context switching. The OS scheduler juggling thousands of processes wastes CPU on switches rather than work.
• Connection setup. Fork, authentication, and backend initialisation cost milliseconds — punishing for short-lived connections that reconnect constantly.

Why "just raise max_connections" fails

Setting max_connections = 5000 does not scale linearly — it amplifies all the costs above. Throughput typically peaks at a connection count near a small multiple of CPU cores and then declines as contention dominates. The correct number of active connections is small; the problem is that applications want to hold many connections while using few at any instant. That mismatch is exactly what a pooler resolves.

PgBouncer and pooling modes

PgBouncer is a lightweight connection pooler that maintains a small set of real server connections and multiplexes many client connections over them. It has three modes:

• session — a server connection is assigned for the life of a client connection. Safest, least multiplexing.
• transaction — a server connection is assigned only for the duration of each transaction, then returned to the pool. The sweet spot for most web apps: hundreds of clients share a handful of server connections.
• statement — returned after each statement; most aggressive, forbids multi-statement transactions.

Transaction mode caveats

Because a client may get a different server connection per transaction, anything that relies on session state can break in transaction mode:

• **Session-level SET**, SET LOCAL excepted (which is transaction-scoped).
• Server-side prepared statements — need max_prepared_statements configured in PgBouncer or a driver that re-prepares.
• Advisory locks held across transactions, LISTEN/NOTIFY, temporary tables, and cursors held outside a transaction.

Designing the application to be stateless between transactions is what unlocks transaction pooling.

A sane topology

# pgbouncer.ini
[databases]
app = host=10.0.0.5 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 5000       # clients PgBouncer accepts
default_pool_size = 25       # real server connections per (db,user)

Here 5000 application clients are served by ~25 PostgreSQL backends — the server stays in its efficient operating range while the app gets all the connections it wants.

Layer 3 — Watch it happen on your own database

-- How many backends exist, and how many are actually doing work?
SELECT state, count(*)
FROM   pg_stat_activity
GROUP  BY state ORDER BY 2 DESC;
--   idle                 480   <- holding a process, doing nothing
--   idle in transaction   12   <- worse: also pinning resources
--   active                18   <- the only ones doing real work

-- The ceiling, and how close you are to it
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

The gap between active and everything else is the entire argument for pooling. If hundreds of backends sit idle while only a dozen are active, you are paying full per-process memory and ProcArray/snapshot cost for connections that do nothing. idle in transaction is the most dangerous state — those sessions also pin xmin and block vacuum.

Layer 4 — The levers this hands you

The fix is a pooler (PgBouncer) in front of a small backend count, with a few configuration disciplines:

• Pool mode. transaction mode is the sweet spot — thousands of clients share a handful of server connections, each borrowed only for the duration of a transaction. session is safest but multiplexes least; statement is most aggressive.
• Size for the server, not the client. Set default_pool_size to a small multiple of cores (e.g. 25) and let max_client_conn be large (e.g. 5000). PostgreSQL max_connections sits comfortably above the pooler's total server connections — not above the client count.
• Design for transaction pooling. Avoid session-level SET (use SET LOCAL), configure max_prepared_statements for server-side prepares, and don't rely on cross-transaction advisory locks, LISTEN/NOTIFY, temp tables, or held cursors.
• Align timeouts between pooler and server to avoid mid-session drops, and run multiple PgBouncer instances at very high core counts so the pooler isn't the bottleneck.

Layer 5 — What an Oracle DBA should expect vs what they get

This is one of the sharpest architectural differences from Oracle:

• No built-in shared server / MTS. Oracle ships Shared Server (dispatchers + shared server processes) and DRCP (Database Resident Connection Pooling) inside the database. PostgreSQL has no equivalent — pooling is an external component (PgBouncer/pgcat), and using one is mandatory at scale, not optional.
• Process-per-connection, always. Oracle's default dedicated-server model is similar, but Oracle gives you the in-database alternative; in Postgres every connection is a full OS process with no shared-server fallback.
• Snapshot cost scales with backends. Because GetSnapshotData() scans the ProcArray, idle Postgres connections impose a cost Oracle's UNDO/SCN model does not — another reason to keep the backend count low.
• **No sessions/processes split.** Where Oracle tunes PROCESSES and SESSIONS separately, Postgres has a single max_connections, and the real tuning happens in the pooler.

Key takeaway

Every PostgreSQL connection is a forked backend with private memory and a ProcArray slot that every snapshot must scan, so connection count — not just activity — has a real cost. Throughput peaks at a small multiple of cores and declines beyond it, which is why "raise max_connections" backfires. Put PgBouncer in transaction mode in front, size the pool for the server, and design the app to be stateless between transactions.