Based on a lot of personal messaging app development and SQL database experience back in the low-performance hardware days....
I speculate that the developers of Lemmy have not populated the database with large amounts of test data. It shows a lot of symptoms of a codebase that never had a test-data-generation app that would create say 1000 test user accounts, 50 test communities, stuff 1000 random postings in each test community - and then put 10 to 150 random comments of various lengths on each of those postings.
It looks to me more like it started with an empty SQL database and has incrementally grown to a few thousand end-users creating a few posts a day with a relatively small number of comments on those postings.
Growing pains are normal, but it may help to have an instance out there for performance testing of the database queries and insert performance... and even invite end-users to hammer on the test instance all at a scheduled time (testing party) to find where the performance bottlenecks lie.