Whether Steam is a resource hog or not is all relative to what resources your computer has.
It should only use a set amount of memory and processing power, but if your computer only has small amounts of those resources, then Steam will use a greater percentage of your computer's total resources.
Most people with modern desktop PC's don't have any problems with Steam "hogging" resources, but if your computer is underpowered, then you may find Steam slows things down a bit.
The only way to be sure is to download it and find out for yourself. Use Task Manager to monitor what Steam is using and if you decide it's too much, then it's easy enough to un-install.
Another option is to close down other, unnecessary processes while you're running Steam in order to free up more resources. For example, you don't need to have your internet browser open while playing a game and if you really need to check something, you can use Steam's built in browser.
A final note about Steam is that by default, it starts up when you turn on your computer. For most people this isn't a problem, but some may find that this is an unnecessary drain on resources and slows their computer down too much, especially if Steam starts downloading large updates for games as soon as you start Windows, but this behaviour and automatic downloads can be switch off in the options.