Why we built yet another Postgres connection pooler - PgDog

We raised a $5.5M seed round.<br>Read the post→

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';<br>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...