It really depends on the kind of program. If you can control the target machines it makes it a bit easier. If you know that even at the extremes, you aren't going to run out of memory, then you ight as well use all you want. There is no advantage in memory not used by anything.
In general I think of things in several categories.
Supplemental programs, If the program is not performing the primary use of the machine then It should try and conserve memory, While not a server thing the examples I usually think of in this case are Desktop widgets and Tomboy.. They are not the primary use, so they shouldn't take too many resources away from the system, that could potentially impair the performance of the primary application.
General applications, These have simple priorities. Firstly do the job required, then if it's slow, make it faster. You shouldn't need to worry about memory too much unless you are being foolhardy (or using python or java :-) )
Many instance applications. If you expect the user to have many instances of the application either as multiple tasks or just multiple instances within the same task (like multiple firefox windows/tabs), Because things multiply you need to keep a handle on memory usage. Speed s not so much an issue of making operations faster as ensuring that idle instances are not actually doing any processing.
Jumbo applications, If your application actually has a huge task to perform, like say image manipulation, then you should consider memory usage from the outset. I suspect Evolution consumes a lot of ram (currently 142 meg on my machine) because they had a jumbo task but didn't realise it. I have a lot of email, mostly incoming from lists,
If you can control your target environment then you can just have as much ram as needed, It's easier for you. If other users are going to have your program then requiring more memory is still easier for you, but it's not friendly to the users.
I'm doing development on an OLPC XO, Largely trying to make the system nice with supplemental programs. That means I'm really focused on low memory usage, but even on a system that is that memory restricted I find that there is not much use in further reducing memory usage. After boot it has over 150 meg free. Which is enough to run all of the lightweight apps you want but most of the heavier apps will be a strain. There is very little middle ground. Further optimising a 2 meg app to only use one meg doesn't give you that much more elbowroom if you are running an app like firefox.