When I see SQL select statements like this: https://lemmy.ml/comment/793612
I start to think about caching.
-
Postings with 200 comments or less I would maybe treat entirely different code path.
-
Postings that are lively and being updated, different code path. If its a fresh-meat topic, with comments going crazy every minute, code may want to consider that.
-
Over 200 comments, maybe just dump the whole damn comments for that posting outside of PostgreSQL into nosql or some cache system, then start processing them independently.
-
Some kind of site self-awareness that the serverr wants to run on a resource budget or is under some major social event load (such as an airplane crash getting huge influx of users for discussion)… and you are willing to shed timely comment publishing to keep your database activity sane. You might be willing to not read new (and edited/deleted) comments from that posting for 2 minutes, just to offload your database.
-
That there may be several front-end “rust code” servers of lemmy_server talking to a single PostgreSQL backend here. And that the caching in #3 above on this list would be duplicated on those mid-tier servers.
Kind of rambling, but it’s on my mind.