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.
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.
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.
Or are you referring to something else?
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.
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...
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.