I'll have to see Requiem for a Dream. I looked at the Wikipedia article and just at first glance I can see I would relate to it, being a person well acquainted with drug addiction and alcohol abuse. Well acquainted meaning: these problems are ongoing. Occasionally reined in and under control, but always rearing their ugly heads.
What really scares me, besides something like the unseen evil and ruthless power as depicted in The Exorcist - is anything that makes me conscious of my primary, lifelong fear: claustrophobia.
Hence, the scariest films for me (many of which which I didn't mention because I find them hard to even talk about) are ones that deal with premature burial.
The Screaming Woman was a made-for-TV film about a woman who was prematurely buried. The man who buried her thought she was dead, but she wasn't. She's in a shallow grave, only barely covered with earth. I don't recall the plot very much, or main details, only a harrowing scene where a man is walking his dog and the dog is alerted to the sounds of the woman moaning under the ground. The owner thinks the dog is just being a dog, until he hears the moaning voice himself. He alerts the police, and the woman is rescued. I have that scene permanently engraved in my brain, where she's dug up and pulled out of the ground, barely alive.
Another classic is The Premature Burial, 1962, based of course on the Poe story. I have major trouble seeing this film, and haven't watched it since I was a lad.
Then there's The Serpent and the Rainbow, 1988. I watched that film twice, since it's not nearly as scary as The Premature Burial, but I'd still have trouble if I watched it again. Which I won't!
Some of the most frightening scenes in Dante's Inferno (the poem, not film versions or the game) deal with extreme claustrophobic situations.
I have a recurring nightmare wherein I'm in some future or alien world, and I'm about to be placed in a tiny compartment for a long voyage across space. I'm either a slave, or used for food (my extensive reading on the unconscionable atrocities of the slave trade has no doubt been a catalyst for this nightmare).
In my dream, I'm promised (vaguely) that I'll be unconscious - in some sort of sci-fi deep sleep - but in my mind I KNOW that I will wake up during the journey, and die horribly, in total blind panic.
Naturally, this is a common phobia. Seminal films have been influential in my particular case, especially Planet of the Apes - which has the female passenger die during transit, and that shocking scene where the men discover her; also Alien, and several others.
There have been quite a few newer films that deal with this claustrophobic element very effectively. One is Eden Log, another is Pandorum with Dennis Quaid. I highly recommend Eden Log. It's a fascinating film.
The Descent, a film about a group of female spelunkers is also terrifying, at certain moments. I have to be mind-altered or thick with a few shots of Rumple Minze to be able to watch that film. There was a sequel, but I don't remember if I liked it.
I was gonna say "The Descent" once you started talking about claustrophobia... Yeah, it wasn't the monsters in that movie that was scary, but the idea of being trapped in those tiny, crushing spaces. Ugh.
So maybe good horror now plays on our psychological fears than rather than the classic supernatural. Because when one generally doesn't believe in the supernatural, it doesn't hold a lot of fear. For example, I first saw The Exorcist when I was 14. It scared the living shit out of me because I was a believer back then. It's still a great movie, but the power of the supernatural element doesn't exist anymore.
I would go even further and say horror movies have been replace with gore, for the same reason you stated above.