We spent 3 weeks here, partly to go to Rapa Nui and partly waiting for two parcels (one of which we changed to Raiatea). Like 99% of Yachties, we never left the main Papeete area and stayed with loads of other boats while the rest of the island has empty anchorages.
Papeete is a little big city, full of bars and clubs opening at 10pm. There are apparently no female prostitutes on the island, but there is a street full of Mahu - men who are brought up or choose to be like girls - (very convincing too).
The locals will gather around floating bars (you wouldn't want to fall off one of these bar stools!) at the pass. Up to 200 people in party mode chill out in the shallow reef waters.
Every day, through the main anchorage, young men paddle up and down in early morning and late afternoon. Incredibly a 1 man local canoe (paddled by a girl) can go as fast as our tender with a 5hp engine. This one is very fully dressed for a local paddler.
Tahitian hospitatility is fantastic. Free meals and booze and never more than 2 minutes for a lift, even when we hitch-hiked at 01.45 from the airport. Expensive, but such a great, friendly ambience.