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”
In the conversation on Github it looks like they considered a trigger and looked at a ton of database query analyses and found a promising direction to go in.
The change requires code refactoring, though, so I wouldn’t expect it to land in a stable version of Lemmy for a little bit.