At the time of writing, Lemmyworld has the second highest number of active users (compared to all lemmy instances)
Also at the time of writing, Lemmyworld has >99% uptime.
By comparison, other lemmy instances with as many users as Lemmyworld keep going down.
What optimizations has Lemmyworld made to their hosting configuration that has made it more resilient than other instances’ hosting configurations?
See also Does Lemmy cache the frontpage by default (read-only)? on !lemmy_support@lemmy.ml
The image-hosting component: https://crates.io/crates/pict-rs
It’s not a crazy idea, but it’s not obviously a panacea either.
All of which is to say, maybe there’s something there… but often distributed databases have fewer features and have bigger footguns than rdbms’s. If you want to try cockroach or aurora, you don’t need the devs help. You can stay to stand up a Lemmy instance that points at them. If the compatibility is really good enough, it will “just work” and you can try some performance testing. If it doesn’t, then you have your answer than porting effort is requires for speculative benefit.