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

61–70 of 75 posts

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

#61

Post by emilsedgh »

sidcool wrote: post #40
fabian2k wrote: post #21

Earlier quoted context omitted.

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.

I don't think 10K updates per second are really that difficult for the db (Unless you have locking issues).

But at that point your reads should be probably be sent to read-only replica's. So you write to a master but all your read-heavy apps and queries run against replicas.

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

#62

Post by [deleted] »

benzible wrote: post #54
benzible wrote: post #18

> There are more managed hosting options than ever (Crunchy Data, Render, Fly.io, and on and on) From fly.io's docs [1]: > This Is Not Managed Postgres [1] https://fly.io/docs/postgres/getting-started/what-you-should...

Why would someone downvote this? Seems pretty relevant.

[deleted]

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

#63

Post by perrygeo »

Deadron wrote: post #9
mscrivo wrote: post #5

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

That's a big assumption. If your app is just an HTTP wrapper for SQL queries against a poorly-optimized database, sure.

But there are plenty of applications that spend time the majority of their time doing other stuff (http requests, FFI calls, g/cpu-intensive processing, etc.) where the database interaction is a small part of the overall performance profile - certainly not the bottleneck, even with hundreds of concurrent clients.

In those cases, rather than artificially throttling the concurrency to stay within your global max_connections, you would ideally run as many as you can (ie fully utilize your infrastructure) and grab database connections from an external pool when you need them.

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

#64

Post by stubish »

merb wrote: post #56
oppositelock wrote: post #49

Earlier quoted context omitted.

I've been running pgBouncer in large production systems for years (~10k connections to pgbouncer per DB, and 200-500 active connections to postgres). We have so many connections because microservices breed like rabbits in spring once developers make the first one, but I could rant about that in a different post. We use transaction level sharing. Practically, this means we occasionally see problems when some per-conne…

> microservices breed like rabbits in spring once developers make the first one microservices talking to the same db... thats not microservices thats a disaster. you basically combine the negatives of the microservice world with the negatives of the monolith - tight coupling.

Databases are there to share data and provide transactional guarantees and even locking. Your data often must be tightly coupled like this, and most databases designed with this in mind and provide benefits when doing so. It doesn't mean your apps need to be, and there are still plenty of benefits in deployment and operations to be had with microservices. Silo the data when it makes sense, but force the issue you end up with a different problem trying to reimplement the benefits of a database in the app layer or with a fault tolerant, guaranteed delivery messaging system (itself a database under the hood).

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

#66

Post by jtc331 »

sidcool wrote: post #40
fabian2k wrote: post #21

Earlier quoted context omitted.

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.

Each of those reads may only be 1ms, which would translate into only 100 connections needed.

So the workload you describe — without more detail — may in fact be just fine with that connection count.

But plenty of people also run Postgres with well above 500 max connections.

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

#67

Post by jtc331 »

lib-dev wrote: post #31
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?

If you require 500 instances of your app to scale, how do you get away with just a single DB instance?

It all depends on your workload. Those 500 instances may be make calls to other services, services, for example, or burning CPU in various ways.

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

#68

Post by jtc331 »

jgalt212 wrote: post #2

> I also think community and industry enthusiasm around Postgres is at an all time high. There are more managed hosting options than ever (Crunchy Data, Render, Fly.io, and on and on), deep extensions like PostgresML, Citus and Timescale, serverless options like Neon, and real-time services like Supabase with Postgres at their center. Please sell me why you'd use one vendor to host your DB and another one to host you…

If deploying on different racks is that big of problem for your latency then you’re severely limited in how far you can scale your application just by physical rack space. IME this just isn’t true.

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

#69

Post by jtc331 »

jtc331 wrote: post #68
jgalt212 wrote: post #2

> I also think community and industry enthusiasm around Postgres is at an all time high. There are more managed hosting options than ever (Crunchy Data, Render, Fly.io, and on and on), deep extensions like PostgresML, Citus and Timescale, serverless options like Neon, and real-time services like Supabase with Postgres at their center. Please sell me why you'd use one vendor to host your DB and another one to host you…

If deploying on different racks is that big of problem for your latency then you’re severely limited in how far you can scale your application just by physical rack space. IME this just isn’t true.

And even if in different data centers (say different AZs), query latency is probably 1ms; in practice that’s quite low. How many queries are you doing in a request?

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

#70

Post by physicles »

My understanding now is that using pgBouncer falls under the category of YAGNI / “the best part is no part”: don’t use it unless you have a demonstrated need for it, because it adds complexity.

About 5 years ago we migrated to postgres and installed pgBouncer because of course you do. Sometime last year I started seeing some weird issues like the article points out, and the more I looked into the gory details, the more shocked I became. (The fact that statement level isolation even exists is… wow)

So I did an experiment and deleted it. Things kept working just fine. We’re not living in a microservices world so application-level pooling is enough — I don’t think I’ve ever seen us use half of available connections.

Post reply on HN