There is no best setup. The most important equipment for macro photography is between your ears. It's mostly up to the photographer to use his/her experience shooting, full mastery of his equipment, and a little luck sometimes. We all have a tendency to be gear heads, I know I do. It's better in my opinion to shoot with what you have, to the best of your ability.
I don't think you need anything beyond the camera and housing to do macro in shallow water. Deeper, strobes are a must.
The S90 has essentially just been obsoleted by the S95, same price, more features. So, buying new, start there (or maybe, take advantage of all the people dumping their S90s on ebay and get a cheap one!). Looking at the specs on the Canon website, I don't see any improvements that really matter to me. Note that it has the same lens, so essentially the same close focus distance of 2 inches from the lens, maybe 1.5 inches from your housing? That's only at the wide angle, I never can get it to close focus on tele. That's plenty close, because you need some space to aim your stobes. I think if you put a 6 diopter macro lens on (like the popular Inon 165), you can focus with the tele closer.
You can try a wet macro lens, if you have a way to mount it on your housing. Each housing has its own methods. The standard +6 diopter Inon 165 is all I would want; more magnification gives away too much depth of field to be practical. I've tried a +20 diopter lens, it was a challenge to do anything useful with it. Maybe on Pygmy seahorses?
How you focus is important. With macro, you have almost no depth of field, so be sure to focus on the key feature of your subject (usually the eye) and try to keep its body in the plane of focus.
On the S90, I set AF Frame to Center, and Small. The camera always seems to find the wrong subject to focus on, so I essentially force it to spot focus and recompose.
You may want to manual focus. That uses the rear dial, or if you have the Canon or Ikelite housing, the combination of the S button held down and the left or right arrow buttons. Memorize all that, so you can set it quickly when that once in a lifetime macro shot appears.
I mentioned lighting. You don't need a powerful strobe for macro, but you need to be able to reduce it's output, either manually or using TTL control or a diffuser. A ring flash might be ideal, if you can afford it. Some don't like the flat lighting of a ring flash, personal preference, but it gets even light for sure. I'd experiment before you dive, in a pool, to see what works best for you.
