I pin my Debian machines to the "Testing" branch and "apt-get dist-upgrade" once a month, and I've never had a problem, except once or twice when NVIDIA drivers needed to be re-installed. It's worked great for me for 15 years, but YMMV.
The CPU/memory usage almost certainly has more to do with the software being used than with the distro. The Linux kernel and GNU system utilities can run on very low resource machines, but most "desktop environments," like GNOME, KDE, and even XFCE, are targeting up to date systems. IME, using old hardware usually means putting in some effort to find and configure software that will run well on it - I'm pretty sure it's the same on any OS.