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

31–40 of 91 posts

Re: Scalable PostgreSQL Connection Pooler

#31

Post by x4m »

whitepoplar wrote: post #30
x4m wrote: post #25

Earlier quoted context omitted.

Connection pooler allows you to get more transactions per second on the same hardware. All it does - pack a lot of connections into handful of concurrently running PG backends. In theory all it does - relay bytes between sockets. But there are workloads when it's CPU intensive, for example lots of concurrent TLS handshakes. And pgbouncer is single-threaded! Odyssey is more scalable. PgBouncer is a great software, act…

How does Odyssey compare to other high-performance poolers like pgagroal?

Depends on how you measure performance :) pgagroal utilizes a lot of linux processes to track each connection state, we use coroutines packed into small number of processes. Actually if you have a bottleneck in multithreaded pooler - something really goes wrong. The database should consume most of resources. I'd be happy to help tuning Odyssey to someone who will bachmark both poolers (in fact there's only one number - number of worker processes..well, maybe pool_size too).

pgagroal claims performance superiority over all poolers [0]. I doubt that Odyssey was used in transaction pooling mode in those experiments.

[0] https://github.com/agroal/pgagroal/blob/master/doc/PERFORMAN...

Re: Scalable PostgreSQL Connection Pooler

#32

Post by SOLAR_FIELDS »

sudhirj wrote: post #12

For those wondering why use a connection pooler at all with Postgres, I wrote a (really long) post about it: https://sudhir.io/understanding-connections-pools/

This is a great write up that really helped me understand the issue in depth. Thanks for sharing.

Re: Scalable PostgreSQL Connection Pooler

#33

Post by x4m »

sandGorgon wrote: post #18

is the Dockerfile not production ready ? https://github.com/yandex/odyssey/blob/master/docker/Dockerf... i mean i see valgrind, vim, etc in there. this would be a very fat dockerfile. It seems that way - https://github.com/yandex/odyssey/issues/29#issuecomment-764... > But it's hard to "bless" some "official" as image: no one from active contributors uses Odyssey in Docker to maintain it thoroughly. OTOH pgbouncer do…

Yes, that's a problem. We use Docker only for developement. And mostly because Odyssey is based on epoll and just will not compile on MacOS.

Re: Scalable PostgreSQL Connection Pooler

#34

Post by whitepoplar »

x4m wrote: post #31
whitepoplar wrote: post #30

Earlier quoted context omitted.

How does Odyssey compare to other high-performance poolers like pgagroal?

Depends on how you measure performance :) pgagroal utilizes a lot of linux processes to track each connection state, we use coroutines packed into small number of processes. Actually if you have a bottleneck in multithreaded pooler - something really goes wrong. The database should consume most of resources. I'd be happy to help tuning Odyssey to someone who will bachmark both poolers (in fact there's only one number…

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

Re: Scalable PostgreSQL Connection Pooler

#35

Post by x4m »

whitepoplar wrote: post #34
x4m wrote: post #31

Earlier quoted context omitted.

Depends on how you measure performance :) pgagroal utilizes a lot of linux processes to track each connection state, we use coroutines packed into small number of processes. Actually if you have a bottleneck in multithreaded pooler - something really goes wrong. The database should consume most of resources. I'd be happy to help tuning Odyssey to someone who will bachmark both poolers (in fact there's only one number…

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.

Re: Scalable PostgreSQL Connection Pooler

#36

Post by ggregoire »

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)

Re: Scalable PostgreSQL Connection Pooler

#37

Post by speedyapoc »

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)

I run pgBouncer on an EC2 instance, connecting to my database on RDS. Does not have to be on the same box.

Re: Scalable PostgreSQL Connection Pooler

#38

Post by x4m »

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/

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

Re: Scalable PostgreSQL Connection Pooler

#39

Post by ggregoire »

speedyapoc wrote: post #37
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)

I run pgBouncer on an EC2 instance, connecting to my database on RDS. Does not have to be on the same box.

Oh nice. Thanks for the quick answer!

Re: Scalable PostgreSQL Connection Pooler

#40

Post by sandGorgon »

x4m wrote: post #33
sandGorgon wrote: post #18

is the Dockerfile not production ready ? https://github.com/yandex/odyssey/blob/master/docker/Dockerf... i mean i see valgrind, vim, etc in there. this would be a very fat dockerfile. It seems that way - https://github.com/yandex/odyssey/issues/29#issuecomment-764... > But it's hard to "bless" some "official" as image: no one from active contributors uses Odyssey in Docker to maintain it thoroughly. OTOH pgbouncer do…

Yes, that's a problem. We use Docker only for developement. And mostly because Odyssey is based on epoll and just will not compile on MacOS.

so im not sure what you meant by that - because MacOS compatibility is generally unrelated to production (which is almost always on Linux).

Am I understanding this wrong ? do you have production workloads that use Odyssey on MacOS ?

Post reply on HN