Rethinking the Postgres Connection Pooler

If you have ever scaled a PostgreSQL database under heavy load, you already know the architectural tax of its process-per-connection model. Unlike databases that handle connections using lightweight threads, Postgres spawns a dedicated operating system process for every single client connection. This design makes connection pooling not just a nice optimization, but an absolute operational necessity for production environments.

For nearly two decades, PgBouncer has been the default answer to this problem. It is lightweight, single-threaded, and highly reliable. But PgBouncer forces developers into a frustrating compromise. To get real scaling benefits, you have to run it in transaction pooling mode. In doing so, you immediately break fundamental Postgres features like session variables, Row Level Security (RLS), and pub/sub commands.

A new wave of connection poolers, led by projects like PgDog, is attempting to eliminate this compromise. By moving from simple byte-stream proxying to protocol-aware state management, these modern tools are trying to fix the leaky abstractions that have plagued database scaling for years.

The Leaky Abstraction of Transaction Pooling

To understand why we need a new approach, we have to look at how traditional poolers handle transaction mode. In session pooling, a client is assigned a database connection for the entire duration of its session. This is safe, but it limits your maximum concurrent clients to the size of your database connection pool.

Transaction pooling solves this by releasing the database connection back to the pool as soon as a transaction ends. A single database connection can thus serve dozens of active clients. But this introduces a massive problem: connection state leaks.

If a client runs a session-modifying command, such as changing a timeout parameter:

SET statement_timeout TO '5m';

That state is applied to the underlying Postgres backend process. When the transaction ends, the pooler assigns that same backend connection to a different client. The second client now inherits the five-minute timeout, entirely unaware that its session state has been modified.

In the worst-case scenario, this state leakage breaks Row Level Security. If your RLS policies rely on a session variable to determine which tenant owns a row, a leaked variable means data can silently disappear from queries, or worse, leak to the wrong user. Because of this risk, the standard advice for teams adopting PgBouncer has always been simple: stop using SET statements entirely. For legacy applications with thousands of lines of code, that advice is often impossible to follow.

State Tracking and SQL Parsing

PgDog addresses this issue by abandoning the idea that a proxy should be a dumb pipe. Instead, it ships with a built-in SQL parser.

When a client sends a query, PgDog parses the SQL to detect SET commands. It extracts the variable names and values, caching them on the proxy side as part of the client's virtual session. Before executing a query on a physical Postgres connection, PgDog checks whether the physical connection's current state matches the client's cached state. If there is a mismatch, the proxy automatically issues its own pipelined SET commands to sync the database connection before running the client's query.

By using query pipelining, the proxy can update multiple mismatched variables in a single network round trip, minimizing the latency penalty. This allows developers to keep using standard Postgres session features without worrying about state contamination across pooled connections.

Virtualizing Pub/Sub

Another casualty of traditional transaction pooling is the Postgres LISTEN and NOTIFY suite. These commands provide a built-in publish/subscribe mechanism that allows applications to coordinate without bringing in an external message broker like Redis.

In a standard transaction-pooled environment, LISTEN and NOTIFY are unusable because the client that registers the listener is not guaranteed to hold onto the same database connection to receive the notification.

Modern poolers solve this by virtualizing the pub/sub layer. PgDog, for example, is built on Tokio, a multi-threaded asynchronous runtime for Rust. It handles LISTEN and NOTIFY commands internally. When a client issues a LISTEN, the proxy registers the interest using Tokio's broadcast channels. To support multi-process deployments (such as running multiple proxy containers in a Kubernetes cluster), the proxy maintains a dedicated, separate connection to Postgres to act as the global broker. To the client application, the pub/sub mechanism works exactly as expected, while the proxy handles the complex multiplexing behind the scenes.

Multithreading vs. Sharded Pools

Beyond protocol features, there is a fundamental architectural difference in how these poolers utilize hardware. PgBouncer is single-threaded. To scale it across multiple CPU cores, platform engineers typically rely on the Linux kernel's SO_REUSEPORT option to run multiple PgBouncer processes listening on the same port, or they rely on cloud-managed alternatives like RDS Proxy.

This approach introduces what is known as a sharded pool. Because each proxy process maintains its own isolated pool of database connections, you lose global connection efficiency. If one proxy process experiences a sudden burst of traffic, it cannot borrow idle connections from a sibling process. This leads to connection starvation even when the database has free capacity.

Because PgDog is built on Tokio's multi-threaded worker pool, a single proxy process can scale across all available CPU cores. This allows a single, unified pool of database connections to be shared dynamically among all incoming client tasks, maximizing connection utilization and smoothing out traffic spikes without relying on complex external autoscaling mechanisms.

The Developer's Trade-off

For developers, the choice between a traditional pooler and a state-aware proxy comes down to a trade-off between simplicity and capability.

PgBouncer remains an incredibly reliable tool. If you are building a greenfield application where you can strictly avoid session-level configuration, or if you are already using an external message broker for pub/sub, PgBouncer's low CPU footprint and decades of production hardening make it hard to beat.

However, if you are managing a legacy application, utilizing Row Level Security, or trying to avoid the operational overhead of managing sharded connection pools across multiple single-threaded proxy instances, the state-aware proxy pattern is a major step forward. While parsing SQL at the proxy layer introduces a minor CPU overhead, the ability to scale your database without rewriting thousands of lines of application code is a trade-off most engineering teams will gladly accept.