PgDog

PgDog: Scale Postgres n Times with the Ultimate Horizontal Scaling Proxy

In the evolving landscape of database management, scaling PostgreSQL has often been a complex challenge for developers and engineers. PgDog emerges as a revolutionary solution, recently backed by a $5.5M seed round, designed to simplify the way we handle database performance at scale. As a high-performance PostgreSQL proxy, PgDog acts as a connection pooler, load balancer, and distributed database coordinator all in one executable.

Whether you are dealing with millions of queries per second or tens of terabytes of data, PgDog provides the infrastructure necessary to scale your Postgres environment horizontally without requiring extensive changes to your application code. In production, PgDog is already proving its worth, handling over 2 million queries per second and managing 20 TB+ of sharded data.

What's PgDog?

PgDog is a drop-in PostgreSQL proxy that allows you to scale your databases horizontally. It is a multi-purpose tool that functions as a connection pooler, a load balancer, and a distributed database engine. Unlike traditional solutions that require complex re-architecting of your application tier, PgDog is designed to be transparent. It sits between your application and your database shards, managing traffic intelligently to ensure maximum efficiency and reliability.

Deployable anywhere via Docker or Helm, PgDog is a single executable that integrates seamlessly with popular client drivers like asyncpg, pgx, libpq, and ruby-pg. It is the bridge that allows you to treat a cluster of PostgreSQL instances as a single, powerful, and scalable unit.

Core Features of PgDog

PgDog provides three critical infrastructure tools in a single package. Below are the core features that make it the preferred choice for high-growth enterprises.

1. High-Performance Connection Pooler

PgDog features a real transaction mode that allows it to share a small number of Postgres connections among 100,000+ clients. This is achieved without breaking essential Postgres features.

• Session State Support: PgDog handles SET commands, advisory locks, and LISTEN/NOTIFY without resorting to connection pinning.
• Multi-threaded and Async: Capable of processing 50,000+ transactions per second per thread, PgDog operates without query length or connection limits.
• Prepared Statements: It remains compatible with all client drivers with zero overhead, ensuring your performance optimizations stay intact.

2. Intelligent Load Balancer (ALB for Your Database)

PgDog acts as an Application Load Balancer (ALB) specifically for your database. It can distribute traffic across multiple replicas to optimize resource usage.

• Read/Write Splitting: PgDog uses an internal Postgres SQL parser to automatically route SELECT queries to replicas while sending all other traffic to the primary node.
• Health Checks: It proactively blocks out-of-date or broken replicas from serving queries, ensuring data integrity.
• Failover Detection: In the event of a hardware failure or primary failover, PgDog moves write traffic to the new primary automatically, requiring no configuration changes from the user.

3. Distributed Database and Sharding

PgDog allows you to shard Postgres without any application changes. By extracting the sharding key directly from your queries, it routes traffic to the correct shard with precision.

• Fast OLTP: Use sharded databases just like standard Postgres with a coordinator that scales horizontally.
• Fast OLAP: PgDog functions as a scatter/gather engine, supporting operations like GROUP BY, COUNT(), AVG(), ORDER BY, MIN(), MAX(), and COPY out of the box.
• Flexible Sharding: Shard by any criteria using configuration-driven distribution, compatible with all standard Postgres data types.

Distributed, Consistent, and Relational

One of the most significant advantages of PgDog is that it does not trade speed for consistency. It is built to maintain full ACID compliance even at massive scale.

• Cross-shard Transactions: Using native Postgres prepared transactions and two-phase commit (2PC), PgDog allows you to write data to all shards atomically. Any error during the process triggers an automatic rollback.
• Replicated Tables: For data that doesn't need to be sharded, PgDog can store the same information on all shards, enabling fast, shard-local joins.
• Integer Primary Keys: You don't need to switch to UUIDs. PgDog supports monotonic, big integer primary keys generated automatically within the proxy.
• Consistent Schema: Data Definition Language (DDL) is automatically applied across shards. Migration tools like Alembic and ActiveRecord work perfectly without additional configuration.

How to Use PgDog

PgDog is designed for easy deployment and configuration. You can get started quickly using Helm for Kubernetes environments.

Installation

To install PgDog using Helm, run the following commands:

helm repo add pgdogdev https://helm.pgdog.dev
helm install pgdog pgdogdev/pgdog

Configuration Example

PgDog uses a simple pgdog.toml configuration file. Below is an example of how to configure a connection pool with a primary and replica setup:

# pgdog.toml
[general]
default_pool_size = 100

[[databases]]
name = "prod"
host = "10.0.0.1"
role = "primary"

[[databases]]
name = "prod"
host = "10.0.0.2"
role = "replica"

For sharding, you can define your shards and sharded tables as follows:

[[databases]]
name = "prod"
host = "10.0.0.1"
shard = 0

[[databases]]
name = "prod"
host = "10.0.0.2"
shard = 1

[[sharded_tables]]
database = "prod"
column = "tenant_id"

Use Cases

PgDog is utilized by engineering teams across various industries to solve complex scaling issues:

• High-Traffic Web Applications: Companies like Ramp use PgDog to overcome connection scalability bottlenecks found in other proxies like RDS Proxy.
• Data Ingestion: Tripstack leverages PgDog to manage massive data ingestion with 100% uptime.
• Geospatial Sharding: Earth Genome moved complicated geospatial sharding logic from the application tier into PgDog, speeding up queries across 4 billion rows of data.
• Massive Pod Deployments: Modal runs thousands of pods and relies on PgDog to scale their infrastructure seamlessly.

FAQ

Q: Does PgDog require changes to my application code? No. PgDog is a drop-in PostgreSQL proxy. It acts as a transparent layer, so your application continues to interact with it as if it were a standard Postgres instance.

Q: How does PgDog handle replication lag? PgDog's intelligent load balancer includes health checks that can detect and block out-of-date replicas from serving queries until they are caught up.

Q: Can I use standard migration tools with PgDog? Yes. PgDog supports consistent schema management, meaning DDL is automatically applied cross-shard. Tools like Alembic and ActiveRecord work out of the box.

Q: What happens if a transaction fails during a cross-shard write? PgDog uses two-phase commit (2PC). If any error occurs on any shard, an automatic rollback is triggered to maintain ACID compliance.

Q: Does PgDog support primary key generation? Yes, PgDog can automatically generate monotonic, big integer primary keys in the proxy, removing the need to switch your schema to UUIDs for sharding.

"PgDog has been incredibly reliable for us. It’s the kind of infrastructure that just quietly works... it gave us a huge step up in connection scalability." — Maher Beg, Staff Software Engineer at Ramp