I am comfortable with the definition of adjoint functors. I have done a few exercises proving that certain pairs of functors are adjoint (tensor and hom, sheafification and forgetful, direct image and inverse image of sheaves, spec and global sections ect) but I am missing the bigger picture.

Why should I care if a functor has a left adjoint? What does it tell me about the functor?

The answer to this question is the same as the answer to every question of this genre (“why should I care about groups,” “why should I care about rings”): they show up everywhere and are an extremely useful organizing principle.

There is a meta-principle that any time you’re trying to understand something about categories, it’s a good idea to restrict to the special case of posets first, regarded as categories where $a \le b$ means there is a single arrow $a \to b$. For example:

• Products are the same thing as infima. In particular, the terminal object is the empty infimum, which is the maximum element. (This may seem backwards, but it really is what you get out of the definitions.)
• Coproducts are the same thing as suprema. In particular, the initial object is the empty supremum, which is the minimum element.

So what’s a pair of adjoint functors in this context? Well, if $P, Q$ are two posets, then a functor $f : P \to Q$ is just an order-preserving function. So a pair of adjoint functors is first of all a pair $f : P \to Q, g : Q \to P$ of order-preserving functions. The definition I think is most convenient when studying posets is that $f, g$ must satisfy

for all $a \in P, b \in Q$. But this is the same thing as requiring that

This relationship is called a Galois connection. Important examples of Galois connections include:

• $K \to L$ is a finite Galois extension, $P$ is the poset of subgroups of $\text{Gal}(L/K)$, $Q$ is the poset of subfields $K \to M \to L$, $f$ sends a subgroup to its fixed field, $g$ sends a subfield to $\text{Gal}(L/M)$. (One has to reverse the order of one of these posets for this to work.)
• $P$ is the poset of ideals of $\mathbb{C}[x_1, ... x_n]$, $Q$ is the poset of subsets of $\mathbb{C}^n$, $f$ sends an ideal to the set of points defined by its elements vanishing, $g$ sends a set of points to the ideal of functions vanishing on it. (Again, one has to reverse the order of one of these posets.)

Galois connections exist in extreme generality and are, by themselves, already an important organizing principle in mathematics. So adjoint functors are even more important than that!

Edit: It’s probably worth explaining what’s going on in the above examples abstractly. Let $A, B$ be two sets, and let $r : A \times B \to \{ 0, 1 \}$ be a relation. Then $r$ induces an order-reversing Galois connection (a pair of contravariant adjoint functors) between the poset $\mathcal{P}(A)$ of subsets of $A$ and the poset $\mathcal{P}(B)$ of subsets of $B$ as follows: if $S \subset A$ then $f(S) = \{ b \in B : r(a, b) = 1 \forall a \in S \}$ and if $S \subset B$ then $g(S) = \{ a \in A : r(a, b) = 1 \forall b \in S \}$. I’ll leave it as an exercise to figure out what $A, B, r$ are in the above examples.

Note also that the fact that left adjoints preserve colimits and right adjoints preserve limits continues to hold for Galois connections, and shows that some of the properties of the Galois connections above are purely formal (in the sense that they follow from this “abstract nonsense”). Unfortunately it is generally not emphasized which properties those are.

The Wikipedia article does a nice job of explaining some broad general motivation (and has a lot of good discussion on this question besides): very roughly, an adjoint is the best substitute for an inverse that exists in a lot of cases that we care about. You can sort of see how this works in the above examples.

An important property of adjoint pairs is that they restrict to equivalences on subcategories, and this is what we get in the Galois theory and algebraic geometry examples above: the first adjoint pair is an equivalence by the fundamental theorem of Galois theory, and the second adjoint pair restricts to an equivalence between reduced ideals and varieties by the Nullstellensatz.

Since your question is tagged [algebraic-geometry], here is an important non-example related to the second half of Arturo’s answer. There is a functor $F : \text{Aff} \to \text{Set}$ sending an affine scheme to its set of points (the prime ideals of the corresponding ring), and it does not have a left adjoint: there is no “free affine scheme” on a set. The reason is that $F$ does not preserve limits. (Note that a functor has a left adjoint if and only if it is a right adjoint.) In fact, it does not even preserve products. The product of two affine schemes $\text{Spec } R, \text{Spec } S$ is $\text{Spec } R \otimes_{\mathbb{Z}} S$, and it is a basic property of schemes that this is not the set-theoretic product.

It follows that the functor $F : \text{Aff} \to \text{Top}$ sending an affine scheme to its set of points in the Zariski topology also does not have a left adjoint. If you’ve ever wondered why the Zariski topology on $\mathbb{A}^2$ isn’t the product topology on $\mathbb{A}^1 \times \mathbb{A}^1$, now you know.