Desktop managers and graphics intensive installation programs are what fundamentally consume a lot of resources, and large, memory hungry applications are also capable of slowing a system. If, however, you use a lightweight window manager and moderate sized applications, it is possible to use quite an old system (~10 years old) and actually perform useful work. Fluxbox was mentioned multiple times as a window manager that is not too heavy on resources. IceWM, JWM, twm, and fvwm are four other window managers that will not over tax even an old system.
Puppy Linux can boot straight from a Live CD and can load completely into RAM. It is great on old computers, but it can optionally be installed either to disk or to USB stick (though an old system may or may not support this kind of USB usage). DSL (DamnSmall Linux) is also capable of running on old hardware. Both Puppy and DSL make use of either IceWM, JWM, or Fluxbox, depending on what you select during setup configuration - and they also permit switching between at least two different window managers, depending on your interests and your tastes. Puppy can be built up into a full system, based on Slackware packages (at least in the most recent release). DSL, similarly, can be built up with Debian packages, should you want to customize either of these systems.
For that matter, both Debian and Slackware also make potential candidates for a full system, provided you avoid using one of their desktop environments and instead stick with one of the window managers I mentioned earlier.
Xubuntu can cut it on some older hardware, but if XFCE (a moderate sized desktop environment) consumes too many resources, simply install one of the window managers suggested above and use it with Xubuntu INSTEAD of XFCE and Xubuntu will also get the job done for you.