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

41–50 of 75 posts

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

#41

Post by dkhenry »

Deadron wrote: post #28
dkhenry wrote: post #16

Earlier quoted context omitted.

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

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

#42

Post by mason55 »

Deadron wrote: post #10
taspeotis wrote: post #4

Earlier quoted context omitted.

Lambdas

If you are running aws lambdas I believe you should be using the RDS proxy product(This is a very similar product though).

Super annoying that RDS Proxy doesn’t support IAM auth against the DB.

We moved all our DB users to use IAM auth based on instance roles and then found out that RDS proxy doesn’t support it.

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

#43

Post by dkhenry »

mrits wrote: post #27
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.

What about running PHP, Django, Node, or Ruby based applications ? They all do connection per request so the pooler on host gives you an immediate improvement to connection latency.

Even if you are using a language with a built in connection pooler like Go or Java being able to manage your connections external to your application is enough of a benefit to keep the pooler separate.

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

#44

Post by severino »

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…

In those cases, does one keep the application-level pool at each pod, in addition to the "communal" PgBouncer pool, or does not offer any advantage?

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

#45

Post by brazzy »

Deadron wrote: post #25
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…

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.

We're not necessarily talking about 50 pods for the same application, could also be a zoo of smaller low traffic applications sharing a Postgres instance.

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

#46

Post by __s »

Deadron wrote: post #39
__s wrote: post #33

Earlier quoted context omitted.

Also IoT

I would hope you are not allowing IoT devices direct access to your database. There is no saving that haha.

Unfortunately some of my customers are. Hopefully they're setting up isolated roles which can only access stored procedures to log readings so that at worst they're opening themselves up to DDoS

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

#47

Post by kerblang »

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?

I think you would look at

1. "Server level connection pooling" in the article (all clients share one pgbouncer instance) and

2. "Statement mode" if you can live without transactions (connection is re-pooled after every statement) otherwise "Transaction Mode" (connection is repooled upon transaction commit or rollback).

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

#48

Post by cryvate1284 »

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)

Django does this too

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

#49

Post by oppositelock »

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-connection state "leaks" from one client to another when someone issues a SQL statement that affects global connection state, and it affects the query of a subsequent client inheriting that state. It's annoying to track down, but given the understanding of behavior, developers generally know how to limit their queries at this point. Some queries aren't appropriate for going through pgbouncer, like cursor based queries, so we just connect directly to the DB for the rare cases where this is needed.

Why so many connections? Say you make a Go based service, which launches one goroutine per request, and your API handlers talk to the DB - the way the sql.dB connection pooling works in Go is that it'll grow its own pool to be large enough to satisfy the working parallelism, and it doesn't yield them for a while. Similar things happen in Java, Scala, etc, and with dozens of services replicated across multiple failure domains, you get a lot of connections.

It's a great tool. It allows you to provision smaller databases and save cost, at the cost of some complexity.

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

#50

Post by timacles »

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.
Post reply on HN