... I've heard that a bad PSU can sometimes fry a motherboard and everything on it ...
The typical death of a junk PSU that can't really develop half the advertised power is the switching transistors blowing up:
This kills the PSU, but as the fireworks happen in the primary (left zone in video), the mobo & CPU usually survive the ordeal.
But if the +5VSB rail goes amok, that's a roasted southbridge and murdered mobo. The Worstecs are particularly infamous:
Also if a secondary rectifier buys the farm chances are good there would be carnage downstream.
With newer systems, this can be a good excuse to upgrade, but with a retro rig full of rare parts, it could be a real catastrophe.
With this in mind, does it make sense to replace aging PSUs before they show any signs of failure? (the idea being that a well-placed $50 could potentially save a lot of hassle and expense later on)
Lots of sense. And you don't need $50, for ~$30 you can get a decent 300~350W PSU with enough juice to power any relic up to Athlon XP/Preshott level.
Alternatively if you are handy you can overhaul any good ole PSU with fresh capacitors, fresh fan/s and other improvements.
I'd be far more inclined to trust a random PSU from the '90s than a random 'modern' one. Ones in the '90s didn't tend to have the problems with crap capacitors and over-rated specs like you find so often in newer PSUs... not to say that there weren't crap PSUs back then, but a unit that's survived this long probably isn't one of them.
Problem is capacitor aging. Even top brand jap caps will be tired more or less after 20 years. But that's nothing a recap can't solve however.