This basically shuts my idea down

it’s not very difficult to modify the code for something like this… and closing off registration wont’ let anyone else login and create new content form your istance.

Personally the load on the major servers by having one more instance that subscribes to everything is why I think people should back off from creating more than the 1500 instances Lemmy network already has. Delivery of every single vote, comment, post 24 hours a day just so one person can read content for an hour or two a day.

That makes sense for email systems where all that content doesn’t have to be sent, but for Lemmy it’s a huge amount of overhead.

Personally I think the issue is more that there is blind loyalty to team sports in USA culture, and no matter how many bad things are documented about a specific person (Donald Trump, Richard Nixon)… people are loyal to the image of that person, the brand and logo. People are raised in the USA to be inundated with breakfast cereal and toy company logo/brand recognition. It’s a faith system. Breakfast cereals and fast-food “Happy plates” that fund a lot of children’s TV are incredibly unhealthy and profit machines - and parents think this is psychologically healthy.

Politics will eventually be replaced by imagery. The politician will be only too happy to abdicate in favor of his image, because the image will be much more powerful than he could ever be. -Marshall McLuhan

Do you have any opinions on how they should work?

given lemmy’s heavy focus on purging a user’s content on account delete, I think comment links should include the username.

lemmy.world/the_username@home_instance.tld/comment/xxxx

This also gives self-promotion to the person, a sense of identity on their content.

Thank you

Good results with this approach. I hadn’t considered the RANK OVER PARTITION BY criteria_a values and it works like a champ. It moves the ORDER BY into the realm of focus (criteria_a) and performance seems decent enough… and it isn’t difficult to read the short statement.

SELECT COUNT(ranked_recency.*) AS post_row_count
FROM
  (
     SELECT id, post_id, community_id, published,
        rank() OVER (
           PARTITION BY community_id
           ORDER BY published DESC, id DESC
           )
     FROM post_aggregates) ranked_recency
WHERE rank <= 1000
;

Gives me the expected results over the 5+ million test rows I ran it against.

If you could elaborate on your idea of TOP, please do. I’m hoping there might be a way to wall the LIMIT 1000 into the inner query and not have the outer query need to WHERE filter rank on so many results?

Ok, I’m doing some reading: https://medium.com/@amulya349/how-to-select-top-n-rows-from-each-category-in-postgresql-39e3cfebb020

An Instance is just another word for ‘server’ in lemmy terminology. HDTV is a classic form of media that doesn’t involve TCP/IP to watch films and other video content.

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.

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.

The question seems like a misuse of the word ‘federate’.

sdfasdf

Wait… Did they CASH IN? Sell out?: Odorono?

The very moderator of [email protected]

!sdfasdf isn’t a community?

EDIT: we did it Lemmy!!! [email protected]

Me and my brother were talking to each other
About what makes a man a man
Was it brain or brawn, or the month you were born?
We just couldn’t understand

!sdfasdf isn’t a community?