You see this done right when the author remembers that magic isn't just a cheat code. It's a muscle, and muscles get tired. The best stories make the caster's strength come from a finite pool—mana reserves, stamina, mental fortitude—that drains visibly under pressure. In 'Mother of Learning', Zorian's early struggles are perfect; he's clever but his mana is pathetic, so he has to be a strategist, not a blaster. That limitation defines his entire arc.
But vulnerability isn't just about running out of juice. It's about the casting time, the incantations that can be interrupted, the somatic gestures that tie up your hands. A mage in the middle of a ritual is a sitting duck. I think some newer 'system' novels forget this—they give instant-cast spells and infinite mana, which turns fights into boring stat comparisons. The tension evaporates. For me, the balance tips when the caster's power creates bigger problems than it solves, like attracting magical backlash or drawing the attention of something far worse. That's the good stuff.