How is the relationship between Holmes and Watson portrayed throughout the story?

The relationship between the legendary detective and his loyal assistant is largely unequal. In terms of intellect, logic, and powers of deduction, Holmes is unequivocally Watson's superior. Yet the uneven dynamic at the heart of this relationship cuts both ways. For in relation to what we might today call emotional intelligence, Watson is streets ahead of Holmes.
If Holmes provides the head of this relationship, it's Watson who supplies the heart. Watson's romantic attachment to Mary Morstan makes him a more recognizably human character than the great detective. Although Watson will never be able to match Holmes in the mystery-solving department, his basic sense of decency and humanity provides his friend with the kind of moral support that he needs. In that sense one could say that in The Sign of Four, Watson fulfills the traditional role of the wife in the Victorian household, an unfailingly loyal helpmate who provides stability, warmth, and much-needed companionship.

In this mystery, Sherlock Holmes is the cool head while John Watson is the warm heart. Watson's warm heart is expressed most strongly in his falling in love with Mary Morstan, who comes to Holmes in hopes that he can find her missing father. Another sign of the caring Watson is revealed when he confronts Holmes about his cocaine habit. Watson has watched Holmes use cocaine three times a day, and as a doctor, he is worried about the effects on his friend. Holmes, ever valuing his intellect, insists the cocaine helps him think more clearly.
While Watson is busy falling in love, worrying about Holmes, and jumping to obvious—and wrong—about the case, Sherlock Holmes is using his great intelligence to logically unravel the mystery. Holmes is always two steps ahead of the game, and with forethought, he has people in place, like his Baker Street irregulars, to help him in his quest.
As always, the openhearted Watson, who lives more moment to moment, is awed by the intelligence, foresight, and talent of his friend. Holmes, in turn, relies on Watson's caring nature.

In the first chapter, Watson confesses to the reader that he is somewhat intimidated by Sherlock Holmes's confidence and brilliance; so much so, in fact, that he is reluctant to call him out for his drug abuse. But as a doctor, he does so, and they have a frank discussion about Holmes's use of morphine and cocaine. Also in the first chapter, Holmes is frank in his criticism of Watson's writing, and Watson is annoyed by the criticism, since he was looking for Holmes's approval.
As the chapters progress, Watson's deferential attitude towards Holmes is further developed. He is eager to be of service to Holmes, and Holmes seems to enjoy their conversations. Watson also smooths out social situations for Holmes, such as when Holmes is too deep in thought to politely engage in conversation with Miss Morstan in chapter three when the trio travels together.
Watson often acts as Holmes's sounding board, such as in chapter six when Holmes floats his theory of how a man has gained access to Pondicherry Lodge. In that conversation, Watson not only asks questions but also floats theories of his own. In subsequent chapters this dynamic continues, and the two men often share laughter.
Overall, Watson is deferential and admiring of Holmes, who can be arrogant and supercilious, but there seems to be mutual respect in their relationship.