This summarizes my understanding of some of the basic ideas. Because it is hard to find all of them clearly described and summarized in one place, I could be wrong or misleading about some of them: comments and corrections are welcome.
"Geoids" are approximations to a surface of gravitational equipotential.
The geoid is a hypothetical Earth surface that represents the mean sea level in the absence of winds, currents, and most tides. The geoid is a useful reference surface. It defines the horizontal everywhere and gravity acts perpendicular to it. A carpenter’s level aligns itself along the geoid and a carpenter’s plumb bob points down the vertical or perpendicular to the geoid. Water will not flow in aqueducts if the pipes are perfectly aligned along the geoid. Surveyors use knowledge of the geoid and the horizontal when they lay out highways and boundaries.
(NASA)
To get a sense of what is gained relative to a sphere or ellipsoid, note that
The difference in apparent elevations between a spherical model and a good ellipsoid is up to two dozen kilometers. This translates to maximum positioning discrepancies of about 22 kilometers. The relatively large amount of positioning discrepancy occurs because there is a systematic distortion of the sphere relative to the ellipsoid: it attains one extreme at the poles and another extreme at the Equator.
The difference in apparent elevations between a good ellipsoid and a geoid is typically less than 100 meters (about 0.1 kilometers). This is not a systematic difference: it varies a lot across relatively short sections of the earth (on the order of hundreds of kilometers). Consequently, the maximum horizontal positioning discrepancy resulting from any hypothetical geoid-based projection is likely on the order of meters or less (usually much less except perhaps over large, carefully chosen areas).
However, the deflection of the geoid (which is the amount by which true gravitational vertical direction varies) reaches up to about an arc-second, which makes it unsuitable for any kind of very high-accuracy mapping based on measuring latitude in terms of a local upward-pointing angle. An arc-second of deflection translates to almost 30 meters on the ground, and such deflections can vary from one extreme to the other over just a few hundred kilometers.
In return for squeezing out that last 0.5% of accuracy in describing how the geoid varies from the ellipsoid, you need hundreds to hundreds of thousands of parameters compared to two to describe an ellipsoid. Yes, it is mathematically possible to define a projection based on a geoid instead of an ellipsoid. [See "Coordinate charts" on pp 4-5 of this text, for instance. The modern mathematical definition of smooth curved surfaces, like a geoid, is based on a set of projections. The Implicit Function Theorem guarantees such projections exist for the geoid.] Computation would be, to say the least, inefficient (although it could be sped up by interpolation in precomputed tables). When necessary, the difference in vertical positioning can be computed after ellipsoid-based projection in terms of the geoid parameters or by interpolating in a precomputed grid of geoid values.
A serious potential problem with basing map projections on a geoid as reference surface is that the geoid is constantly changing worldwide. It will change with changes in sea level, for instance.
Because nowadays much geopositioning is done in geocentric coordinates, rather than by means of gravitational-based triangulating devices (such as levels), use of a geoid is practically irrelevant: an ellipsoid--however well it might or not be related to gravity, sea level, or the actual shape of the earth--serves as a reasonably stable reference surface relative to which everything else can be located and mapped. The geoid is then described relative to this reference. Its description is used in mapping primarily to permit GPS satellites to improve their positioning accuracy.