ES version is available. Content is displayed in original English for accuracy.
Advertisement
Advertisement
⚡ Community Insights
Discussion Sentiment
69% Positive
Analyzed from 1353 words in the discussion.
Trending Topics
#pgbouncer#postgres#https#connection#peering#more#com#process#connections#built
Discussion (54 Comments)Read Original on HackerNews
Side note: I’m not a big fan of having 10K+ connections on Postgres, 100s are more than enough to scale Postgres well. But that’s a story for another day. ;)
Also consider SQPR - it's a connection pooler with sharding capabilities. It handles data migration between shard on top of request routing. Odyssey will inherit this capability once it is stable enough in functions set.
I'd want to know what the workload is. That's true of lots of projects, especially internal tools (even for multinationals). But for my last project, that would have been tough. And by FAANG standards my last project was 'medium' sized, even though it was large by the standards of many places I've worked.
(The galling thing is I shrunk the hardware by 40% but if I'd been there during the architecture phase I'm pretty sure I could have shrunk it by 8x by solving a completely different problem that had higher margins than what we actually did)
> Peering fixes this. The processes are aware of one another, so a cancel that lands on the wrong process is forwarded to the one that actually owns the session.
I understand "peering" as a concept here but have never tried this with PostgreSQL before. May I ask:
A) Does PostgreSQL have a mode/setting for peering that makes this easy? I'm imagining a mechanism that either goes round robin (re-sending the cancel to peers until it doesn't return an error of some kind) or some metadata in the cancel request that enables the wrong-destination process to somehow identify the proper process.
B) And by what mechanism? If all the PostgreSQL processes are listening to clients via so_reuseport, I guess there must be some other IPC method used for the peering chatter.
See from slide 26 https://www.pgevents.ca/events/pgconfdev2024/sessions/sessio... Jelte's a pgbouncer maintainer, video of a talk on this by him: https://www.youtube.com/watch?v=X-nCHcZ6vQU
But if your backend app is a shared nothing fork of many processes then this is surely needed.
I gather things haven't improved since?
However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot.
Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.
I find it ridiculous that PgBouncer even needs to exist. Postgres should be doing this.
Postgres and SQLite were being designed at that same time but by industry veterans. People who had been deploying high load systems before any of this threading nonsense was around. And they were supporting people running on old hardware.
So in 2005 I didn't expect Postgres to do super well here, but it's 21 years later and we're still pgbouncin'. It's just kinda sad.
1: https://lwn.net/Articles/542629
Clickhouse's so_reuseport rendezvous needs are obviously for a very different, but fun to see some so_reuseport coordination like this (for a much more practical use)!
It'd be really neat to have some kind of general peering protocol that different apps could use. This whole exercise was gratuitous as heck for my application, I don't even really intend to use this, but it was a fun path to walk down. So I don't really know what the broader protocol would really be for, what we would use it for. But it seems like such a cool idea! A shared Turso database would probably be a bit more practical than the rpc system, honestly. Ha.
https://github.com/rektide/rqbit/tree/peering
[pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 so_reuseport = 1 peer_id = 1 unix_socket_dir = /tmp/pgbouncer1
[peers] 1 = host=/tmp/pgbouncer1 2 = host=/tmp/pgbouncer2 3 = host=/tmp/pgbouncer3 4 = host=/tmp/pgbouncer4