Wir haben PgBouncer auf den 4-fachen Durchsatz skaliert(clickhouse.com)
clickhouse.com
We scaled PgBouncer to 4x throughput
https://clickhouse.com/blog/pgbouncer-clickhouse-managed-postgres
31 comments
you encode information in the token, this was a motivation for postgres to remove 32 byte cap on tokens: https://www.postgresql.org/message-id/508d0505-8b7a-4864-a68...
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
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
AI clearly wrote TFA. The cancellation thing is apparently a PgBouncer feature - the peering is between bouncer processes, not server processes. It sounds like it should be easy enough to make the bouncer process ID part of the cancel key.
Just use https://github.com/yandex/odyssey :) It's a scalable PgBouncer.
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. ;)
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. ;)
Fun (semi-related) fact, ClickHouse was originally developed by Yandex :)
AI-ready ????
yandex is a russian company, so no, don't use it
I have bad news for you about where ClickHouse comes from then.
Do you boycott American companies too? I'm assuming you boycott Russian companies because Russia causes death and destruction. But the USA caused much more death and destruction than Russia ever did - do you boycott them too?
uhhhh
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.
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.
[1]: https://www.abc.net.au/news/2026-05-08/russia-nuclear-power-...
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-...
nginx is also Russian, so don't use... a third of the web.
nginx was built by one person. I'm talking about a russian corporation that has (inescapable) ties to kremlin
thank you for the advice mr mcarthy, i'll happily use deepseek and GLM to vibe my next project.
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.
Cross zone latency has noticeable effect: https://news.ycombinator.com/item?id=45512351
I've been using pgdog (https://github.com/pgdogdev/pgdog) and it has worked really well for my needs!
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)!
Ack, we will consider offering pgdog in the future! More context on why we chose pgbouncer https://news.ycombinator.com/item?id=48873867
It's been a pong pong back and forth between PG reverse proxy submissions lately! "Why we built [Pgdog]":
https://news.ycombinator.com/item?id=48819308
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?
I'm 46 now. I remember being shocked at Postgres's heavy connection model when I was 23.
I gather things haven't improved since?
I gather things haven't improved since?
It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/imp....
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.
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.
Was there a disadvantage to using HAProxy + multiple PGBouncer instances?
SO_REUSEPORT[1] pretty much does all we want in kernelspace vs unnecessary userspace hop inbetween. These all run on same VM
1: https://lwn.net/Articles/542629
1: https://lwn.net/Articles/542629
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.
https://github.com/rektide/rqbit/tree/peering
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
Article should show the config:
[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
[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
> 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.