Pgbouncer scaling
I am using pgbouncer to manage the connection for my postgresql instance. I wanted to know how can I scale pgbouncer with respect to the number of users. As the users increase the connection to the database will also increase which will reach to a limit. How can I make sure that this never happens.
8 Replies
variable-lime•4mo ago
Can you confirm for me, by "number of users", do you mean end users of your application, or postgres roles?
quickest-silver•4mo ago
end users
variable-lime•4mo ago
You can limit how many connections pgbouncer will make to your database, and also run multiple instances of pgbouncer. Generally you will also have a connection cache on your application server, and never open too many connections from your application as well.
Are you running your application on a serverless platform? That's generally when you need to start thinking about pgbouncer scaling issues
quickest-silver•4mo ago
I am running on a EC2 instance.
not a serverless for now though. Might switch there.
AAactually the thing is postgresql tend to give errors regarding the connections. I wanted to know how I can make sure that it never reaches that limit or has infinite connection limit.
variable-lime•4mo ago
Generally I think there's no pgbouncer limitation if you are running your apps on ec2. If you don't run many instances of your app, you're not going to have thousands of open connections to the database. The limitation is how fast postgres itself can process the transactions as more users means more queries per second.
Usually I'd recommend skipping pgbouncer if you can. Many postgres drivers support connection pooling - in non-serverless workloads this is efficient as the pool cache can stay warm. You do just have to be mindful with how many instances you run vs how large your postgres database is. Postgres has a max connections setting that you can tweak accordingly
A medium postgres instance could handle a few hundred connections. You should allocate some spare for administrative purposes, but if you run 10 instances of your app and each instance has a limit of 20 postgres connections, then that's 200 connections to postgres
quickest-silver•4mo ago
You can delete this file
variable-lime•4mo ago
I got it. Not sure what that's meant to be :ferrisThink:
quickest-silver•4mo ago
Ditto