Every combination of community block and subscribe is too much…
But can we leverage community_aggregates. With all the overhead of updating it on every ++ of post and ++ of comment… can we just write a hint of where page 20 in sorts may fall?
each community could set a dirty flag on community_aggregates update. count alone can’t be relied upon because it also decrements.
The hierarchy is important to consider. Every comment is owned by a post, every post is owned by a community, every community is owned by an instance. For Reddit, the user is secondary and their comment and post traditionally can be linked to and read even if the user deletes their account. For Lemmy, removal of content was an early design priority, but think that harms search engine use users and goes against the spirit of what a public forum is about (yes, everyone wants to turn it into private playland, and ignore that hackers break into major websites with full time security teams - and Lemmy being operated by unknown strangers isn’t exactly something they should count on).
Ok, back on topic. With all the overhead currently in place to update community)_aggregates on each new comment and post, it makes sense to try and filter out the cruft of 5000 communities that may be stale yet bog down every PostgreSQL for posts for the most recent hour. I don’t think I’ve seen one single mention by anyone analyzing the situation that lemmy.world having over 9,000 local communities is a huge factor - as Lemmy was sort of build with “20 communities” in 2020.
Brainstorming…
Every combination of community block and subscribe is too much…
But can we leverage community_aggregates. With all the overhead of updating it on every ++ of post and ++ of comment… can we just write a hint of where page 20 in sorts may fall?
each community could set a dirty flag on community_aggregates update. count alone can’t be relied upon because it also decrements.
The hierarchy is important to consider. Every comment is owned by a post, every post is owned by a community, every community is owned by an instance. For Reddit, the user is secondary and their comment and post traditionally can be linked to and read even if the user deletes their account. For Lemmy, removal of content was an early design priority, but think that harms search engine use users and goes against the spirit of what a public forum is about (yes, everyone wants to turn it into private playland, and ignore that hackers break into major websites with full time security teams - and Lemmy being operated by unknown strangers isn’t exactly something they should count on).
Ok, back on topic. With all the overhead currently in place to update community)_aggregates on each new comment and post, it makes sense to try and filter out the cruft of 5000 communities that may be stale yet bog down every PostgreSQL for posts for the most recent hour. I don’t think I’ve seen one single mention by anyone analyzing the situation that lemmy.world having over 9,000 local communities is a huge factor - as Lemmy was sort of build with “20 communities” in 2020.