The “Hot” score is causing database issues for Lemmy instances. - Every time a
page of lemmy instance is viewed, the “Hot” score is recalculated. - Every hour,
the entire database is reindexed. This means that just visitors to a lemmy
instance can create problems. I’m sure this isn’t the only aspect of the
database that could use some optimization. Any one with database expertise out
there want to help? I’ve linked to an illuminating comment on a lemmy’s github:
“Slow SQL queries · Issue #2877 · LemmyNet/lemmy”
I’m also curious why every page view (to be clear, do you mean every time a post is viewed?) triggers a recalc on the Hot score. I would think you could recalc that like every 5 minutes and be just fine. (If people are refreshing a feed more frequently than every 5 minutes, they should be expecting to see the same thing most of the time).
This is the current design, yes. I believe the devs have already started a related issue to change this. I’m assuming the next version of Lemmy will have some major performance improvements.
Yeah, just thinking about things logically here, the more they can automate expensive queries on the back end on a more periodic basis and then cache the results regardless of what’s happening with page views on the front end, the better things should be. It’s a lot cheaper for a database to serve cached results in lookups. I’m not a database guy and not really qualified to help fix any of these issues, but I’m just drawing from what I remember from my databases course in college.