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.
The issues with TRIGGER statements on site_aggregates would have been difficult to simulate, requiring 1800 federated instances to match what Lemmy.ml likely has in the live database. But an example of how testing and studying execution time of SQL is important with quantities of data matching production.
Lemmy.ca found the issue by making a full copy of their database to test on locally to see the triggers were hitting so many rows… and that site goes back to 2020 data.