Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PgBouncer is useful, important, and fraught with peril

jpcamara.com

Post reply on HN
Print view View original

21–30 of 75 posts

Re: PgBouncer is useful, important, and fraught with peril

#21

Post by fabian2k »

sidcool wrote: post #20

If 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 you can't give generic answers and it depends a lot on your specific use case and what you do with that database.

Re: PgBouncer is useful, important, and fraught with peril

#22

Post by Deadron »

film42 wrote: post #19
brazzy wrote: post #12

Earlier quoted context omitted.

The 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…

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

Is this because your default approach is to horizontally scale or because you have tried other options? Logically scaling vertically a single server with pooled connections should have a better effect than horizontally scaling to multiple servers that need to share connections through a proxy. The proxy doesn't magically increase the number of connections available to the database. It will add overhead to all your database interactions though.

Re: PgBouncer is useful, important, and fraught with peril

#23

Post by ComputerGuru »

On-topic tangent: reminder or heads-up (depending on if you’ve already seen this) that Postgres is experimenting with thread-based instead of process-based connections (which would pretty much obviate the need for pgBouncer if it works out and becomes the connection model going forward).

HN discussion from a few months ago, with lots of commentary relevant to any pgBouncer scenarios: https://news.ycombinator.com/item?id=36393030

Re: PgBouncer is useful, important, and fraught with peril

#24

Post by adontz »

brazzy wrote: post #12
Deadron wrote: post #9

Earlier quoted context omitted.

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…

The 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…

If one has CPU load for 50 pods, then it is not a usual project I guess? Average load should be thousands of requests per second I believe.

So the question is how many applications have CPU load for 50 pods but also cannot saturate a single database, so sharding is not considered yet? Gut feeling is that they are few, more like exceptions.

From my personal experience pgBouncer is used a lot when framework/library does not support application level pooling in the first place, and not so much when it does.

Re: PgBouncer is useful, important, and fraught with peril

#25

Post by Deadron »

brazzy wrote: post #12
Deadron wrote: post #9

Earlier quoted context omitted.

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…

The 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…

Don't spin up 50 pods. You have outscaled your database. You can't make IO operations faster by throwing more workers at it and you can only have so many connections working at once. As a side note if your application is a typical IO bound web app its very unlikely you can process enough transactions to effectively use 50 workers in a single region.

Re: PgBouncer is useful, important, and fraught with peril

#26

Post by Deadron »

dharmab wrote: post #11
Deadron wrote: post #9

Earlier quoted context omitted.

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…

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 are already saturating the database, will slow down other operations overall.

Re: PgBouncer is useful, important, and fraught with peril

#27

Post by mrits »

dkhenry wrote: post #13

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

Re: PgBouncer is useful, important, and fraught with peril

#28

Post by Deadron »

dkhenry wrote: post #16
Deadron wrote: post #9

Earlier quoted context omitted.

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…

The constraint is less often the database in reality then in theory. Expecially when you consider many large scale applications are doing complex things. A normal request trace might only spend 30% or 40% of its time in the DB call. When you consider that a single postgres database can clear 200k QPS, you start to get to a world where you have thousands of hosts. If you tried to tune the in application connection poo…

Most large scale web applications spend their time reading and writing data, both to/from clients and to/from other remote services such as databases. You don't need thousands of hosts. Stackoverflow famously ran 9 server instances in 2016 with 7 dedicated to the primary sites.

Unlike postgres, Oracle and Sql server can support thousands of connections but they see performance degradation at a certain point. So I have never seen them crash from too many connections(Although they definitely get slower.).

Re: PgBouncer is useful, important, and fraught with peril

#30

Post by film42 »

Deadron wrote: post #22
film42 wrote: post #19

Earlier quoted context omitted.

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

Is this because your default approach is to horizontally scale or because you have tried other options? Logically scaling vertically a single server with pooled connections should have a better effect than horizontally scaling to multiple servers that need to share connections through a proxy. The proxy doesn't magically increase the number of connections available to the database. It will add overhead to all your da…

Vertically scaling has a cost component. If you're able to use pgbouncer with transaction pooling to better utilize but not exceed your existing vertical postgres limit, then you're set. If you do, then you must scale up. Pgbouncer can help you lengthen your runway. Tuning your pgbouncer pool sizes can help minimize the overhead but as with anything we do, it's about weighing trade-offs for your situation.
Post reply on HN