Details here: https://github.com/LemmyNet/lemmy/issues/3165

This will VASTLY decrease the server load of I/O for PostgreSQL, as this mistaken code is doing writes of ~1700 rows (each known Lemmy instance in the database) on every single comment & post creation. This creates record-locking issues given it is writes, which are harsh on the system. Once this is fixed, some site operators will be able to downgrade their hardware! ;)

  • RoundSparrow@lemmy.mlOPM
    link
    fedilink
    arrow-up
    28
    ·
    edit-2
    1 year ago

    It’s not on every comment,

    My testing with latest code is that it is indeed on every single comment INSERT, every new comment. I have the ability to view my live data while using Lemmy: https://lemmyadmin.bulletintree.com/query/raw_site_aggregates?output=table

    Every one of the 1486 rows on that table gets +1 on comment when I post a new comment on my instance.

    it’s mostly triggered on deletions and edits

    That is not correct. Edits do not change the count of comments column on site_aggregates - because the number isn’t changing. Deletes (of a comment or post) in Lemmy are also not SQL DELETE statements, they are just a delete data column in the table. That DELETE PostgreSQL trigger only gets run when a end-user cancels their Lemmy account in their profile.

    • ShadowA
      link
      fedilink
      arrow-up
      11
      ·
      1 year ago

      Ah Gotcha. That’s true, but the cascading issue that causes thousands of inserts happens on a delete.

      That table update you’re looking at is blazing fast due to the relatively low number of sites, until you run it thousands of times on a delete.