Let $M$ be a module over a ring $R$. In nice situations (though I don't know what exactly nice means...) the following two numbers are equal:
1.) The codimension of the support of $M$
2.) The biggest $k$ such that $\text{Ext}^k(M,.)$ doesn't vanish
Why do we expect this intuitively? Why should lengths of injective/projective/flat resolutions have anything to do with the support?
To understand what "nice" is in your sense has been a very interesting question in commutative algebra.
In the following discussion I will assume, unless otherwise notice, that $(R,m,k)$ is Noetherian local, and $M$ is finitely generated. Let $(1)$ be the codimension of support of $M$ and $(2)$ be the biggest non-vanishing index of $\text{Ext}(M,-)$.
First, the number (2) is finite forces $M$ to have finite projective dimension by taking $N=k$ the residue field. So we will assume $\text{pd}\ M <\infty$. Then, as BCrd pointed out:
$$ (2) = \text{pd} \ M = \text{depth} \ R - \text{depth} \ M$$
The first inequality is easy by computing Ext via a projective res. of M + Nakayama lemma. The second is the Auslander-Buchsbaum theorem.
On the other hand:
$$(1) = \text{dim} \ R - \text{dim} \ M $$
So
$$(1) - (2) = (\text{dim} \ R - \text{depth} \ R) - (\text{dim} \ M-\text{depth} \ M) $$
Thus, if both $R,M$ are Cohen-Macaulay (which by def. means dim=depth) and $\text{pd}\ M <\infty$ then $(1) = (2)$. If $R$ is "more Cohen-Macaulay" then $M$, we will have $(1)<(2)$.
The situation described in Emerton's answer is also very interesting. In general, the smallest index for which $\text{Ext}^i(M,N) \neq 0$ is the biggest length of an $N$-regular sequence inside the annihilator of $M$. When $N=R$ this number is called the grade of $M$, which I will call (3).
It is easy to see that $(3) \leq (1)$ in general. One can prove that $(1) = (3)$ if $R$ is Gorenstein as follows: By Local Duality, $\text{Ext}^i(M,R)$ is Matlis dual to the local cohomology module $\text{H}_{m}^{d-i}(M)$, here $d= \text{dim}\ R$. It is not hard to show that local cohomology vanish beyond $\text{dim}\ M$, QED.
Amazingly, it has been an open conjecture for 50 years that $(1)=(3)$ whenever $M$ has finite projective dimension!
For "intuitive" understanding, I would offer the following: often when study modules of finite projective dimension one draw inspirations from those of the form $R$ modulo a regular sequence (so the resolution is a Koszul complex). In such case one can easily see that $(1) = (2) =(3)$.
EDIT: I got too caught up in the results and forgot your main question: why bigger codimension implies bigger projective resolution? A very low-tech way to see it is: bigger codimension means bigger annihilator of $M$. Now each element of the annihilator of $M$ gives a non-trivial relation on elements of $M$, namely $ax=0$, so it is not surprising that the modules with bigger annihilators have more complicated resolutions.
One intuitive explanation (in the case $R$ regular that Emerton discusses) is that on a derived level a module knows all about the formal neighborhood of its support, and the higher ext's (up to the codimension) account for the missing directions.
Let me assume for simplicity that $M$ is the structure sheaf of a subscheme. Then the functor $Ext(M,-)$ is derived restriction (more precisely restriction-with-supports) to the support of $M$. This functor sets up a derived equivalence (by the derived form of the Barr-Beck theorem, or a fancy form of Koszul duality) between the completion of the regular ring $R$ along the support and a derived refinement of $M$. The idea is that transversally to the support we are using Koszul duality to identify modules over a symmetric algebra and an exterior algebra (ie if the support of $M$ is a smooth variety of codim k, we enhance the structure sheaf of the support by an exterior algebra on a k-dim vector space).
(edit:) More generally, for any $M$ coherent or a perfect complex, ($R$ still regular) $Ext(M,-)$ preserves all limits and colimits on the derived level (in the dg derived catetgory of $R$), and sets up an equivalence between $Ext(M,M)$-modules and whichever $R$-modules are not annihilated by the functor $Ext(M,-)$. For $M$ a structure sheaf this means the formal completion along the support, shouldn't be hard to describe in general. I think it should be easy to deduce from this that we have to have Exts up to the codimension, since we have to account for all the homological complexity of an "open nbhd" of the support using a sheaf of dg algebras supported on the subscheme.
If $R$ is regular local, and $M$ is a finite type $R$-module, then the first $i$ for which $Ext^i(M,R)$ is non-zero coincides with the dimension of the support of $M$.
To understand this, first think about the case when $i=0$: clearly $M$ will have a non-zero Hom to $R$ if and only if $M$ has non-zero generic fibre, i.e. full support.
On the other hand, if $k$ is the residue field of $R$ (the case of maximal codimension of support), then $k$ has a projective resolution of length equal to the dimension of $R$, given by the usual Koszul complex, from which one easily computes that all $Ext^i(k,R)$ vanishes unless $i$ is maximal (i.e. equal to the dimension of $R$).
For a slightly more intermediate case, imagine $M = R/f R$, where $f$ is a non-zero, non-unit element of $R$. Then $M$ has the proj. res. $0 \to R \to R \to M \to 0$ (the second arrow being mult. by $f$), from which one sees that $Ext^1(M,R)$ is non-zero.
The general case is proved by Koszul-complex style induction arguments, as indicated already in the comments.
