Depends on the game. Demon's Souls is the most punishing... But Dark Souls 1 was pretty brutal as well. Not only did you lose all of your souls upon death, but you would lose your "humanity" as well, lowering all of your stats significantly and you can only cure it with a special item. Shit was pretty annoying. Elden Ring did something similar with the whatchamacallit rune thing in the top left corner that deactivates when you die, but that was more forgiving.

It's mostly the same in the previous games, but without Torrent to avoid enemies and grab your souls. You had to navigate the same enemies on foot again, which isn't hard once you're used to the games. You can easily run past everything.

You're right it is fairly toothless, compared to a game where you reload a save. People like to pretend the Souls games are hard, but they aren't. They're very forgiving, but challenge you. You always make progress, which isn't true when you load a save which is what most games used to do.

Bioshock though, and some games of that time, just had no penelty. Bioshock you die, all enemies stay dead and you keep everything you picked up. You just respawn with full stats and keep all progress.

Souls has a great middle ground of keeping progress but also having some minor penelty to death.

I didn't play much Elden Ring as it strayed too far from what I liked about the earlier Souls games, personally. Demon's would only give you a checkpoint after killing a boss, though you could open up shortcuts instead. Dark Souls 1 had a few more checkpoints but there was none of this respawning right outside the boss door that you get in ER and some of the later series games (to make up for the overtuned boss challenge in those games).

This meant, at least on your first playthrough, you tended to be doing this slow, tense exploration of hostile areas. Because dying would not only cost you progress, but potentially your next level if you failed to retrieve your souls.