Oh man remember that final level in metal slug X where mordens army joins you to fight the aliens? That was amazing.
If I gameover in that stage, I give up. Seriously. Nothing kills your will to game more than gameovering there.

Yeah, that prison level was pretty awesome.
As for Ravenholm, I usually skip that section whenever I play Half-Life 2. It scared me to death in my first playthrough, and after I fought my way in and out of the mines I knew there was no way in hell I'd ever go back there.

I always remember the best levels either scaring me half to death or making me take a moment to gawk at its beauty. Its weird how attractive extremes are.

I fully agree.
I was constantly watching over my shoulder for enemies, only there never were any real enemies. But the atmosphere really drew me in.

A few of my favorites:
Psychonauts: Black Velvetopia. The level was so well done and had me completely captivated the entire time I was playing through it. The way painting was incorporated into the level design was truly incredible. Hard to choose a favorite from Psychonauts, but there it is.
Call of Duty: The Chase Scene in France. This level was so well scripted. I had a terrific time, even when I got shot out of the car because I wasn't using cover or reloading properly.
King's Quest Mask of Eterntiy: The Swamp. A lot of people hate this game because it isn't true to the original series. While this is true, there are some positive things about the game, and most of them happen in the swamp, the coolest, eeriest level in the game.
Myst III Exile: Amateria (the balls on tracks). This level had me captivated the entire time I was playing it. There's something really satisfying when all of the contraptions work exactly the way you want them to, and they really hit the spot with Amateria.
Wizardry 8: Trynton. How can you not enjoy the massive city in the treetops, with winding bridges, weird magic rooms, and rickety elevators? Definitely a treat.
Torin's Passage: Escarpa. Chock-full of the best humor and puzzles in the game, this level has bumbling kings, fast-talking skunks, a sitcom parody, two-headed vultures, dinosaur bones, and a philosopher with a green beard!
Sorry, I'm done (!).

The last level of Braid certainly impressed the hell out of me, the way the dialogue & actions when seen through the rewinding process really twisted the story from "please rescue me" into "get me away from this psychotic nutjob!"

Now I agree with you that Psychonauts had the best level design, but I think the level that takes the cake would have to be the Milkman level.
2nd place for me would go to the Barrens in World of Warcraft, oh what fun I have had in those desolate plains.

shalebridge was by far the creepiest level i've ever played. Ravenholm can't hold a candle to that place because it probably would've been swallowed alive by attracting attention.
going to a space based game, i looooove the level in fs2 where you emerge from the nebula to shivan space all you see is a gigantic enemy ship coming at you full speed and your wingman screaming DIVE DIVE DIVE so that it won't run you over.

****************Spoilers For Metal Gear Solid 4 ******************
In MGS4 when you go back to Shadow Moses Island. That was a very emotional and powerful level.

The entire France or Hong Kong sections of Deus Ex. Such good memories....
Oh Jesus, yeah, Shadow Moses in MGS4 (and everything that followed--THE MICROWAVE CORRIDOR AGHRHRHGGH).
The level design in Hitman: Blood Money was fantastic, for reals, though. So many great levels. The Heaven/Hell party, the opera house, the casino/hotel...
Shalebridge was certainly a good level, but I was disappointed in its spookiness, which achieved legendary levels even before I played it. Scariest thing ever = System Shock 2.
I am of course ignoring older games, like Mario...freakin' Mario! The world where everything is super huge in Mario Bros. 3! Not the best levels, really, but they were the favorites of every child for just being so COOL. And cetera.
Deus Ex wins for being the best game ever made.

Yep, have to agree with 'Shalebridge Cradle' from Thief DS. Definitely unsettling.

Ugh! The Shalebridge Cradle - I hated that thing. It was horrible.
My favourite levels though are all from Thief.
Thief 1 - Lord Bafford's Manor
Thief 2 - Shipping... And Receiving
Thief 3 - The House of Widow Moira
I also love Hangar from Doom and Hollywood Holocaust for it's great soundtrack on DN3D.

Oh, man... I've got a friend that absolutely loves that level. Whenever he's bored he'll just say "I think I'm going to kill a opera singer", and plays it. Still, it was a rather entertaining set up.

Oh and the city on Planet Kerwan in the original Ratchet and Clank.