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

81–90 of 91 posts

Re: Scalable PostgreSQL Connection Pooler

#81

Post by funny_falcon »

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

It is being worked on: https://www.postgresql.org/message-id/flat/ac873432-31cf-d5e... !

But it is quite limited at the moment:

- connection is bound to backend process it were passed to, - backend process can perform only one transaction simultaneously, - therefore if backend process performs long transaction, all other connections on same backend process are waiting for.

Even pgbouncer in transaction mode performs better since it could switch incoming connections to different outgoing connections.

There will be no good builtin connection pooler until PostgreSQL adopts some kind of intra-process concurrency - threads or green-threads (preemptive coroutines). And I say, most likely it will never happen due to all extensions written for PostgreSQL.

Re: Scalable PostgreSQL Connection Pooler

#82

Post by 3np »

x4m wrote: post #70
3np wrote: post #69

Earlier quoted context omitted.

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.

Thanks for the pointers! I did play around a bit but it seems to segfault and I'm not sure how to proceed.

https://github.com/yandex/odyssey/issues/361

Re: Scalable PostgreSQL Connection Pooler

#83

Post by x4m »

ddorian43 wrote: post #79
x4m wrote: post #38

Earlier quoted context omitted.

We are also developing managed databases for US\EU market https://double.cloud/

Do you have a plan to support dedicated servers? Ie: I put ssh keys and you manage the cluster.

I'm not actually aware of all detailed plans. My job is to advance PostgreSQL ecosystem. But I'll pass a question to someone with broader view.

Re: Scalable PostgreSQL Connection Pooler

#85

Post by x4m »

jerrysievert wrote: post #45
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.

Any thoughts on an arm64 version?

Odyssey could work fine on arm, but we've a small ASM function https://github.com/yandex/odyssey/blob/master/third_party/ma... . We need to reimplement it for ARM64 or at least fallback on some generic context swap.

Re: Scalable PostgreSQL Connection Pooler

#86

Post by bradknowles »

hn_throwaway_99 wrote: post #21
riyadparvez wrote: post #13

Earlier quoted context omitted.

Can someone point to an article that explains the connection issue in detail?

Short of it is that Postgres uses a process per connection, so architectures that spin up and close connections frequently can have serious scalability issues. Note the landing page for the AWS RDS Proxy, https://aws.amazon.com/rds/proxy/ , is as good a discussion as any as to why you'd want to put a pooling proxy in front of Postgres.

So, we tried AWS RDS Proxy for MySQL (well, actually AWS RDS Aurora MySQL), and we found that it did not improve our situation at all. It added latency, but no additional performance, and memory usage was unchanged.

The applications already had their own connection pooling functionality built-in, so AWS RDS Proxy didn’t buy us anything more beyond that.

I can’t speak for what this technology can do for situations where your application code does not already have connection pooling, or for cases regarding RDS Postgres.

Re: Scalable PostgreSQL Connection Pooler

#87

Post by d0uble »

ddorian43 wrote: post #79
x4m wrote: post #38

Earlier quoted context omitted.

We are also developing managed databases for US\EU market https://double.cloud/

Do you have a plan to support dedicated servers? Ie: I put ssh keys and you manage the cluster.

That sounds like DBA as a Service. No, we don't plan to support that in the near future. One thing that is definitely on our roadmap and may be relevant is BYOA (bring your own account) when hosts of the cluster are running in your AWS/Azure/GCP account, not ours.

Re: Scalable PostgreSQL Connection Pooler

#88

Post by ddorian43 »

d0uble wrote: post #87
ddorian43 wrote: post #79

Earlier quoted context omitted.

Do you have a plan to support dedicated servers? Ie: I put ssh keys and you manage the cluster.

That sounds like DBA as a Service. No, we don't plan to support that in the near future. One thing that is definitely on our roadmap and may be relevant is BYOA (bring your own account) when hosts of the cluster are running in your AWS/Azure/GCP account, not ours.

It's the same thing but you don't have to pay 10x for your aws vps.

Re: Scalable PostgreSQL Connection Pooler

#89

Post by x4m »

3np wrote: post #82
x4m wrote: post #70

Earlier quoted context omitted.

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.

Thanks for the pointers! I did play around a bit but it seems to segfault and I'm not sure how to proceed. https://github.com/yandex/odyssey/issues/361

It seems like there's issue when you have a user for accessing authentication table you login with the same user. It fails when due to mixing same pool for auth and for work. I'll look into differentiating these pools more closely. As a simple workaround you can setup different user for accessing password and not login into Odyssey with this user.

Re: Scalable PostgreSQL Connection Pooler

#90

Post by x4m »

kerblang wrote: post #55
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?

Transaction pooling rules are equivalent in Odyssey and PgBouncer.
Post reply on HN