If cockroach is truly PG compatible, lemmy admins can swap it in without developer support. I suspect Cockroach constrains some SQL features and has poor performance on others, but that or AWS Aurora are things you can experiment with without dev support if you’re passionate about the proving out the value of scale-out.

The statement that spawned my response though was this:

I think lemmy will be bitten in the ass by not having considered clustering/horizontal scaling from the start. Federation alone as a scaling mechanism is only feasible for “nerds”. But if the network wants to grow, we will need a few scale-able large hosted instances.

I still don’t think it’s true that we need horizontal scaling to support sufficiently large instances. The amount of vertical and horizontal scaling ability built into Lemmy today is both useful, and likely to outstrip the current ability of its code to scale a single instance. Any algorithms that scale super-linearly with respect to comment-count, post-count, user-count, or community-count, will fail just as hard with distributed backends as they do with an RDBMS. And as you note, PG-compatible distributed systems provide a potential lower-cost on-ramp to distributed systems once the codebase is efficient-enough to warrant such a transition to scale further. I suspect I’ve contributed everything of use I have to this thread though, and don’t expect to respond further.