Sometimes they are fun, like in Portal.

But I saw the remake of Shadow of the Colossus had achievements for killing the colossi, and it really fucks up the mood of the game.

I agree with you completely regarding achievements. In SotC they feel especially jarring. I’m glad I still own my original copy.

You can apparently disable notifications for achievements on consoles and in Steam, which would potentially help this somewhat if you know to do it beforehand.

The secret is to just ignore them unless they seem fun. Going for 100% is miserable

Why's that? I used to mainly game on the 360 and I've never particularly found them a hindrance to my enjoyment, or thought game designs were being altered negatively for their sake - despite not being really much into getting them myself.

Do you have some examples of games that have been made worse by the inclusion of achievements?

Now, in game purchases, on the other hand...

What would fix the model IMO are mutually exclusive achievements, and the ongoing addition thereof. Only travel down the achievement paths that are fun to you. Avoid the others or you might get locked out of some future fun ones.

That seems even worse