I did this trip last year July 25 2024. It was breathtaking. So many puffins even though they note the puffins start to leave at this time. This was a last minute addition (tickets booked 10 days in advance) so don't worry about tickets selling out.
I used Pembrokshire Island Boat Trips. 20 pound per adult to go to island and sit among the Puffins on the water. About 1 1/2 hours across to the island and sitting (plus arriving one hour before.) I didn't choose to go on the island as I wanted to use the afternoon to go onto St. Davids. In hindsight I was really glad not to have booked landing on the island as it is barren. There was very little to see. Our tour boat was smaller and very basic - no modern audio - so make sure to sit near the guide. The guide was an older very knowledgeable bird expert who obviously loved wildlife and had a seagull that flew in and rested on his arm (brilliant).
I caught the boat from a remote peninsular - we drove as we had rental - about 1 hour (can't remember exactly) away from Tenby. The parking lot was literally a field run by the National Trust: Martins Haven Nation Trust Car Park. It cost 7 Pounds cash to park. The meeting place was a short walk from the field to the grandly named Lockley Lodge Visitor Center (a small wooden hut).
I still think this is one of the most unexpected experiences I have had. I am Welsh (living in CA) and had no idea about this Puffin breeding program. It is one of the most successful in the world.
(P.S. If you are looking for another interesting outdoor activity nearby Tenby/Saundersfoot try beach foraging: www.coastalforaging.co.uk with Craig Evans)