If a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?
If you require 500 instances of your app to scale, how do you get away with just a single DB instance?
PgBouncer is useful, important, and fraught with peril
31–40 of 75 posts
Re: PgBouncer is useful, important, and fraught with peril
#32This might be the best explanation I have seen of the benefits and pitfalls of deploying pgbouncer ( or any connection pooler as they are all similar ). The only thing I would add is from a deployment perspective, you should run two layers of pgbouncer. One that runs on the same host as your application ( sidecar container in k8s ), and one global layer that all connections to your DB must pass through. Every large s…
My large scale application certainly didn't run the pooler on the host. I don't see any reason to do this except for not wanting to become familiar with postgres itself.
I ran the Pooler on the host.. I ran a few hundred hosts, so having a central pool was not ideal.
Re: PgBouncer is useful, important, and fraught with peril
#33PgBouncer 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.
Application 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.
Also IoT
Re: PgBouncer is useful, important, and fraught with peril
#34Earlier quoted context omitted.
If 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
To 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 a…
Yup, one of the things we're doing is moving parts of the system away from Postgres into queues.
Re: PgBouncer is useful, important, and fraught with peril
#35Earlier quoted context omitted.
Application 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.
This 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 on…
> You can't scale an application that relies on a database heavily to this level because your fundamental constraint IS the database.
I pool ~10.000 connections down to under 500. I can't do application level pooling because the application is just thousands of individual processes, you often see this with Python, PHP or NodeJS applications.
500 open connections is way less overhead than 10.000 on the Postgres server. I'm very happy to "pass the buck" of connection pooling to separate machines with pgBouncer.
Re: PgBouncer is useful, important, and fraught with peril
#36If a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?
If you require 500 instances of your app to scale, how do you get away with just a single DB instance?
By having relatively simple queries. You can push 50k queries per second on a 64 CPU Postgres machine.
This is real world experience from a moderately complex application.
Re: PgBouncer is useful, important, and fraught with peril
#37Earlier quoted context omitted.
Application 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.
This 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 on…
I think it's an issue with a microservice setup. TFA suggests ~300 connections being optimal for Postgres and I've seen microservice setups which have more than 300 processes, which means even limiting each process to a single DB connection might not be enough.
But yeah, for less distributed applications, just have N worker threads and don't close the DB connection after each job.
Re: PgBouncer is useful, important, and fraught with peril
#38If a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?
If you require 500 instances of your app to scale, how do you get away with just a single DB instance?
simple queries, grouping writes, minimizing updates, caching, connection pooling
Re: PgBouncer is useful, important, and fraught with peril
#39Earlier quoted context omitted.
Application 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.
Also IoT
I would hope you are not allowing IoT devices direct access to your database. There is no saving that haha.
Re: PgBouncer is useful, important, and fraught with peril
#40If a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?
What exactly do you want to scale? But in the end the answer is likely "connection pooling", either integrated in the application or with a dedicated pooler like PgBouncer which this article is about. For Postgres a good way to scale is to just use a bigger server. You can get a lot of very fast storage and lots of CPU cores inside a single server today. And if that isn't enough you're far, far into territory where y…
I mean let's say I have to support 10000 updates per second and 100000 reads per second. Surely 500 connection pool won't suffice.