What is overdrive?
Overdrive is what it says, an overdriven amp. When an amp reaches a certain level of volume the tubes works so hard that they go into overdrive mode, which causes them to distort. An overdrive pedal can simulate this sound so you won't have to crank your 100 watt amp up to ten (or eleven, as Spinal Tap would), or it can be used to overdrive a tube amp that's already working hard with a little extra boost. The sound of overdrive is often described as natural or warm sounding.
Any good examples?
Tons of guitarists have used overdrive, so there's plenty. But in terms of classic overdrive pedal users both Eric Johnson and Stevie Ray Vaughan should be mentioned. Listen to their great tone on songs like "Cliffs Of Dover" (Johnson) and "Pride And Joy" (Vaughan). Or if you want a link between them, listen to Johnson's hommage to Vaughan on his song "SRV". Overdrive is a popular choice for many blues rock players and it may come as no surprise that a guy like John Mayer uses it frequently.
Which pedal should I get?
If the Fuzz Face is the grandfather of fuzz then the Tube Screamer is the ditto for overdrive. Originally issued in 1979 (though Maxon, who built this unit for Ibanez already had a model out in 1974) this pedal has gone through many incarnations with different chips inside and different names. The first one was called TS-808 and this model and the TS9 are often the most desired ones (or the Maxon OD808, a "copy" from the company that originally built the Tube Screamer). Both Eric Johnson and Stevie Ray Vaughan has used Tube Screamers. Another classic pedal is the Boss SD-1, famous users includes Eddie Van Halen and Zakk Wylde (who nowadays has a signature overdrive pedal from MXR called the ZW-44). Other units that are worth checking out includes Electro-Harmonix Soul Food, Mad Professor Sweet Honey, Vox Tone Garage Straight 6, DigiTech Bad Monkey and MXR Custom Badass Modified Overdrive. And of course the legendary Timmy, made by Paul Cochrane, or the copy from Danelectro called Cool Cat Tranparent Overdrive - models includes CTO-1 and CTO-2.