We scaled PgBouncer to 4x throughput

odie5533

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

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

jauntywundrkind

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

nzeid

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

x4m

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

JustSkyfall

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

DylanSp

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?

bradfitz

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?

ezekiel68

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

desert3agle

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

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