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

Scalable PostgreSQL Connection Pooler

github.com

Post reply on HN
Print view View original

51–60 of 91 posts

Re: Scalable PostgreSQL Connection Pooler

#51

Post by tsenart »

x4m wrote: post #35
whitepoplar wrote: post #34

Earlier quoted context omitted.

Thanks! For a Postgres n00b, can Odyssey work alongside a HA solution like pg_auto_failover running on the same nodes?

Sure. We use Odyssey as a component of HA cluster in Yandex.Cloud. Odyssey runs on each node of a Patroni-like system. One day we want to implement load balancing functionality in Odyssey. So that your queries go to other node if local is overloaded or lagging long behind primary.

This, please! Native support for read-replicas would be awesome. Ideally it would now if a query is read-only or not without application changes.

Re: Scalable PostgreSQL Connection Pooler

#52

Post by anarazel »

saurik wrote: post #46
anarazel wrote: post #7

Earlier quoted context omitted.

There has been some work towards that - things did get cheaper in 14. Note that you very well might still want a separately run pooler for some workloads - having local poolers on application servers can be good for latency.

Yeah: the latency of establishing a new connection fundamentally requires at least one network round trip, and likely a few (and so I tend to have my servers going through two layers of pgbouncer, one on each side of the network); and using in-process pools couples your pool state to your front end code updates... so, since you are going to want to build this connection pooling mechanism anyway--and then are likely g…

> it doesn't really make much sense to prioritize this inside the database server itself.

I wouldn't go that far. There's a lot of things that are hard to do within an external pooler, unless the pooler uses a 1:1 connection model. Leading to most setups using 1:1 connection pooling methods. Which in turn puts pressure on postgres to handle large numbers of connections gracefully.

Re: Scalable PostgreSQL Connection Pooler

#53

Post by gfody »

x4m wrote: post #14
Tostino wrote: post #3

I appreciate the work going on to make high quality poolers for Postgres...I just really wish the work was done in-core so we could have built in connection pooling, or the work done to make grabbing a new connection as cheap as if you were using a pooler. It sucks to have to add complexity to your stack to fix a "deficiency" in Postgres' design. Still, I am glad there is effort put into project even if I selfishly w…

There's ongoing work on making Postgres connections more scalable. I really hope that one day we will not need a pooler. https://www.citusdata.com/blog/2020/10/08/analyzing-connecti...

pooling at all is still pretty much a hack. ideally resource management (and tuning) wouldn't revolve around the connection and actual connections would become as thin as possible

Re: Scalable PostgreSQL Connection Pooler

#54

Post by welder »

r1b wrote: post #22

Why might I want to use this or pgbouncer instead of, say, the connection pooling built into ORMs like SQLAlchemy?

Because the way you scale up a Python web app is by adding more servers, and ORMs like SQLAlchemy can't share(aka pool) connections across processes let alone servers. Scaling a web app means thousands of open connections to Postgres, and the only way to fix that is server side connection pooling. (Server as in Postgres, because here your app server is the client)

Re: Scalable PostgreSQL Connection Pooler

#55

Post by kerblang »

x4m wrote: post #11

FWIW I maintain Odyssey and will be happy to answer any question regarding it. Actually there was just a CVE-triggered release... I really hope to release more often with more new functionality. If you are interested in connection pooling maybe you will find interesing SPQR too https://github.com/pg-sharding/spqr Currently it's our experiment to build pooling-based PostgreSQL sharding.

Maybe a dumb question but does it lock the postgres connection down to one pool client (no sharing) when the client has a transaction in progress? How does it compare to pgbouncer in this regard?

Re: Scalable PostgreSQL Connection Pooler

#56

Post by phamilton »

ggregoire wrote: post #36

Just to be sure, Odyssey (or pgBouncer) requires to run on the same box than Postgres, right? So if you are on AWS, you would have to migrate from RDS to running Postgres on a EC2 and install the pooler on the same EC2? Which comes with some big drawbacks (you obviously lose all the benefits of using RDS) but also some benefits (can install every pg extension that you need, have access to every pg settings, etc)

We've run in both of these configurations connecting to Aurora:

1) Run a pgbouncer ASG with a load balancer in front for applications to connect to. 2) Run pgbouncer on all our hosts and application connect to localhost.

(2) was significantly cheaper once we switched over. All our hosts are 24xlarge so there was plenty of application traffic to make pgbouncer meaningful on the same host.

Re: Scalable PostgreSQL Connection Pooler

#57

Post by whitepoplar »

anarazel wrote: post #52
saurik wrote: post #46

Earlier quoted context omitted.

Yeah: the latency of establishing a new connection fundamentally requires at least one network round trip, and likely a few (and so I tend to have my servers going through two layers of pgbouncer, one on each side of the network); and using in-process pools couples your pool state to your front end code updates... so, since you are going to want to build this connection pooling mechanism anyway--and then are likely g…

> it doesn't really make much sense to prioritize this inside the database server itself. I wouldn't go that far. There's a lot of things that are hard to do within an external pooler, unless the pooler uses a 1:1 connection model. Leading to most setups using 1:1 connection pooling methods. Which in turn puts pressure on postgres to handle large numbers of connections gracefully.

Would an app-side ORM that has built-in connection pooling + PG14 (w/ connection scalability improvements) generally benefit from a connection pooler like Odyssey/PgBouncer, either server-side or app-side, when the number of app servers is low (<5-10)?

Re: Scalable PostgreSQL Connection Pooler

#58

Post by sojournerc »

I lean on client-side pooling these days. It works and is easier to reason about for me, e.g. this client process gets 2 connections. Specifically knex has client pooling.

Provides good back pressure and hasn't fallen down yet. Any reason not to do it this way?

Re: Scalable PostgreSQL Connection Pooler

#59

Post by manquer »

sojournerc wrote: post #58

I lean on client-side pooling these days. It works and is easier to reason about for me, e.g. this client process gets 2 connections. Specifically knex has client pooling. Provides good back pressure and hasn't fallen down yet. Any reason not to do it this way?

If you have lot of client sources (say in each micro service) different languages and frameworks connecting to same server ( perhaps different db/schema ).

It is easier then to manage it in one place . Also dynamic scaling of server pools etc you need change the pooler config instead of every application.

And postgres connection management(open/close) is expensive, if you can handle it outside the the db infra it can be beneficial ( only some client libs will pool their connections and others won't). Even with client pooling support in the library, each container/forked runner will have its own connection, and that can be quickly few hundreds.

Finally AFAIK most libraries don't handle master failover or server restart without crashing the connection, pooler will usually keep the upstream open for some time.

Post reply on HN