It does depend on your cpu. If you reach the chip's limitations, you could very well hit a voltage wall before temperature becomes an issue. Heat isn't the only factor in an overclock. If you're well within the safe voltage limits but reaching maximum thermal limits then a better cooler will help. If your temps are ok and the chip is becoming unstable at higher voltages or increasing vcore isn't producing much gain in terms of speed, it won't matter if you have 5 480 radiators linked in series it won't help.
The reason I say this (and again depends on the processor you're using), my cpu is a perfect example of this. I'm using a dark rock pro 3 which is a larger tower style air cooler. Temps aren't my limiting factor, my vcore has to be pushed from around 1.275v to 1.33-1.34 to go from 4.6 to 4.7ghz on a 4690k. Requiring that much additional vcore for the very small gain of 100mhz tells me that my chip's physical limitations are being reached. My temps under load during stress testing still stay around the upper 60's and low 70's at 4.7ghz - if I pushed it harder with more vcore I won't be risking thermal damage but voltage.
Liquid coolers can be reliable, they can also have issues. More so than air coolers tend to have. Air coolers don't leak and while it's not overly common, any liquid cooler can leak as an inherent risk. Sometimes there are problems getting the software that comes with some liquid coolers to work properly, sometimes pumps are faulty or can go out. As a general rule in just about anything, the more moving parts the more chances for problems. A water cooler, the fittings can leak, the hoses can leak, the radiator can leak, the fan(s) could fail or the pump can fail. In an air cooler, the fan(s) could fail since they're really the only moving part.
Here's a good write up by 4Ryan6 here on tom's.
http://www.tomshardware.com/forum/id-2196038/air-cooling-water-cooling-things.html
Another article on tom's..
http://www.tomshardware.com/reviews/h100i-elc240-seidon-240m-lq320,3380.html
It depends on what you're after. If noise isn't a problem most aio coolers do a decent job and the larger they are (dual rad over single) the better they cool. They're not always the quietest solutions and even though they tend to outperform larger air coolers by a couple degrees the air coolers pretty well keep up with less noise. Water cooling doesn't have to be loud and can be quieter, but usually that's if using slightly larger (over rad) designs with a custom loop and multiple fans run at lower rpm's. It's hard to just say 'water cooling' as a blanket statement, that could be a single h55/h80i with a single noisy fan, an h100i/h110 dual fan radiator clc cooler or it could mean separate water block(s), pump(s), reservoir and a any number of custom radiator configurations on a full blown water cooling loop.
Then there's cost to be considered. A noctua nh-d14/d15, phanteks tc14pe, dark rock pro 2/3, cryorig r1 etc run around $75-$90 - an h100i/h110, thermaltake water 3.0 or kraken x61 run around $100-140 or so. Full blown custom water cooling can run $200-300 or more.