Show HN: PgDog – Scale Postgres without changing the app

Source: Hacker News

Introduction

Hey HN! Lev and Justin here, authors of PgDog (https://pgdog.dev/), a connection pooler, load balancer, and database sharder for PostgreSQL. If you build apps with a lot of traffic, you know the first thing to break is the database. We are solving this with a network proxy that works without requiring application code changes or database migrations.

Our post from last year: https://news.ycombinator.com/item?id=44099187

Production Update

The most important update: we are in production. Sharding is used heavily, with direct‑to‑shard queries (one shard per query) working pretty much all the time. Cross‑shard (or multi‑database) queries are still a work in progress, but we are making headway.

Features

Aggregate Functions

Aggregate functions like COUNT(), MIN(), MAX(), AVG(), STDDEV() and VARIANCE() work without refactoring the app. PgDog calculates the aggregate in‑transit while transparently rewriting queries to fetch any missing info. For example, a multi‑database average calculation requires a total count of rows to compute the original sum. PgDog will add COUNT() to the query if it isn’t there already and remove it from the rows sent to the app.

Sorting and Grouping

Sorting and grouping work, including DISTINCT, as long as the referenced column(s) appear in the result set. Over 10 data types are supported, such as timestamp with time zone, all integer types, varchar, etc.

Cross‑Shard Writes

Cross‑shard writes, including schema changes (CREATE/DROP/ALTER), are now atomic and synchronized between all shards using a two‑phase commit. PgDog tracks the transaction state internally and rolls back the transaction if the first phase fails. No ORM monkey‑patching is required: PgDog intercepts the COMMIT statement and executes PREPARE TRANSACTION followed by COMMIT PREPARED.

Omnisharded Tables

Omnisharded tables (replicated or mirrored across all shards) support atomic reads and writes. This is important because most databases can’t be completely sharded and need some common data kept in sync across all instances.

Multi‑Tuple Inserts

Multi‑tuple inserts, e.g.:

INSERT INTO table_x VALUES ($1, $2), ($3, $4);

are split by PgDog’s query rewriter and distributed to the appropriate shards automatically. This works with ORMs like Prisma, Sequelize, and others, allowing them to operate without code changes.

Sharding‑Key Mutations

When a sharding key is updated, PgDog rewrites the UPDATE into three queries (SELECT, INSERT, and DELETE) to move the row between shards. If you’re using Citus (a PostgreSQL extension for sharding), this approach may be of interest.

Cross‑Shard Unique Sequence

If you prefer integers to UUIDs for primary keys, PgDog provides a cross‑shard unique sequence implemented inside the proxy. It uses the system clock (and a few other inputs) and can be called like a regular PostgreSQL function:

INSERT INTO my_table (id, created_at) VALUES (pgdog.unique_id(), now());

The sequence is monotonically increasing, can generate up to 4 million numbers per second, and has a range of 69.73 years, eliminating the immediate need to migrate to UUIDv7.

Load Balancing

Sharding hardly works without a good load balancer. PgDog can monitor replicas and move write traffic to a promoted primary during a failover. This works with managed PostgreSQL services (RDS/Aurora, Azure PostgreSQL, GCP Cloud SQL) because it simply polls each instance with SELECT pg_is_in_recovery(). Primary election is not supported yet, so for self‑hosted setups with Patroni you should keep your existing election mechanism, but you no longer need HAProxy in front of the databases.

The load balancer also handles edge cases like SELECT FOR UPDATE and CTEs with INSERT/UPDATE statements. If you prefer to manage read/write separation in code, you can still do so by providing PgDog a hint at runtime via a connection parameter (-c pgdog.role=primary), a SET statement, or a query comment.

Connection Pooling & Transaction Management

PgDog can automatically roll back unfinished transactions and drain/re‑sync partially sent queries, helping preserve connections to the database. If a Postgres instance spikes to 100 % CPU due to a connection storm after an application crash, PgDog can mitigate the issue. Draining works by receiving and discarding rows from abandoned queries and sending a Sync message via the PostgreSQL wire protocol, which clears the query context and returns the connection to a normal state.

Open Source and Documentation

PgDog is open source and welcomes contributions and feedback in any form. All features are configurable and can be turned on or off, so you can adopt it at your own pace.

Documentation: https://docs.pgdog.dev

Thanks for reading and happy hacking!