News for hackers. Stuff that matters.
Re:Scalable PostgreSQL Connection Pooler by hn_throwaway_99 on Friday November 12, 2021 @12:58PM (#29202416)
Thanks very much for posting this. Odyssey is analogous to pgbouncer, correct? I haven't set up a connection pooler yet for our postgres instance but was planning to do it eventually with pgbouncer - are you able to comment how Odyssey compares?
Re:Scalable PostgreSQL Connection Pooler by x4m on Friday November 12, 2021 @01:20PM (#29202722)
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, actually. We built Odyssey merely to create competitor for PgBouncer. But then started to add functionality that we needed for cloud installations. E.g. Odyssey computes transaction time quantiles, when PgBouncer computes average transaction time.

Re:Scalable PostgreSQL Connection Pooler by whitepoplar on Friday November 12, 2021 @01:38PM (#29202941)
How does Odyssey compare to other high-performance poolers like pgagroal?
Re:Scalable PostgreSQL Connection Pooler by x4m on Friday November 12, 2021 @01:49PM (#29203068)
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 by whitepoplar on Friday November 12, 2021 @01:53PM (#29203121)
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 by x4m on Friday November 12, 2021 @01:57PM (#29203160)
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 by tsenart on Friday November 12, 2021 @05:36PM (#29205334)
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 by jpgvm on Friday November 12, 2021 @08:36PM (#29206523)
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 by x4m on Saturday November 13, 2021 @12:28AM (#29207663)
There's already working part of libpq protocol for this - target_session_attrs. But the problem with target_session_attrs is that it just takes too long to discover new primary after failolver. We want to fix this within Odyssey.