Hardcore or permanent (perma)-death modes are an interesting dilemma. On one hand, they absolutely do amp up the tension of most games in a very noticeable way. Many players will become hyper-vigilant, or think of extremely creative ways that a designer never conceived in order to solve challenges. Perma-death is also incredibly frustrating.
Although perma-death is almost universally considered a frustrating mechanic, it clearly exists/existed in games, and is desired by at least a small group of players as a game mechanic. I know that for myself, that immediately makes me wonder why, other than the obvious frustration issue, perma-death died out.
I think the answer comes down to a dichotomy of how interesting in gameplay terms the death is compared to how frustrating it is to start over. If the former can clearly and cleanly overcome the latter, perma-death becomes a compelling mechanic (for some people, at least). One example of this is seen in old school dungeon crawler and roguelike games that had such modes: leaving behind your character and all his stuff and allowing the player to find it with a new character.
This is but one method, but it’s a core one to the concept of perma-death. It creates a sense of permanence of the world. It’s not a world of heroes that revolves around a single earth-shattering protagonist, it’s a brutal, grey and drudgery filled death trap, where the slightest misstep can spell your doom. It also means that the world can go on without your character. The story doesn’t end because they died on dungeon level 25, the game doesn’t force you to reload a save or checkpoint. That character is simply dead, lying on dungeon level 25, their bones being picked clean, loot forgotten….
Until their outraged sibling/wife/offspring/relation comes hunting for the family heirlooms and a big helping of revenge.
So if you’re considering adding perma-death to your game as an optional mode, strive to make it more interesting than frustrating, and use it to make the world more alive.