PgDog is a proxy for scaling Postgres. One of its features is connection pooling, which allows many client applications to use the same database without exceeding its connection limit.

There are many connection poolers out there, notably PgBouncer, RDS Proxy, Pgpool-II, Supavisor, and others. So, why build another one?

Leaky abstractions

Most tools in the Postgres ecosystem take the UNIX philosophy very seriously: do one thing and do it well. In general, this is a good way to build reliable software, and it is why PgBouncer is so popular today. It works.

However, it works by introducing what we in the industry call a leaky abstraction.

When you deploy PgBouncer or RDS Proxy, you quickly become aware that you added a piece of infrastructure that changes the way you use your database. It requires you to make trade-offs, often by changing your application code.

If you’ve been building your app for a while, which is often the case when you need to add connection pooling, this could mean changing thousands of lines of production code, usually with very little test coverage.

With PgDog, that’s no longer necessary.

Connection state

The first thing to go when you add a connection pooler is session control, i.e., SET commands. They are used to temporarily override database settings in order to, for example, execute slower queries:

SET statement_timeout TO '5m';
SELECT * FROM users WHERE banned IS true;

Since connection poolers reuse connections between clients, the connection state of one client “leaks” into the connection state of another.

In production, this could be pretty bad. Best case scenario, a slow query gets to run for a while, causing a database incident. Worst case, your Row Level Security (RLS) policy, which relies on a session variable, stops working and rows silently disappear.

So, the typical advice when migrating to connection pooling is to just not use SET anymore.

However, SET is a real Postgres feature. If you’re not allowed to use a database feature, you are making a trade-off and have to change how you write your apps. And if you’re using it for something important, like RLS, you can’t use connection pooling at all.

Handling SET statements

PgDog ships with a built-in SQL parser. It can detect SET statements, extract variable names and values, and store them on each client connection in the proxy.

When a client runs a query, PgDog first checks that its state matches that of the server, and if it doesn’t, it updates it by running a series of SET statements of its own.

The algorithm we use to detect SET statements is very quick. If several variables are different, we use query pipelining to update them in one round trip.

This makes the performance impact small, and you can keep using a Postgres feature you’ve been relying on, as you scale.

LISTEN/NOTIFY

LISTEN and NOTIFY are Postgres commands that implement a publish/subscribe queue inside Postgres. It’s a neat feature, and it works pretty well without having to add another database to your stack.

It’s also a feature you had to give up when you added a pooler, at least if you wanted to use transaction mode.

If you built an app in the last 10 years (PostgreSQL 10 came out in 2017), you probably used it, before migrating to SQS or Redis.

PgDog makes that work too. It handles both commands internally, while moving messages between multiple PgDog processes. To the client, this looks like PgDog is the broker, but actually, it’s still Postgres.

We also make sure to preserve all of NOTIFY’s transactional semantics, even the ones that made some of our friends take down their database (we fixed that, by the way).

The internal implementation is kind of interesting. We use Tokio’s broadcast channel to move messages between clients in the same PgDog process. To support multiple PgDog processes (e.g., in production, you would run several containers), we also send all LISTEN and NOTIFY commands to Postgres via a dedicated connection.

So in effect, PgDog acts as a pub/sub client, proxying other pub/sub clients, using Postgres as the broker. A bit of creative engineering, just to make a feature we use “just work”, as we scale Postgres.

Multithreading

PgDog is built on Tokio, a Rust async runtime with multithreaded workers. Each client is handled by its own async task, which scales linearly with the number of connections.

Using Tokio allows us to take advantage of multiple CPUs, serving more clients and more queries per second from one PgDog process.

However, with PgBouncer supporting SO_REUSEPORT and RDS Proxy “serverless” autoscaling, why does this matter?

Both tools require you to “shard” your connection pools. Each proxy process has its own dedicated set of Postgres connections. Once a client connects, it cannot change instances anymore, so if it becomes overloaded, all other clients get stuck too.

By multithreading on multiple CPUs, PgDog processes can handle a lot more traffic. This allows it to pool more clients with fewer server connections, increasing connection utilization and efficiency.

Multithreaded processes can also better handle sudden bursts of queries, because they don’t need to wait for autoscaling. If your app has a latency SLA, you don’t want your proxy getting in the way.

Last but not least, the runbook for managing multithreaded processes is shorter: just one source of metrics and health checks, without pushing complexity into hard-to-debug places, like the Linux kernel or the AWS RDS control plane.

Closing thoughts

It can be hard to replace a 20-year-old project like PgBouncer but, when we think something isn’t quite right, we fix it. PgDog has been in production for over a year, pooling connections at 2M queries per second.

It’s free (as in freedom to use and modify), it’s open source, and you can deploy it anywhere.