PgDog: Eliminating Leaky Abstractions in Postgres Connection Pooling via State Tracking and Multithreading
PgDog is an open-source Rust-based PostgreSQL proxy designed to preserve native database features like session state and pub/sub semantics within pooled environments. By employing an inline SQL parser and a multithreaded Tokio architecture, it mitigates the operational trade-offs and application-level refactoring typically demanded by traditional poolers.
The Cost of Traditional Connection Pooling
Traditional PostgreSQL connection poolers, such as PgBouncer and RDS Proxy, enforce architectural trade-offs that compromise core database features. Because these poolers reuse server connections across different client sessions, any modification to session state—such as overriding execution parameters using `SET` commands—leaks to subsequent transactions.
This behavior breaks critical application features, including custom statement timeouts and Row Level Security (RLS) policies that rely on session-level variables. Consequently, integrating traditional poolers often requires extensive, high-risk refactoring of production application code to eliminate session-state dependencies, or forces teams to abandon pooling entirely for sensitive workloads.
Session State Tracking and Query Pipelining
PgDog resolves the session-state dilemma by embedding a fast, specialized SQL parser directly within the proxy. When a client executes a `SET` command, PgDog's parser identifies the statement, extracts the variable names and values, and caches this state on the proxy-side client connection. Before dispatching subsequent queries from that client to an available database connection, PgDog compares the active client state with the current state of the target server connection.
If a discrepancy is detected, the proxy issues the necessary `SET` commands to align the server connection state before running the client's query. To minimize the latency penalty of these administrative commands, PgDog leverages query pipelining. Multiple state updates are bundled into a single round trip, mitigating network overhead and allowing applications to maintain native PostgreSQL session configurations at scale.
Preserving Pub/Sub Semantics under Transaction Pooling
Standard transaction-mode poolers break PostgreSQL's native `LISTEN` and `NOTIFY` commands because client connections cannot maintain the persistent state required to receive asynchronous notifications. PgDog bypasses this limitation by managing pub/sub state internally and decoupling client listening from the backend connection pool.
To distribute notifications across instances and maintain transactional semantics, PgDog coordinates communication using a hybrid architecture:
- It uses Tokio’s broadcast channels to route messages between clients connected to the same PgDog process.
- It establishes dedicated background connections to PostgreSQL to forward all `LISTEN` and `NOTIFY` commands across multiple distributed PgDog instances.
Through this design, Postgres functions as the central broker while PgDog acts as a pub/sub proxy client, allowing applications to utilize native notification queues without requiring external queuing infrastructure like Redis or SQS.
Multithreaded Concurrency vs. Process Sharding
Unlike PgBouncer, which relies on a single-threaded architecture (often scaled via `SO_REUSEPORT` kernel-level port sharing), PgDog is built on Rust's multithreaded Tokio async runtime. Each client connection is handled by a dedicated async task, enabling linear scaling across multiple CPU cores within a single OS process.
Traditional scaling mechanisms require partition-based connection pools, where each proxy process maintains its own isolated pool of database connections. Under high load, this configuration leads to poor connection utilization; a single overloaded proxy process cannot share its connection pool with underutilized instances.
PgDog's multithreaded architecture allows a single process to handle high-concurrency bursts while maintaining high connection utilization across all threads. This design simplifies operational overhead by consolidating performance metrics and health checks into a single process, avoiding the complexity of debugging kernel-level routing or cloud-provider control planes.