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

61–70 of 91 posts

Re: Scalable PostgreSQL Connection Pooler

#61

Post by remram »

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…

Applications shouldn't be connecting once warm though.

Re: Scalable PostgreSQL Connection Pooler

#62

Post by jpgvm »

tsenart wrote: post #51
x4m wrote: post #35

Earlier quoted context omitted.

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.

For a variety of reasons this is incredibly difficult. Functions, etc make SELECT queries writes, not just UPDATE/DELETE, etc.

It's a lot easier for your application to know what a write is and just establish connections to 2 separate poolers (or hosts on the same poolers) and direct the reads/writes appropriately.

Re: Scalable PostgreSQL Connection Pooler

#63

Post by anarazel »

whitepoplar wrote: post #57
anarazel wrote: post #52

Earlier quoted context omitted.

> 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)?

Unless the app servers are particularly large, or the app-side ORM connection pool doesn't work well (e.g. because the number of concurrent requests changes sufficiently over time that thread/process count constantly changes), I'd guess not. But without knowing a bit more it's hard to say.

Re: Scalable PostgreSQL Connection Pooler

#64

Post by nh2 »

djbusby wrote: post #48
PhoenixReborn wrote: post #26

Earlier quoted context omitted.

The ORM connection pooler is client-side - so each instance of your application using SQLAlchemy will have its own connection pool. This may become unwieldy as you scale up the number of app servers/containers/processes. In contrast, connection poolers like Odyssey or pgbouncer live external to your applications, meaning that: 1. You can connect to them from any application written in any language (not just Python) 2…

Why not python? Works on my machine.

I believe you read "just not Python", when the parent poster wrote "not just Python", meaning that a connection pooler can be used also from other languages besides Python, which don't have SQLAlchemy specifically.

Re: Scalable PostgreSQL Connection Pooler

#65

Post by hodgesrm »

zaius wrote: post #9
colesantiago wrote: post #4

Has anyone used Yandex Cloud [0] in production?, How does it compare to DigitalOcean, AWS, GCP and others? Also the tech from Yandex such as Clickhouse [1] is really interesting, as they recently spun it out of Yandex. [0] https://cloud.yandex.com/en/ [1] https://clickhouse.com/

Last time I tried they wouldn’t take a US credit card, which made it too big of a challenge to consider

Altinity.Cloud accepts credit card payments.

Disclaimer: I work at Altinity.

Re: Scalable PostgreSQL Connection Pooler

#67

Post by djbusby »

nh2 wrote: post #64
djbusby wrote: post #48

Earlier quoted context omitted.

Why not python? Works on my machine.

I believe you read "just not Python", when the parent poster wrote "not just Python", meaning that a connection pooler can be used also from other languages besides Python, which don't have SQLAlchemy specifically.

Oof. Yep. And the timer is over so I can't edit or delete :(

Re: Scalable PostgreSQL Connection Pooler

#68

Post by whitepoplar »

anarazel wrote: post #63
whitepoplar wrote: post #57

Earlier quoted context omitted.

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)?

Unless the app servers are particularly large, or the app-side ORM connection pool doesn't work well (e.g. because the number of concurrent requests changes sufficiently over time that thread/process count constantly changes), I'd guess not. But without knowing a bit more it's hard to say.

Thanks!

Re: Scalable PostgreSQL Connection Pooler

#69

Post by 3np »

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.

Some questions on auth. We have one cluster with a large number of databases, all with automatically provisioned and rotating credentials for service roles using postgres password auth. Currently, clients connect directly to the db cluster (there's a tcp lb but no pgbouncer etc)

In this case, I'd like for services to continue authenticating as previously without changing authentication method, just switching address.

Should I use password_passthrough/auth_query? Does odyssey need pg credentials itself? Is storage_db referring to the db where odyssey stores internal data for itself...?

I guess I don't get why a connection pooler would need to handle auth, much like an HTTPS proxy doesn't need API tokens itself.

Re: Scalable PostgreSQL Connection Pooler

#70

Post by x4m »

3np wrote: post #69
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.

Some questions on auth. We have one cluster with a large number of databases, all with automatically provisioned and rotating credentials for service roles using postgres password auth. Currently, clients connect directly to the db cluster (there's a tcp lb but no pgbouncer etc) In this case, I'd like for services to continue authenticating as previously without changing authentication method, just switching address.…

Currently in Odyssey password_passthrough works only for cleartext auth, PAM and LDAP. Probably, we could make this for MD5 work too. And it's the whole purpose of SCRAM to defeat this feature :)

The other way to do so is auth_query - you provide a storage password to access auth data of the DB. This works for MD5 auth. When user wants to authenticate we just check credentials against what we see in the database.

Post reply on HN