Interesting. We run pgbouncer via kubernetes so it was straightforward to make multiple pgbouncer processes on one machine. Also straightforward to get them running on multiple machines, which helps because we run on Azure and they like to cause rolling outages across our fleet via VM maintenance...
Ack, makes sense. I’m very curious on how this affects throughput due to a potential extra network hop from pgbouncer to Postgres. Expecting it to have a minor difference, but still curious.
This was more for fun than real use, but I greatly enjoyed hacking something similar into rqbit bittorrent client.
I wanted to run an instance of 'rqbit download' per torrent via so_reuseport. When a peer tries to connect, it gets sent to a random instance. So I built a whole rendezvous system, where instances find each other & either proxy data to each other or fd pass the socket to each other directly to get the peer socket to the instance that needs it. It uses postcard rpc to chat between instances.
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.
We started with the most battle-tested and native option to Postgres, which is PgBouncer and tried tuning it the right way. Also now that long due kinks like support for prepared statements are solved, it’s been working really well. There are many customers scaling well with 10K+ Postgres connections. We will consider other options like odyssey, pgdog in the future!
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. ;)
> 100s are more than enough to scale Postgres well
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)
With Odyssey we have customers with 50k+ connections operating normally.
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.
Cool, another whataboutism. USA caused a lot of damage, but USA is a grey country - they do a lot of bad things but they also do a lot of good things (science & technology).
russia is one-dimensional and that dimension is violence. They don't invent things, they don't innovate, they don't export anything (apart from gas/minerals) and they only have imperialistic ambitions.
except for perhaps… a scalable pg bouncer? And good nuclear reactors [1]?
Not to say I support many Russian political moves, but I think discrediting an entire people and their outputs on Russian politics is brusque - particularly open source ones.
Neither pg bouncer nor nuclear reactors are unique inventions in and of themselves - these are just iterations on what has existed previously. Of course russia can take an existing thing and iterate on it (computers, rockets, AK-47, etc.) but that wasn't my point.
Objectively speaking, Russia is one of the most creative and innovative societies of all time. Literature, science, music, sporting, mathematics, computing, you name it, a Russian has innovated in it.
I don't get the "its from [country] so I don't use it". Unless this is somehow funding e.g. a war, what did the maintainers do to deserve such backlash?
Edit: just read what ties ClickHouse has, nevermind.
But in this case you would be supporting one of the good things (science & technology) they've done, right? A database connection pooler is not necessarily military technology.
If open source code can have political beliefs then the only reasonable solution is to live as a hermit.
I can guarantee that there is software on your computer written by communists, nazis, mormons, every single political ideology is represented by lines of code that you run every day, because open source doesn't require any political vetting to contribute.
Again, there's a difference between software written by a person that represents a 'bad' (to me) ideology, and software written by corporations in terrorist states.
As Odyssey maintainer, I have to admit that it is exceptionally difficult to achieve stability (e.i. quality) of PgBouncer.
It has two limitations: it is single threaded and it is developed only re-actively. PgBouncer docs used to say that prepared statements can never be supported, in 2022 we supported it in Odyssey and then PgBouncer implemented it too.
In a sense Odyssey is developed to motivate PgBouncer development. To provide alternatives that keep PgBouncer from stagnation.
Yet, I can't say that scalability with SO_REUSEPORT is a good solution.
I have choose pgbouncer for my small db, because it does one thing and does it good - transaction pooling, other solutions seemed too complicated for me. All that features which should keep you allow to use listen/notify and set was unnecessary for me, i solved it on code level
Glad you like it. It was built to fix some of PgBouncers shortcomings that we ran into at Instacart many years ago, and to have a stronger foundation for scaling Postgres horizontally (that's sharding)!
First time I've heard of so_reuseport, which is interesting. The important parts of the setup seem to be that + peering; is peering built-in to PgBouncer and simple to set up?
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.
Why Postgres should be doing this?
Not every client creates a lot of connections and spinning up PgBouncer is easy.
On the other hand, debugging async multithreaded complex code is hard.
Postres project also once lacked replication, calling it unnecessary to the core effort. Now it has two means of replication in core and I'd argue is better for it, especially after suffering through both Pgpool and Slony.
I hope they do develop a native, threaded pooling, even if it were incompatible with some libraries or extensions.
Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall, first out 2005-08-01) doing raw system calls because libc on the distros of the time (at least Debian) didn't have epoll support yet.
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.
> The cancel lands on a process that has never heard of the query, and nothing happens.
> 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.
can it work in kubernetes with peering? since there won't be any need to reuse ports there. or separate pods will have separate pools and will act as independent?
Why does PgBouncer need to care about cancellation requests at all? Why can't it just forward the cancellation to postgres, which then responds to the cancelled query with an error instead of a response, and then the bouncer for that connection handles that error?
Query cancels are out of bound of the query connection so PGBouncer has to land it on the right server and the right connection. So PGBouncer serves a bogus-but-tracked cancel PID+secret to the client, which then goes to PGBouncer and PGBouncer figures out the right server/process to send the actual PID+secret to.
is this for microservice scenarios where you gate access to the psql server through a connection pool thing? because if there is monolithic backend this is not needed.
most decent backend frameworks have built-in connection pooling. that covers 98% of use cases for which microservices are not needed, nor recommended.
62 comments
[ 0.22 ms ] story [ 66.3 ms ] threadClickhouse'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
1: https://lwn.net/Articles/542629
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. ;)
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)
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.
russia is one-dimensional and that dimension is violence. They don't invent things, they don't innovate, they don't export anything (apart from gas/minerals) and they only have imperialistic ambitions.
Not to say I support many Russian political moves, but I think discrediting an entire people and their outputs on Russian politics is brusque - particularly open source ones.
[1]: https://www.abc.net.au/news/2026-05-08/russia-nuclear-power-...
Edit: just read what ties ClickHouse has, nevermind.
I can guarantee that there is software on your computer written by communists, nazis, mormons, every single political ideology is represented by lines of code that you run every day, because open source doesn't require any political vetting to contribute.
Check out Nebius
It has two limitations: it is single threaded and it is developed only re-actively. PgBouncer docs used to say that prepared statements can never be supported, in 2022 we supported it in Odyssey and then PgBouncer implemented it too.
In a sense Odyssey is developed to motivate PgBouncer development. To provide alternatives that keep PgBouncer from stagnation.
Yet, I can't say that scalability with SO_REUSEPORT is a good solution.
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.
I hope they do develop a native, threaded pooling, even if it were incompatible with some libraries or extensions.
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.
> 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.