What a connection pooler is, and why Postgres needs one

The problem: PostgreSQL connections are expensive and capped

A connection is a single open network session between one of your application instances and the database. PostgreSQL handles each connection in an unusual way: it starts a separate operating-system process — a full, independent program running on the database server — for every connection. That process exists for the entire life of the connection, whether it is running a query or sitting idle.

Two consequences follow, and both bite at scale:

• Idle connections still cost memory. An application instance that opened a connection but is not currently running a query still holds an OS process on the server, and that process still consumes real server memory. Idle is not free.
• There is a hard ceiling. PostgreSQL has a setting called max_connections — the maximum number of connections it will accept. It is fixed when the database server starts; you cannot raise it without a restart. Once that many connections are open, the very next connection attempt is rejected outright with an error (too many clients already), even if every existing connection is idle.

Now picture a modern deployment: pods that autoscale up and down, serverless functions that spin up per request, several services all talking to the same database. Each one opens its own connections, and most of those connections sit idle most of the time — waiting on the application, not on the database. The fleet routinely wants to open far more connections than PostgreSQL's max_connections allows. The database hits its ceiling and starts refusing connections while it is barely working. That is the wall a pooler exists to remove.

The fix: put a pooler in front of the database

A connection pooler is a small service that sits on the network path between your applications and PostgreSQL. It maintains a pool — a small, fixed set of long-lived connections to the database, called backend connections. Your applications no longer connect to PostgreSQL. They connect to the pooler instead; those are the client connections.

The pooler multiplexes: it takes the many short-lived client connections and routes their queries across the few long-lived backend connections, handing a backend to whichever client needs it right now and reclaiming it when that client is done. Hundreds or thousands of mostly-idle client connections can share a few dozen busy backend connections, because at any instant only a fraction of clients are actually running a query.

From your side the change is small and mechanical: point your applications' database connection string at the pooler's address instead of at PostgreSQL's. The pooler speaks the same protocol PostgreSQL does, so applications do not know the difference. That is the one change.

Pooling modes: the trade-off you have to choose

The whole value of a pooler is reusing backend connections across clients. The question is when a backend is allowed to be handed to the next client. Reusing it sooner serves more clients with fewer backends — but a backend connection can carry state left behind by the previous client, and reusing it too soon breaks anything that depended on that state surviving. The mode controls that timing. There are three.

Session pooling

A session is everything that happens on one client connection from the moment it connects to the moment it disconnects. In session pooling, a client holds its backend connection for the whole session; the backend is returned to the pool and reused only after that client disconnects.

This is the safest mode and gives full feature compatibility — the client gets a dedicated backend for its entire life, so nothing it sets up can be disturbed by another client. It also gives the least reuse: an idle-but-connected client still ties up a backend, which limits how many clients you can pack onto a small pool.

Transaction pooling

A transaction is a group of database statements that succeed or fail as a single unit. In transaction pooling, the backend is returned to the pool at the end of each transaction, not at disconnect. A client only holds a backend while it is actively mid-transaction, so far more clients can share far fewer backends. This is the mode that delivers the big multiplexing win.

The cost: because the backend changes between transactions, anything that relies on state persisting on one specific connection beyond a single transaction breaks. The next transaction may land on a different backend that never saw the setup. Specifically, it breaks:

• Session-level SET — a command that changes a setting for the rest of the connection (for example a timezone or timeout); the change is lost when the backend rotates.
• Server-side prepared statements — a query the client parses and stores once on the connection, then re-runs many times by name; the next backend does not have it stored.
• Advisory locks — application-defined locks held on a connection to coordinate work across clients; released or orphaned when the backend is handed off.
• LISTEN/NOTIFY— PostgreSQL's built-in publish/subscribe, where a connection subscribes with LISTEN and later receives messages; the subscription lives on the connection and is lost on handoff.
• Temporary tables — tables that exist only for the life of the connection that created them; an unrelated backend cannot see them.
• WITH HOLD cursors — a saved query position you page through across transactions; it depends on staying on the same connection, which transaction pooling does not guarantee.

None of this is a bug. It is the direct, inevitable consequence of returning the backend between transactions. If your application does not use any of the above, transaction pooling is safe and far more efficient.

Statement pooling

A statement is a single command sent to the database. Statement pooling returns the backend after every individual statement — even more aggressive reuse than transaction pooling. The consequence is severe: it breaks multi-statement transactions outright, because the second statement of a transaction may land on a different backend that knows nothing of the first. It is rarely used and most applications should avoid it.

How pgagroal names these modes

pgagroal — the open-source pooler this product is built on — does not use the words "session" and "transaction" the way the sections above do. It calls its modes pipelines and ships three: performance, session, and transaction. The mapping to the generic terms is:

So both performance and session are session-scoped reuse — they hand a backend back only when the client disconnects, and carry the full feature compatibility described above. transaction is transaction-scoped reuse, with exactly the trade-offs listed in the transaction-pooling section. pgagroal has no separate statement pipeline. For the per-pipeline detail — what distinguishes performance from session, and how each is implemented — see pgagroal pipelines, in detail.

Which mode to pick

Default to a session-scoped pipeline — performance or session. It works with every application and every PostgreSQL feature, so you never have to audit your code for the broken cases above. The cost is lower multiplexing, which is acceptable for most workloads.

Choose transaction only when two things are both true: your workload is high-concurrency with short transactions (many clients, each holding a backend only briefly), and it holds no session state — no session-level SET, no server-side prepared statements, no advisory locks, no LISTEN/NOTIFY, no temporary tables, no WITH HOLD cursors. Under those conditions transaction pooling lets a small pool serve a very large fleet.

Unsure, or app uses any session state   ->  performance / session  (safe default)
High concurrency, short txns, no state   ->  transaction           (max multiplexing)

See also: Enterprise architecture for how the pooler fits into the control plane, and Install with Helm to deploy one.