I asked this in the other comment, what is your server specs and user base? Constant pain as in moderation or server breaking ?
Here is the last time I complained about matrix. https://news.ycombinator.com/item?id=25100873
I saw your comment and considered posting a facetious comment about how you would need 80TB of ram and at least twice as much disk space.... but that would add nothing to the discussion.
Of course I was using sqlite when there weren't nearly as many users as there are today or when I had federation disabled.