High Availability improvements coming? Use Replicas for reads?

So I've been extensively investigating the following horizontal scale databases:

* Citus
* Vitess
* CockroachDB
* Yugabyte

Citus wins in a lot of ways... performance, ease of re-balancing, licensing, overall architectural simplicity of how tables are sharded and how you define sharding keys.. it's elegant! (In Vitess land, maintaining the VSchema and specifying shard boundaries yourself is a **pain in the butt**. Bravo to Citus.)

The biggest downside I've hit regarding Citus is the poor HA story in 2024. Please correct me if I'm missing something (please share your thoughts, solutions!).

* Replicas seem to be "stand by" only and not usable for reads? This means adding 2x hardware that sits idle, just to achieve HA.
* If you want synchronous writes, you're gonna have 3x the hardware sitting idle because the first replica needs its own replica to not hang.
* The ideal place to have HA logic is in Citus. [Patroni](https://www.citusdata.com/blog/2023/03/06/patroni-3-0-and-citus-scalable-ha-postgres/) + etcd is more moving parts when Citus could just talk to itself. Creates coordination issues like the one above, and blows up Ops complexity.
* Patroni also seems to require Ops intervention to "reset" it (or a risky "fail back")

As a newbie `citus.shard_replication_factor = 2` looked like a low friction path forward for HA (no manual Ops intervention is wonderful!), but it breaks in my testing for this purpose (and apparently) [can't be used](https://github.com/citusdata/citus/issues/7330) for [HA at all](https://github.com/citusdata/citus/issues/7121) even if you're [willing to give up FKs](https://github.com/citusdata/citus/issues/998) and sacrifice a bit of consistency. HA should ideally be this easy, even if the usable feature set is a bit limited, so we all don't have to wait years for a better HA solution.

* Vitess HA is built-in with VTOrc- just put more replicas online whenever, they will be promoted as needed. Queries to both primary and replicas re-route in VTGate.
* CockroachDB and Yugabyte just get HA for free with the replication model.

Citus really needs a simple baked in answer to HA. The Citus default would be an under-replicated dangerous state in the other databases listed.

---

Side note, example of the community getting confused by this:

* https://www.percona.com/blog/data-redundancy-with-the-postgresql-citus-extension/
* https://www.percona.com/blog/how-to-scale-a-single-host-postgresql-database-with-citus/

These look great at first, but...

Not sure if Percona is aware but these tutorials only work once. `shard_replication_factor = 2` breaks the 2nd time you fail / recover a node (ex: `citus_disable_node()` then `citus_activate_node()` then `rebalance_table_shards()`)... You can witness the replicas disappear using: `SELECT * from citus_shards;`

Start by reproducing the reported fail/recover sequence with citus.shard_replication_factor, citus_disable_node(), citus_activate_node(), rebalance_table_shards(), and SELECT * from citus_shards. Review linked issues #7330, #7121, and #998 plus the Patroni and Percona references; the issue has no named source file, test, or bounded completion criteria.

Written by the indexing model from the issue text.