Short demonstration of my UI.

Full list of the addons I used at the time of this video (not all are demonstrated in the video).
All addons can be found at either WoWInterface.com or Curse.com

First a few rules i have for addons:
Low memory use
Fill a clear need that Blizzard default UI can't fulfill
Fit the look n' feel of WoW
Non intrusive look, meaning - only there when I need it.

I'm also allergic to minimap buttons. I only have the ones for temporary use there like mail and BG queue. I move all minimap buttons to my TitanPanel instead.
There are some exceptions to this, but most of my addons follow the above.

Buttons, Targets and Unit frames
I use a compilation called RothUI.
However, it contains a lot of stuff I find rubbish, so I've disabled the following parts of it: evl_Combattext, idChat, rBuff, rEmote and IHasNoScope.It does however hold a couple of stuff I'd get alone otherwise like OmniCC, but I mainly use the compilation for the look.
oPie - I love this one. It's a very easy and simple way to hide those buttons you don't want on your screen, yet not look forever for when you do need them.
It also auto adds quest items to a quick bind, making it very nice for questing.

Buffs, healing, decursive, clickcasting
For all of this I use Healbot. Even if you are not healing, it's a great addon for monitoring buffs and debuffs.
I know alot of you use Grid, and it has it's uses, but I like it all in one package, which is what you get from HB, using less memory.

Bags
Combuctor - Same author as bagnon, just more WoW looking and some extra features. Also helps you keep track of how many of X item you have on all your chars on the tooltip.
Scrap - A simple Junk seller. Combined with Combuctor Scrap it works very well with the above.

Chat
Prat - Very easy to use, modular chat addon. Lets you keep track of who's alts are whose and so on.
Bad Boy - Anti-Spam. nuf said!
WIM - very good when you have numerous whisper conversations simultaneous

Business & Professions
Auctionator - I always felt that Auctioneer was a bit too bloated for me. It just has too many functions and very inefficient. Enter Auctionator. Speeds up you AH business and just as accurate.
GatherMate - Again, slightly less bloated than Gatherer.
NutCounter - If there's something Auctionator miss, then it's keeping track of sell through. Enter NutCounter, just a very simple way to see how an item has sold in the past.
Fishing Buddy - a true Fisherman's Friend
TradeskillInfo - a complete library of all crafts, searchable. Very useful when you are looking for upgrades and such stuff. Also helps you keep track of which of your alts might be able to do the recipe.
Routes - helps you plot the most efficient route for when gathering herbs or mining. (Requires Gatherer or GatherMate for data)

Maps
With the addition of instance maps in 3.1 I decided to drop Atlas after 4 years. Don't miss it.
Mapster - Cartographer is definitely more advanced, but Mapster fulfills my simple needs, using lot less memory.
TomTom - Adds maybe the one feature I miss from Cart, and it's adding waypoints and navigator arrow. Useful for long, cross continent flights and you are too cheap to pay for it. Or questing.
InFlight - Are we there yet?

Items
Again, 3.1 made a change as I dropped Outfitter for the built in one.
ClosetGnome - I only use it get which items are part of each item set and so on. Use the built-in since 3.1 for gear swaps.
Crossdresser - Predefine a set to a spec and whenever you change spec, it changes your gear for you. Silly but handy.
EquipSetUpdater - One of the biggest misses in the Blizz Equipment Manager is that it lacks a update set feature. This sorts that.
AtlasLoot - This is one of my few exceptions when it comes to memory friendly. It's just too nice to have and saves you alt-tabbing.
RatingBuster - use it to compare items, what effect it really has. I'll toss it in 3.2 since it will be built-in from Blizzard.

Raiding
DeadlyBossMods - a must have. No further talk needed really.
EnsidiaFails - good to keep an eye on raid mistakes. We all make them, just make sure we don't do them 9 times in a row (no names).
Raid Roll - Don't you hate you can't really tell which number is which when Raid Rolling - this sorts that
Skada - Omen, DPS and damage meter all built into one. Useful in solo as well when testing your DPS. Like Recount just better designed in my opinion. For in depth analysis I always use World of Logs anyways.

PvP
Gladius - very very good for all your arena things
WinterTime - helps you with a lot of Wintergrasp related things
DeadlyBossMods - very useful here as well with flag timers and similar.
VanasKoS - jump me once, grats to you. Jump me twice - your mistake :-)

Miscellaneous
Titan Panel - like FuBar it adds bars to either top or bottom of your screen. I think it looks a lot better than FuBar and fits better into my UI. It also has built-in LDB support which FuBar lacks.
TitanPanel is key for many of my other addons and hiding them. The following I put in TitanPanel, hiding them from minimap: Fishing Buddy, Healbot, TradeskillInfo, AtlasLoot, DBM, Skada DPS, VanasKoS and WinterTime.Note! TitanPanel and rBuff (which is part of RothUI) doesn't work together, so I've disabled rBuff. It's just a cosmetic thing anyways.
TourGuide - QuestHelper and Carbonite are all good leveling addons, but VERY memory heavy. Tourguide takes up minimal memory and if you follow it, it's a very fast and nice way to speed level your characters.
Basically, it's like all those leveling guides you either pay for or have to print. Here in-game with a item help and with TomTom full navigation.
OverAchiever - Nice for a achievement geek like me. Helps you keep track of and find ones you've missed.
Buffet - a small macro generating addon for allowing keybinds to food and water or pots while in combat.
Coconuts - Helps you pick (and if you wish randomize) your mounts. However, I'm looking for a replacement for this one atm since I think it has been abandoned.