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

51–60 of 75 posts

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

#51

Post by stickfigure »

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…

If you have 10 application instances each with a pool of 20 connections (half of which are idle), you have 100 active connections and 100 idle connections.

If you have a single "real" connection pool, the idle pool is effectively shared among all application instances. You can have 100 active connections and 10 (or maybe 20) idle.

I have run into this problem, but I solved it with careful tuning of automatic instance scaling parameters, connection pool size and timeout. But this only gets you so far; a single real connection pool would be more effective (at the cost of added complexity).

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

#52

Post by skybrian »

timacles wrote: post #50
ComputerGuru wrote: post #23

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…

I follow the PG world very closely and would like to add that Postgres community essentially "soft" turned that down and its not going to be a thing any time soon, like in the next five years at least.

I’m wondering if they’ve considered less radical solutions? Something like adding a new thread-based front end to Postgres itself while keeping the processes, and then gradually fixing whatever makes that less than seamless.

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

#53

Post by honkycat »

Deadron wrote: post #3

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

it lets me create a load balancer in front of multiple database instances without having to do a bunch of application-level BS

additionally with microservices, managing connection pooling can be difficult across legacy software, service versions, teams, etc.

PGBouncer lets me have a front-end and manage that at an infra level.

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

#54

Post by benzible »

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.

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

#55

Post by JelteF »

Pgbouncer maintainer here. Overall I think this is a great description of the tradeoffs that PgBouncer brings and how to work around/manage them. I'm actively working on fixing quite a few of the issues in this blog though

1. Named protocol-level prepared statements in transaction mode has a PR that's pretty close to being merged: https://github.com/pgbouncer/pgbouncer/pull/845

2. SET/RESET tracking in transaction mode. For this we need some changes in the postgres protocol to ask for postgres to tell pgbouncer about setting updates. This is being worked on here: https://www.postgresql.org/message-id/flat/CAGECzQQOOhH1Rztu...

3. The single threading issue can be worked around by using multiple processes that listen on the same port using so_reuseport=1 https://www.pgbouncer.org/config.html#so_reuseport

4. The pg_dump issue can actually be solved already by installing the Citus extension and using track_extra_parameters (otherwise you have to wait for the same postgres protocol addition that's needed for the other SET/RESET commands) https://www.pgbouncer.org/config.html#track_extra_parameters

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

#56

Post by merb »

oppositelock wrote: post #49
mickeyp wrote: post #6

I mean... if you multiplex disparate statements into the same connection and session then, well... yes, that is fraught with an incredible amount of complexity. That stuff's for OLAP, read-only replica servers and so on. High-throughput "hey I just need this one thing." Your large-scale app probably won't need that by default. You pool connections in pgBouncer (or your application) because they're slow and expensive…

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.

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

#57

Post by samokhvalov »

mst wrote: post #17

> Postgres doesn’t have a concept of nested transactions It has savepoints and those nest fine. (perl's DBIx::Class can be configured to automatically convert transactions into savepoints if it's already inside another transaction; presumably any other ORM-like thing could also do that for you in theory but whether the one you're currently using -does- is left as an exercise to the reader)

beware of the issues related to postgres subtransactions https://postgres.ai/blog/20210831-postgresql-subtransactions...

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

#58

Post by raspasov »

dkhenry wrote: post #41
Deadron wrote: post #28

Earlier quoted context omitted.

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

Stackoverflow is very much the exception not the rule. Most of your top tier software companies have server fleets that scale well past the 10,000's of nodes level, and for container based workloads I don't think its uncommon to have even medium sized companies running 100k+ containers.

Tldr; aim to be the exception!

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

#60

Post by bilekas »

mickeyp wrote: post #6

I mean... if you multiplex disparate statements into the same connection and session then, well... yes, that is fraught with an incredible amount of complexity. That stuff's for OLAP, read-only replica servers and so on. High-throughput "hey I just need this one thing." Your large-scale app probably won't need that by default. You pool connections in pgBouncer (or your application) because they're slow and expensive…

> If you're running into "connection limits" on Heroku or whatever... maybe now is the time to stop letting other people manage your DB and just run it yourself? You're clearly big enough to run up against that.

This is top advice actually. I would usually always suggest this! I've felt a lot other services too that can run better on the local/server deployment.

Post reply on HN