Yeah, that seems completely absurd. If the experiments are rigorous enough, and the p value is low enough, you should be able to trust the results, no matter how small the effect is.

If, for example, research shows that social media use increases one's risk of depression by, say, between 32% and 34%, with p < 0.000001, they're going to discount a result that only has a 1-in-a-million chance of being a fluke simply because it can only show a 1/3 rise in risk, rather than a 3x rise?

That's not skepticism. That's dogma.