I get asked all the time if the additional cost for the overwater villas in places like Tahiti are "worth it." I now answer a resounding "YES!"
While it's true that the cost differential can truly be significant (especially in, say, Bora Bora), if you can swing it, it's definitely a once-in-a-lifetime (for most people) and you most certainly get what you pay for!
I went on a Tahiti cruise with a friend and we spent a few nights at the end staying at the InterContinental Resort in Papeete. We got a good deal on a "Panoramic Bay View" room, but shortly after arriving and visiting friends in their overwater villa, we quickly ran to the front desk to see about upgrading our room. Thankfully, they were able to upgrade us on the second and third night.
It was amazing... The privacy, the beauty... Being able to walk out the back of our hut and jump into the water directly... No land-bound room could possibly compare.
And so case closed. Any time I travel now to a place where "overwater" is even an option, that will become my only option.