This reinforced in my mind that as much as I like the idea of lemmy (or any of the other threadiverse SW), this is only something experts should try hosting. Sadly, this will lead to more centralization of the lemmy community to few big servers instead of many small ones, but given the nature of problems one can encounter and the lack of support to fix them if they’re not experts, I don’t see an option.

This also gave me an insight about how the federation of lemmy will eventually break when a single server (say, lemmy.world) grows big enough to start overwhelming even servers who are not badly setup like mine was.

Lemmy has many scalability problems to solve, and not all of these problems are slow database queries. I believe your experience is going to become increasingly common as the community grows because that increased centralization will compound the scalability problems and continue to drive up the technical know-how required to host a successful instance. The software eventually needs to do more to detect and present operational problems to administrators in a friendly way. I2P is an example of a distributed network that's quite good at reporting issues with the node.

With that said, not everything is doom and gloom. The community has proven itself highly resilient and smart people like yourself are finding solutions. It's going to be tough road ahead.