I recently finished Metro 2033 on my Switch which I loved. I was genuinely on the edge of my seat during the part where you have to navigate through the library trying to avoid the librarians. It was a real tense moment and I was fully immersed in the experience. Last time I felt that tense was the nursery in Zombie U.

What gaming moments have caused you the most tension. 

Castle of Cainhurst/Old Yharnam - Bloodborne
The Nursery - ZombiU (I see you are a man of culture as well xD)
GFS Valhalla - Metroid Prime 3: Corruption
True Lab - Undertale

Tetsucabra, from Monster Hunter 4 Ultimate. He's very violent and he's the first boss in the game that's really challenging.

Mt. Itoi - EarthBound Beginnings
Basically the entire end of EarthBound
Chimera Laboratory - Mother 3
The Bunker - Uncharted: Drake's Fortune

Being stalked then attacked by Salazar's "Right Hand" in RE4. The sequence where he hunts you, followed by having to fight him off/avoid him while waiting for the elevator to arrive so you can escape is nerve-shatteringly intense.

Time Trial of the last stage in Mirror's Edge. 10 minutes of intensity, because a single misstep will cause you to restart it.

RE7 on PSVR, nothing else comes close. Most tense moments, first time getting chased in the house, the fight in the basement and the quarry section.
Before that it was Silent Hill 2, exploring the apartment block or was it a hospital, long time ago.

Probably the last half of TMNT on NES......along with so many other NES games, especially when the games has a 'Game Over' screen and all your progress can't be saved lol

Shalebridge Cradle in Thief 3.