It's funny you make this thread now because I literally just finished playing through Burial At Sea, which is Bioshock Infinite's story DLC (yeah I know, it's a little old), and it reminded me just how FUCKING GARBAGE Infinite's save system is.
You're not wrong that save anywhere load anywhere systems reduce tension, but jesus christ if the alternative is something like Infinite's system, then fuck that. Fuck. That. Shit.
For those of you that haven't played Infinite, the main issue is the inconsistency of save points: the game only saves at certain points, such as when you first transition into a new area, or when you defeat all the enemies in a set-piece. So you can actually go about exploring for an hour or more (I saw some videos on YouTube where a guy said he lost 5 hours...) and not encounter a save point, then die incidentally, or get stuck, or crash, or whatever, and lose a shit-ton of progress for no real reason.
So ultimately I agree that we need better save systems to improve tension, but so far the industry has proved it does not have the intelligence or competence to construct better save systems than 'save anywhere'.