6 ms·

We scaled PgBouncer to 4x throughput

241 points by saisrirampur 3mo ago

bradfitz 3mo ago
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?
- saisrirampur 3mo ago
  It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 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.
  - bradfitz 3mo ago
    In other words, it's still super heavy if it's forking a process per connection. I find it ridiculous that PgBouncer even needs to exist. Postgres should be doing this.
    - kmeaw 3mo ago
      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.
      - paulryanrogers 3mo ago
        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.
- hinkley 3mo ago
  You were coming into the field just as companies were fielding their first reasonable answers to the Threading Model that Java put forward, which was sort of Windows' but with extra features. Even Solaris choked on Java. HP UX did worse and I can't recall if SGI was worse or better than HP. But getting compatible with Java shook a lot of companies up, in how they handled concurrency. 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.
  - bradfitz 3mo ago
    Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall 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.
odie5533 3mo ago
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
nosefrog 3mo ago
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...
- saisrirampur 3mo ago
  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.
  - toredash 3mo ago
    Cross zone latency has noticeable effect: https://news.ycombinator.com/item?id=45512351 https://news.ycombinator.com/item?id=45512351
jauntywundrkind 3mo ago
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 https://github.com/rektide/rqbit/tree/peering
nzeid 3mo ago
Was there a disadvantage to using HAProxy + multiple PGBouncer instances?
- __s 3mo ago
  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 https://lwn.net/Articles/542629
x4m 3mo ago
Just use https://github.com/yandex/odyssey https://github.com/yandex/odyssey :) It's a scalable PgBouncer.
- sevg 3mo ago
  Fun (semi-related) fact, ClickHouse was originally developed by Yandex :)
- saisrirampur 3mo ago
  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. ;)
  - hinkley 3mo ago
    > 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)
  - x4m 3mo ago
    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.
- deleted 3mo ago
  [deleted]
JustSkyfall 3mo ago
I've been using pgdog (https://github.com/pgdogdev/pgdog https://github.com/pgdogdev/pgdog) and it has worked really well for my needs!
- saisrirampur 3mo ago
  Ack, we will consider offering pgdog in the future! More context on why we chose pgbouncer https://news.ycombinator.com/item?id=48873867 https://news.ycombinator.com/item?id=48873867
- levkk 3mo ago
  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)!
- jauntywundrkind 3mo ago
  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 https://news.ycombinator.com/item?id=48819308
DylanSp 3mo ago
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?
- __s 3mo ago
  Yes, https://www.pgbouncer.org/config.html#section-peers https://www.pgbouncer.org/config.html#section-peers
ezekiel68 3mo ago
> 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.
- inigyou 3mo ago
  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.
- __s 3mo ago
  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-a681-e7e5edfe32aa@iki.fi https://www.postgresql.org/message-id/508d0505-8b7a-4864-a68... See from slide 26 https://www.pgevents.ca/events/pgconfdev2024/sessions/session/107/slides/48/2024-05-29-the-postgres-protocol%20(split).pdf 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 https://www.youtube.com/watch?v=X-nCHcZ6vQU
  - ezekiel68 3mo ago
    Thanks for this!
desert3agle 3mo ago
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?
madduci 3mo ago
PgBouncer is a great piece of software. We use that heavily and had made us DB operations pretty easy
CodesInChaos 3mo ago
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?
- jkingsman 3mo ago
  Query cancels are out of band from 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. PGBouncer also has to make sure that the connection/PID is still actually running the query; strictly speaking, a client can believe a query is still running when PGBouncer knows it's already complete and the connection has been reused.
z3ratul163071 3mo ago
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.
- didip 3mo ago
  if your backend app is one big binary per host? Sure. But if your backend app is a shared nothing fork of many processes then this is surely needed.
  - z3ratul163071 3mo ago
    didn't that end with the cgi era? what is going on :)
snikes 3mo ago
PgBouncer works great for us with Vercel Workers. We use ClickHouse PG behind it, and haven't had any issues so far :)
guillaume_code 3mo ago
The connection-pooler-as-bottleneck story is so common and so under-discussed, nice writeup. Did the 4x come mostly from the multi-process model, or did you also have to change how the app holds connections? The app-side pattern usually eats half the gains.

241 points by saisrirampur 3mo ago

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?

saisrirampur 3mo ago
It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 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.
- bradfitz 3mo ago
  In other words, it's still super heavy if it's forking a process per connection. I find it ridiculous that PgBouncer even needs to exist. Postgres should be doing this.
  - kmeaw 3mo ago
    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.
    - paulryanrogers 3mo ago
      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.
hinkley 3mo ago
You were coming into the field just as companies were fielding their first reasonable answers to the Threading Model that Java put forward, which was sort of Windows' but with extra features. Even Solaris choked on Java. HP UX did worse and I can't recall if SGI was worse or better than HP. But getting compatible with Java shook a lot of companies up, in how they handled concurrency. 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.
- bradfitz 3mo ago
  Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall 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.

odie5533 3mo ago

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

nosefrog 3mo ago

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...

saisrirampur 3mo ago
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.
- toredash 3mo ago
  Cross zone latency has noticeable effect: https://news.ycombinator.com/item?id=45512351 https://news.ycombinator.com/item?id=45512351

jauntywundrkind 3mo ago

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 https://github.com/rektide/rqbit/tree/peering

nzeid 3mo ago

Was there a disadvantage to using HAProxy + multiple PGBouncer instances?

__s 3mo ago
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 https://lwn.net/Articles/542629

x4m 3mo ago

Just use https://github.com/yandex/odyssey https://github.com/yandex/odyssey :) It's a scalable PgBouncer.

sevg 3mo ago
Fun (semi-related) fact, ClickHouse was originally developed by Yandex :)
saisrirampur 3mo ago
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. ;)
- hinkley 3mo ago
  > 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)
- x4m 3mo ago
  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.
deleted 3mo ago
[deleted]

JustSkyfall 3mo ago

I've been using pgdog (https://github.com/pgdogdev/pgdog https://github.com/pgdogdev/pgdog) and it has worked really well for my needs!

saisrirampur 3mo ago
Ack, we will consider offering pgdog in the future! More context on why we chose pgbouncer https://news.ycombinator.com/item?id=48873867 https://news.ycombinator.com/item?id=48873867
levkk 3mo ago
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)!
jauntywundrkind 3mo ago
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 https://news.ycombinator.com/item?id=48819308

DylanSp 3mo ago

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?

__s 3mo ago
Yes, https://www.pgbouncer.org/config.html#section-peers https://www.pgbouncer.org/config.html#section-peers

ezekiel68 3mo ago

> 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.

inigyou 3mo ago
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.
__s 3mo ago
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-a681-e7e5edfe32aa@iki.fi https://www.postgresql.org/message-id/508d0505-8b7a-4864-a68... See from slide 26 https://www.pgevents.ca/events/pgconfdev2024/sessions/session/107/slides/48/2024-05-29-the-postgres-protocol%20(split).pdf 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 https://www.youtube.com/watch?v=X-nCHcZ6vQU
- ezekiel68 3mo ago
  Thanks for this!

desert3agle 3mo ago

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?

madduci 3mo ago

PgBouncer is a great piece of software. We use that heavily and had made us DB operations pretty easy

CodesInChaos 3mo ago

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?

jkingsman 3mo ago
Query cancels are out of band from 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. PGBouncer also has to make sure that the connection/PID is still actually running the query; strictly speaking, a client can believe a query is still running when PGBouncer knows it's already complete and the connection has been reused.

z3ratul163071 3mo ago

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.

didip 3mo ago
if your backend app is one big binary per host? Sure. But if your backend app is a shared nothing fork of many processes then this is surely needed.
- z3ratul163071 3mo ago
  didn't that end with the cgi era? what is going on :)

snikes 3mo ago

PgBouncer works great for us with Vercel Workers. We use ClickHouse PG behind it, and haven't had any issues so far :)

guillaume_code 3mo ago

The connection-pooler-as-bottleneck story is so common and so under-discussed, nice writeup. Did the 4x come mostly from the multi-process model, or did you also have to change how the app holds connections? The app-side pattern usually eats half the gains.