As someone who now flies over water literally on every flight, I've got a somewhat unusual perspective.
When we first moved to the island, we were uncomfortable and tended to hug the land. We flew with a full set of life preservers, and made plans for what to do in the event of an engine failure at any point.
With our RV, carrying life preservers in an accessible spot is not possible. We have talked about wearing them, but the discomfort level is high, and our exposure to the warm waters of the Gulf is limited. For example, when landing at our airport, if you do a long straight-in, you are over water, at relatively low altitude, for about four miles. This is out of gliding distance, but our exposure is brief -- less than three minutes.
We figure our odds of the engine continuing to develop power for that length of time are pretty good.
Of greater concern is losing an engine on takeoff. Our options are limited to dunes, smooth beach full of tourists, or water. Our plan is to aim for the beach, but turn out into the water just offshore to avoid hitting someone. Hopefully we will never need to implement this plan.
Another concern is egress. With a sliding canopy that is not approved for opening in flight, and can't be locked open anyway, our best hope is that the canopy frame doesn't deform to the extent that we cannot escape.
Everything in GA is a calculated risk.