When you make a PC, you're investing a little bit of creativity into them. You put some time and thought into who they are and what they want. Creativity is, to my mind, quite personal; sharing the fruits of your creativity with others is exposing a little bit of your inner self to them (this, incidentally, is at the heart of the issues I have with many 'pass the talking-stick' story-games). The more you play the PC, the more you invest in them emotionally. The more of an emotional stake you have in them surviving and prospering.
When a PC dies (or is otherwise irretrievably fucked up), all that emotional energy you'd tied to them is lost with them. It's a sudden gut-punch of loss. The risk of that upset is what makes the game exciting. IC successes (gaining magic items, levelling up, increasing IC status) feel good because we know that they make the gut-punch of character death less likely; IC setbacks feel a bit bad for the same reason.
Things that drag the loss out (such as being temporarily turned into a frog, knocked unconcious or otherwise rendered unplayable) feel worse than mere death, because after a PC dies you can quickly recover, make a new PC and get back into the swing of things. An extended time where your PC is useless means you're stuck in that low-point for longer, hoping to get the PC you've invested in back soon.
Having agency over how a character's arc ends has been, in my experience, important. Retiring a PC who's become difficult to play (due to curses, injuries, etc etc) feels better than having them die, because the player gets to choose it, and can imagine them sitting in a nice cottage somewhere, with a big pile of gold, a sword hung over the fire-place, and a small child on their knee that they're telling the story of 'how I got my eye torn out'. It's a Good Ending. In effect, you're cashing out your 'emotional chips', and calling this gamble a success.
For this emotional gamble to be worth it, you need to carefully balance the emotions invested in the PC, the likelihood of death, and the magnitude of the gut-punch when the PC does die. I think the OSR gets this right with it's lower investment into new PC - rolling up a character is quick and doesn't require much deep thought, and low level PCs are fragile as fuck - that allows greater investment over time, corresponding to greater survival chances. Compare this to modern D&D where character creation takes ages (resulting in high investment from the get-go), CR-balanced encounters mean that your chance of death is constant rather than scaling to reflect investment, and death is rare enough that its easy to disregard entirely; here, your investment-risk-gutpunch balance is all off, you invest highly in a PC but have little tension, there's no sense of safety from levelling up since the challenges get harder to match you, and when you DO die it feels arbitrary, unexpected and unusually horrid (which feeds back into GMs not being willing to kill PCs, resulting in EVEN LESS tension).
One thing which I do in my games is to include horrible wounds. These actually tend to result in slightly longer gut-punches, as playing a PC missing a bunch of body parts is kind of difficult, and bleeding out extends the process of dying. However, they also soften the gut-punch as you're more likely to be able to successfully retire a (now crippled but also very wealthy) PC, getting them a good ending.
I tend to avoid anything which temporarily makes a PC unplayable (such as hard mind-control, extended unconciousness, transformation, etc), since this keeps the gut-punch just as horrid but draaaags it out. Instead, these effects tend to be either permanent (effectively character death) or just a debuff that encourages you retire the character.