5 ms·
PgBouncer has always left me confused in the world of application level connection pooling. I have never quite understand the value of it if we are already usi
by Deadron 3y ago
PgBouncer has always left me confused in the world of application level connection pooling. I have never quite understand the value of it if we are already using connection pools in our applications. I don't want to pool connections to another pool.
- taspeotis 3y agoLambdas
- Deadron 3y agoIf you are running aws lambdas I believe you should be using the RDS proxy product(This is a very similar product though).
- mason55 3y agoSuper annoying that RDS Proxy doesn’t support IAM auth against the DB. We moved all our DB users to use IAM auth based on instance roles and then found out that RDS proxy doesn’t support it.
- mscrivo 3y agoApplication level connection pools are not enough if you're using something like k8s and have your "application" running across hundreds of pods, each with their own application level connection pool. pgBouncer helps tremendously in that situation because all those pods will use a single pool. We cut down avg open connections dramatically by doing that from over 1000 to less than 400.
- Deadron 3y agoThis still doesn't really make sense to me. You can't scale an application that relies on a database heavily to this level because your fundamental constraint IS the database. If you are already hitting your max number of connections with a small number of applications there are no benefits to further horizontal scaling. You are just passing the buck around because only a limited number can hit the database at any one time.
- dharmab 3y agoIf you limit yourself to a subset of Postgres' features, connections can become your bottleneck. I work with a production system where the major scaling constraints are 1) the VM capacity of the cloud region it runs in and 2) available connections to Postgres
- Deadron 3y agoTo be clear pgbouncer does not add connections to postgres or remove the connection bottleneck. Its still there under the covers. If you are saturating your connections it will not be able to improve on throughput. It sounds like you need a different architecture to allow for queueing work. The approach pgbouncer takes may actually reduce performance overall as it will intermix work on the pg instance which, if you are already saturating the database, will slow down other operations overall.
- dharmab 3y agoYup, one of the things we're doing is moving parts of the system away from Postgres into queues.
- brazzy 3y agoThe point is that if you have 50 k8s pods that each have their individual connection pool, some of them will be holding idle connections while others are hitting their max connection limit. A single pool is much more flexible. Additionally, the "transaction" mode of PgBouncer can increase the utilization of connections further by making them available to a different application when one application holds a connection while doing something different (e.g. waiting for a call to an external service).
- film42 3y agoThis. For the web service workloads most of us run, I’ve always run out of postgres connections before exceeding the vertical limit of a postgres server.
- __s 3y agoAlso IoT
- Deadron 3y agoI would hope you are not allowing IoT devices direct access to your database. There is no saving that haha.
- __s 3y agoUnfortunately some of my customers are. Hopefully they're setting up isolated roles which can only access stored procedures to log readings so that at worst they're opening themselves up to DDoS
- honkycat 3y agoit lets me create a load balancer in front of multiple database instances without having to do a bunch of application-level BS additionally with microservices, managing connection pooling can be difficult across legacy software, service versions, teams, etc. PGBouncer lets me have a front-end and manage that at an infra level.