This is a real finesse thing. As others have said, feeling weak/barely scraping by all the time can be as boring as stomping everything you come across.
I will say that some of my players strive to be as optimized as possible, while others make characters that are very middle of the road- the optimized characters end up feeling like it's their job to protect the "normies."
I heard this from Matt Colville years ago and I still agree with it, that overall players want their characters to be John McClain- they want to struggle, be bloody and bruised, but succeed despite the odds. Buuut it's important IMO for them to feel powerful here and there, to see how far they've come. Let their fireballs toast some hordes, let the fighter defeat some pompous blowhard knight.
I personally have the issue with 5e though where high level characters, t3+, can wreck near anything... And some of my players that got to t4 didn't want to do it again because "it feels silly" powerful. But others are not at all against reaching those heights....
Meanwhile I'm over here with my E9 rules that I have yet to actually implement in a game... I like the idea of the PCs having to pull in allies etc to take on big threats rather than becoming demigods that can 1v1 a balor without a scratch... But then that means, practically speaking during a combat, that they get less playtime unless I give them control of the allies.
Anyway, tangent. I don't like super powerful characters.
I discovered that I like Sword and Sorcery rather over High Fantasy... It's more personal.