If I have UV maps like the one shown in the image (in the zoomed-in image you can see the very small/tiny UV islands circled red which will occur when using Auto UV solutions in Houdini (even after running something like LABS merge small islands and it also helps to avoid overlaps)), is this really bad when doing a normal map bake in say substance painter? Is there anything I can do in Houdini to mitigate potential problems, or is this not a problem for map bakers?

I'm only doing these sorts of auto UV stuff for models that I create in ZBrush and use auto re-topology solutions because they aren't deforming meshes and as such, excellent topology is not a must and I would hate to spend the time doing manually re-topology if it's not necessary; but UVs must be able to work great for normal bakers and such. Any guidance in this area is much much appreciated.

Merge Small Island option in autoUV by labs should help with this issue

osong
Merge Small Island option in autoUV by labs should help with this issue

Sometimes the merge small islands cause overlaps; but what kinds of major issues will these small islands cause when baking and texturing in a tool like substance painter?

Off the top of my head:

• Many small UV islands, some of them, from what I reckon, on continuous surfaces, will make seams very visible in certain conditions (on metallic surfaces of medium to high roughness, for example).
  
• You probably will have to forget about UV projection and will need to rely solely on Tri-Planar. Which is problematic with materials that have a directional pattern, like wood.
  
• On UV islands smaller than 1 pixel of target texture resolution (or close to it), you'll get bleeding, or it might not be possible to paint on that island at all.
  

the overlaps then can be helped out by "Remove Overlap" from Labs.
Major issues.. i don't think anything "major" will be present, if you don't use Sharpen effect in sbsP, which usually messes up the seams.
If you have latest sbsP, definitely check out built-in auto-UV of theirs. I find it usually working better than Houdini solution, and it too can pack into UDIMs, pretty neat.