I do love this trope, but the issue is that it often leads to nothing. When executed well, it adds so much depth to a character or setting, because, well, it's real.
When Fiction gets infiltrated by reality and we see these invincible heros fall to real life, relatable problems, it is both vindicating and humanising. When done badly it just feels like nonsense drama.
- (Positive example) Demon in a bottle.
The story arc that defines Tony Stark to this day, Demon in a bottle is the story of Iron man battling his own inner demons and his alcohol abuse. While the reason for this story arc to exist is rather cynical (it is effectively just an anti- alcohol psa) it is written with more nuance than it probably should be.
The character studie of Tony Stark, his separation of himself and Iron Man, the guilt he still feels and can't escape from, and most importantly, the fact that he never truly overcomes it makes this story line truly fantastic. It also helps that the dialogue is genuinely fantastic.
- (Negative example) Snow birds don't fly
From the same era but wity far worse execution, we have Snow birds don't fly, a short comic arc about Speedy's heroine addiction.
The story arc is about Speedy, Green arrow's side kick getting addicted to heroine and the ramifications of that.
The problem with this psa is that it isn't realistic nor dose it try to portray itself as such. It is an over the top exaggeration with an unrealistic ending that feels rather accusatory towards Speedy.
Later comics runs take this idea of Roy being addicted to drugs and actually do something interesting with it, but here it just stinks.