https://www.postgresql.org/message-id/27781.1284040185%40sss.pgh.pa.us

just hitting the first page, limit 20, using lemmy-ui http://localhost:1234/c/zy_photography default sort of "Active" taking over 26 full seconds, each refresh. 463K posts in community, 6.18M posts in database 12K communities block lists should be entirely empty this is while logged-in 2023-08-16 12:46:44.049 MST [1520415] lemmy@lemmy_alpha LOG: duration: 26064.185 ms execute s22174: SELECT "post"."id", "post"."name", "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed", "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description", "post"."thumbnail_url", "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local", "person"."id", "person"."name", "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated", "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at", "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id", "person"."admin", "person"."bot_account", "person"."ban_expires", "person"."instance_id", "community"."id", "community"."name", "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted", "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner", "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods", "community"."instance_id", "community"."moderators_url", "community"."featured_url", ("community_person_ban"."id" IS NOT NULL), "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published", "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local", "post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank", "community_follower"."pending", ("post_saved"."id" IS NOT NULL), ("post_read"."id" IS NOT NULL), ("person_block"."id" IS NOT NULL), "post_like"."score", coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") FROM ((((((((((((( "post_aggregates" INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")) INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")) LEFT OUTER JOIN "community_person_ban" ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))) INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $1))) LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = $2))) LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = $3))) LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = $4))) LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $5))) LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = $6))) LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = $7))) LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = $8))) LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $9))) WHERE ((((((((("community"."removed" = $10) AND ("post"."removed" = $11)) AND ("post_aggregates"."community_id" = $12)) AND (("community"."hidden" = $13) OR ("community_follower"."person_id" = $14))) AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16)) AND ("local_user_language"."language_id" IS NOT NULL)) AND ("community_block"."person_id" IS NULL)) AND ("person_block"."person_id" IS NULL)) ORDER BY "post_aggregates"."featured_community" DESC , "post_aggregates"."hot_rank_active" DESC , "post_aggregates"."published" DESC LIMIT $17 OFFSET $18

In April and May, Lemmy had a great brand... but server crashes and then lemmy.ml closed sign-up.... sort of confusing people and then Lemmy.world became the brand, then it too has had to close home page many times and constant crashes.... The SQL performance issues that were in lemmy for years really - became trouble once data was introduced. I don't even know how many times people have come to sing-up and heard about Lemmy to find it slow, crashing, errroring. Social hazing, rock star glamour attitude among the audience who keeps using Lemmy... but these odd trends in 2023 with social media aren't unique to lemmy, are they? Threads, Twitter to X, Reddit API change, Lemmy crashing and not really caring about the crashes - and Redis or Memcache to mitigate it.... it's all such an odd year. It's as if everyone stirred the pot with social media but no place to really land upon that isn't crashing or in some form of Elon Musk kind of chaos. I remember the rise of Reddit, the rise of Gmail - and it just felt like the growth was being dealt with. In 2023, Twitter seems to be leading the entire audience into accepting a kind of crash bad experience and 'stick around, no matter how bad it is' that Reddit users seem to have accepted. I don't want to be a Lemmy developer... I want the people who know Rust Diesel and such to actually make it work - or choose something else that does work. I want Lemmy to actually not crash all the time and at least be where Reddit was in 2008 when there were just a few programmers doing it. The SQL statements and server crashes in Lemmy speak for them self, just like the chaos of Reddit and Elon Musk Twitter speaks for itself... but a lot of people accept it. The audience is not asking for stability. Strange times in 2023!

So this is currently a thought experiment or brainstorming, whatever... When a community is local, the "home server" for community, that means that the federation is sent out to subscribed servers. Now it also means that the total subscribe list is only know to that one server... As least I think it works that way, that the subscribe of "home" server is the complete list and everyone else has partial lists. There is a lot of the structure of a community that offers some data opportunities to Lemmy. For one, a community object can be edited by any of the moderators. and any moderator can Lock a post, Remove a post, etc. Reddit has a not-often-used Wiki feature, that even basically means more than one person could edit content too. Other than the sidebar of a community, I don't think Lemmy has any concept of multiple people being able to edit a post or a comment. But again, multiple mods can feature or lock a post... so there is some concept of multiple-actors on data. I think when you get into creating flair / tags and even playlists (multi-community lists), you want multiple people to be able to edit data. Which in Reddit was the Wiki structure. i know some of it is kind of a waste of time.. because people tend to avoid Wiki and highly favor posting the same repeat questions and content over and over, reposts. But a man can dream, can't he :)

github.com

cross-posted from: https://campfyre.nickwebster.dev/post/89316 > I was getting close to hitting the end of my free object storage so there was time pressure involved haha. > > Seems to work but I haven't tested it too much. Currently running on my instance.

I did get permission to share this private message. For the record, I loath having public Lemmy topics being discussed in private messages. I created this community to get all this OUT in the OPEN. I already made my decision on NOT changing privacy policy. But now I see that the very idea of having more than one "Subscribed" list for an end-user is raising threats of defederation! 

Rust RoundSparrow • 1 year ago • 91%

Help! I want to divorce ReadFn from ListFN - bypassing the Queries mutual closure behavior so I can better isolate some logic. My need is to get an independent ListFn... ``` fn queries_<'a>() -> Queries< impl ReadFn<'a, PostView, (PostId, Option, bool)>, impl ListFn<'a, PostView, PostQuery<'a>>, > { ```  Context: https://github.com/LemmyNet/lemmy/blob/main/crates/db_views/src/post_view.rs For sake of clarity... I'm not wanting to break the whole Queries joined closure marriage project-wide, just this one source file I want to be able to copy/paste this code twice and have just a single closure for ListFn. Thank you.

politics RoundSparrow • 1 year ago • 95%

Donald Trump faces four indictments, 91 criminal charges and hundreds of years of maximum prison time combined. This is a former president who — according to the latest grand jury indictment in Fulton County, Georgia — participated in a “criminal enterprise.” Trump and 18 co-defendants are accused of trying “to unlawfully change the outcome of the election” in 2020. Among the 13 felony charges he faces is one count of violating the Georgia RICO (Racketeer Influenced and Corrupt Organizations) Act and two counts of conspiracy to commit forgery. Most of those charges are related to a fake elector scheme by the Trump campaign in which a slate of “alternate” electors in Georgia would cast electoral votes for Trump instead of Joe Biden. The president of the most powerful democracy in the world allegedly tried to steal an election. We can’t say it often enough: This is serious. Americans cannot shrug this off or normalize it, no matter how many times Trump gets indicted. Yet it feels like business as usual. Not only is Trump favored to win the GOP presidential nomination, he’s also neck and neck with President Biden in the 2024 general election, according to a July poll by the New York Times/Siena Poll. MORE THAN A CULT Trump’s support cannot only be explained as the product of the cult-like power he has over his MAGA base, which accounts for roughly 40% of Republican voters who believe those indictments are nothing but a conspiracy against him. more: https://www.miamiherald.com/opinion/editorials/article278265068.html

Rust RoundSparrow • 1 year ago • 100%

I'm trying to wrangle in and get 'back to basics' with Lemmy's Diesel code and at every turn I run into not understanding the complexity of the Rust code. You may want to do a GitHub checkout of this branch if you want to see what I'm attempting: https://github.com/LemmyNet/lemmy/pull/3865 I'm basing my experiments off the code in that pull request, which links to this branch on this repository: https://github.com/dullbananas/lemmy/tree/post-view-same-joins Right now Lemmy's Diesel code spins up many SQL table joins and for an anonymous user it just passes a -1 user id to all the joins - and it really makes for difficult to read SQL statements. So I decided to experiment with removing as much logic as I could to get the bare-bones behavior on generating the desired SQL statement.... I copied/pasted [the queries function/method ](https://github.com/dullbananas/lemmy/blob/5ddaf516111f4ea0e7962472d3a8296d735be0f4/crates/db_views/src/post_view.rs#L65)and gave it a new name, kept removing as much as I could see that referenced the user being logged-in vs. anonymous, and got to this point: ``` fn queries_anonymous<'a>() -> Queries< impl ReadFn<'a, PostView, (PostId, Option, bool)>, impl ListFn<'a, PostView, PostQuery<'a>>, > { let is_creator_banned_from_community = exists( community_person_ban::table.filter( post_aggregates::community_id .eq(community_person_ban::community_id) .and(community_person_ban::person_id.eq(post_aggregates::creator_id)), ), ); // how do we eliminate these next 3 assignments, this is anonymous user, not needed let is_saved = |person_id_join| { exists( post_saved::table.filter( post_aggregates::post_id .eq(post_saved::post_id) .and(post_saved::person_id.eq(person_id_join)), ), ) }; let is_read = |person_id_join| { exists( post_read::table.filter( post_aggregates::post_id .eq(post_read::post_id) .and(post_read::person_id.eq(person_id_join)), ), ) }; let is_creator_blocked = |person_id_join| { exists( person_block::table.filter( post_aggregates::creator_id .eq(person_block::target_id) .and(person_block::person_id.eq(person_id_join)), ), ) }; let all_joins = move |query: post_aggregates::BoxedQuery<'a, Pg>, my_person_id: Option| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); query .inner_join(person::table) .inner_join(community::table) .inner_join(post::table) // how do we eliminate these next 3 joins that are user/person references? .left_join( community_follower::table.on( post_aggregates::community_id .eq(community_follower::community_id) ), ) .left_join( community_moderator::table.on( post::community_id .eq(community_moderator::community_id) ), ) .left_join( post_like::table.on( post_aggregates::post_id .eq(post_like::post_id) ), ) .left_join( person_post_aggregates::table.on( post_aggregates::post_id .eq(person_post_aggregates::post_id) ), ) .select(( post::all_columns, person::all_columns, community::all_columns, is_creator_banned_from_community, post_aggregates::all_columns, CommunityFollower::select_subscribed_type(), // how do we eliminate these next 3 for anonymous? is_saved(person_id_join), is_read(person_id_join), is_creator_blocked(person_id_join), post_like::score.nullable(), coalesce( post_aggregates::comments.nullable() - person_post_aggregates::read_comments.nullable(), post_aggregates::comments, ), )) }; let read = move |mut conn: DbConn<'a>, (post_id, my_person_id, is_mod_or_admin): (PostId, Option, bool)| async move { let mut query = all_joins( post_aggregates::table .filter(post_aggregates::post_id.eq(post_id)) .into_boxed(), my_person_id, ); query = query .filter(community::removed.eq(false)) .filter(post::removed.eq(false)) ; query.first::(&mut conn).await }; let list = move |mut conn: DbConn<'a>, options: PostQuery<'a>| async move { let person_id = options.local_user.map(|l| l.person.id); let mut query = all_joins(post_aggregates::table.into_boxed(), person_id); query = query .filter(community::deleted.eq(false)) .filter(post::deleted.eq(false)); // every SELECT has to labor away on removed filtering query = query .filter(community::removed.eq(false)) .filter(post::removed.eq(false)); if options.community_id.is_none() { query = query.then_order_by(post_aggregates::featured_local.desc()); } else if let Some(community_id) = options.community_id { query = query .filter(post_aggregates::community_id.eq(community_id)) .then_order_by(post_aggregates::featured_community.desc()); } if let Some(creator_id) = options.creator_id { query = query.filter(post_aggregates::creator_id.eq(creator_id)); } if let Some(url_search) = options.url_search { query = query.filter(post::url.eq(url_search)); } if let Some(search_term) = options.search_term { let searcher = fuzzy_search(&search_term); query = query.filter( post::name .ilike(searcher.clone()) .or(post::body.ilike(searcher)), ); } query = query .filter(post::nsfw.eq(false)) .filter(community::nsfw.eq(false)); query = match options.sort.unwrap_or(SortType::Hot) { SortType::Active => query .then_order_by(post_aggregates::hot_rank_active.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Hot => query .then_order_by(post_aggregates::hot_rank.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()), SortType::New => query.then_order_by(post_aggregates::published.desc()), SortType::Old => query.then_order_by(post_aggregates::published.asc()), SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()), SortType::MostComments => query .then_order_by(post_aggregates::comments.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopAll => query .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopYear => query .filter(post_aggregates::published.gt(now - 1.years())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopMonth => query .filter(post_aggregates::published.gt(now - 1.months())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopWeek => query .filter(post_aggregates::published.gt(now - 1.weeks())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopDay => query .filter(post_aggregates::published.gt(now - 1.days())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopHour => query .filter(post_aggregates::published.gt(now - 1.hours())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopSixHour => query .filter(post_aggregates::published.gt(now - 6.hours())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopTwelveHour => query .filter(post_aggregates::published.gt(now - 12.hours())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopThreeMonths => query .filter(post_aggregates::published.gt(now - 3.months())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopSixMonths => query .filter(post_aggregates::published.gt(now - 6.months())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), SortType::TopNineMonths => query .filter(post_aggregates::published.gt(now - 9.months())) .then_order_by(post_aggregates::score.desc()) .then_order_by(post_aggregates::published.desc()), }; let (limit, offset) = limit_and_offset(options.page, options.limit)?; query = query.limit(limit).offset(offset); debug!("Post View Query: {:?}", debug_query::(&query)); query.load::(&mut conn).await }; Queries::new(read, list) } ``` This compiles, but I can not progress further. There are 3 joins more that aren't really needed for an anonymous user... but the interdependent Rust object structures I can't unravel enough to remove them from the code. For example, Lemmy allows you to "save" a post, but an anonymous user doesn't have that ability - so how can I remove the JOIN + select related to that while still satisfying the object requirements? I even tried creating a variation of PostViewTuple object without one of the bool fields, but it all cascades into 50 lines of compiler errors. Thank you.

PostgreSQL RoundSparrow • 1 year ago • 100%

``` SELECT id FROM my_table WHERE id IN ( SELECT id FROM my_table WHERE criteria_a = 19 ORDER BY create_when DESC LIMIT 1000 ); ``` This is the pattern I am looking for, but I need the criteria_a to be repeated for every value of criteria_a with the important focus being the LIMIT 1000 for any single value of criteria_a. There is no need to put a total LIMIT on the query, just to limit to the 1000 per criteria_a with the specific ORDER BY at that point. Put another way... ``` SELECT id FROM my_table WHERE id IN ( SELECT id FROM my_table WHERE criteria_a = 19 ORDER BY create_when DESC LIMIT 1000 ) OR id IN ( SELECT id FROM my_table WHERE criteria_a = 20 ORDER BY create_when DESC LIMIT 1000 ); ``` Where I desire 2000 total rows. I could turn this into programming code (even a PostgreSQL FUNCTION) that loops over every value of criteria_a and replaces 19 in the example. I don't care of it is a JOIN or an IN, I'm more stuck on how to repeat the inner SELECT with the LIMIT 1000 based on sort and criteria_a. Can I do it without looping and/or UNION? Thank you.

This [discussion about fans of](https://old.reddit.com/r/LinusTechTips/comments/15sjv78/for_anyone_wondering_why_madison_stayed_silent/jwerdno/) Linus Tech Tips:  LTT is a "entertainment news show", also of reference is [how Fox News says it is "entertainment" and not factual](https://www.npr.org/2020/09/29/917747123/you-literally-cant-believe-the-facts-tucker-carlson-tells-you-so-say-fox-s-lawye) news. [It's still all Mythology storytelling to me](https://www.youtube.com/watch?v=5eAQa4MOGkE), as defined by Finnegans Wake by James Joyce - and Marshall McLuhan's highlighting of Finnegans Wake.

Lemmy App Development RoundSparrow • 1 year ago • 100%

cross-posted from: https://lemmy.world/post/3252643 > [lemmy.readme.io](https://lemmy.readme.io/) uploaded some great API documentation to get started making your own Lemmy client. > > Proved very useful in making my iOS client [Lunar](https://github.com/mani-sh-reddy/Lunar)

PostgreSQL RoundSparrow • 1 year ago • 96%

lemmy_server PostgreSQL table for comment does not keep parent comment id directly, it uses a path field of ltree type. by default, every comment has a path of it's own primary key id. comment id 101, path = "0.101" comment id 102, path = "0.102" comment id 103, path = "0.101.103" comment id 104, path = "0.101.103.104" comment 103 is a reply to comment 101, 104 is a reply to 103. A second table named comment_aggregates has a count field with comment_id column linking to comment table id key. On each new comment reply, lemmy_server issues an update statement to update the counts on every parent in the tree. [Rust code issues this](https://github.com/LemmyNet/lemmy/blob/c8063f3267cf2b3622f1fdc69128c6b55feefbbc/crates/db_schema/src/impls/comment.rs#L104) to PostgreSQL: ``` if let Some(parent_id) = parent_id { let top_parent = format!("0.{}", parent_id); let update_child_count_stmt = format!( " update comment_aggregates ca set child_count = c.child_count from ( 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 <@ '{top_parent}' group by c.id ) as c where ca.comment_id = c.id" ); ``` sql_query(update_child_count_stmt).execute(conn).await?; } I've been playing with doing bulk INSERT of thousands of comments at once to test SELECT query performance. So far, this is the only SQL statement I have found that does a mass UPDATE of child_count from path for the entire comment table: ``` UPDATE comment_aggregates ca SET child_count = c2.child_count FROM ( SELECT c.id, c.path, count(c2.id) AS child_count FROM comment c LEFT JOIN comment c2 ON c2.path <@ c.path AND c2.path != c.path GROUP BY c.id) AS c2 WHERE ca.comment_id = c2.id; ``` There are 1 to 2 millions comments stored on lemmy.ml and lemmy.world - ~~this rebuild of child_count can take hours, and may not complete at all. Even on 100,000 rows in a test system, it's a harsh UPDATE statement to execute.~~ EDIT: I found my API connection to production server was timing out and the run-time on the total rebuild isn't as bad as I thought. With my testing system I'm also finding it is taking under 19 seconds with 312684 comments. The query does seem to execute and run normal, not stuck. Anyone have suggestions on how to improve this and help make Lemmy PostgreSQL servers more efficient? EDIT: lemmy 0.18.3 and 0.18.4 are munging the less-than and greater-than signs in these code blocks.

The Scary Door RoundSparrow • 1 year ago • 100%

They had me going with the plot build-up....

youtu.be

https://youtu.be/ZFKyi2BWyC4?t=828

Lemmy App Development RoundSparrow • 1 year ago • 100%

I'm starting lemmy-ui with: `LEMMY_UI_LEMMY_INTERNAL_HOST=lemmy-alpha:8541 node dist/js/server.js` Running against the drone-lemmy instances created by lemmy_server's api-tests scripts. I'm running latest main checkout on both projects. My browser gives me: Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at http://localhost:8536/api/v3/community/list?type_=Local&sort=TopMonth&limit=50&page=1. (Reason: CORS request did not succeed). Status code: (null). api-tests scripts build dev mode, I thought CORS was turned off? I've played around with adding LEMMY_CORS_ORIGIN=* - but it doesn't help. EDIT: reading this post closer, I see 8536 vs. 8541. If I manually refresh my browser against lemmy-ui on port 1234, it works... but I wonder where 8536 is getting picked up? Thank you and I hope you are having a great weekend.

chaos about big servers crashing from PostgreSQL overload continues... 

This kind of attitude is why I got so upset over GitHub Issue 2910 being ignored for months - and not given specific project call-out/squeaky wheel needs fixed spotlight...  Anyway, PostgreSQL 15.3.1 upgrade to 15.4 now released today, waiting on .deb files.

Right now, every single comment and post vote is federated and is a single row in PostgreSQL tables. Each person and community has a home instance. They only accurate counts of total post, comments, and votes is on that home instance. And even then it is theoretically possible that to save disk space and/or improve performance, the home of a community could purge older data (or have loss of data). For lemmy to lemmy, I think instead of federating the votes independent of the posts and comments, there could be sharing of aggregate data directly. The model for this is how profiles of a person are federated. When a person revises their profile on their home instance, every other instance has to get the updated change. Such as a new image or revised bio. Same with the profile of a community is revised, such as changing image or sidebar of a community. The code redesign could start out by making person and community aggregate count sharing part of those revisions. Those numbers are not that time-sensitive, the statistics of the number of users, posts, comments in a community could be behind by many hours without any real impact on the end-user experience of reading posts and comments on Lemmy. With votes, posts it is more tricky. But some experiments could be done such as only sending post aggregates when a comment on that post is created, deleted, or edited.... and a more back-fill-oriented bulk operation take care of correcting and discovering out of sync information.

I think a client-API self-awareness of performance problems and cost of running a server.... could be built into the design as an owner/operator choice. A search engine should see generic content, posts and filters, without changes. But the lemmy SQL logic for PostgreSQL is to burden fetching posts and comments with all kinds of user-specific customization. This kills caching potential if it's done way at the backend. Page 3 of posts in [!memes@lemmy.ml](https://lemmy.ml/c/memes) will be different for a user who has blocked a person in that list. Right now, that burden is placed upon PostgreSQL and having to rewrite indexes on every INSERT and do steps in every SELECT. For massive scale on lower-cost hardware, I suggest that the idea be placed where a smarter-client API is self-aware of this problem and page 3 of a community or All hot/active/top hour - be the same - and the client is given the burden of fetching the person block list and filtering. ---OR--- an intermediate front-end ,component of Lemmy that could run on multiple servers / scale out / do the filtering for that specific user. Even paging itself, the page length - is already variable - another cache issue. Eliminate that and just encourage over-fetching of post and comments and filtering out duplicates. ---OR---- even just fetching ID numbers of new/old and a very-smart client having an ID listing of the entire page and filling in content. But certainly during heavy server load, when servers are on the verge of crashing from too much data - eliminating personal exclusions of communities and persons on fetching posts/comments can have some PostgreSQL offloading. Even NSFW might fit into that. Sorry about my language this morning, sloppy English.

Rust Programming RoundSparrow • 1 year ago • 100%

In lemmy_server, at this code point: https://github.com/LemmyNet/lemmy/blob/91c024fd987b61ac8892b9e7d1896ee4574751da/crates/db_schema/src/impls/comment.rs#L62 How would I determine if it was an SQL UPDATE or INSERT and skip the remaining blocks of code that do work that does not need to be repeated when a comment edit (UPDATE) is being performed. Thank you.

www.youtube.com

Secrets that we shared, mountains that we moved

or federation.lemmy as root... allowing: federation.kbin federation.lemmy etc. And it emphasizes owner/operator/home of community, instance_name (subdomain/domain name).

late May... what I saw was lemmy.ml turned off new user signup, community creation, and load-shed by letting the server error out. now lemmy.world is bigger in content and activity but the drastic blocking of incoming links to comments is in place. I think the number of active users has less to do with the performance problems than the amount of data in the database. The heavy use of JOIN and custom account-specific filters. An account with a large block list and a huge list of subscribed communities likely performs wildly different when listings posts than a non-logged in user.