The Motivation Behind PgDog: Why Another Postgres Connection Pooler?

PgDog is a specialized proxy designed to scale PostgreSQL. Its primary function is connection pooling, a mechanism that enables a vast number of client applications to share a limited set of database connections without hitting the server's hard limits.

While the market is already crowded with options—including PgBouncer, RDS Proxy, Pgpool-II, and Supavisor—the creators of PgDog found that existing solutions often forced unacceptable compromises.

The Problem: Leaky Abstractions

Most PostgreSQL utilities adhere strictly to the UNIX philosophy: do one thing, and do it well. While this approach generally leads to stable software (which explains the ubiquity of PgBouncer), it often results in what is known as a leaky abstraction.

A "leaky abstraction" occurs when the underlying implementation details of a tool force the user to change how they interact with the system, breaking the illusion of a seamless interface.

When introducing a pooler like RDS Proxy or PgBouncer, you aren't just adding a layer of infrastructure; you are changing the rules of your database interaction. For established applications, this often necessitates minor tweaks massive refactors of thousands of lines of production code, frequently in areas with poor test coverage.

The "Connection State" Dilemma

The most immediate casualty of connection pooling is session control, specifically the use of SET commands. These are typically used to modify settings for a specific session:

-- Example: Increasing timeout for a heavy report query
SET statement_timeout TO '5m'; 
SELECT * FROM users WHERE banned IS true;

Because poolers recycle connections between different clients, the state set by Client A can "leak" into the session of Client B. This leads to two primary risks:

1. Performance Degradation: A slow query might inherit a long timeout, triggering a database incident.
2. Security Failures: If your Row Level Security (RLS) relies on session variables, those variables might persist or vanish, causing rows to disappear silently.

The industry standard advice is usually: "Just stop using SET commands." This is a significant trade-off. If a core feature like RLS depends on these commands, connection pooling becomes an impossibility.

How PgDog Solves State Management

PgDog integrates a native SQL parser to handle this. Instead of ignoring SET statements, it intercepts them, extracts the variables, and maps them to the specific client connection within the proxy.

The Logic Flow:

To minimize latency, PgDog utilizes query pipelining, updating multiple mismatched variables in a single network round trip. This ensures that Postgres features remain available even as you scale.

---

Restoring LISTEN/NOTIFY

Postgres provides a built-in pub/sub system via LISTEN and NOTIFY. It is a powerful tool for real-time updates without needing an external message broker. However, this feature is typically broken when using a pooler in transaction mode.

PgDog restores this functionality by acting as an internal broker. It manages the movement of messages across multiple PgDog processes using Tokio’s broadcast channel.

Architecture Overview:

• Internal: Messages move between clients in the same process via async channels.
• External: To support multi-container deployments, PgDog maintains a dedicated connection to Postgres to proxy all LISTEN/NOTIFY traffic.
• Integrity: It preserves all transactional semantics of NOTIFY (and fixes previous bugs that could crash databases).

---

Leveraging Modern Multithreading

PgDog is engineered using Rust and the Tokio async runtime. Every client connection is handled by its own asynchronous task, allowing the system to scale linearly with the number of connections.

Comparison of Scaling Strategies

By utilizing multiple CPUs within a single process, PgDog achieves higher connection utilization. This prevents the "stuck client" problem where a single overloaded proxy instance bottlenecks all attached clients. Furthermore, it eliminates the latency spikes associated with waiting for an autoscaler to spin up new instances.

---

Summary & Final Thoughts

Replacing a legacy tool like PgBouncer is a daunting task, but PgDog was built to solve specific, painful frictions in the Postgres ecosystem.

Current Status Checklist:

• Production-tested for $1+$ year
• Handles throughput of $\approx 2 \times 10^6$ queries per second
• Open source (Free as in freedom)
• Deployable in any environment

By treating the proxy not just as a traffic cop, but as an intelligent layer that understands SQL state and pub/sub semantics, PgDog allows developers to scale their databases without rewriting their applications.