5 ms·

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

by x4m 5y ago

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 https://github.com/pg-sharding/spqr Currently it's our experiment to build pooling-based PostgreSQL sharding.

hn_throwaway_99 5y ago
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?
- x4m 5y ago
  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.
  - whitepoplar 5y ago
    How does Odyssey compare to other high-performance poolers like pgagroal?
    - x4m 5y ago
      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/PERFORMANCE.md https://github.com/agroal/pgagroal/blob/master/doc/PERFORMAN...
      - whitepoplar 5y ago
        Thanks! For a Postgres n00b, can Odyssey work alongside a HA solution like pg_auto_failover running on the same nodes?
        x4m 5y ago
        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.
        tsenart 5y ago
        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.
        jpgvm 5y ago
        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.
        x4m 5y ago
        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.
jerrysievert 5y ago
Any thoughts on an arm64 version?
- x4m 5y ago
  Odyssey could work fine on arm, but we've a small ASM function https://github.com/yandex/odyssey/blob/master/third_party/machinarium/sources/context.c#L75 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.
kerblang 5y ago
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?
- x4m 5y ago
  Transaction pooling rules are equivalent in Odyssey and PgBouncer.
3np 5y ago
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.
- x4m 5y ago
  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.
  - 3np 5y ago
    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 https://github.com/yandex/odyssey/issues/361
    - x4m 5y ago
      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.
      - 3np 5y ago
        Thanks for following up. auth_query_user is not the same as the user I'm attempting to authenticate with, though. And I get the same behavior with other users, including non-existing ones. Or are you referring to something else?

by x4m 5y ago

hn_throwaway_99 5y ago

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?

x4m 5y ago
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.
- whitepoplar 5y ago
  How does Odyssey compare to other high-performance poolers like pgagroal?
  - x4m 5y ago
    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/PERFORMANCE.md https://github.com/agroal/pgagroal/blob/master/doc/PERFORMAN...
    - whitepoplar 5y ago
      Thanks! For a Postgres n00b, can Odyssey work alongside a HA solution like pg_auto_failover running on the same nodes?
      - x4m 5y ago
        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.
        tsenart 5y ago
        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.
        jpgvm 5y ago
        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.
        x4m 5y ago
        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.

jerrysievert 5y ago

Any thoughts on an arm64 version?

x4m 5y ago
Odyssey could work fine on arm, but we've a small ASM function https://github.com/yandex/odyssey/blob/master/third_party/machinarium/sources/context.c#L75 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.

kerblang 5y ago

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?

x4m 5y ago
Transaction pooling rules are equivalent in Odyssey and PgBouncer.

3np 5y ago

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.

x4m 5y ago
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.
- 3np 5y ago
  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 https://github.com/yandex/odyssey/issues/361
  - x4m 5y ago
    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.
    - 3np 5y ago
      Thanks for following up. auth_query_user is not the same as the user I'm attempting to authenticate with, though. And I get the same behavior with other users, including non-existing ones. Or are you referring to something else?