Most games have a reasonably good threat-to-excitement curve. During the early game, you are dealing with minor threats, then you save the world in the end. In fiction, challenging your heroes is important. Long running series (or table top adventures) often run into the problem where the new threat is only novel; not actually dangerous. You can take a breather episode or session to deal with a minor nuisance, build character or whatever. But, don't have threats vary as wildly as the stock market.
Third edition does this with challenge ratings; goblins were things adventurers should no longer be worried about by about third level. Dragons were appropriate challenges later on. Most level-based games throw bigger piles of modifiers and hit points at the heroes.
However, like when we talked about horror, to make your players feel challenged and threatened requires more than mere mechanical fear of character death. You have to threaten something the players value while also being a respectable threat. If your PCs spend the first few sessions solving a murder that ends with them closing a portal to a dark realm, a plain old jewelry store robbery is going to be lame.
Low-level adventures should have contained (but real) threats to an important area. A few people might die, and players are generally on their own. There is a single, strong villain, though it may have mooks. At the low tier, players might deal with a murder/theft of a unique or valuable item by a known criminal, a haunted house may need to be purged of an evil spirit, a mountain pass is blocked by bandits.
Late low-tier and mid-tier challenges increase in scope. You have one well-established enemy leader, with several lieutenants and resources, and they are legitimate threats to society. The things that are at risk are bigger than the PCs. The stolen item is an artifact or heirloom, with two gangs fighting over it; a powerful cult in the service of a vampire has established themselves in the haunted manor; or the bandits have extended from a mountain force to claim outlying farmlands and charging tribute while attacking merchants.
By the mid-tier, the day of the lone, powerful figure is over. A single werewolf terrorizing a town is, by most CRs, a reasonable challenge for mid-level players. But, it is down in scale from what they started with. Players should start to feel pressured by multiple, unique threats. One gang may have the police in their pocket, the stolen artifact is an eldritch tome of unknown horrors; the bandits are a proxy army of a competing noble looking to destabilize the region, rival bounty hunters are trying to claim the reward for defeating the bandits; the vampire is allied with local demi-humans and has a legitimate claim to the barony.
In high tier, players face world shattering events. That book the gangs are fighting over is no longer merely an artifact, one of the gangs belongs to an evil cult looking to wake a sleeping demon, while the other's motives are equally dastardly (both have other worldly aid); the noble has coordinated with surrounding nations to press the attack, while his bandits cut off the needed food supply while he maneuvers to take control after the war; the vampire is an agent from the afterlife of your choice, hoping to bring in an age of undeath to the world.
My last bit of advice on this: Avoid Saturday morning cartoon syndrome, where each challenge is seemingly random. Try starting a session with: "The last few months you have effortlessly dealt with goblin incursions. However, [Insert Plot Hook here]."
This is actually a fun topic, that I wish I had more space to expand on without being boring.
1. Everyday heroes as PCs
2. Why writing comic books is hard
3. Why comedy is harder than drama
4. Making magic magical
5. Post a link to a short story; describe the writing and editing process
6. Playing ethical villains
7. Play a random song on Pandora; write a short fiction piece using lyrics from the song.
8. Making fantasy travel fantastic
9. Unity of purpose (in writing fiction)
10. Unity of purpose (in designing table top adventures)
11. Applying history to your table top adventures
12. Popular history is still history
13. History, as events and actions
14. History, as people and interactions
15. History, as movements and reactions
16. Living History: What Our Memoirs May Look Like
17. Why modern politics is not history (Yet!)
18. Historical perspective: the Internet
19. Why I studied history: True stories are important
20. Why I studied history: Case studies in success and failure