Bizarrely it feels way more active, the people leaving were never going to contribute anyway and that’s fine. It seems to be stabilizing at a good amount of content per day.
I do not think people understand. Lemmy is not going to become a forum guzzling behemoth like Reddit. Nothing will. The userbase will expand over time, as people on Reddit start getting fed up, and at some point there will be a combination of aggressive censorship (if IPO goes through) and privacy intrusive authentication. Many hobbyists do not like it, and are only quiet because they can make unidentifiable accounts, even if it requires official Reddit apps or websites to access.
People do not even understand how platforms work. Lemmy has become a non-mainstream, sane platform with federation that is not a shithole like any other previous alt-right failed Reddit clones. There is plenty activity for what are initial days, as users figure out the signup part and the cultural differences between Lemmy and Big Tech social media like Reddit. As a long term Reddit user, Reddit has been becoming shittier by the day, and is largely used due to decades of valuable posts and comments, and niche hobby communities. Ones that exclusively use frontpage are worthless audience and overlap hugely with Tiktok and Instagram users.
I think this is a fundamental property of social media. It’s a basic catch-22 - you need new users to attract new users. Sometimes a seismic shift will occur like the migration from MySpace or Digg, but neither of those websites were as big as any of the big social media sites are now, so the gravity well wasn’t nearly as strong.
People will just normalize the new anti-user features and get used to them.
It’s a basic catch-22 - you need new users to attract new users.
This is the best thing about federation.
If the Fediverse became really popular and I created a new alternative to Lemmy/Kbin that was significantly better than both, it would be way easier to gain the momentum required to become a real player in the field compared to trying to compete with Reddit when most people aren’t in the Fediverse yet.
Yeah people act like Reddit has some inevitable death date because Digg did, but the reality is we are getting further and further away from a time in which a big social media entity actually DID die. I mean people say Facebook is old and washed but it’s still growing in users and has been non-stop for over 15 years. The only one that has died since the end of Digg was Vine, and that was partially just because its owners didn’t really care about its fate anymore.
The only one that has died since the end of Digg was Vine, and that was partially just because its owners didn’t really care about its fate anymore.
Vine was killed by facebook & its regulatory capture. Otherwise it would’ve killed facebook and we never would’ve gotten tik tok (for better or worse).
FB, the gram and now twitter are dying. Just because they still exist doesn’t mean they’re not on their way out. Anyone with accounts on the first two can tell you that the number of active users on their feeds has been the same people for 5-10 years and are dwindling (and the feed of the third is lmao since boosting paid user content). Their traffic numbers might look fine but that’s because they lie about those numbers and they make it impossible to delete accounts.
Most importantly, it’s been 15 years since any of these companies couldn’t get free financing. Often a focus on profitability results in misplaced user-hostility over short-sighted moves, which is what killed a lot of companies in 99 and again in 2007. We just haven’t had a financial climate that requires it since. Hilariously this climate would make it a perfect time to take twitter private to push it past the other two and tik tok but it got bought by the worst failson the world has ever seen.
Don’t mistake still existing for not being dead. Digg.com still employs dozens of people, that doesn’t mean its been a useful link aggregator for the last 12 years.
It always dies down after the initial hype. It seems pretty stable now. Compare it to pre-exodus and it is still like hundreds of times more popular then before.
Yeah, still we lack variety because our algo doesn’t do a good job of promoting smaller communities. I’d like a lot more niche subs get more popular rather then our few dozen or so that have gotten big, which is still a good thing don’t get me wrong.
I feel that you underestimate how stubborn I can be with low effort comments. I’ve been making off color, not particularly funny attempted witty comebacks on BBSes, the Internet, and then the World Wide Web for longer than… Oh, it’s been since the early 90’s now. Lemmy is the latest stomping grounds and I’m not giving up here just yet.
Pretty sure it’s going to just be like 12 of us. If the third party app thing on reddit didn’t drive users here, unfortunately I don’t think anything else will. At this point if you are already content with the reddit app it’s going to be a hard sell to say, yeah come check out Lemmy, it’s like reddit but if you have a question about your sick betta fish instead of getting a helpful answer in a few minutes, you need to first create a betta fish community, then go back on reddit and recruit users to your Lemmy community. Post content on it daily to maintain interest, and then, if you are really lucky, ask your question and wait a few months and maybe if your fish is still alive (doubtful), you might get a response, but it will probably be just be an anticapitalist shit-post. I’m sorry to say it is this way, but this be the way that it is.
Hexbear has been very active for 3 years before we even federated. There’s plenty of room for growth. We’re not going to become reddit (and that’s a good thing) but acting like it’s just going to die (or is already dead) is just ridiculous
Hexbear has become a cesspool. Not exactly a great example to aspire to. I actually wish we would become very much like Reddit used to be back in the day. I very much enjoyed that experience and want it back. I’m sure plenty of others do to. Im just disappointed that it seems much more likely that Lemmy fizzles than soars. I can’t emphasize enough how big and bad a deal the stripping of third party API access to reddit data is. I wish more people felt more strongly about this beyond posting pictures of John Oliver. Not sure if you are old enough to remember when high quality RSS feeds were a thing, but this direct access to data that users could custom curate was amazing. When you take control of how users consent data, you start to take control of the users. Lemmy has immense potential and at face value people are largely fed up with being manipulated and taken advantage of by internet giants, but most are clearly not fed up enough to leave their comfort zone. Spez and the others are well aware of this and happy to take advantage. It takes a ton of effort to keep something like Lemmy afloat. Just like a new restaurant, if after a few months it’s not taking off, it’s pretty unlikely to do so with more time. I hope I’m wrong, but the Spez nonsense was a huge gift to growing Lemmy, and in the grand scheme of things the effect was quite small.
Hexbear is an aggressively inclusive space full of thoughtful people who are passionate about making the world a better place and educating their fellow man. If every online space were more like hexbear we’d all be much better off
I actually wish we would become very much like Reddit used to be back in the day. I very much enjoyed that experience and want it back. I’m sure plenty of others do to
Oh yeah back in the good old days of /r/jailbait and /r/creepshots and /r/sexwithdogs
Lol… so inclusive wanting to bring down America, listing out a bunch of people you’d love to get rid of (anyone you consider bourgeoise), unyielding fanboying of China…
I think the overall Lemmy experience has gone downhill since hexbear federated. I also think seeing the propaganda posts constantly hitting the All feed will drive people away. Are there even any non-political hexbear communities? All I ever see are anti-is and pro-China posts.
I still can’t wrap my head around being so pro-trans rights (a fabulous thing), yet in the same breath simping for China. Not the most stellar LGBTQIA+ record there.
Everything is political. We have comms that we don’t consider political, but you would still consider them political because you would rather they cater to your politics rather than ours. We have !games for example, but because it’s not a liberal comm you would consider it political.
I still can’t wrap my head around being so pro-trans rights (a fabulous thing), yet in the same breath simping for China
You’re literally professing your support for trans rights and simping for the US in one comment so you must be mega confused
We have comms that we don’t consider political, but you would still consider them political because you would rather they cater to your politics rather than ours. We have !games for example, but because it’s not a liberal comm you would consider it political
This sounds like treating politics like religion. No thanks.
I still can’t wrap my head around being so pro-trans rights (a fabulous thing), yet in the same breath simping for the USA. Not the most stellar LGBTQIA+ record there.
I live in the US and it’s pretty bad for LGBT people here. I don’t feel the need to cheerlead US interventions because they have a coat of rainbow paint.
Good for you! They cloak not only in that, but in Human Rights and Democracy. Yeah, right, sure Sleepy Joe. :-) My gay daughter hates Putin and loves the war because Russia doesn't accept homesexualtiy. I think that's a very poor reason to go to war, especially when the raesons stated aren't true. It's colonialism all over again. Russia has all the raw materials and rare earth metals that the west covets.
> I live in the US and it’ pretty bad for LGBT people here. I don’t feel the need to cheerlead US interventions because they have a coat of rainbow paint.
“What if they invaded, pillaged, and coup’d the countries WE were going to invade, pillage, and coup! Can you imagine the tragedy?! Better that us, The Good Guys, exploit these countries than The Bad Guys!”
I still can’t wrap my head around being so pro-trans rights (a fabulous thing), yet in the same breath simping for China. Not the most stellar LGBTQIA+ record there.
Yeah, it reminds me of the behavior found on 4chan. A lot of the things I’ve heard about hexbear - someone made a mega post about their userbase and how it’s filled with trolls, just lines right up with 4chan type mentality and pushing contrarian ideologies that are meant to put people in an uproar - like ragebaiting.
And then all hear from hexbear users is this creepy rhetoric about how they’re free thinker with ideas or some shit. Sounds like what Jehovah Witnesses say to get people to join their cult.
I am critical of China and I’ve never been attacked for it on Hexbear. It’s ok if you approach topics in good faith and have nuanced arguments rather than just “CPC evil”.
Personally I think wanting to destroy the American state as it is today and historically is actually very inclusive.
how come everything I hear about hexbear sounds like 4chan though? Like there even seems to be mimic of the 4chan lgbt board mentality coming from hexbear users.
You think collectively antagonizing, using “libs” in a derogatory form and calling others “imperialist running dogs” constitutes as defense and not toxic behavior?
Calling out imperialist running dogs isn’t toxic. Just like being aggressively anti-racist isn’t toxic. If a person upholds capitalism, which is by nature exploitive and anti-egalitarian, they are toxic and deserving of rebuke.
So as long as they don’t ideologically agree with you it’s acceptable to be toxic towards them, because their “wrong ideology” makes them toxic?
Are you also aware that most of the proletariats unknowingly uphold capitalism? Considering you say they’re toxic are you against the proletariat or are you a fake socialist trying to create a class divide, the ones who agree with you and the ones who don’t, within the proletariat?
if you asked questions in good faith, you’d know that the community is also aggressively welcoming to such people, even when we disagree. but you don’t so you won’t.
Let’s forget forget about the rest of our discussion and focus solely on the very first response you wrote to me. Based on that response I could’ve applied that same thought process you just described, decided that you’re here in bad faith and respond in the way Hexbear users tend to reply. And all this current discussion wouldn’t have ever happened because based on that response you’d believe I’m here in bad faith and responded in kind. In fact that way no discussion would’ve happened.
The way we communicate is prone to errors and misinterpretations. It’s why I’m focusing on your your first response because it’s an excellent example of miscommunication. You used “you” which implies it’s directed at me, but in a later response you clarify that it wasn’t directed at me. Thus discussions require a certain level of benefit of doubt, because it’s actually very easy to misrepresent what was said and just as easy to misinterpret what was said. I gave you that benefit of doubt and we seem to be having a rather civil discussion. And I’ve already somewhat explained what would’ve happened if I hadn’t given it. That benefit of doubt is crucial if you’re wanting to discuss in good faith, because you need to give a chance to correct miscommunications.
And that’s why I think the thought process you’ve described is a bad faith thought process, because it doesn’t give the benefit of the doubt. At least that is my general experience with Hexbear users. Someone says something disagreeable in a manner that could be misinterpreted in the way you described and it’s very rare to see a Hexbear user give the benefit of doubt. Instead you see, well everything here. One guy says Hexbear is a cesspool and seemingly only one of you gives him some benefit of doubt, the rest very much troll, antagonize, make snide remarks etc. The vast majority of you responded in the same way you’d claim someone else is responding in bad faith. What if he previously had a miscommunication that Hexbear users didn’t give benefit of the doubt either? He gets piled on in a manner you’ve described as bad faith. With those bad faith responses he now believes you are all acting bad faith, hence the cesspool remark. And what is the response he gets? More bad faith responses from Hexbear users because the vast majority don’t give him any benefit of doubt.
You think others act out in bad faith so you respond in bad faith which makes others believe you act in bad faith which prompts more of you to act in bad faith. It’s a a bad faith feedback loop. Genuine question, what’s the goal of such behavior?
my first response was directed at you. the second was not. I was answering the question you asked.
Genuine question, what’s the goal of such behavior?
to apply adequate pushback to erroneous understandings of the world. the goal isn’t to convince the interlocutor. it’s to encourage the people reading to investigate the topic. on many of the topics in question, the history and ideologies involved take entire books to deconstruct - doing so in an internet comment is extraordinarily difficult. the people we’re talking to don’t even agree with us on the meanings of basic words - there’s not even a basis for debate. because such debate is so unproductive, the aggressive tone encourages many people to stop and ask more serious questions. this undoubtedly works because so many of the posters on hexbear responded in exactly that way here or on reddit at some point in the past. and when they asked those questions, they got detailed answers, including links to sources so they could investigate for themselves. in actual fact, many of the people on hexbear received exactly the kind of aggressive pushback you’re decrying and ended up eventually convinced that our viewpoint had something to offer.
and as point of fact, when someone starts asking questions, we’ll tell each other to stop treating them so harshly cause they’re acting in good faith. that courtesy is not extended to people who continue down a path of antagonism. nor is it offered to someone who devolves into racism, transphobia, or other forms of bigotry. one of the benefits of the aggressive approach is that it encourages so many bigots to immediately out themselves.
lastly, civility is not an unmitigated good unto itself. civility is the false peace – it masks tensions, pretending they don’t exist. real peace is not civility – it’s a state in which tensions are brought to the fore so they can actually be resolved. civility is a white, middle class sensibility – our world is incredibly fucked up and the people affected by it do not owe anyone that masking of the horrors of our world. nor do we owe anyone an education they will neither ask for nor appreciate.
to apply adequate pushback to erroneous understandings of the world. the goal isn’t to convince the interlocutor. it’s to encourage the people reading to investigate the topic. on many of the topics in question, the history and ideologies involved take entire books to deconstruct - doing so in an internet comment is extraordinarily difficult. the people we’re talking to don’t even agree with us on the meanings of basic words - there’s not even a basis for debate. because such debate is so unproductive, the aggressive tone encourages many people to stop and ask more serious questions. this undoubtedly works because so many of the posters on hexbear responded in exactly that way here or on reddit at some point in the past. and when they asked those questions, they got detailed answers, including links to sources so they could investigate for themselves. in actual fact, many of the people on hexbear received exactly the kind of aggressive pushback you’re decrying and ended up eventually convinced that our viewpoint had something to offer.
Maybe at one point but if recent events are of any indication that is hardly true anymore. The reason these defederation threads prop up if your aggressive presentation made people inquisitive. It’s an indication that people respond negatively to such behavior. And I’m inclined to believe people respond more negatively than positively because the responses I’ve seen about subject I know about have been less about making people inquisitive and more about just throwing in their face that they don’t understand something the same way you do without explaining anything.
lastly, civility is not an unmitigated good unto itself. civility is the false peace – it masks tensions, pretending they don’t exist. real peace is not civility – it’s a state in which tensions are brought to the fore so they can actually be resolved. civility is a white, middle class sensibility – our world is incredibly fucked up and the people affected by it do not owe anyone that masking of the horrors of our world.
I disagree. Yes, there’s no space for niceness as you need to be ready for conflict to test your ideas and beliefs. But it doesn’t mean we should completely disregard civility. Are you really going to take me seriously if I call you shitstain in this post, bitch lover the next, steamy turd the next etc? I know I wouldn’t take anything you say seriously if you came with such disrespect. Similarly I have no problem trolling the living shit out of you, but that already means I have zero respect for you or your beliefs and nothing you say or do will even get true critical examination, outside of how to better troll back. I could easily derail this discussion, drag you down into shit slinging contest and then sling shit until you stop responding but that’s pretty far from civil discourse and not at all constructive. Discourse needs to have some mutual respect and if none is given then none is received, which means the discussion will go nowhere. The world is fucked but slinging shit between eachother doesn’t really unfuck the world.
nor do we owe anyone an education they will neither ask for nor appreciate
And this is probably where we completely disagree. Your stance is that nobody asks or appreciates it so we shouldn’t give it unless they really ask. I believe we should give it regardless because it’s still a chance for them to open up to something new. I would’ve never familiarized myself with Das Kapital if not for someone else explaining to me that Marxist understanding of “capital” is not the same as “capital” taught to you in school. Had someone told me “How did they get the fucking money mf?” I would probably still believe capitalism is not that bad. Explaining socialism to someone who won’t listen doesn’t take a piece out of me, so why should I act like it does? To me it’s a net positive. If someone listens and becomes a socialist that’s good and if someone doesn’t listen then really nothing actually bad happens because as you said, the world is fucked regardless.
So as long as they don’t ideologically agree with you it’s acceptable to be toxic towards them, because their “wrong ideology” makes them toxic?
Think about this in the context of, idk, race science or something. Let’s say you have someone who is openly a big fan of Charles Murray, owns a copy of The Bellcurve, gets the whole nine yards. Would you deny that such a person is necessarily toxic?
Actually, without any other context, I would. I would label them as misguided. Just because they believe in what I believe as the wrong thing doesn’t mean they’re automatically toxic. If they’re unwilling to even consider alternate perspectives or decide to just be antagonistic then they’re toxic.
So if they come in swinging about how they “don’t deny” that black people are genetically less intelligent and say that their opposition is either propagandized or propagandists, that would tilt the scales for you?
We might be running into a Nazi Bar, paradox of tolerance type issue here. If you treat him that way, there’s a fair chance that he’s just going to use the opportunity to propagandize to whoever will listen.
Right, being in a federated instance isn’t lurking. Glad we agree.
And no I don’t, I’ve had a few meaningful discussing with some true leftists but most of y’all only have a knee jerk reaction available to criticism. You end up pushing near-allies away for lacking your dogmatic devotion, seemingly to xi? As you have a bunch of photos of him saved?
You should come to Hexbear. Have a real look around. Not being a fan of NATO bombing (like Yugoslavia) or puting children in cages at concentration camps at the border, doesn’t mean someone is a dogmatic follower of China. But they are far better that USA, that’s for sure.
But come to Hexbear, let’s challenge years and years of western indoctrination. And we also have an awesome colletion of emojis! (or, as you call it, “photos”).
Summed up my feelings too. Reddit’s larger communities were trash, but for really specific questions, it was unbeatable. Not to mention the fact that most Lemmy pages are either tech-related or tankie propaganda. There’s very little in the way of active hobby/lifestyle boards so unless you’re in either a nerd (non-derogatory) or a communist (derogatory), Lemmy’s not got much going on for you
There’s very little in the way of active hobby/lifestyle boards so unless you’re in either a nerd (non-derogatory) or a communist (derogatory), Lemmy’s not got much going on for you
There’s a bunch of really niche subs I used to be on. Finding info on some of my old cars has been a bitch since I cut out reddit but it’s taught me a lot about self-reliance haha
Well, i am here directly due to reddit policy changes. The loss of a viable mobile option forced me here. I can’t believe I am not an average case. I am enjoying this experience so far and will definitely spread the word. But i will continue to use reddit on the computer… I am surprised that there are only 60,000 of us here though.
Long time hexbear user, I’ve actually had pretty good luck getting input on non-political questions. No sick fish, but I’ve asked quite a variety of questions and gotten help. Maybe I would have gotten a higher quality answer on Reddit, but my experience with modern reddit (last 6ish years) has been hit or miss. Reminds me in a way of the forums I used back in the really 2000s. Even though the forums I was on were primarily oriented around tabletop gaming, the “general/off-topic” sections would have quite a variety of people and interests. And those people, since they all had a common interest, were far more talkative and generous with their time than what I’ve experienced in Reddit. IMO this makes up for the smaller population. Hexbear has that vibe for me, just with a non-sectarian socialist shitposting focus. Which works for me.
And at the beginning everyone was worried about “Eternal September”. It’s only been two months.
People will come in waves, instances and communities will grow and die, just like how it was on reddit, we’ll probably start seeing meme/politics free or even more specialized instances soon. But all of this is going to take time.
The turning point will be when companies/websites start spinning up their own Lemmy instances as their official one to replace their forums, which I think will happen.
So, being on Lemmy is a long term investment for me.
Hopefully this works out, gotta get that first mover advantage in, then Lemmy’s only real celebrity will be recognized as the marketing genius that she is. :)
I like Lemmy better when it’s when it’s nicer and quieter a month ago honestly.
Yeah, so that’s why I’m expecting way more alt hopping and defederations and people splitting into smaller groups soon until everything finally settles.
One of advantage of the fediverse honestly that it prevents powertripping mods, since it’s so easy to move to another community on the same topic on a different instance with different admins and mods, and while a person can be banned off a particular comm or instance, they can’t be banned from Lemmy as a whole, so reputation matters a lot more right now when everybody kinda knows each other here.
No, they’re the mod of an android comm on here so obviously not her. I think the roleplayers from back in the LiveJournal days are showing up here cause you can still grab newish usernames to impersonate someone.
The turning point will be when companies/websites start spinning up their own Lemmy instances as their official one to replace their forums, which I think will happen.
I don’t know if this is going to happen, and to be honest I hope it doesn’t. Lemmy is not designed to be a forum and shouldn’t try to be used as a replacement for one.
Lemmy is a link aggregator. Yes it can serve a lot of (if not all) the functions of a forum but it’s not designed to be a drop-in replacement for something like Discuss or phpBB. It’s different enough that I feel like calling it a forum is not the right term.
Id say finding the latest comment is harder here. Sure, its not that hard when looking post replies. But comment replies? They can be nested, pretty much buried behind the “See more replies” button.
Oh i wouldnt say its worse at all. I prefer nested replies all the way. Regardless, i still wouldnt say lemmy fits the forum format, again due to the way you access the most recent replies.
In a forum thread, you go to the last page and youve found the latest comment. In a Lemmy post, even if the nested comments arent hidden, its not obvious at first glance which one is the latest comment.
Also, if you “bookmark” a forum thread, youll get notified of any new replies in said thread. On Lemmy, you can check the latest comments from an entire instance or community. But not for a specific post.
Again, id never phrase lemmys format as worse, for i greatly prefer it. But i wouldnt consider it a forum. It simply displays the information diferently
As I said in a comment below, I would like this to be a signal for interest groups to choose one of the dozens communities they have, stick to one and make it grow.
Looking at gaming or books, always seems detrimental to have the . world, .ml, .sh.itjust.works and so on with the same content posted everywhere.
But just remember: Some of those people that are not staying are the types of people you wouldn’t want to interact with anyway. If the roughly 10k people who quit were Nazis (for example), it’s a good thing.
Yeah, I tend to think that most of the people who left wouldn’t be valuable members of the community anyway. Maybe they’re too impatient to deal with software that isn’t fully mature, maybe they can’t deal with the fact that most Lemmy instances are somewhere between leftish and outright communism, or maybe the somewhat chaotic nature of the fediverse turns them off. Whatever. I hope they find something that suits them.
I also hope, for their own sake, that the “something” doesn’t involve going back to reddit.
Right. Everything negative about Lemmy is being turned into a positive for some reason. Truth is this is still a difficult concept for a lot of people to get on board with and the overall reliability of instances leaves much to be desired. All we need to do is continue to contribute and see what takes off.
If the stats are accurate then this is not necessarily due to people being unhappy and leaving as both comments and posts are still stable - indicating that the lower active count are lurkers, duplicates or otherwise non engaging accounts.
Yeah, I wouldn’t be surprised if duplicate accounts are a part of this but that seems like it would be a natural part of growing pains for lemmy. The way the fediverse is built would suggest that people who are serious about long-term participation may bounce around a bit. For example, I joined in June but in that time I still managed to test out two other instances before settling on a third that seemed to strike the ideal balance between admin policies and reliable uptime to suit my needs.
Let the servers keep crashing, tell everyone to add new instances to help with performance, which puts 1500 rows into the database tables that used to have 50 rows and invokes a massive federation 1-vote-1-https overhead… causing more crashing… all the while ignoring the SQL design of machine-generated ORM statements and counting logic hidden in the background triggers.
… keep users off your sever as a method of scaling by crashing. It’s one of the more interesting experiences I’ve had this year! And I spent all of February and March with the release of GPT-4… which was also interesting!
They are perfectly normal. Unlike giant corporations, the people who run Lemmy don’t have the money to support a fleet of failover servers that take over when the main server goes offline. That’s basically the only reason you don’t see lots of downtime from major corporations: investment in redundancy, so when something breaks, a perfect copy takes over. Server crashes happen all the time for major corporations, you just never see them due to investment in redundancy.
That’s the difference between a community and a company. One takes actual investment from the community as a whole, and the other ruthlessly exploits for profit.
the people who run Lemmy don’t have the money to support a fleet of failover servers that take over when the main server goes offline.
That has nothing to do with the issue I’m talking about. Every server with the amount of data in them would fail. Doesn’t matter if you had 100 servers on standby.
The Rust logic for database access and PostgreSQL logic in lemmy is unoptimized and there is a serious lack of Diesel programming skills. site_aggregates table had a mistake where 1500 rows were updated for every single new comment and post - and it only got noticed when lemmy.ca was crashing so hard they made a complete copy of the data and studied what was gong on.
Throwing hardware at it, as you describe, has been the other thing… massive numbers of CPU cores. What’s needed is to learn what Reddit did before 2010 with PostgreSQL… as Reddit also used PostgreSQL (and is open source).
That’s basically the only reason you don’t see lots of downtime from major corporations: investment in redundancy,
Downtime because you avoid using Redis or Memcached caching at all costs in your project isn’t common to see in major corporations. But Lemmy avoids caching any data from PostgreSQL at all costs. Been that way for several years. May 17, 2010: “Lesson 5: Memcache;”
As I said in my very first comment, server crashing as a way to scale is a very interesting approach.
EDIT: Freudian slip, “memecached” instead of Memcached
If anyone bothered to actually look at the SQL SELECT that Lemmy uses to list posts every time you hit refresh it would be blindingly obvious how convoluted it is. yet the community does not talk about the programming issues and instead keeps raising money for 64 core hardware upgrades without recognizing just how tiny Lemmy’s database really is and how 57K users is not a large number at all!
your original one, friend. I wouldn’t have argued this point if you had started here.
I mentioned “ORM” right in my first comment.
<span style="color:#323232;">SELECT
</span><span style="color:#323232;"> "post"."id" AS post_id, "post"."name" AS post_title,
</span><span style="color:#323232;"> -- "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",
</span><span style="color:#323232;"> -- "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local",
</span><span style="color:#323232;"> "person"."id" AS p_id, "person"."name",
</span><span style="color:#323232;"> -- "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated",
</span><span style="color:#323232;"> -- "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",
</span><span style="color:#323232;"> -- "person"."bot_account", "person"."ban_expires",
</span><span style="color:#323232;"> "person"."instance_id" AS p_inst,
</span><span style="color:#323232;"> "community"."id" AS c_id, "community"."name" AS community_name,
</span><span style="color:#323232;"> -- "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted",
</span><span style="color:#323232;"> -- "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner",
</span><span style="color:#323232;"> -- "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods",
</span><span style="color:#323232;"> "community"."instance_id" AS c_inst,
</span><span style="color:#323232;"> -- "community"."moderators_url", "community"."featured_url",
</span><span style="color:#323232;"> ("community_person_ban"."id" IS NOT NULL) AS ban,
</span><span style="color:#323232;"> -- "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published",
</span><span style="color:#323232;"> -- "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local",
</span><span style="color:#323232;"> --"post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank",
</span><span style="color:#323232;"> -- "community_follower"."pending",
</span><span style="color:#323232;"> ("post_saved"."id" IS NOT NULL) AS save,
</span><span style="color:#323232;"> ("post_read"."id" IS NOT NULL) AS read,
</span><span style="color:#323232;"> ("person_block"."id" IS NOT NULL) as block,
</span><span style="color:#323232;"> "post_like"."score",
</span><span style="color:#323232;"> coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") AS unread
</span><span style="color:#323232;">
</span><span style="color:#323232;">FROM (
</span><span style="color:#323232;"> ((((((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> "post_aggregates"
</span><span style="color:#323232;"> INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_person_ban"
</span><span style="color:#323232;"> ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = 3)))
</span><span style="color:#323232;"> LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">WHERE (((((((
</span><span style="color:#323232;"> ((("community"."deleted" = false) AND ("post"."deleted" = false)) AND ("community"."removed" = false))
</span><span style="color:#323232;"> AND ("post"."removed" = false)) AND ("post_aggregates"."creator_id" = 3)) AND ("post"."nsfw" = false))
</span><span style="color:#323232;"> AND ("community"."nsfw" = false)) AND ("local_user_language"."language_id" IS NOT NULL))
</span><span style="color:#323232;"> AND ("community_block"."person_id" IS NULL))
</span><span style="color:#323232;"> AND ("person_block"."person_id" IS NULL))
</span><span style="color:#323232;">ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;">LIMIT 10
</span><span style="color:#323232;">OFFSET 0
</span><span style="color:#323232;">;
</span>
First optimization is to not fetch every field and prune it down. For example, it gets public key and private key for every community and user account - then does nothing with them. That’s just pushing data between Rust and PostgreSQL for no reason. That kind of thing is pretty obvious… the huge number of things listed after “SELECT”.
The whole approach is what I recently described as: make a JOIN fusion implosion bomb, then wait for null columns to fall out
There are short-term and long-term solutions. Right now there is already a new feature that will add one more JOIN that is pending merge… “instance blocking” by each single user.
Based on the server overloads and resulting crashes, I think some obvious solutions would be to remove post_aggregates table entirely and just throw more columns on the post table… I’ve seen people do stuff like that. But really you have to have a concept of core foundation.
To me the core foundation of Lemmy data is that people want fresh meat, when world events get into a frenzy, they want to F5 and get the LATEST post and the LATEST comments. Data should have a big wall between the most recent 5 days and everything else. It’s the heart of the beast of human events and a platform like this.
From that perspective, that fresh posts and fresh comments mean everything, you can optimize by just doing a INNER SELECT before any JOIN… or partition the database TABLE into recent and non-recent, or some out-of-band steps to prepare recent data before this SELECT even comes up from an API call… and not let PostgreSQL do so much heavy lifting each page refresh.
If I remember, I’m gonna look into that tomorrow when I’m not on a phone screen. Not that I could contribute anything, but this seems like a good opportunity to learn some advanced stuff. Thanks for your answer!
Community stuff can work well if done right. For example you don’t see Debian repositories constantly crashing.
I don’t follow your comment, are you suggesting I said something negative about open source project communities? I was talking about the Lemmy social media communities who actually comment and fund the 64-core server upgrades without asking why the site crashes with only 57K users… the people who comment and post on Lemmy… not the “open source” programmer community, but the social media community of Lemmy.
I’ve largely given up on pull requests… for sake of sanity. But I waded back in…
I made a pull request today… and I very strategically choose to do it with minimal of features so that it would just go through… and I got lectured that JOIN is never a concern and that filtering based on the core function of the site (presenting fresh meat to readers) was a bad use of the database. I’ve never seen hazing on a project like this. Memcached and Redis should be discussed every day as “why are we not doing what every website does?”, but mum is the word.
I already feel like I have to keep sticking my neck out to get them to question if using the ORM and a dozen JOIN statements isn’t a problem… but I guess I’ll link it: github.com/LemmyNet/lemmy/pull/3900
As stated on my Lemmy user profile, I’m “RocketDerp” on GitHiub.
Honestly, the reason I keep making noise is because I’m sick of Lemmy crashing all the time when I come to use it… and I am on many servers that this happens. I really am not trying to piss off the developers, I even said I felt like I am being hazed, and I feel like hazing in general might explain what is going on with how much they are avoiding the elephant in the ROOMthat ORM and a dozen JOIN might be the cause! Let alone the lack of Redis or Memcached addition being avoided, that’s a second elephant on the second floor tap-dancing… GitHub Issue 2910 was the straw that broke my back weeks ago, it took months for them to address it when it could be fixed in a couple hours (and it was weeks before the Reddti API deadline at the end of June… and issue 2910 was neglected). The whole thing was a nightmare for me to watch…
I’m glad for you that mental control is so trival and you aren’t near death in your life from your brain damage.
Go outside and take a breath
I just got back from dinner ant the months of hazing I’ve witnessed hasn’t gone away. The level of social games being played with PostgreSQL in this project are levels beyond anything I’ve encountered in my 50+ years alive. And I’ve first hand seen Bill Gates and his team do all kinds of odd things to groups.
I am at a total loss to explain why such fundamentals of basic relational database are avoided in this project. If it isn’t social hazing, what is it?
I notice the scientific facts of server crashing and SQL statements you won’t discuss, but you sure dish out the social advice for me to “move along” like a Jedi mind trick. Let’s talk about the human attraction to truth and honesty since you are so great at handing out life advice to people. What do you know about the works of Marshall McLuhan on media?
Repeating: Its’ as if the mere concept of Redis or Memcache never existed… and that nobody ever heard of JOIN performance problems. If it isn’t extreme social hazing, what is it?
your comments there are exceptionally aggressive. you accuse the developers of “hazing” you when they disagree with a technical decision you’ve made, and then insult them, posting wild rants. i’ve seen you do this many times. you need to step back, relax, and not take technical feedback so personally
ild rants. i’ve seen you do this many times. you need to step back, relax, and not take technical feedback so personally
I’ve stepped back and watched them ignore the issue since May when all the servers were crashing. Every single Lemmy server was falling over while they ignored the PostgreSQL problems.
The mistakes are obvious and huge. These are not minor topics.
our comments there are exceptionally aggressive. you accuse the developers of “hazing” you
I think they are hazing the entire World Wide Web, Reddit users, etc. How else can you explain such basic SQL problems that they have allowed to go on for so long?
Its’ as if the mere concept of Redis or Memcache never existed… and that nobody ever heard of JOIN performance problems. If it isn’t extreme social hazing, what is it?
the poor database performance of Lemmy seems to be what puts LemmyWorld down so often
agreed. but lashing out at developers who are just trying to find out why the db performance is subpar with accusations of “hazing” is incredibly aggravating and antithetical to the entire concept of FOSS. there is a reason why almost every open source organization uses codes of conduct
This is unfortunate to hear. Have you considered creating a proof-of-concept fork with synthetic data that demonstrates how much more performant a cached, filtered approach would be? I think a magnitude or two improvement of some key metrics with heavy simulated load would be quite convincing.
Of course, that would be an insane amount of work, especially if it would get ignored, but something to consider!
Of course, that would be an insane amount of work, especially if it would get ignored, but something to consider!
I already did an insane amount of work to populate a Lemmy database with over 10 million posts. It is so incredibly slow out of the box that the normal API would take days to accomplish this. i had to rewrite the SQL TRIGGER logic to allow bulk inserts.
Here is my work on that:
<span style="color:#323232;">DROP TRIGGER site_aggregates_post_insert ON public.post;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE TRIGGER site_aggregates_post_insert
</span><span style="color:#323232;"> AFTER INSERT ON public.post
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION site_aggregates_post_insert();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER community_aggregates_post_count ON public.post;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE TRIGGER community_aggregates_post_count
</span><span style="color:#323232;"> AFTER INSERT ON public.post
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION community_aggregates_post_count();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER person_aggregates_post_count ON public.post;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE TRIGGER person_aggregates_post_count
</span><span style="color:#323232;"> AFTER INSERT ON public.post
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION person_aggregates_post_count();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">no Lemmy-delete or SQL DELETE to be performed during this period.
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.site_aggregates_post_insert() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;"> UPDATE site_aggregates SET posts = posts +
</span><span style="color:#323232;"> (SELECT count(*) FROM new_rows WHERE local = true)
</span><span style="color:#323232;"> WHERE site_id = 1
</span><span style="color:#323232;"> ;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.community_aggregates_post_count() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> community_aggregates ca
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> posts = posts + p.new_post_count
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT count(*) AS new_post_count, community_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> GROUP BY community_id
</span><span style="color:#323232;"> ) AS p
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> ca.community_id = p.community_id;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">no Lemmy-delete or SQL DELETE to be performed during this period.
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.person_aggregates_post_count() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> person_aggregates personagg
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> post_count = post_count + p.new_post_count
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT count(*) AS new_post_count, creator_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> GROUP BY creator_id
</span><span style="color:#323232;"> ) AS p
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> personagg.person_id = p.creator_id;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">***********************************************************************************************
</span><span style="color:#323232;">** comment table
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER post_aggregates_comment_count ON public.comment;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">/*
</span><span style="color:#323232;">TRIGGER will be replaced with per-statement INSERT only
</span><span style="color:#323232;">*/
</span><span style="color:#323232;">CREATE TRIGGER post_aggregates_comment_count
</span><span style="color:#323232;"> AFTER INSERT ON public.comment
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION post_aggregates_comment_count();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">-- IMPORTANT NOTE: this logic for INSERT TRIGGER always assumes that the published datestamp is now(), which was a logical assumption with general use of Lemmy prior to federation being added.
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.post_aggregates_comment_count() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> -- per statement update 1
</span><span style="color:#323232;"> post_aggregates postagg
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> comments = comments + c.new_comment_count
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT count(*) AS new_comment_count, post_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> GROUP BY post_id
</span><span style="color:#323232;"> ) AS c
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> postagg.post_id = c.post_id;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> -- per statement update 2
</span><span style="color:#323232;"> post_aggregates postagg
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> newest_comment_time = max_published
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT MAX(published) AS max_published, post_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> GROUP BY post_id
</span><span style="color:#323232;"> ) AS c
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> postagg.post_id = c.post_id;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> -- per statement update 3
</span><span style="color:#323232;"> post_aggregates postagg
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> newest_comment_time_necro = max_published
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT MAX(published) AS max_published, post_id, creator_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> WHERE published > ('now'::timestamp - '2 days'::interval)
</span><span style="color:#323232;"> GROUP BY post_id, creator_id
</span><span style="color:#323232;"> ) AS c
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> postagg.post_id = c.post_id
</span><span style="color:#323232;"> AND c.creator_id != postagg.creator_id
</span><span style="color:#323232;"> ;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER community_aggregates_comment_count ON public.comment;
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE TRIGGER community_aggregates_comment_count
</span><span style="color:#323232;"> AFTER INSERT ON public.comment
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION public.community_aggregates_comment_count();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.community_aggregates_comment_count() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> community_aggregates ca
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> comments = comments + p.new_comment_count
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT count(*) AS new_comment_count, community_id
</span><span style="color:#323232;"> FROM new_rows AS nr
</span><span style="color:#323232;"> JOIN post AS pp ON nr.post_id = pp.id
</span><span style="color:#323232;"> GROUP BY pp.community_id
</span><span style="color:#323232;"> ) AS p
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> ca.community_id = p.community_id
</span><span style="color:#323232;"> ;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER person_aggregates_comment_count ON public.comment;
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE TRIGGER person_aggregates_comment_count
</span><span style="color:#323232;"> AFTER INSERT ON public.comment
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION public.person_aggregates_comment_count();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.person_aggregates_comment_count() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE
</span><span style="color:#323232;"> person_aggregates personagg
</span><span style="color:#323232;"> SET
</span><span style="color:#323232;"> comment_count = comment_count + p.new_comment_count
</span><span style="color:#323232;"> FROM (
</span><span style="color:#323232;"> SELECT count(*) AS new_comment_count, creator_id
</span><span style="color:#323232;"> FROM new_rows
</span><span style="color:#323232;"> GROUP BY creator_id
</span><span style="color:#323232;"> ) AS p
</span><span style="color:#323232;"> WHERE
</span><span style="color:#323232;"> personagg.person_id = p.creator_id;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">DROP TRIGGER site_aggregates_comment_insert ON public.comment;
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE TRIGGER site_aggregates_comment_insert
</span><span style="color:#323232;"> AFTER INSERT ON public.comment
</span><span style="color:#323232;"> REFERENCING NEW TABLE AS new_rows
</span><span style="color:#323232;"> FOR EACH STATEMENT
</span><span style="color:#323232;"> EXECUTE FUNCTION public.site_aggregates_comment_insert();
</span><span style="color:#323232;">
</span><span style="color:#323232;">
</span><span style="color:#323232;">CREATE OR REPLACE FUNCTION public.site_aggregates_comment_insert() RETURNS trigger
</span><span style="color:#323232;"> LANGUAGE plpgsql
</span><span style="color:#323232;"> AS $$
</span><span style="color:#323232;">BEGIN
</span><span style="color:#323232;">
</span><span style="color:#323232;"> UPDATE site_aggregates
</span><span style="color:#323232;"> SET comments = comments +
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> SELECT count(*) FROM new_rows WHERE local = true
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> WHERE site_id = 1
</span><span style="color:#323232;"> ;
</span><span style="color:#323232;">
</span><span style="color:#323232;"> RETURN NULL;
</span><span style="color:#323232;">END
</span><span style="color:#323232;">$$;
</span>
With this in place, 300,000 posts a minute can be generated and reaching levels of 5 million or 10 million don’t take too long.
That’s really cool work! It’s a bit beyond my pay grade, so I can’t really comment too much about it.
I had a look at the PR you mentioned, and again, while I can’t comment on the contents because I am a little out of my depth, may I voice my opinion on the exchange? This is coming from a place of trying to help, since I really do appreciate all the work you’ve put in and are putting in, and the fediverse can really use your talents, so I hope I don’t offend you.
From my reading, it didn’t appear that you were being ignored/hazed, and it seemed like the devs would have been open to your improvements. From working and leading big teams, I’ve noticed that communication and managing emotions is often much harder than writing code. In the thread, it appeared that communication had broken down on both sides (and seemed to have been the case in prior interactions too). Since you mentioned your struggles with autism in the thread, I wonder if that played a part in the tone of the devs perhaps being misinterpreted ? This is, of course only my interpretation, and I could be completely wrong.
Ultimately Lemmy itself is an example of trying to build a community and consensus amongst a broad and diverse group of people, who will often not see eye to eye.
In any case I would like to say I personally appreciate your hard work and really do hope you’re able to help make Lemmy better. Thank you!
may I voice my opinion on the exchange? This is coming from a place of trying to help, since I really do appreciate all the work you’ve put in and are putting in, and the fediverse can really use your talents, so I hope I don’t offend you.
Can you explain to me why it isn’t social hazing?
it didn’t appear that you were being ignored/hazed
Do you know how to read a SQL statement? I just can’t grasp how it isn’t social hazing. I’ve been reading SQL statements for decades, this is obviously a problematic one.
Can you offer alternate explanations of how 3 people could think that SQL statement isn’t … poor performing and gong to cause problems? And how an SQL statement without a WHERE clause took them months to discover and fix?
Extreme hazing is my best answer. I just can’t accept that the SQL statements don’t speak for themselves along with the server crashes. 57K users for 1300 servers is very… taking several seconds to load 10 posts…
Like I said, this was my interpretation based on reading that exchange. It’s difficult to convey tone or intention with text, but I didn’t detect hostility from the devs, but I did sense that they were frustrated that process wasn’t being followed. Perhaps they should not have gotten hung up on that, but it didn’t appear to be malicious.
Do you know how to read a SQL statement? I just can’t grasp how it isn’t social hazing. I’ve been reading SQL statements for decades, this is obviously a problematic one.
I do, and your arguments about the joins being problematic seemed solid. From having worked on systems with huge scale, I also agree that Lemmy doesn’t seem to be big enough to be brought to its knees by the volume of posts it’s processing. However, I’m far from an expert, so I don’t want to suggest any certainty about the root causes, especially as I don’t have the energy or inclination to dig as deep into it as I would to form that opinion.
I don’t know why they weren’t receptive, but perhaps they themselves felt attacked. I know that wasn’t your intention, but misunderstanding happen, especially over text.
Funny, because I’m a published author and expert on messaging systems… like Lemmy. Iv’e been building them since 1986 professionally.
There was a massive thread I posted dozens of comments on that came before today’s pull request… I suggest you read that too.
Did you notice them even acknowledge server crashes are happening? Do you think developers ever suggest Memcache or Redis? Or discuss how Reddit solved their scaling in 2010 with PostgreSQL?
but perhaps they themselves felt attacked. I know that wasn’t your intention, but misunderstanding happen, especially over text.
I don’t have any trouble understanding a bad SQL statement that has 14 JOINs and being told “JOIN is a distraction” after posting tons of examples.
Do we really need to spoon fed the stuff I did post?
Have you never seen social hazing in action? is it possible that I might be on to something going on psychologically besides my autism?
I can’t believe anyone thinks a server should be crashing with 1 user on it.
Have you never seen social hazing in action? is it possible that I might be on to something going on psychologically besides my autism?
Okay, I can’t speak to whether social hazing happened or not, but I can tell you that you’re making me extremely uncomfortable.
I started a dialogue, but at this point you’re now sending multiple messages for each of my replies, and asking a lot from me in terms of attention. I do not wish to continue this conversation, but I wish you all the best.
who would have predicted that Elon Musk would do all the wild things he did with Twitter. Reddit pissing everyone off in June… pretty odd how audiences are behaving in 2023 towards all this. Oh yha, Threads, that coming on the scene too. 2023 has really been odd for audiences.
The SQL speaks for itself, but I don’t know what’s going on in terms of why people are treating social media platforms like Lemmy, Twitter, Threads, Reddit this year so unusually. This SQL statement kind of thing has been covered in so many books, conferences, etc. It’s like forgotten history now in the era of Elon Musk X and Reddit Apollo times.
I don’t know what to say other than I can try to hire a translator or teacher to explain how this SQL problem is obvious and well understood 13 years ago. I mean, there was a whole “NoSQL movement” because of this kind of thing. But I clearly can’t get people to hear past all the Elon Musk, Threads, Lemmy from Reddit … and I’m left describing it as ‘social hazing’ or whatever is gong on with social media.
Lemmy has like 5 different Rust programming communities, but nobody fixing Lemmy. It’s surreal in 2023 the Elon Musk X days. I think it’s making all of us uncomfortable. The social movement underway.
Haha, indeed. Any time I see an open-source discussion (especially a heated one), I’m reminded about just how much effort it takes to contribute. I’m happy to just stick to browsing memes :P
joins are better than in queries with potentially thousands of inserted IDs.
Given that more than 8 JOIN statements is something PostgreSQL specifically concerns itself with (join_collapse_limit). I hand-edit the query with a single IN clause and the performance problem disappears. 8 full seconds becomes less than 200ms against 5,431,043 posts. And that 200ms is still high, as I was extremely over-reaching with “LIMIT 1000” in case the end-user went wild with blocking lists or some other filtering before reaching the final “LIMIT 10”. When I change it to “LIMIT 20” in the subquery, it drops almost in half to 115ms… still meeting the needs of the outer “LIMIT 10” by double. More of the core query filtering can be put into the IN subquery, as we aren’t dealing with more than 500 length pages (currently limited to 50).
<span style="color:#323232;">SELECT
</span><span style="color:#323232;"> "post"."id" AS post_id, "post"."name" AS post_title,
</span><span style="color:#323232;"> -- "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",
</span><span style="color:#323232;"> -- "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local",
</span><span style="color:#323232;"> "person"."id" AS p_id, "person"."name",
</span><span style="color:#323232;"> -- "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated",
</span><span style="color:#323232;"> -- "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",
</span><span style="color:#323232;"> -- "person"."bot_account", "person"."ban_expires",
</span><span style="color:#323232;"> "person"."instance_id" AS p_inst,
</span><span style="color:#323232;"> "community"."id" AS c_id, "community"."name" AS community_name,
</span><span style="color:#323232;"> -- "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted",
</span><span style="color:#323232;"> -- "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner",
</span><span style="color:#323232;"> -- "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods",
</span><span style="color:#323232;"> "community"."instance_id" AS c_inst,
</span><span style="color:#323232;"> -- "community"."moderators_url", "community"."featured_url",
</span><span style="color:#323232;"> ("community_person_ban"."id" IS NOT NULL) AS ban,
</span><span style="color:#323232;"> -- "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published",
</span><span style="color:#323232;"> -- "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local",
</span><span style="color:#323232;"> --"post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank",
</span><span style="color:#323232;"> -- "community_follower"."pending",
</span><span style="color:#323232;"> ("post_saved"."id" IS NOT NULL) AS save,
</span><span style="color:#323232;"> ("post_read"."id" IS NOT NULL) AS read,
</span><span style="color:#323232;"> ("person_block"."id" IS NOT NULL) as block,
</span><span style="color:#323232;"> "post_like"."score",
</span><span style="color:#323232;"> coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") AS unread
</span><span style="color:#323232;">
</span><span style="color:#323232;">FROM (
</span><span style="color:#323232;"> ((((((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> "post_aggregates"
</span><span style="color:#323232;"> INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_person_ban"
</span><span style="color:#323232;"> ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = 3)))
</span><span style="color:#323232;"> LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">WHERE
</span><span style="color:#323232;"> post_aggregates.id IN (
</span><span style="color:#323232;"> SELECT id FROM post_aggregates
</span><span style="color:#323232;"> WHERE "post_aggregates"."creator_id" = 3
</span><span style="color:#323232;"> ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;"> LIMIT 1000
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND
</span><span style="color:#323232;"> (((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (("community"."deleted" = false) AND ("post"."deleted" = false))
</span><span style="color:#323232;"> AND ("community"."removed" = false))
</span><span style="color:#323232;"> AND ("post"."removed" = false)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("post_aggregates"."creator_id" = 3)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("post"."nsfw" = false))
</span><span style="color:#323232;"> AND ("community"."nsfw" = false)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("local_user_language"."language_id" IS NOT NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("community_block"."person_id" IS NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("person_block"."person_id" IS NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;">LIMIT 10
</span><span style="color:#323232;">OFFSET 0
</span><span style="color:#323232;">;
</span>
If it isn’t social hazing, then what is going on here? Why has this issue gone on since May and servers are crashing every day?
One of which makes entire tree of comments disappear. Do you see developers fretting over this and fixing it? Or do you see them ignoring the May 27 PostgreSQL JOIN problem.
How did such a bug go out? Do you see Lemmy developers actually using Lemmy to test things and notice these crashes and problems? Do you look at their posting and comment history? Do they actually go login over at Beehaw and Lemmy.world and see just how terrible the code performance is?
If it isn’t hazing, what is it?
It’s as if they build a product only for other people to use… and they don’t notice any of the constant crashes, incredibly slow performance etc - and they act like nobody in the computer industry ever heard of Memcache or Redis to solve performance problems. If it isn’t extreme hazing going on, then what is it?
I’ve been reading a lot of your exchanges on the Lemmy GitHub and I can tell you with a high degree of confidence that you are not the subject of a hazing ritual. What’s going on is a miscommunication issue; there’s no ill will directed towards you.
The Lemmy devs are under a great deal of stress these days due to the recent influx of activity, both on the big Lemmy instances as well as the Lemmy GitHub. You’ve clearly gone to great lengths to investigate various SQL bottlenecks in Lemmy, and this work does not go unnoticed or unappreciated.
The problem you’re likely running into is that the Lemmy devs are trying to address a wide array of issues, whereas you are zoomed in on some very specific performance problems. Whether or not the core devs are wrong when they say your findings are irrelevant is beside the point. What they are really saying is that they do not have the attention bandwidth to try to see what you are seeing right now.
If you find yourself unable to work with the Lemmy project, there are other fedi projects in Rust like Mitra or Kitsune which might be more receptive to your contributions. I’m personally very interested in seeing rudimentary Lemmy (Groups et.al.) compatibility in Kitsune.
The reason I’m still here instead of there is that I absolutely can’t use their official app. I just can’t. It’s so awful. Lemmy isn’t perfect but at least it isn’t that. So I do spend less time doom scrolling and that’s probably good for me.
Client as in app? I go back and forth between Connect and Voyager. They both have some benefits and drawbacks but overall I really just want something super simple and plain. I used RIF for so long and it was just to the point and functional. I never had a single issue with it. The lemmy apps will improve though. They’ve been developed fastly and furiously (haha) but it usually takes a little more time to put something together that’s really good.
First impression is that the UI isore customizable and I can get closer to RIF which looks way better to me. I haven’t even logged in yet though but at first glance I don’t hate it. I guess I need to check liftoff too though.
I used Reddit Sync for about a decade before switching to Infinity for Reddit, tried giving Sync another go after the Lemmy version released and it just felt wrong whereas the Lemmy fork of Infinity is a joy to use (and is FOSS)!
There is a middle ground between “infinite doomscrolling” and just barren. I miss a lot of communities I used to browse on Reddit and they aren’t taking roots here. Losing more people isn’t a good sign.
A variety of fandom and franchise-based communities, some artistic communities, some more specific sub-communities like Patient Gamers which exists but it’s pretty slow.
Same here, I actually have a much healthier relationship with social media when on Lemmy vs Reddit. That might change as Lemmy grows in user content but for now I’ll enjoy the quieter experience
It’s probably more likely that we’re losing more of the “Fediverse is just Reddit 2.0” kind of people - which is great because that’s 10k or w/e less Redditors that’ll go back on the platform they actually want to use.
Fediverse doesn’t have an ocean of communities and content (yet), but that’s fine with me since I’m more active here and trying to offer more insightful comments outside of the Reddit staple “this” kind of comments.
Yeah, there’s even upcoming third-party apps to fill the void left by apps like Apollo (at least for a while).
What keeps me there and not here are some niche subs for music genres/subgenres and bands, and I don’t see any of them crossing over to the fediverse until Reddit completely and utterly dies. It even seems like some went over to Discord and I’m not really a fan of that, but it’s probably more lively there than here for my interests.
lemmy.fediverse.observer
Hot