I had a water birth with my first, but not with my second (there was no pool available). I can tell you that water definitely helps with the pain! It hurt a lot less and was also much easier to manoeuvre my body. My baby was born in the water and then once I'd delivered the placenta I climbed out of the pool and sat on some beanbags for a short while. I did tear, but not too badly and only needed a few stitches.
I am definitely going to have a water birth again for my third, as long as there is a pool available at the hospital!
There were no cons for me, but I guess it does limit your options in terms of pain relief. You can't have an epidural. But I dont want one (the thought scares me more than the pain) so it's a no brainer for me.