>PgDog is a sharder, connection pooler and load balancer for PostgreSQL. Written in Rust, PgDog is fast, reliable and scales databases horizontally without requiring changes to application code.
Still trying to figure out how this works technically, is the performance gain really just re-write in rust?
I tried out PgDog a while ago, but couldn't find a good way of handling the config except for having this users / pgdog toml file, which makes it a bit awkward to handle in kubernetes where we often do multi-tenancy in postgres - or rather having many databases on the same instance(s), and have them come and go at will.
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.
It’s surprising they don’t mention advantages over other sharding systems like Citus. Maybe it’s just the fact that it’s only a proxy and not core extensions? But that could limit capabilities.
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.
i am not using any tool like pgbouncer and have not run into any issues so far. Is it even required these days? Have you guys tested your setup without these connection poolers/multiplexers?
Suggestion: have more than just helm and Docker in your quickstart documentation. I'd like to try this out just to see what it can do, but not quite enough to fire up one of those systems for it.
the reason mongo is a joy to use in scaled env is because no additional setup/software needed and all drivers natively support secondary/primary writes/reads and topological changes. so it's end to end, and adding is as a new proxy in frontend of postgres leads to all clients being incompatible or the code itself has no control anymore about when to use a secondary and what allowed stall is acceptable for a particular query. Any solutions to this by pgdog?
once mongo rewrote their engine - it's performant, scales & easy to run. seems a lot of devs got burnt by the early issues don't consider it all together.
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.
I'm a big PGDog fan! It really helped us scale our connection proxy needs pretty substantially and it has great features like auto mode to support Aurora failovers neatly. It's infra that just works.
I am trying to gain a basic understanding of this:
Right now I have a 4TB DB on one large box.
Is the idea that using a proxy tool like PGDog I could spin up 8 smaller boxes handling ~500GB each and then one medium box for the proxy?
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 the idea of sharding. If you read the pgdog docs, you'll notice you need to tell it which shard server to route your request to - it doesn't just magically work. It's still providing value by reusing connections, which are particularly expensive in postgres.
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.
I'm curious how this might help with our biggest downtime-causer with postgres, which is major version upgrades. Poolers do a great job for failover and load balancing, but we consistently need ~10-20 minutes of downtime once or twice a year to do upgrades. Logical replication between old->new versions could probably help, but it would still require flipping everything over to the new cluster without partial writes or anything silly. Anybody have experience with this?
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.
I've loved using pgdog for the last 6 months. It's been incredibly stable. It's nifty how they've solved the LISTEN/NOTIFY on a transaction pooler problem.
#1 is a problem with the client's code, I don't know any easy workaround. Usually a long-running transaction means you're accidentally waiting on stuff like RPCs in the middle, or maybe doing something that doesn't really need to be in a xact.
#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.
Nit-Pick: It might be anti-marketing, still it would be helpful if the use cases can be articulated in a way where it would make sense to use this Vs any other type of database. Honesty goes a long way with the more technical folks for anything related to infrastructure.
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.
"Why Us" => "I ran Postgres at Instacart, where we scaled the company 5x in April of 2020. The biggest problem we had was making Postgres serve 100,000s of grocery delivery orders per minute"
It doesn't actually distribute postgres. It lets you use one connection to talk to multiple postgres databases by switching between them and if you're very careful you can sort of see it like a single database, ht it's not really.
77 comments
[ 2.9 ms ] story [ 87.5 ms ] threadStill trying to figure out how this works technically, is the performance gain really just re-write in rust?
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.
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.
Is there a binary I can run directly?
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.
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.
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.
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
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.
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.
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.
Couldn't be a better why us :)
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?