You probably will not have any problems with using new(er) software on older hardware. I run Tiger on a G3 iBook. The ram is maxed out at 640mb, and while fairly slow, the machine is still very usable. If only the hard drive was larger...
On the other hand, older software may not run on newer hardware. Unlike Microsoft, who seems to support old APIs forever, an Intel Mac will not run ancient (Classic) software (even in Rosetta). I assume that is one of the reasons Windows has so many problems. Having to support software that is over 20 years old doesn't seem like something that would make an OS very efficient. (I still play games 10+ years old on Windows XP).
-Sam