PgDog - Horizontal scaling for PostgreSQL

Real transaction mode

PgDog can share just a few Postgres connections between 100,000+ clients, without breaking Postgres features.

• ✓Session state: SET commands, advisory locks and LISTEN/NOTIFY; no connection pinning.
• ✓Multi-threaded and async: 50,000+ transactions/s per thread, without query length or connection limits.
• ✓Prepared statements: compatible with all client drivers, with zero overhead.

config diagram

ALB for your database

PgDog can distribute reads between replicas, detect replication lag, hardware failure, and primary failovers. Use just one endpoint for all your queries.

• ✓Health checks: block out-of-date or broken replicas from serving queries.
• ✓Failover detection: move write traffic to the new primary, without config changes.
• ✓Read / write split: route SELECT queries to the replicas, all others to the primary. Uses the internal Postgres SQL parser for maximum compatibility.

config diagram

Shard Postgres, without app changes

PgDog gets the sharding key directly from queries and sends them to the right shard. Queries without one are executed across all databases, in parallel.

• ✓Fast OLTP: use sharded databases just like plain old Postgres, with a horizontally scalable coordinator.
• ✓Fast OLAP: GROUP BY, COUNT(), AVG(), ORDER BY, MIN(), MAX(), COPY and many more ops supported out of the box. Postgres + PgDog = scatter/gather engine.
• ✓Shard by anything: flexible, configuration-driven data distribution, compatible with standard Postgres data types.

config diagram

Distributed, consistent, relational

PgDog doesn't trade speed for consistency. It maintains ACID compliance, at scale.

• ✓Cross-shard transactions: write data to all shards, atomically. Any error triggers an automatic rollback, using native Postgres prepared transactions and two-phase commit.
• ✓Replicated tables: not all tables need to be sharded. Store the same data on all shards and use it for fast, shard-local joins.
• ✓Integer primary keys: no need to switch to UUIDs. PgDog supports monotonic, big integer primary keys, generated automatically in the proxy.
• ✓Sharding key mutation: move rows between shards using only UPDATE statements.
• ✓Consistent schema: DDL is automatically cross-shard. Migration tools like Alembic, ActiveRecord migrations and others work out of the box.

1-- Cross-shard transactions,

2-- with big integer primary keys.

3BEGIN;

4 

5INSERT INTO users (email, ip_addr, created_at)

6VALUES ($1, $2, now()), ($3, $4, now())

7RETURNING id;

8 

9COMMIT;

10 

11-- Replicated tables for shard-local joins.

12SELECT users.*, city.name FROM users

13INNER JOIN cities

14ON users.city_id = city.id

15WHERE users.id = $1

16AND users.tenant_id = $2;

17 

18-- Sharding key updates.

19UPDATE users SET tenant_id = $1 WHERE id = $2;