We get a lot of people who come here asking about "lightweight" addons. I get tired of writing this repeatedly, so here we go in one happy sticky place:
When talking about addon performance, you really need to consider two things: How much memory the addon consumes, and how much of your computer's processing time (CPU time) it uses. Of the two, CPU time is by far the one you should be caring about the most.
But I only have 2GB of memory!
For most people, WoW uses somewhere between 600 and 700 megabytes of memory. I was running about 35 addons when I took that screenshot, about 30 megabytes of memory to addons (which is already included in the big number.) Compared to how much memory the game itself uses, even if you use double or triple the amount I was there - it's a nonissue.
Yeah, if you ran it up to 200 or 300 megabytes of memory used by addons, it might become a problem. Then again, if you have a fast system with a lot of memory in it, it probably isn't a problem.
But addons kill my framerate!
Addons can affect your framerate, but that's because of addons that are poorly coded and chew up lots of CPU time. You are not losing performance because of the amount of memory an addon uses. Almost never.
Case 1: Gatherer uses a ton of memory to store the locations of resource nodes. Gatherer does nothing at all in an instance, so that memory just sits there and causes you no grief.
Case 2: Many addon authors will memoize (pre-calculate and store) data ahead of time. This might mean that when you are in combat, the result is simply a memory lookup instead of a calculation. This is done to increase performance. You are trading space, which is cheap, for CPU time, which is expensive. You are gaining performance by using more memory.
With that said, memory that is bloating in an addon for no apparent reason can be an indicator of a badly written addon. In this case, the problem is the addon using memory badly, indicating excessive CPU time used that shouldn't be and an addon that does not manage its resources well. The other side effect is that the Lua garbage collector has to work overtime to keep up with the memory churn, which can degrade performance. In this case, the culprit is still not memory. The memory usage is simply a clue for you. Note that things like Recount are a good example. Recount simply stores a hell of a lot of data.
TL;DR: Memory is the least of your concern in 95% of all cases.