Many of your examples of "bad" moderation are more about site administration (including use of tech tools and appeals) than the degree of moderation. Like, yes - Reddit's moderation ecosystem, particularly in large subreddits, is fundamentally broken. Powermods, lack of accountability, malfunctioning digital filters, mods who lack of options for alternatives (or, where those alternatives exist, they are frequently overwhelmingly cesspools)... it's got issues. But this isn't about "more" or "less" moderation; it's about poorly-applied controls in the first place.
I'm not so sure Lemmy is so "perfect" either. I've seen plenty of moderation based on political views rather than actual misbehavior here, and conversely plenty of actual hatred and bigotry getting a pass because those in charge of a give space viewed it as aimed at the "correct" people. Likewise, while the Fediverse allegedly lets parallel communities develop, in reality it can be hard to overcome the inertia of people moving towards a popular community, unless the mods/staff there really screw up.
Okay, so what's the actual right amount in a given community?
My admittedly cop-out answer is "That depends on the community". There were some where extremely rigidly-enforced rules - particularly about quality or contents of answers or posts - helped to ensure communities retained a high degree of quality and reliability in what was posted. But others might want a more casual, relaxed space to goof around in - including in ways that others might not like - which require looser rules.
And that's really the rub: There's no absolute right answer. We can point to lots of wrong answers, but getting it right is a complex journey for each space. My personal focus is that whatever level is agreed on, it must be fairly applied for all users. You cannot be passing one user's slipup and coming down hard another. Be fair.
...and in the end, there will be people who simply cannot follow the rules, no matter how clearly they are explained.
Huh. Today I learned. Neat!