Connection Multiplexing: The Core Feature

The M:N Mapping

The most critical feature of any database proxy is Connection Multiplexing — also called connection pooling. The idea is to decouple the number of connections your application opens from the number of connections your database maintains.

Without a proxy, the relationship is 1:1. Every application thread that wants to run a query needs its own database connection. With a proxy, the relationship becomes M:N: M application connections share N backend connections, where N is much smaller than M.

Here is how it works mechanically:

1. Frontend connections (App → Proxy): Your application opens 10,000 connections to the proxy. The proxy is a lightweight process designed to handle this — it does not run queries, it does not allocate large buffers per connection. Accepting 10,000 TCP connections is cheap.
2. Backend connections (Proxy → DB): The proxy maintains a fixed pool of, say, 50 persistent connections to the database. These connections are authenticated once at startup and kept open indefinitely.
3. The multiplexing step: When App Connection #99 submits a query, the proxy picks an idle backend connection (say, Backend #3), forwards the query, receives the result, returns it to App Connection #99, and immediately marks Backend #3 as available again. The entire borrow-and-return cycle takes microseconds.

The application believes it has a dedicated connection. The database sees a stable, predictable 50 connections — regardless of whether 100 or 100,000 users are active.

Pooling Modes

Not all pooling is equal. PgBouncer, for example, offers three modes with different trade-offs:

• Session pooling: A backend connection is assigned to a client for the entire session duration. Safest, but least efficient — barely better than no pooling.
• Transaction pooling: A backend connection is held only for the duration of a transaction. Released immediately after COMMIT or ROLLBACK. This is the most common production mode and provides the best multiplexing ratio.
• Statement pooling: A backend connection is released after every single statement. Maximum efficiency, but incompatible with multi-statement transactions or prepared statements. Rarely used.

For most applications, transaction pooling is the right default. The caveat: session-level state (like SET search_path or advisory locks) does not survive across borrowed connections, which can break applications that rely on session variables.