Is foreshadowing a good literary technique?
Of course it's a good technique. But as with ANY technique, the key is to do it WELL. If something is, to use your banal phrasing, 'super overused and played out', then OBVIOUSLY it's unsatisfactory. But when there are tiny hints that you might or might not pick up on, equally obviously that can be very satisfying.
It's a useful technique to set the mood of a novel, but it needs to be used carefully.
I like it and I use it myself. One of my beta-readers will usually read my novels twice, in part because she likes to find the foreshadowing bits. You're right, there is a lot of obvious foreshadowing going on in novels and movies, especially movies, it's rare that I'm surprised by a movie ending. But, it doesn't have to be like a game, it can also be a device so that you don't spring a machina ex deus ending on the reader - or that a character suddenly does something out of character or that's difficult to believe. The trick is to be subtle and find the right balance. Well executed foreshadowing will keep the element of surprise without the reader throwing the book across the room when done and muttering "cheat!" or doing an eye-roll halfway through the book because everything is too obvious.
I like using it
I like it for the rereads later on