In an old Pathfinder campaign I ran, early on, when I was still learning the system ropes, I grabbed a flavorful “make this monster two challenge ratings tougher” template to add to the boss of an encounter to bump a wolf mother from CR3 to CR5, not realizing it was a template from an old Paizo module that was intended to be applied to monsters closer to CR14 base. She wound up much stronger than I expected, and two players got overconfident because they both had excellent saves - one of those, “I’m only in danger if I roll a 3 twice!” situations. As you might expect, 3s were rolled and I didn’t realize I probably needed to start fudging until they both suddenly and very anticlimactically died. It was a crappy ending to what was meant to just be an interstitial encounter to cap off a one off side quest they picked up while traveling to the next big town, and now two main characters were dead with basically no payoff or point.
Now, in some kinds of games, with some kinds of players, that’s super okay! The world is dangerous, the monsters are scary, and if our tactics and preparation aren’t Dark Souls level on point, we’re gonna die. For what it’s worth, I don’t run or enjoy playing in games like that, and people who play with me generally know that. I’m all about crafting an engaging player driven storyline that feeds off character actions and choices, using bits of my overprepared world building to find a narrative thread that their actions will lead them toward. My combats are usually more intended to be about Something Larger than to just grind you down or maybe kill someone. And those deaths were really counter to everything we’d been planning on.
On the other hand, when players approached me and said they wanted to play a new character and have their current one go out with a bang, we worked together to build up something really cool and explosive to take them out, and I usually then got to spin a cool new storyline out of that, like the grim faced cleric who was resurrected by the evil god caught in the spiritual mesh cutting off their world from the afterlife to help free him - that player even got to play his old character again when his paths crossed with the party’s!
To keep the tension level high, I try to offer them stakes larger than their own mortal lives that derive from the motivations and back stories they develop. Yes sure this demon lord might kill your characters while they’re stuck in the 1600s without access to their superpowers, but if he’s successful in warping the family line of these famous Master Mages, in 400 years, Hell will claim the earth, AND YOU’LL NEVER GET TO GO BACK TO YOUR PROM DATE TO FINISH THE LAST DANCE. dun dun dunnnnn
Some crews are easy to motivate, with classic stuff like money or treasure. Others might have an npc family member they love, or a nation they serve, or a god they’re bound to. Defeat might not mean death, but failing to stop something worse from happening in time - fighting the heroes was just a minor distraction from the villain’s real goal all along, etc.
Edit to note, none of this is to say you can’t tell a player driven storyline while also having deadly tactical combat. That’s generally not how I personally roll, so my advice is geared away from that. Other GMing styles will provide good insights there, Colville included! He runs a pretty lethal game.