It heavily depends on the tone of the campaign and the players’ preferences. Some groups, in some games, enjoy death being a potential outcome, while others prefer for it to be completely out of the question.
There are also many degrees between both, like how much should potential death be telegraphed before making a decision that could end on it, or whether it should be relegated purely to an outcome of bad decisions and never caused by bad luck with dice.
My personal favourite is for players to make a decision when they are defeated: accept death (or retirement) and have their sacrifice improve the situation, or be left at the mercy of fate and have to face other consequences (easily worse than a heroic death, if the players care about the story and the world).
The conversation around this topic always seems directly or indirectly framed around a zero-sum framing: what's better and what's worse? Which side wins? Even if you disagree with the premise, that's what's shaping the conversation. I don't think the article suggested there's a "correct" answer, but it was clearly inspired by people who think the author was doing things wrong.
It can simultaneously be true that there are successful long-term campaigns with and without high character turnover due to death. It's a mater of personal preference and successful execution. The only thing categorically false is the idea that character deaths, in and of themselves, are inherently bad for long-term play.
A game can be challenging without involving character death. And the funny thing is, I'm not sure being able to die is what makes it challenging. I think it all comes down to : how much did character actions and player decisions mattered into the character surviving or dying ?
I do love the possibility of dying myself because I know that if I don't act right or do something stupid, I won't be saved and will suffer the consequences of it. But this implies that it's my choice that kills me. If my choice doesn't kill me, then it sucks. If it's the choice of another character, then it's akind to pvp and fuck that. And if it's the choice of a NPC or a God, might as well tell me that I shouldn't have come to play.
Note, I said CHOICE, not actions. A NPc can choose to try and kill me, if I also have choices to get me out of there.