I tend to fall into the “death should happen, but it should be on the rare side and tend towards meaningful” camp.
Even if adventures are not actually stories, the PCs are their player’s chosen protagonists for any story you are recounting after the fact and “I slipped and fell to my death on the way to the dungeon” is just not terribly interesting.
Generally, I prefer systems where death requires a string of bad decisions to occur; definitely not just a single bad roll.
It’s part of my reasoning for having rather high starting hit points that increase slowly (a max level PC has just less than 4x the hp of a starting PC) with even high-level attacks only able to deal that base level in a single attack on a crit.
The idea being that, as protagonists, PC’s have got just enough luck that if they’ve unexpectedly entered the lair of a very powerful creature (possible because I run sandboxes) AND they turn and run immediately they’ll most likely survive. If they stay and fight after seeing an ally lose 90% of their hit points in a single attack... that’s on them and they’ve chosen a path of certain death.
Now, I will also make the distinction between systems where the character generation is fast and often heavily randomized (ex. roll 3d6 in order, then choose a class and basic gear and you’re ready to play) vs. systems where you have full control of the character creation process and that system is a bit more in depth (ex. assign stat array, choose race and racial options, choose class and class options, choose background and background options, choose equipment).
The former can get away with a lot of PC deaths/churn; particularly if the campaign is set up for easy replacement within game sessions because there’s no particular investment; you’re not even guaranteed to get a class you really want to play because your rolled stats were only good for something else.
The latter tend to start with stronger investment by the player both because they spent longer and because each choice was their first choice and each time they have to make a new character due to death they’re penalized by having to go with second or third choices if they want to avoid the “identical twin” PC (which, at least in these parts, is fairly stigmatized). Such systems generally need a softer approach to PC death as a result.
Similarly, another factor in the equation is permanence. A system where a relatively trivial expenditure (for PCs anyway) can easily restore a dead PC to life (bonus points if, like 3e, the XP system was geared to allow PCs who fell behind due to level drain/death to catch back up) then death can be common and cheap because it’s not REALLY the end of the PC, it’s a speed bump.
By contrast, a setting where death is virtually always permanent needs protagonist death to be relatively rare... though such settings tend to use “badly beaten/wounded and need time to recover” the way those casual resurrection settings use death.
So at the two extremes... a setting where chargen is quick and randomized with easy resurrection for PCs you care enough about to bring back can get away with cheap and common death. By contrast, a setting where chargen is detailed and all aspects are player chosen and where resurrection is nearly impossible at best will be best served by systems where death is rare and, ideally, other setbacks can be employed to inflict loss.