I remember hearing about of lots of different mods during my Diablo II days. They were a violation of the terms of service, so Blizzard was threatening to remove accounts that ran even a single addon. "Even Maphack?" people would say. Yes, even Maphack, said Blizzard. I was never tempted to download any of these things (even Maphack) afraid that I would inadvertantly pick up a virus, or something that would cause my computer to go up in flames the next time I killed Baal.
So, I waited a long time before running any addons with WoW. I guess I assumed they would be a violation of the ToS for this game, too, but evidently they're not. I'm sure there are some that Blizzard frowns upon (and I've read about creators that have retired addons after such frowning, as with BottomFeeder), but there are tons of useful, legitimate addons out there. I know there are some that view using any addons as cheating, but if Blizzard thinks it's ok, then I'm okay with it, too.
I got a little crazy with the addons at first, and downloaded all kinds of stuff that I've long since abandoned. I used Koordinator to show my in-game coordinates, but scrapped it once I picked up Titan Panel. Also abandoned were Cartographer (maybe this is weird, but I like having the terrain slowly revealed to me as I explore), and DamageMeters. You know, I actually really want DamageMeters, but when I installed it, the little module thing appeared in the middle of my screen, so I clicked on it with the gloved pointer to move it. Then, I somehow could not let go of it. No amount of clicking (with shifting, alting, or anything else I could think of) would allow me to get it off my glove (get it off meh!), nor could I even use the pointer any more to exit the game. I had to alt-tab out and end the game through the Task Manager. That sucked. Any insight into this one is welcome.
Initially, I picked up KLHThreatmeter, because it seemed like the most popular threatmeter at wow.curse.com (one site my brother-in-law told me it should be perfectly safe to download stuff from). I liked it fine, but heard from several (usually with a bit of superiority in their tone) that Omen was better. Sometime after the most recent patch, KTM started behaving strangely, appearing in random spots on my screen when I logged in, often just off the edge of my screen, offering barely a sliver for me to grab it and move it to a better position. (Sometimes I'd never be able to grab it, so I'd just have to give up and keep it hidden for the session, since I didn't know how to reposition it.) So, I got rid of it and have Omen now, and yes, it is lovely.
I also run Auctioneer, though I still don't think I've scanned the auction house enough times to truly benefit from this mod. I don't use the automatic pricing features, but I do use it to give me a ballpark idea of what things are going for. I have certainly made more money since I picked up this addon, but I also spend more time in the AH these days anyway.
Another one of my favorites is Lightheaded, which is like having wowhead right in the game. This is very convenient for me, because if I alt-tab out for a quest hint and open my browser, my computer sometimes makes a funny grinding sound, and the game is sluggish when I return. No matter where I am, I'm doomed to lag like I'm in front of the bank in Orgrimmar until the next computer restart.
For consolidating my bags, I'm currently using Bagnon. It broke after the recent patch, but it was updated and seems to be fine now. The only thing I don't like is that I can never see what keys I have. When I click on the key icon, it just opens my inventory. I may switch to something else when seeing these keys becomes more important.
Two recent additions (courtesy of BRK's addon list) are Autoprofit and Bartender3, and both are quite handy. Autoprofit is a bit dangerous when you have low level characters that might be carrying around gray items that they actually intend to use. My poor little paladin accidently sold her shield without noticing it when I clicked autoprofit. When she decided to put away the 2H weapon and level up the sword, well... turned out to be too late to get it back in the buyback. Heh...
I'm sure I'll collect more addons as I advance in the game, particularly as I start running higher level instances. I imagine cooldown timers and things like that can really improve one's efficiency.
What's the addon you couldn't play without?