PgDog

Product Introduction

1. Definition: PgDog is an open-source, Postgres-compliant proxy server that functions as a high-performance connection pooler, intelligent read/write load balancer, and a distributed database sharding layer for PostgreSQL.
2. Core Value Proposition: It exists to provide horizontal scalability for PostgreSQL databases without requiring application code changes. Its primary value is delivering a "three-in-one" solution—connection pooling, load balancing, and transparent sharding—through a single, lightweight executable that is deployable anywhere, from Kubernetes to bare metal.

Main Features

1. Real Transaction Mode Connection Pooler: PgDog efficiently shares a small pool of persistent PostgreSQL connections among tens of thousands of client connections. It uniquely maintains full session state (e.g., SET commands, advisory locks, LISTEN/NOTIFY) without pinning clients to specific backend connections, a common limitation in other poolers. It achieves high throughput (50,000+ transactions/s per thread) and supports prepared statements from all client drivers with zero overhead.
2. Intelligent Load Balancer with SQL Parsing: PgDog acts as an Application Load Balancer (ALB) for PostgreSQL. It uses an internal PostgreSQL SQL parser to accurately split read (SELECT) and write (all other) queries, routing them to configured replicas and the primary, respectively. It performs continuous health checks to block lagging or failed replicas and automatically detects primary failovers to redirect write traffic.
3. Transparent, Configuration-Driven Sharding: PgDog enables horizontal sharding of PostgreSQL databases. It extracts the sharding key from query parameters or literal values and routes queries to the correct shard. For queries without a sharding key, it performs a parallel scatter/gather operation across all shards, supporting aggregate functions like COUNT(), AVG(), GROUP BY, and ORDER BY. Sharding logic is defined declaratively in a TOML configuration file.

Problems Solved

1. Pain Point: PostgreSQL connection overload and inefficient resource utilization, leading to high memory usage on the database server and connection limit errors.
2. Target Audience: Database Reliability Engineers (DBREs), Platform Engineers, and Backend Developers managing high-traffic PostgreSQL deployments who need to scale connection handling, improve read scalability, or shard their database without a costly, invasive application rewrite.
3. Use Cases: Essential for SaaS applications needing per-tenant data isolation (sharding by tenant_id), high-throughput OLTP services requiring massive connection pooling, read-heavy analytical platforms benefiting from replica load balancing, and legacy PostgreSQL applications that need to scale horizontally but cannot be easily re-architected.

Unique Advantages

1. Differentiation: Unlike basic connection poolers (like PgBouncer), PgDog maintains full transaction semantics and session state. Compared to cloud vendor proxies (like RDS Proxy), it is open-source, offers advanced sharding, and provides more granular load balancing control. It differs from traditional sharding middleware by being a lightweight proxy rather than a heavy query-rewriting layer.
2. Key Innovation: Its scatter/gather engine for distributed SQL execution and cross-shard ACID transactions using PostgreSQL's native two-phase commit (2PC) protocol. This allows it to maintain strong consistency across shards—supporting multi-shard writes, automatic rollback on error, and consistent DDL migrations—which is a significant advantage over eventual consistency models used by many distributed databases.

Frequently Asked Questions (FAQ)

1. How does PgDog handle PostgreSQL prepared statements? PgDog is fully compatible with prepared statements from all client drivers (like libpq, asyncpg, pgx) and manages them with zero overhead, automatically mapping client-prepared statements to the appropriate backend connections in the pool.
2. Can PgDog shard my existing PostgreSQL database without changing my application's SQL? Yes, PgDog is designed as a drop-in proxy. It parses incoming SQL to extract sharding keys from WHERE clauses and values, meaning most existing queries will work immediately. It also supports cross-shard queries and transactions transparently.
3. What happens during a PostgreSQL primary failover when using PgDog's load balancer? PgDog continuously monitors the health and role of configured database instances. Upon detecting a primary failure and a replica promotion, it automatically updates its internal routing table to send write traffic to the new primary, minimizing downtime without manual configuration changes.
4. Does PgDog support distributed transactions across multiple database shards? Yes, PgDog supports fully ACID-compliant cross-shard transactions. It uses PostgreSQL's prepared transaction and two-phase commit features to ensure atomicity—all shards commit or all shards rollback—maintaining strong consistency for multi-shard operations.
5. How is PgDog deployed and configured? PgDog is distributed as a single binary. It can be deployed via Docker, Kubernetes (using the provided Helm chart), or directly on a server. Configuration is driven by a pgdog.toml file where you define databases, their roles (primary/replica), shard mappings, and pooling behavior.