Well that will largely depend on what you want to do in WoW. For instance if you are a hardcore raider, the answer would be different than if all you do is PVP. Also there are quite a few class-specific addons that are good depending on your class.
But, there are quite a few that are almost essential to have for anyone.
ArkInventory, I like this one personally because it helps keep my inventory organized. I can setup filters to sort my gear and items into columns depending on what they are, etc. There are tons of features to it.
Auctioneer - Scans the auction house and keeps average prices for all of the items. Also shows how much things are worth to disenchant, vendor, etc. Incredibly useful and easy way to make money.
Autobar - Automatically sorts all of your consumables, trinkets, mounts, etc into a bar. If you pick up a new piece of food or potion it will automatically show up on your bar on your screen. Even assign hotkeys to the different categories.
AutoProfitX - Really great little add-on that allows you to automatically sell all your grey vendor trash items to a vendor with one button press. Saves tons of time hunting through your inventory. You'd be surprised how much that vendor trash is worth! After doing some daily quests in Outlands I'll usually have 10g - 15g just from selling vendor trash with this add-on.
Bartender3 - Button replacement, allows all kinds of really powerful modifications to your interface bars. There are lots of other options in this category though, that is just my favorite one.
Cartographer - Incredibly useful map addon. Takes away all of the black stuff hiding the map, allows you to insert points, send points to friends, see where your guildmates are at all time if they have the mod installed, track herb/mining points, etc.
EquipCompare - Better interface for comparing items side-by-side.
Questhelper or
Lightheaded - Pulls information about quests from the internet to tell you where to go, how to complete quests, what order to do them in, etc. Really great for leveling up.
Mobinfo2 - Keeps track of the health that each mob has, what it drops, how many more you need to kill to level, etc.
RatingBuster - Converts all those ratings and values into percentages and other easy to understand terms for your gear.
There are hundreds more that are useful but those are a few good ones that would help almost anyone.
Checkout the
Addon Spotlight on WoWInsider for other good ones.
And of course be aware of the addon sites, they usually have "top rated" or "Most downloaded" sections that will give you a good start as well.
Curse
WoWUI
WoWInterface
WoWAce
