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”

Is there anyone out there with database expertise willing to help?

  •  Aaron   ( @aaronbieber@beehaw.org ) English
    4•1 year ago

    (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.

    •  ericjmorey   ( @ericjmorey@beehaw.org ) OPEnglish
      3•1 year ago

      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.