Sharding Postgres at network speed
Up until now, PgDog has been a very smart connection pooler for Postgres. While it could shard and load balance queries, databases are rarely greenfield projects. We needed a way to migrate existing apps to a sharded database and keep all their data, without taking the apps offline.
PR #279 introduced a long promised architectural change: we can now use logical replication to re-shard databases. This happens online and doesn’t affect production performance.
In this post, we describe how the new feature works. In our tests, we were able to copy and shard terabytes of data in a matter of minutes.
If you’re familiar with Postgres logical replication and replication slots in Postgres v16, you can skip straight to architecture. For everyone else, a quick introduction to what’s going on.
Logical replication is key to sharding Postgres online. It allows copying data between two or more databases, in real time. Short of taking your app offline, it’s the only reliable way to do this. Manually running COPY commands or dual-writing in the application code is, more likely than not, to lose data.
