Day of the Tentacle? It’s literally impossible (as far as I ever discovered) to encounter a game-over or unwinnable situation. It just requires good design. (Edit: You did say “almost never”)
It is a good idea, but there are plenty of games around that still require you to manage saves (although they are often helped/hindered by autosaves). Cyberpunk, No Man’s Sky or any game from Bethesda comes to mind. Generally it is found in but not limited to deep RPGs.
I may be a glutton for punishment then. My first IF experience was The Temple of Raaka-Tu. If memory serves, it was a particularly brutal game with no save feature which I completed in a couple sessions (after many, many death/restarts). I had no programming experience when I played it, and had just a few months of tinkering with basic on a Commodore Vic-20 when I first encountered Zork on my cousin’s Mac. I only got one or two sessions with it there and didn’t beat it, but I remember I did get as far as bringing the Sarcophagus to the house. I didn’t solve it until I got it for my C-64.
IF, just like any other kind of game, appeals to certain people. Reading and typing are slower-paced and require more concentration than wiggling a joystick while watching stuff happen. As difficult and brutal as Elden Ring is (I’m thinking battling Malenia here), people will try repeatedly dozens or hundreds of times. Yes, manual saves, no-win situations and having to retrace your steps are annoying and will turn off some people, but I can’t believe it is as big a factor as people want to think.