Not all mods are like that.
That is true, but there are lots of mods out there, very good mods that will cripple some systems out there, especially if you rig just barely meets the minimum requirements.
Here is a small sample of some of the mods that I run for FO3:
Dc Interiors
Marts Mutant Mod
Dynamic Weather
Enhanced Weather
GNR:More Where That Came from
Project Beauty
Weapon Mod kits
that doesn't even include the numerous texture replacers that I have installed; which include but not limited to:
NMC's Performace Pack
Babe MK3 female Body replacer
Breeze's male body.
All of these put together will absolutly cripple alot of systems out there, so yes, mods can and do add an extra load to your system.