Does it run all the time, gobbling up RAM and CPU?
No. Run it when you want. You might want to run the Impulse Dock all the time though.
Does it mess around with the registry?
It adds keys and values . . but no more that Stardock Central, the current app.
Is it set up so that it loads at startup?
Nope. Again though . . the dock has value so you might wnat to enable that option.
Can I still play Sins online without it? Does it have to be running in the background in order for me to play Sins online?
No and yes.

What if I don't want hand holding and want to go download patches and mods and maps on my own the old-fashioned way without Impulse?
Mods and maps will still be available through the website. Updates and patches will follow the same pattern as with SDC and the web site.
What will it do for me that I can't already to on my own?
Improved game matching for online play!