I apologize if this is a naïve question. I'm a mathematician with, essentially, no upper-level physics knowledge.
From the little I've read, it seems that spacetime is Lorentzian. Unfortunately, the need for a metric that isn't positive-definite escapes my understanding. Could someone explain the reasoning?
Firstly, the Equivalence Principle reduces to the statement that in a freefall frame, the spacetime manifold is locally exactly as it is for special relativity (see my answer here for a fuller explanation of why this is so).
So you can swiftly reduce your question to "Why does Minkowski spacetime have a nontrivial, non-Euclidean signature?".
Since we're now doing special relativity, there are many approaches to answering this latter, equivalent question. My favourite is as follows. I expand on the following in my answer to the Physics SE question "What's so special about the speed of light?", but the following is a summary.
To get special relativity, you begin with Galilean Relativity and basic symmetry and homogeneity assumptions about the World. If you assume absolute time (i.e. that all observers agree on the time between two events) then these assumptions uniquely define the the Galilean Group as the group of co-ordinate transformations between relatively moving observers. Time of course is not acted on by that group: $t=t^\prime$ for any two co-ordinate systems.
Now you relax the assumption of absolute time. You now find that there are a whole family of possible transformation groups, each parameterized by a constant $c$ defining the group. The Galilean group is the family member for $c\to\infty$. The only way time can enter these transformations and be consistent with our homogeneity and symmetry assumptions is if it is mixed with a Euclidean spatial co-ordinate along the direction of relative motion by either a Lorentz boost or a Euclidean rotation. Another way of saying this is that the whole transformation group must be either $\mathrm{SO}(1,\,3)$ or $\mathrm{SO}(4)$.
But we must also uphold causality. That is, even though relatively moving inertial observers may disagree on the time between two events at the same spatial point, they will not disagree on the sign - if one event happens before the other in one frame, it must do so in the other. So our transformations can't mix time and space by rotations, because one could then always find a boost which would switch the direction of the time order of any two events for some inertial observer. Hence our isometry group must be $\mathrm{SO}(1,\,3)$ and not $\mathrm{SO}(4)$, and spacetime must therefore be Lorentzian and not Riemannian.
This is what user bechira means when he makes the excellent, pithy but probably mysterious comment:
To describe causal structure.
From the little I've read, it seems that spacetime is Lorentzian. Unfortunately, the need for a metric that isn't positive-definite escapes my understanding. Could someone explain the reasoning?
The short answer: - because of the attractiveness of the geometrical description of special relativity; the Lorentz transformations (cornerstone of SR) do not preserve any positive definite form of coordinates, but do preserve certain special indefinite form.
The long answer: In ordinary spatial geometry, for any pair of points $x,y$ in space the value of the quantity
$$ D=\sum_{i=1}^3 (x_i-y_i)^2 $$ where $x_i,y_i$ are coordinates of the points in a cartesian coordinate system, is the same no matter how the coordinate system is oriented in space. In other words, $D$ is invariant with respect to any change of the coordinate system that is rotation around its origin.
In addition, in Newtonian physics this expression is invariant with respect to even greater class of changes of the coordinate system - not only rotations of the coordinate system, but also changes that correspond to a switch from one to another inertial frame. In Newtonian physics We can express this change of coordinates by the so-called Galilei transformation. Distance between two points is (in Newtonian physics) independent of the inertial frame they are observed from. The Galilei transformation is consistent with that; $D$ is invariant with respect to this transformation.
However, in special relativity switching inertial frames is not described by the Galilei transformation, but by the so-called Lorentz transformation, which does not conserve $D$. This is easily seen from the phenomenon of length contraction: length of a rod in general depends on the inertial frame.
Minkowski (in essence) pointed out that the Lorentz transformation is similar to the Galilei transformation in that there is a function of coordinates and time
$$ I = \bigg(\sum_{i=1}^3 (x_i-y_i)^2\bigg) - c^2(t-s)^2 $$
($t$, $s$ are times when two events occurred) that is preserved when the switch from one to another inertial frame is done. He proposed a geometrical language where the events in 3D space as observed in an inertial frame are described as points in 4D space. According to special relativity, $I$ is independent of the inertial frame.
With this geometrical picture, the Lorentz transformations become similar to rotations; they change coordinates but preserve certain (indefinite) quadratic form of coordinates.
I read the previous answers and I think they both miss the point, which is this: without that minus sign there would be no difference between time and space.
(I don't know much about general relativity so I'll restrict my answer to the special theory, i.e. Euclidean vs. Minkowski space.)
In Euclidean space, e.g. our three-dimensional space at a fixed point in time, in the absence of gravity, the basic magnitude that all observers agree on regardless of their choice of coordinates is the distance between any two given points. An important property of Euclidean space is that it is isotropic, i.e. that given any point O and any distance R, the sphere with radius R centered on O looks the same to all observers located at O (any two of which are related to each other by a rotation in SO(3)).
In E4 (4-dimensional Euclidean space) the situation is exactly the same: you can choose some direction and call it "time", but this choice is completely arbitrary since all directions are inherently the same. However, in Minkowski space the situation is fundamentally different: instead of a sphere centered around each point (a.k.a. "event") in spacetime, the thing that looks the same to all observers is the light cones emanating from that event in both directions of time: the past and the future. In other words, even though their time and space coordinates are mixed with each other to a certain degree, the Minkowski metric allows all observers to agree on which directions are "time-like" (the ones inside one of the light cones) and which are "space-like" (the ones outside both cones).
