I always follow what's going on, but I have never tried the water cooled setups.
What I'm looking for is total silence, and if water cooled setups would allow for that, I would probably try. Zalman reserator some 5+ years ago seemed pretty neat.
But the PSUs are always air cooled, and this sort of breaks the whole fun.
Also, I'm afraid of leaks. I tend to leave my PC unattended from time to time, and some pipe getting loose over hot wires seems to be pretty dangerous.
How silent your watercooled system is basically comes down to the radiator fans and the pump. If you are not concerned with getting maximum cooling performance, you can get some quiet fans and run them at fairly low RPMs. The noise of the pump has a lot to do with placement and care of installation (ie, if you insulate it from the case with vibration absorptive materials, etc). You can also get pumps that are PWM controlled, so you have some flexibility between noise/performance. Theoretically, if your motherboard comes with good fan control software, or if you invest in fan controllers, you could set up a profile so the pump and fans only spin up with temperature. The other thing the noise level depends on is the amount of radiator surface area. Obviously, if you have more rads, you don't need to push as much air over each to achieve the same amount of heat dissipation. All that said, if your objective is to push a bunch of overclocked components to the limit, a water cooled system usually won't be all that quiet - but at least it can be setup to be while you're not pushing your hardware using dynamic fan/pump control.
As far as leaks go, I'm still a little nervous about that too - this being my first time watercooling. So far, so good (only been a few months). If you don't already, I would recommend using compression fittings between the tubing and components. You pay a bit more for them vs barbs perhaps, but they give you a bit more piece of mind re leaks, look better, and generally make assembly/disassembly easier. I'm also using a few rotating angled fittings (all Koolance). I was nervous about those leaking at the rotating joint, but again, so far so good. I kind of needed them in my loop as I have very little space between some of my components - I have a 2x120 rad at the back, a 3x120 rad at the front and my reservoir, cpu, VRM, GPU blocks in between, all inside an Antec 1200, including the rads.
You can get PSUs with fan profiles that are completely silent when they're not being pushed. For example the Seasonic X-series, you can set the fan to shut off with lightest use and spins down drastically with moderate load. The XFX PSUs based on this platform do similar.