lemmy is…alpha software at best imho.
lemmy_server development team needs some Database Management Experts to identify the key SQL queries and optimize them, possibly denormalize data and suggest wheres application caching can be done.
It looks to me like /communities list is fetched from SQL every time a user brings up that page, that data could be at least cached for 2 minutes in the Rust code. Or even add a trigger to generate a flat table when a new insert is done (addition of a new community).
So far, there hasn't really been but a couple people looking at the SQL problem of scaling:
Any rust developers willing to update the API interfaces to the open source lemmy-lite application? https://github.com/IronOxidizer/lemmy-lite
It doesn't use websockets, it uses the HTTP API