Your web app is fast in development and then falls over the first time real traffic arrives. The database CPU looks fine, the queries are quick in isolation, but requests pile up and you start seeing FATAL: sorry, too many clients already. The bottleneck usually isn't your SQL — it's the connections themselves. Understanding why is the difference between throwing hardware at the problem and fixing it properly.

Why Postgres connections are expensive

PostgreSQL uses a process-per-connection model. Every time a client connects, the server forks a dedicated backend operating-system process to handle it. That is a robust, isolation-friendly design, but it makes connections heavy. Each backend consumes real memory — commonly cited on the order of several megabytes each, and more once it touches large query buffers — and the operating system pays a scheduling cost for every process it juggles.

This is why Postgres ships with a conservative max_connections default of 100. It is not being stingy for no reason; it is protecting you from spawning thousands of memory-hungry processes that would starve the machine. The trouble is that modern applications are built the opposite way: dozens of stateless web workers, each with its own pool, serverless functions that scale horizontally, background jobs — all wanting connections. It is easy to demand far more connections than Postgres can safely fork, and once you hit the ceiling, new clients are simply rejected.

What a connection pooler actually does

The key insight is that most of those client connections are idle at any given instant. A web request holds a connection for a few milliseconds to run a query, then spends the rest of its life waiting on the network, rendering, or thinking. So instead of giving every client its own permanent server process, you put a pooler in front of the database that maintains a small set of real server connections and hands them out only when a client actually needs to run something.

PgBouncer is the standard choice for Postgres. It is lightweight — a couple of kilobytes of memory per client connection rather than megabytes — so it can happily accept thousands of client connections while multiplexing them onto a few dozen backend processes. The database only ever sees the small, controlled number of connections it can comfortably serve, and the fleet of clients stops fighting over process slots. A pool of a few dozen server connections can serve thousands of clients, provided you pick the right pooling mode.

Session vs transaction vs statement pooling

PgBouncer offers three modes, and the choice determines everything about how much multiplexing you actually get. Straight from its documentation:

  • Session pooling: a server connection is assigned to a client for the whole duration it stays connected, and only returned to the pool when the client disconnects. This is the safest and most compatible mode — but it barely helps with connection counts. If 300 clients are connected, you still need 300 server connections.
  • Transaction pooling: a server connection is assigned to a client only during a transaction, and returned the instant the transaction commits or rolls back. This is the mode that delivers the dramatic reduction, because connections are held for milliseconds instead of minutes. It is the most common choice for high-volume web workloads.
  • Statement pooling: transaction pooling's more aggressive cousin — multi-statement transactions are disallowed entirely.

A nice practical pattern: run different pools in different modes. Transaction mode for your high-traffic web app, session mode for admin scripts or background jobs that genuinely need session features.

ini
# Illustrative pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction     ; reuse a server connection per-transaction
max_client_conn = 5000      ; clients the pooler will accept
default_pool_size = 25      ; real server connections per user/db pair

The transaction-mode gotchas

Transaction pooling gives you the big win, but it changes an assumption your code may rely on: that you keep talking to the same backend across statements. Because a connection is reassigned after every transaction, anything that stores state on the server connection breaks. PgBouncer's own feature map marks these as never safe under transaction pooling:

  • SET / RESET session variables
  • LISTEN / NOTIFY
  • WITH HOLD cursors
  • PREPARE / DEALLOCATE (server-side prepared statements)
  • PRESERVE/DELETE ROWS temp tables
  • LOAD
  • session-level advisory locks

The one that bites people most is prepared statements, because many drivers and ORMs use them automatically. If your stack silently prepares statements server-side, transaction mode can produce baffling intermittent errors as those statements land on different backends. The fixes are to disable server-side prepared statements in your driver, or to check whether your PgBouncer version supports protocol-level prepared statements (newer releases added limited support — consult your version's docs). Reach for LISTEN/NOTIFY or session variables? Put that workload on a session-mode pool instead.

Key Takeaways

  • Postgres forks an OS process per connection (several MB each), which is why max_connections defaults to a cautious 100.
  • A pooler like PgBouncer multiplexes thousands of cheap client connections onto a few dozen real server connections.
  • Transaction pooling is where the dramatic reduction comes from; session pooling barely reduces connection counts.
  • Transaction mode breaks anything that relies on per-connection state — prepared statements, SET, LISTEN/NOTIFY, session advisory locks. Know the list before you flip the switch.

References

DatabasesPostgreSQLPgBouncerConnection PoolingScalability