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”
Question really is why is it developed that way? Why does it need to do a reindex every hour? I have not looked at Lemmy source code, but wondering if a trigger function could do the same thing.
I’m going to guess inexperience and a focus on developing other parts of Lemmy.
(Apparently the UI allows you to edit your own deleted comment, but saving it doesn’t un-delete it, you just lose what you wrote… Bummer!)
In the conversation on Github, it looks like they considered a trigger and some other options based on DB query analyses, and arrived at a solution. It requires code refactoring, though, so I wouldn’t expect it to be out in the wild right away.
There’s a branch that likely fixes the issue with a pull request to main, but it failed some automated testing and needs to be tested in main before a new release. The timing of this is unfortunate. I suspect Monday and Tuesday to have many 50x codes on any instances that get attention.
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.