Show HN: PgCat, Postgres pooler with sharding, load balancing and failover
github.com
github.com
This is my take on what a modern Postgres pooler can be. Besides supporting same session and transaction pooling modes as Pgbouncer, it also adds support for load balancing between replicas (round robin at the moment), failover in case a replica fails a health check, and the coolest thing yet I think: sharding at the pooler level.
It's written in Rust, which I think makes it much easier to iterate on and improve.
Note in case it wasn't obvious: this is super experimental, please don't use this in your production environment unless you're adventurous. In which case, please do and let me know how it goes!
- Lev
Looking forwards to where the project goes!
But I've always struggled to understand the use cases of pgbouncer. In most cases, you want to front your database with a service and don't want many clients connecting to it directly. And I've seen fairly large workloads easily managed over a small number of connections, so a good client-side pool implementation should easily suffice. In what sort of situations does pgbouncer come into play?
As you say, you can write a service to stand in front of the DB and mediate access, but PgBouncer is already written AND production ready
Also, 20 connections per service process seems pretty high. Unless there are many long running queries and clients are willing to wait long times for responses.
The harder part is ensuring people don’t hold onto connections during long-running operations. In some client libraries, it’s easy to accidentally hold one open. That plus autoscaling results in ticking bombs that are hard to catch with automated tests
There are some architectures that tear-down all resources at the end of every requests, e.g. PHP, or lambda-like things, so you can't use a connection pool, because the "pool" would only exist for that request.
pgbouncer can be used locally too, however.
E: Oh, and how could I forget all the mod_php based scripts for these web-app front-ends.
During busy time, Apache Airflow can launch hundreds of workers, each of them need at least 1 connection to Postgresql to save its result. Because each worker run in its own process due to Python's lack of support for threading, client-side connection pool cannot be use effectively.
Where should new people contribute or can help most ( I do golang and am good with k8s stuff ) all the best!
GoLang seems like the language du jour, but I think this post [1] really illustrates how powerful it can be when you have a compiler that handles so much more for you. It's not just about performance. Deadlock detection and a robust compile time set of error checks are huge wins over looser languages.
1: https://fasterthanli.me/articles/some-mistakes-rust-doesnt-c...
Since I can see basic functionality exercised in the test suites, and trust that Rust has validated its more general correctness, this gives me more confidence in your project than I would historically expect to be able to gather in such a short amount of time.
As for tests, it's covered with pgbench, python (psycopg2), ruby (pg / active record gems), psql and a couple inline tests for trivial things like config.
As for bytes and hardcoded ints...give pgbouncer's and postgres' source code a look :).
That said I've struggled with more than my fair share of pgbouncer misconfigurations and outright bugs. I personally would love a simpler (harder to misconfigure) and less buggy alternative.
This statement does not make any sense. That, or we have vastly different definitions of what “safe” means.
That said some of the things this one looks to solve are indeed very interesting.
I've seen issues at times with pgbouncer being single threaded. Since it's only running against a single core it is possible to peg that core and have a max throughput. While not common we have seen this for customers before, both at Crunchy Data[1] on and back at Citus.
The other interesting piece is sharding. We considered this approach for years at Citus and never managed to pull it off. If you can specify a identifier and it can route accordingly it can give you a lightweight option for sharding really quite easily.
Again, probably has a ways to go, everything we've tried over the last 10 years has failed to live up to a replacement for pgbouncer. But pgcat is attempting to do I'm fully supportive of if it can accomplish it.
[1] On Crunchy Bridge www.crunchybridge.com we have pgbouncer built-in, with multiple pgbouncer support to alleviate single threaded issue.
So i can better understand sharding/load balancing/failover/indexing etc.
For those wondering why a pooler is needed, Tl;DR containers. It's much more normal to have hundreds of containers running an application configured to have tens of connections. Now you have thousands. Each connection costs postgres something on the order of ~2.5MB ram, so once you get into the thousands you're starting to talk real numbers = ~2.5GB
Problems I've had with pgbouncer in the past:
- stats. Any modern tool should natively emit its own statistics in something like statsd format. - pgbouncer is a single process - I can't throw more CPU cores at it to make it faster.
Problems that I'm still struggling to solve...
We use RDS/Aurora which uses DNS to fail over. Yes applications should handle this. Yes they should retry. Yes devs should have tested this. But in any large company this is a uphill battle involving many teams and apps. Much easier to introduce a proxy layer to handle fast failover. Even better if it can re-try queries that fail transparently to the application.
[1] https://github.com/prometheus-community/pgbouncer_exporter
Hi there, thanks for mentioning Citus. Could you share a bit more about the user experience you're looking for with sharding?
With Citus, you create your Postgres table as-is. If you'd like for the table to be distributed, yes, you'd need to pick a distribution key. You'd do this by calling: SELECT create_distributed_table('postgres-table-name', 'distribution-column');
We also thought about picking a distribution key on behalf of the user. This however has performance implications, particularly as you add more nodes to the cluster.
The state of the art for sharding, connection scaling, and failover for Postgres is far behind everything else. MySql has Orchestrator and Vitess, the NewSQL systems are doing lots of interesting stuff with replication and sharding, etc. etc.
edit: Look at the work that Notion had to as of just 3 months ago to shard Postges: https://www.notion.so/blog/sharding-postgres-at-notion. Maybe they would make the same choice over again, and that's fine, but doing stone age level work to shard a database in 2021 doesn't jive with the whole "just use Postgres" idea to me.
>> Besides introducing needless complexity, an underrated danger of premature sharding is that it can constrain the product model before it has been well-defined on the business side. For example, if a team shards by user and subsequently pivots to a team-focused product strategy, the architectural impedance mismatch can cause significant technical pain and even constrain certain features.
>> During our initial research, we also considered packaged sharding/clustering solutions such as Citus for Postgres or Vitess for MySQL. While these solutions appeal in their simplicity and provide cross-shard tooling out of the box, the actual clustering logic is opaque, and we wanted control over the distribution of our data.²
The advise is clearly targeted at those with a scale orders of magnitude smaller than Notion.
I don’t think anyone is claiming "you should just use Postgres" without knowing about your constraints. And if they do, I guess that’s a useful heuristic to know when someone doesn’t know what they’re talking about.
It’s still good advise for the 95%, even if you yourself fall outside of that. Part of the reason you get paid well is to know when advise applies and whet it does not, and to not waste energy arguing in either case.
Of course, there will always be fanboys who take things too far.
MySQL's cannot