This is for DBs that are ~1-1.5TB but doesnt have a huge amount of churn/qps
Effectively what is described here https://www.pgedge.com/blog/always-online-or-bust-zero-downt...
If you use something like CloudNativePG they automate parts of the process with cli tools and declarative syntax. Otherwise you take the time to figure it out by hand. It might sound complicated, but just practice on your staging DB, and if all goes well you do the same procedure in prod.
Edit: Apparently Postgres 19 has a patch for one-shot logical replication of sequences! https://www.depesz.com/2025/11/11/waiting-for-postgresql-19-...
For both MySQL and PostgreSQL you will need to use some kind of logical upgrades if you want no downtime.
At this point i wonder if i'll ever see that.
For example, with Multigres, you should be able to achieve true zero downtime major version upgrade by simply resharding [2]. With vanilla Postgres + pgBouncer, you can only achieve near-zero downtime (few seconds at most), though it's probably good enough for most use cases.
[2] https://multigres.com/docs#migrate-across-postgres-versions
Multi master, from even a conceptual perspective, is incredibly complicated. Databases, transactions, consistency, parallelism are all very complicated.
It’s something that always seems promising at the start but as soon as maintenance and long term improvements enter the picture(ie integrating new Postgres versions), the complexity becomes too much.
I've used Postgres at a few places and the #1 problem was always high availability, not scaling. One Postgres cluster could easily handle 100000 transactions per minute, but when a primary node went down it was a page and manually failing over to the spare then manually replacing the spare. The manual tooling was very finicky but at least it worked, no automated solution came even close. Lack of a good HA story is why I avoid self-managed Postgres as much as possible.
Load balancer with health checks and failover, works out of the box. :) Battle-tested at this point too, so could be worth a look.
We sharded over 20 TB that we know about.
Couldn't be a better why us :)
Instacart doesn't need "100,000s of grocery delivery orders per minute".
There must be some 0s added for the sake of the story.
Don't pay a startup for your DB proxy, you should own that layer yourself inside of your infrastructure.
pg cat
pg dog
What's he going to name the next version?
pg emu ?
Unless you have millions of users, you don't really need this. It would be nice to have but its not a pressing need. So why invest into developing something that you only need once you are at massive scale? At this point you might as well switch away from Postgres because you'll surely have the manpower to do it.
Even with a proxy like PgDog the Postgres sharding story isn't solved. Resharding with logical replication is unlikely to work with databases which are already TBs in size. I never got it to catch up, I had to sync data at the filesystem level which is terrible. Tools like pg_repack also fall apart at scale.
For those that get to a point where a sharding proxy is required, switching databases is a very appealing solution.
And for those that are almost there, application side sharding is more flexible than building a query routing proxy.
Is there a binary I can run directly?
Then again, sharding on a single host probably isn't very useful anyway - but it might work with docker in swarm mode?
Right now I have a project that has very heavy write traffic from multiple services and a web app that reads from this. We are starting to hit the point where no amount of indexing, query optimisation, caching or box upgrades is helping us. We are looking at maybe moving the bulk of the static data to clickhouse to reduce the DB size but I would love to hear if PgDog or other kind of sharding could be useful for this use case.
That's exactly right. Get in touch (lev@pgdog.dev), happy to help or at the very least tell you what current works (or doesn't) so you know what your options are.
Because it's not magic, you do still have to know what's going on under the hood, e.g. no cross-shard transactions.
I'd see if my application can benefit from read replicas before doing sharding, because sharding is difficult (if you care about data consistency). With replicas, each replica does have a full copy of the data and you only write to the master - you have to decide which transactions are suitable for running against replicas, which can lag slightly behind realtime. E.g. reading data to build a webpage is probably safe to do from a replica - any read-modify-write is not.
1. Control plane to manage multi-node deployments; "works out of the box" experience to make PgDog easy to deploy and use
2. QoS (quality of service): automatically block bad queries from taking down the database
Last but not least, you get SLA-backed support from us (up to P0).
New features are broken down into two categories:
1. Sharding / running Postgres at scale: always open source.
2. Infra management / making it easy to run PgDog at scale: enterprise.
Also had an issue with it because it cached authentication requests when doing passthrough it seems, I'd changed the roles password, but it kept using the old one, which was no bueno ;).
PgDog seems to make more sense when you really care about a few databases that need massive scale, rather than a simple proxy in front of postgres. I'll keep following the development though, it is much needed in this space, postgres can use all the investment it can get to get it past the single machine scale that it excels at currently.
We'll get there.
You could also build a watcher side car that watches for changes of the pgdog_users.toml and have pgdog refresh itself then too with this combination. We thought about that but prefer to control the reloads for our needs.
If you’re already sharding by tenant for other reasons, OK… But I see CDC to a true OLAP system as more scalable.
PostgreSQL still needs real columnar tables in the core, hopefully one day
SELECT tenant_id, COUNT(clicks)
FROM users
GROUP BY tenant_id
ORDER BY 2 DESC
LIMIT 25;
So there is more core work happening on support OLAP but I do think it will take some time.
In the meantime, I think we have all the pieces (storage, query engine, table format) to set up a true OLAP. For instance, I created https://github.com/viggy28/streambed to pressure test this idea.
Still trying to figure out how this works technically, is the performance gain really just re-write in rust?
Edit:
Performance gains are from having the ability to load balance reads (horizontal scaling for read queries) and scale out writes (with sharding). Once instance bottleneck in Postgres has many faces:
1. Behind schedule vacuums because of too many dead tuples (too many writes)
2. The WALWriter is single-threaded and IO-bound - Postgres can only do about 200-300MB/sec in writes per instance (real prod numbers on EC2 with NVMes and ZFS, basically best case scenario).
3. Bulkheading: single primary is a single point of failure. With 12 primaries, if one fails, 91% of your customers don't notice.
The list goes on. Rust is just a side effect. We love it because it's fast and correct - the perfect match for a database product.
The same old processes vs. threads debate, plus having the ability to scale the coordinator past a single machine. So, if you're OLTP, definitely consider PgDog. OLAP - Citus still wins because of its advanced query engine. We'll get there.
Since Citus v11 (released 4 years ago), any worker node can also work as a "query router" (a node that you can query against [1], and works from this perspective as a pure coordinator:
> for very demanding applications, you now have the option to load balance distributed queries across the workers
You can also setup such query routers as dedicated nodes by setting the `shouldhaveshards` to `false`, becoming an effective coordinator (for querying; not for metadata operations).
So with Citus you can absolutely have as many query routers (coordinators if you wish) as you want.
[1]: https://www.citusdata.com/updates/v11-0/#metadata-sync
Edit: formatting, typo
TLDR: Tokio concurrency > Process concurrency in OLTP.
https://github.com/pgdogdev/pgdog/commit/36434f93f03dec1d7d4...
I want to have as much fun as the next developer, but that makes me worry, what with supply chain attacks in the news and all.
- Sage
In all seriousness, we review every single line of code that goes in and only people who work for PgDog Inc are allowed to merge.
As long as they don't get undercut by the equivalent of AWS https://aws.amazon.com/rds/proxy/ which is a managed pgbouncer.
I'm really happy that there's more options for Postgres sharding and I applaud Pgdog and the team's efforts and energy.
Having said that, this makes it a no-go for me:
> shard_number = hash(data) % num_shards
https://docs.pgdog.dev/features/sharding/basics/#terminology
Most sharding solutions distribute the hash value over linear ranges, that then split across "virtual shards", that are then placed on the physical shards or worker. This allows for shard replacement when needed. For example, Citus works this way, and even adds convenience functions for shard migration (using logical migration) in an automated way. That's all I'd need.
Operationally, it's worlds apart. With modulo distribution the only way to replace data is to reshard everything --something you don't want to do however fast the operation may be.
Adding a sharding function in our architecture is relatively straightforward. We also support plugins which can control the flow (and direction) for queries, so our users can add their own (and they do!).
* Adding a sharding function, as you say.
* Developing an external service for metadata (shard placement) or alternatively have that metadata in one place and replicate (consistently!) to every query router.
* Implementing functions/catalogs for the users to understand the placement and configure/alter it.
* Implementing shard migration / rebalancing capabilities, possibly using Postgres logical replication (plus notable automation).
Here's one idea if you follow this path, something that Citus doesn't have: make the sharding function pluggable and pick one by default which is well-known and available in many languages (e.g. xxhash). If you do so, and guarantee stability of those functions, they could be used externally (applications) to route queries / inserts especially to the appropriate shard. While it makes application more complex, it may allow (combined with access to the metadata service) for faster ingestion paths (this is often known as application assisted sharding), and its not exclusive of the query routers.
Edit: formatting
Edit: It also might be interesting to point out how your solution differs from what the folks at Planetscale are building https://planetscale.com/neki
1. Neki as you mentioned 2. PgDog 3. Multigres, headed by original creator of Vitesse
1. pool exhaustion from idle connections inside open long-running transactions
2. SQLAlchemy's client-side pool using dead connections that PgBouncer had already killed, causing periodic request errors
3. Some tasks have to bypass PgBouncer when they use SET or prepared statements
I've already sharded large datasets at the application layer, but looks like PgDog solves the above problems for any future work?
#2, shouldn't the client<->PgBouncer connections stay open?
#3 is why I just use client-side pools instead of PgBouncer, but that gets annoying when you have a replicated service so you have to think about the sum of connections across all pools, so I get why people use PgBouncer.
I had to disable application pooling as it was causing read only transactions I could couldnt pin down the cause.
This is years of product development with a three person team. If Enterprise sales and support are a big part of your business plan it will suck up a lot more than that.
My question is, has any of them been talked about being upstreamed to postgres itself? Or, adding a custom built in feature to postgres itself?
Expanding on that a bit, mongo drivers even have a shared specification of the state machine for monitoring topology changes[1] and algorithm for selecting the server to send an operation to[2] (along with various declarative test cases that the drivers use to validate them alongside the specs in the repo). I think people sometimes underestimate how important the client-side work is to this sort of experience; for all of the faults mongo has had over the years, the amount of investment that they put into the client libraries is something I've never seen anywhere else (although having spent several years working on some of these libraries, my take is likely very biased).
[1]: https://github.com/mongodb/specifications/blob/master/source... [2]: https://github.com/mongodb/specifications/blob/master/source...
its probably the easiest database to run at scale. run & forget. you just have to do a little more work on the data modeling part before you write your application i.e consider your query patterns.
You _could_ make that ACID, but it's not going to be faster than a single machine.
Surfacing where and how PG is better than Dynamo or any other database is probably a good starting point instead of calling out PG a silver bullet for everything. At the end of the day its all a trade-off.
Not quite. The reason "DBs" like those exist is purely due to fashion. Lets not kid ourselves into thinking they do anything better, save the exception of making data hard to access, which might be a project goal in some cases.
Wrt. the pooler, how do you compare with pgbouncer?
I'm interested because I have a postgres instance, low-traffic but still like ... tens of r(eads)ps. I was not running anything close to the machine limits but still added pgbouncer to improve performance and didn't see a noticeable difference. I was stress-testing the machine obv., I'm not talking about the 10 rps, lol.
For context, my numbers were something like 10k rps +/- 1k vanilla postgres and like 9k rps +/- 1k with pgbouncer in front of it. So ... slightly slower but big error bars so I wouldn't say for sure. I ended up not using pgbouncer as the benefit was immaterial.
Also yeah, in case you want to check it out, it's the db that backs this project: https://httpstate.com.
Just to say we're happy pgdog users here! One feature we quite like (of the proxy) is the handling of different connection settings per connection (i.e. statement_timeout). When we investigated RDS proxy (ages ago) it wasn't supported, I think the same was true for pgbouncer so it required a bunch of application changes. With pgdog, it just works transparently.
This kind of tool will help in this case?
i'm sure you'll get 100x comments about "why not just have one fast SSD? it can do 2000 trillion writes/s"
I don't know how the pg scaling story gets fixed unless certain things are rewritten. that's my fear of going all in pg.
mysql has vitess etc & even upgrades are easier. though pg is more extensible.
However 95% of projects are going to be fine with a normal single-machine database and another 4% are going to be well served by upgrading the hell out of that machine. Only the absolute busiest projects actually need a distributed database and you can cross that bridge when you actually get to it.
They say Amazon processes 20k orders per second. That seems not unachievable for postgres with fast SSDs and careful query optimization, though they don't choose do it that way. You're not Amazon, you have at most 20 orders per second and that's nothing.
This solves the thousands of clients case for read in a way that is transparent to the clients.
Yes it's required at large scale, especially if you want to distribute reads or shard to a particular geographical area.
Would love to hear the advantages of moving to PgDog.
This just seems like fanboyism to me. At the very least, you need to qualify what scenarios you think it's useful for.
I don't doubt that Postgres is good for all the projects you've ever worked on. Generalizing from that, though, is hubristic.