My own outlook is kind of the opposite I think. I'm more than happy to let the characters develop how they will and if they have not got the stats or capacity to have a reasonable chance of defeating the ancient red dragon; then so be it - they have an unreasonable chance instead. I suppose I'm more than happy to let the dice lay where they will and tell the story and so do not as keenly feel the tension highlighted perhaps.
I think this is one pretty viable approach to RPGing. It is how I GM Rolemaster. It is how Classic Traveller is meant to work, I think (although I've got not much experience of Traveller campaign play).
I think the more the spread of capabilities of typical opponents, and the more the spread of DCs (and depending on the system, these might be the same thing), the harder it is to run this sort of game successfully, because you run the risk as a GM of inadvertantly setting up situations that hose your players in ways you didn't expect. (Rolemaster, for example, has much more modest growth in PC numbers - both bonuses and resilience - than does 3E. And it also has the random element of open-ended action resolution rolls, and critical rolls in combat.)
Is it the pressure to achieve balance so as perhaps the DM can control and pace the narrative of their game more precisely?
This is what is at work in HeroQuest revised edition - the GM is meant to set DCs that scale in a way reflective both of (i) PC bonuses, and (ii) prior PC successes, in order to achieve certain pacing goals (ie rising action, with tension increased by the occasional failure, until the climax ensues).
4e is more opaque in its intentions (ie not as well written as other GM's guides), but to me the best way of making sense of it is something similar - the scaling is all in service of pacing and overall narrative arc (start with kobolds, end with Orcus).
I think Burning Wheel favours a mix of the two approaches. The Adventure Burner stresses that obstacles are to be set in an objective fashion - they are part of building up a consistent setting for the shared fiction. (Very different, therefore, from HeroQuest, Maelstrom Storytelling and 4e. A lot like Traveller, Rolemaster and 3E.) But it also encourages the GM to hold off from statting up the Big Bad until the PCs are close to confronting her/him/it, because you don't want to stat up an important opponent in a fashion that will make the final confrontation anticlimactic.
I think there is some potential for these two imperatives - objective difficulties while preserving the pacing desiderata - to come into conflict. For example, if it's part of the already established story that the Big Bad failed at some task, that suggests an upper limit on her/his/its abilities at that task, which might later turn out to be at odds with the ability that you want to confer in order to make the Big Bad an adequate challenge for the PCs. But there are parts of the mechanics that push the other way - it's always conceivable that all dice rolled will be traitors, and therefore failure is always a possibility (however slight) no matter what the shade or exponent of a character's ability. (This is more like RM, with it's fumble and open-ended low rolls, than either 3E or 4e, both of which allow for auto-success on skill checks once the bonus is >= DC-1.)
And for the reasons I gave above, I also think BW is a bit more forgiving of challenges being unexpectedly hard or unexpectedly easy - advancement requires a range of obstacles, and even low obstacle task can quickly become challenging once some obstacle penalties or dice penalties come into play. Whereas to make kobolds challenging to mid-level D&D PCs (3E or 4e, even maybe classic D&D) would require imposing penalties of -5 or more to hit, which don't come up very often if one is playing the system as written.
