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

71–75 of 75 posts

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

#71

Post by fuy »

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.

they can have their own databases, but still be on the same Postgres instance (aka cluster in Postgres parlance).

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

#72

Post by mst »

samokhvalov wrote: post #57
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...

Solid list of ways to shoot yourself in the foot with it, even if "use savepoints in moderation" is, for a suitable value of "moderation", sufficient to largely avoid them. (though if you're going to use them, read the article and maybe some of the things it links anyway to calibrate your sense of suitable)

And that article also lists a bunch of ORMs that handle subtransactions out of the box, for anybody curious.

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

#73

Post by jgalt212 »

jtc331 wrote: post #69
jtc331 wrote: post #68

Earlier quoted context omitted.

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?

> query latency is probably 1ms

> This generally produces single digit millisecond roundtrip latency between AZs in the same Region.

https://aws.amazon.com/blogs/architecture/improving-performa...

So we're both wrong.

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

#74

Post by ris »

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

Crunchydata deploys in AWS, GCP or Azure if I recall correctly.

Ditto aiven.

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

#75

Post by sgarland »

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…

> maybe now is the time to stop letting other people manage your DB and just run it yourself

> With 100 connections and a p99 request-response cycle with DB hits being You would be shocked and appalled at how little many devs know about DBs, or how long something _should_ take. I've had to explain that triple-digit msec latency on a simple SELECT is in fact not normal, and they should fix it. And that's just using it, not running it. There are a massive number of orgs that don't want to have to ever think about things like backup strategies or patching, and so they happily fork money over to a cloud provider to do it for them.

Post reply on HN