Why must nilpotent elements be allowed in modern algebraic geometry?

I'm a bit confused by the quoted wikipedia entry, because the category of reduced rings also has coproducts (take the tensor product, and then pass to the quotient by the nilradical), and hence the category of reduced schemes, the category of varieties over a field, and so on, all admit fibre products. [Added: See Jim's Borgers series of comments below for a discussion of why, nevertheless, there may be a purely categorical description of the sense in which constructions in the category of reduced rings can be "wrong", while constructions in the category of all rings are the right ones.]

So the answer to the question of why we need nilpotents is not that it is necessary for the existence of fibre products.

Grothendieck introduced nilpotents for many reasons, a number of which are discussed in the other answers: to get correct counting in degenerate situations, it is typically necessary to allow nilpotents; they are also the bedrock of deformation theory and other applications of analytic ideas in algebraic geometry.

It might be helpful to recall another motivation, which forms a significant part of Grothendieck's overall strategy for studying algebraic geometry: Suppose that we want to prove a property about a morphism $f: X \to S$. A typical approach is to first show that it is a local property, in some sense, so that we can reduce to the case when Spec $\mathcal O_{S,s}$ for some point $s \in S$, and hence assume that $S$ is local; and then to use a flat descent argument to pass from $\mathcal O_{S,s}$ to its completion, and thus assume that $S$ is the Spec of a complete local ring. We then write this complete local ring as the projective limit of the quotients by its maximal ideals, and so reduce to the case when $S$ is the Spec of an Artinian local ring. Since such a Spec has a single point, we can then hope to reduce to checking our property on the fibre over this one point, which reduces us to the case when $S$ is the Spec of a field.

This is a powerful method, which absolutely requires us to be able to do geometry over an Artinian ring (and hence requires us to allow nilpotents). It comes up in lots of places, e.g. in establishing basic properties of abelian schemes, by reducing to the abelian variety case. See the anwers to this question for some examples.

Suppose you want to do moduli theory or to put it simpler, you are interested in deformations and degenerations. Often the degenerate objects have a natural non-reduced structure. In fact it is possible that taking the corresponding reduced scheme screws things up.

Here are two simple examples:

Example #1: Consider the morphism $\mathbb A^2\to \mathbb A^1$ defined by $(x,y)\mapsto x^2$. The fibers are the curves defined by $x^2=\lambda$. For $\lambda\neq 0$ this is a parabola and for $\lambda=0$ a (double) line. If we only consider reduced schemes, then this is just a line, but otherwise we would expect that the members of a family of plane curves have the same intersection numbers (counted properly and also counting intersections at infinity) with other curves. Taking another line in general position one can see easily that the parabola intersects it in $2$ points while the line in only $1$. Considering the scheme theoretic fiber $x^2=0$ which is a double line resolves this problem.

Example #2: Let $X=\{(1,\lambda t, t^2,t^3)\vert (t,\lambda)\in \mathbb A^2\}\subset \mathbb A^3$. This is a surface defined "classically". Consider its projection to $\mathbb A^1$ by mapping the point $(1,\lambda t, t^2,t^3)$ to $\lambda$. Denote this by $f:X\to\mathbb A^1$. Still pretty classical. Now notice that the (classical=reduced) fiber of $f$ over $\lambda=0$ is a nodal cubic curve while for $\lambda\neq 0$ it is a twisted cubic. Also notice that this family can easily be compactified to be a projective family, so we get a family of $\mathbb P^1$'s degenerating to a projective nodal curve. However, without nilpotents this leads to severe headache.

Since $X$ is irreducible and $\mathbb A^1$ is non-singular, $f$ should be flat. But fibers of a flat morphism have constant Hilbert polynomials, in particular their arithmetic genus is constant. The arithmetic genus of a twisted cubic (i.e., $\mathbb P^1$) is $0$ while that of a nodal cubic is $1$. If you want a completely classical argument, then one could say that the nodal cubic is also an obvious degeneration of non-singular plane cubic curves. Working over the complex numbers a plane cubic is a torus while a twisted cubic is a sphere. So this would suggest that it is possible to deform a sphere to a torus....

The resolution of this dilemma is that if you compute the scheme theoretic fiber (using fibre products) then you'll see that the correct fibre over $\lambda=0$ is actually the nodal cubic, with a nilpotent sitting at the singularity. So the fibre is a non-reduced scheme and its arithmetic genus is $0$ so we can all sleep peacefully.

Here's an example that highlights why nilpotents are at least informative: Consider the intersection of the curves in $\mathbb{A}^2$ defined by $y=x^2$ and $y=0$. Set-theoretically, there is only the point $(0,0)$, but this doesn't account for the fact that the multiplicity of the intersection is $2$.

However, one can phrase this simple case in terms of fiber products (as you request) as follows: Let $C$ denote the curve in $\mathbb{A}^2$ defined by $y=x^2$ and let $\pi:C\to \mathbb{A}^1$ denote the projection to the $y$ coordinate. Then the fiber product of $\pi$ along the inclusion of $0\hookrightarrow \mathbb{A}^1$ is precisely (the spectrum of) $K[x]/(x^2)$, and this exponent $2$ (which is the ramification index of $\pi$ at $(0,0)\in C$) "sees" this higher multiplicity via. nilpotence!