Conventional wisdom holds that a main character must be, to a certain extent, mainstream. We equate mainstream with relatable. This seems to run a spectrum, though, and follows age lines.
In very young children's lit the MC is often superlative in many ways. In fairy tales, he or she is wittier, braver, or luckier than most. In addition to their amazing talents and gifts, they usually have bigger than average problems of the wicked witch variety. Transition to middle grade and the same holds. I love Artemis Fowl. But he is absolutely not normal. He is extremely wealthy, extremely smart, and only gradually (over the course of several books) does he seem to be plagued by anything remotely resembling a normal kid's problem. Even when the MC starts off "normal", ie: nothing special at school, problems at home, whatever, in middle grade the superlative rule still applies. Harry Potter is socially normal, struggles with regular adolescent woes, but he's a hell of a wizard and very brave. Percy Jackson? Turns out he is a demi-god of the highest order. Gregor the Overlander discovers he is a berserker who might have shamed the Visigoths with his battlelust.
By the time we transition to Lit Fic or Women's Lit the MC must be extremely normal with an exceptional problem or story. They need a job or spouse that does not fulfill all their greatest hopes and aspirations but a hurdle that will allow them to overcome this mundane drudgery.
Somewhere in the middle is YA. Right now, the trend is toward very "I could be that guy" MCs, which makes them very relatable. Their problems, even with the current crop of supernatural twists, revolve around uncertainty about the MC's convictions and place in the world.
The tension arises from normal people in exceptional circumstances or exceptional people in normal circumstances. If you have an exceptional MC in an exceptional circumstance, the only other avenue for tension is that the MC does not realize he or she is exceptional. Like Sookie Stackhouse: a waitress who considers her ability to read minds to be a disability because it inhibits her ability to have a normal relationship with a peer.
Somewhere in the equation, though, exceptional always crops up. How do you make the MC relatable and exceptional at the same time? Give them a blue collar job? Average looks or intelligence? Unhappy with their weight? Whatever it is, my favorite books have it. I'm not a huge consumer of chick lit but Jennifer Weiner is amazing at the normal people/exceptional circumstance combo. So is Mary Kay Andrews. Successful mystery and thriller authors also seem to have the secret handshake into this club.
How do they do it?