I can't speak on Golarion, but this general reluctance and fear of gamebreaking or atmosphere seems silly to me.
Zeus and Odin roamed around quite a bit. Thor made many an appearance. Nobody ever claimed that Greek and Norse mythology were ruined or "broken" by any of that.
Gods have gotten involved in my games many a time over the decades (though very rarely from campaign to campaign). Sometimes it's a just a happy little event that seems like a coincidence. Sometimes it's a messenger or avatar. Sometimes it's a projection or just a voice. On one occasion, god actually showed up. It didn't unleash a tide of other gods showing up, because it was an important moment related to those PCs' actions in that specific campaign. The players were not thinking about other gods, or worrying that this might mean a flood of divine visits or anything as ridiculous (I want to say something much stronger to describe how ridiculous that notion is, but whatever) as that. They were too busy with their own story and immediate needs.
As to the event being underwhelming, I happen to be quite a good writer, but I don't think it was that alone that made the event memorable. The fact that this was a god, that this being could change reality on a whim, send them to oblivion, whatever they had in their own minds, was itself enough to generate awe.
Consider also something mentioned in Order of the Stars for Dragonlance 3.5.
The gods do not fully manifest in the material plane because they discovered when fighting over it during the All Saints War that the material plane couldn't handle being exposed to their full divine glory.
So as not to destroy what they love or covet they send Aspects when they need to directly interact in the physical plane without a full divine intervention.