I mostly agree with @Kini_Hawkeye on this one.
Threat of death is something that adds dramatic stakes to the story, and makes beating those odds even more satisfying. It can be a really hard thing to balance around, though, and there are times where a GM might bend the rules a bit.
For example, recently in my current D&D campaign, my character was knocked unconscious and acquired two death save fails. The problem is that this was only about our third session into a brand new campaign. Obviously, there’s no good story to be told when a character dies that early, it’s disappointing for both the players and the GM. My GM let me have advantage on my next death save due to this fact, and luckily I didn’t need it and was able to pass and get healed next turn, but it goes to show there are moments where the threat of death can be very inconvenient.
But at the end of the day, I advocate to keep death as a mechanic in most roleplaying games where it would make sense, especially in D&D.
On the matter of:
I watched a very interesting video recently that dealt with Taliesin Jaffe from Critical Role and his perspective on the death of his character Mollymawk and PC death in general. It gave some really important advice that any good character needs to be prepared for death, prepared for that story they are trying to build to end at the drop of a hat. Because, like Kini mentioned, the death of a character can be a really powerful moment of storytelling, and if you can plan and prepare and expect that to happen to even your most favorite character, it won’t be this all-crushing event a lot of people view it as.
I’d like to give a sort of testimonial to this notion, because I feel the same way. In my first D&D campaign, I did the exact same thing. Our party was against all odds in a harrowing fight against a Death Tyrant, where any one of us could be killed with no chance of being healed at any moment.
All our backs were against the wall, but the Tyrant was nearing death itself. As a last ditch effort, I willingly allowed the Death Tyrant to strike my character in hopes of using Hellish Rebuke at the last minute to kill it as it killed my character. I had no real plan, no real clue what might happen, or even if my plan would work, but the simple act of me choosing to let my character die, let his story end, for the sake of everyone else made the difference.
And, like any good GM should do, I was well rewarded for my bravery and sacrifice. After my character was disintegrated, he reformed as a massive magical construct, engaging the Tyrant in an epic clash before finally landing the killing blow. And after that last act of heroism, he returned to ash and dust.
Granted, I was allowed by my GM after the session to choose what to do next with my character, and obviously I allowed him to return to the game after a good couple of months playing another character, but that act of sacrifice had lasting effects on the attitude of my character, his story, and even his physical body. What matters is that I was rewarded for my decision, and even more so in the memory I still hold of that session.
When handled right by both player and GM, death is one of the most powerful storytelling tools imaginable.