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?
[ Parent
| on HN ]
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.
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.
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.