I'm not sure whether to reply to this thread because I'm basically a zero death GM outside D&D. But in D&D I tend to kill a character every half dozen or so sessions.
The reason is because in other games there's an expectation that I will do other things to PCs than killing them in ways D&D really doesn't support. For example my Blades in the Dark PCs routinely end up traumatised (and indeed play into it as it leads to XP), and characters in other games will end up injured rather than simply missing a few hit points. The only long term consequence D&D plays into mechanically is death - so I both DM and play with the expectation that that's the main one.
While I agree that D&D doesn't really have any other
mechanical consequences to death (other than exhaustion) I've found non-mechanical consequences tend to work as well, or better.
If players see the only consequence of failure for their PCs is death - than that tends to become a rather boring consequence. You roll up a new PC, join the group and off you go. On the other hand, other consequences tend to be more interesting.
For example, I've found many players are as or more attached to their PCs stuff than they are to the actual PC. I've not seen a look of bigger fear on the
players faces then when they ran into a horde of rust monsters after finding a big cache of adamantine.
Similarly, in many campaigns, fear of failure is as or more pressing than fear of PC death. Because, again, die roll a new character. but fail, and that consequence can stay for the long haul.
So, I guess, my point is - mechanical consequences aren't the be all end all and can often take a back seat to in-game, in-story consequences.