Every single connection to Postgres is a new process, which requires a fork and new memory allocation (at least 10MB plus whatever you need for your query).
PgBouncer opens a pool of connections and then reuses them each time a client asks for a connection. This reduces latency (no more fork) and overhead (reuse memory).
The issue is language ecosystems that don't use client side connection pooling because they're single threaded (node, Python). So scaling up the number of web server threads means scaling the number of Postgres processes, which are expensive.
Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.
120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.
I’m not claiming it’s not fine, but it is a surprising consideration for a relatively small deployment. You have to start planning around Postgres’ architecture for anything larger, hence the solution in PgBouncer.
How many running instances do you need though? e.g. Scala web frameworks should be able to do thousands of RPS on a single core without the application developer really trying to optimize anything, and I always hear that even Ruby, Python, etc. are also fast enough to be IO bound so you should just need 2 copies for redundancy, right? Then give each like 8-16 connections.
That's fine but doesn't address the issue that PgBouncer does. Your application connection pool can multiplex all the connections needed in one application. PgBouncer can multiplex the connections across all applications (whether different apps or many instances of the same app).
If you handle database requests naively, every request to the database may open its own connection. This is a super simple approach but will exceed the amount of connections the database can or wants to handle concurrently.
One solution is to increase your app complexity and introduce a layer that manages connection pooling or queuing.
Or you can just keep your app naive and simple and put pgbouncer transparently in front of your DB. Even for multiple apps, so instead of every app increasing in complexity, reimplementing connection handling, you just have pgbouncer.
Comments
Yes. I've never even heard of PgBouncer.
Me too. I even opened the page and I'm not sure of what's the problem being solved.
Every single connection to Postgres is a new process, which requires a fork and new memory allocation (at least 10MB plus whatever you need for your query).
PgBouncer opens a pool of connections and then reuses them each time a client asks for a connection. This reduces latency (no more fork) and overhead (reuse memory).
If it's designed well, your application also opens a pool of connections and re-uses them each time the code needs a DB connection.
The issue is language ecosystems that don't use client side connection pooling because they're single threaded (node, Python). So scaling up the number of web server threads means scaling the number of Postgres processes, which are expensive.
Python has threads and connection pools work fine on async workers as well.
Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.
120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.
I’m not claiming it’s not fine, but it is a surprising consideration for a relatively small deployment. You have to start planning around Postgres’ architecture for anything larger, hence the solution in PgBouncer.
This is more app instances than 99% of deployments. Most apps and websites run a single server.
This solves the latency but not the overhead--you'll end up with a full pool of connections for each running instance of your application.
How many running instances do you need though? e.g. Scala web frameworks should be able to do thousands of RPS on a single core without the application developer really trying to optimize anything, and I always hear that even Ruby, Python, etc. are also fast enough to be IO bound so you should just need 2 copies for redundancy, right? Then give each like 8-16 connections.
If I'm implementing my own connection pool, I'll certainly make the number of connections configurable.
That's fine but doesn't address the issue that PgBouncer does. Your application connection pool can multiplex all the connections needed in one application. PgBouncer can multiplex the connections across all applications (whether different apps or many instances of the same app).
If you handle database requests naively, every request to the database may open its own connection. This is a super simple approach but will exceed the amount of connections the database can or wants to handle concurrently.
One solution is to increase your app complexity and introduce a layer that manages connection pooling or queuing.
Or you can just keep your app naive and simple and put pgbouncer transparently in front of your DB. Even for multiple apps, so instead of every app increasing in complexity, reimplementing connection handling, you just have pgbouncer.
This is the first time I've heard pgbouncer mentioned in 15 years. I didn't know it was still around.
PgBouncer? Don't even know er.
Oh my goodness lol