RoundSparrow

If everyone was spread out onto different instances

Each instance with an owner/operator making rules... that the average social media user walks in, orders a drink, and starts smoking without any concern that neither one may be allowed. People can be loyal to their media outlets even when it is beyond obvious they are bad. People raised on storybooks that endorse bad behaviors and values, HDTV networks, and social media too. Audience desire to "react comment" to images and not actually read what others have commented - nor learn about the venue operators and reasons for rules is pretty much the baseline experience in 2023.

When it comes to media attraction, what they call themselves (labels) don't really matter that much. It's the praise of strong men, authority, that crosses all mythological media systems. Be it bowing down to a burning bush story, Fox News, or Kremlin.

Keep in mind that you’re going to be retrieving and storing a huge amount of data running these scripts

And you are adding to the overload of lemmy.world, beehaw, lemmy.ml, etc who have all the popular content communities. Federation has a lot of overhead, as does having to distribute a community one vote at a time to 500 subscribed servers.

pend my time on Lemmy scrolling “All”, which I think is a pretty common thing.

There was a lot of advice handed out back in June that the answer to scaling Lemmy was to go create instances. The reason it works is because "All" is empty on a virgin system ;) With no data in the database, the logic Lemmy hands to PostgreSQL works real fast ;)

I found the total table update wasn't as bad performing as I thought and the API gateway was timing out. I'm still generating larger amounts of test data to see how it performs in edge worst-case situations.

@Prefix@lemm.ee - maybe pin this for a while?

otherwise that would be an easy exploit, just creating a practically infinitely long block list to crash the instance.

Perhaps you are unaware of just how unstable Lemmy has been since late May.

I’m asking because I used to run into the maximum number of blocks on my Reddit client

I think they used the multi-reddit list as the back-end for subreddit block list. I too ran into the limits.

I don't think Lemmy has any limit, but performance will likely degrade and it is entirely possible that your personal block list causes overload of servers with lots of data in them.

I just set up

just... it does not backfill previous content and votes. What time window are you talking about here?

The whole design is not what I would describe as robust and chock full of features. A lot of people have pushed federation as the key to scalability (now with over 1500 lemmy instances online ) when it is one of the least-mature parts of the code and carries a lot of overhead. Having 500 servers all subscribing to the same communities on lemmy.world for a dozen readers is causing problems with outbound distribution.

I agree there is potential to reuse the child_count from child/grandchild rows. But there has to be some sense to the order they are updated in so that the deepest child gets count updated first?

So it turns out that the query was finishing within minutes and the API gateway was timing out. Too many Lemmy SQL statements in my head. On a test system update of all the comment second run just took under 17 seconds for 313617 rows that has some decent reply depth, so it isn't as bad as I thought.

given it traverses all the comment X comment space every time a comment is added.

The second query I shared is only referenced for maintenance rebuild. The routine update of count does target only the tree that the reply is to:

 select c.id, c.path, count(c2.id) as child_count from comment c
 join comment c2 on c2.path <@ c.path and c2.path != c.path
 and c.path  <@ '0.1'
group by c.id

I found a particularly complex tree with 300 comments. In production database (with generated test data added for this particular comment tree), it is taking .371 seconds every time a new comment is added, here is the result of the SELECT pulled out without the UPDATE:

Obviously with the UPDATE it will take longer than .371 seconds to execute.