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! ;)
It’s not on every comment, it’s mostly triggered on deletions and edits. The problem is actually infintesimally worse and 1700 rows are updated if you delete 3 comments. If you delete more it’s exponential and just straight up fails and locks your database.
I’ll probably put a patch in there later tonight and then see about a PR unless someone else does.
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.
That is not correct. Edits do not change the count of comments column on site_aggregates - because the number isn’t changing. Deletes in Lemmy are also not SQL DELETE statements, they are just a delete column in the table, so that DELETE PostgreSQL trigger only gets run when a end-user cancels their Lemmy account in their profile.
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.