There can be found a lot of info about tripods in all kind of forums or on youtube. However, I like to share some of my own thoughts specifically related to the macro photography.
In most of the macro cases we need to work close to the ground. The best way to do is to use a standard travel tripod upside down. That makes the center column and the ball head hanging close above the ground.
I am using a Sirui T2204-X, and I am quite happy with it.
There are tripods, where the center column can be removed and replaced upside down. That is not good, if the tripod legs are used in the standard way: In the standard way the angle of the legs is "locked" (with different angle options, but locked) to make the tripod stable. That is very useful for the standard landscape photography, but not for macro photography.
For macro photography it much better to have the flexibility of changing the angle of the legs "unlocked". That allows to change the height and position of the camera easily by increasing or decreasing the angle of the legs. The center of gravity is quite low for the whole setup, so it is stable enough.
Another topic is the "hanging camera": I have tried to take a photo with such hanging camera upside down, but I must admit, I gave up.
In principle it is nothing more than a double ARCA-SWISS compatible profile, including the counter part - double sided, too.
In fact that rail is simple, but without alternative. It allows to use the camera in the correct orientation, even though the ball head is upside down.
At the same time it allows me to shift the camera forth or back to fine-adjust the focus distance.
A possible alternative might be a huge gimbal, which might be used hanging upside down at the center column. Turning the flexible part down allows to mount the camera in the correct orientation in low height, too.
But only the revers rail offers the flexibility to move the camera forth or back a few inch to adjust the focus distance.
The picture on the left shows the Nikon Z6 with the Sigma 105/2.8 close to ground, mounted on the reverse rail.
Another tool, which I learned to appreciate, is a macro binocular. The Pentax Papilio II is available in two versions: 6,5x21 and 8,5x21, both available in the Traumflieger-Shop: www.traumflieger.de
I prefer the 6,5x21, because it is brighter and less critical in terms of blur. And the lower magnification of 6.5x is good enough.
At leats the Pentx Papilio II allows a close-up distance of 50mc (about 20 inch)!
Sometimes I enjoy sitting in the garden with my binocular, and I watch insects without taking a single photo.
My first choice for artifical light for the macro photography is an LED torch. Or better two of them.
It is important to get an LED, which provides a very high CRI (CRI = Color Rendering Index). Standard LEDs end up at ca. 70, and that leads to weird colors, especially if that light is mixed with sunlight.
Most likely there are many sources for such LED with high CRI, but I can again recommend the Shop www.traumflieger.de, whicj offers several of them.
I use two ACEBEAM EC-65 Nichia, which are perfect for my application. They are small and powerful. The brightness can be adjusted in 5 steps - and they offer a CRI above 93.
As additional accessory there is a clamp mounting, in which the torch fits.
This clamp mounting contains a very small ball head, and can be mounted either on a tripod (using the standard ¼‘‘ screw) or using the flash mount of the camera.
The clamp mount including its small ball head then offers the similar position of the light source like a standard system flash (as shown in the picture on the left). It is quite simple to build diffusors or simply use standard flash diffusors.
Sometimes I use a small cheap tripod to mount the LED torch: For moving subjects this is less usable, but other subjects like e.g. flowers or mushrooms can be illuminated nicely.
Playing with the light from the side makes a lot of fun, since the effect can be directly seen on the camera monitor.
The flash does not work in combination with the focus bracketing - at least not with the Z6. But for single photos or small manual stacks a flash can be very useful in macro photography.
There are "real" macro flash kits available, either as a ring flash or with a ring, where multiple flash devices are mounted in front of the macro lens. I do not want say anything bad about such kits, since I am using the Nikon SB200 kit myself from time to time.
However, these kits typically cost a lot of money, and their use cases are limited. The advantage of such a kit is the usability: it is one flexible unit, which allows a nice freehand handling of the camera + lens + flash.
But in most of those cases the light is "flat" and does not lead to attractive photos. For documentary photography it might be ok, but it does hardly trigger creativity...
Ugly reflections on the skin of beetles or in the eyes of insects are typical for such flash kits.
A diffusor is required to avoid such reflections. And such a diffusor can be used together with a standard system flash anyhow.
So normally no specific macro flash is required.
Action photographer Mark Twain:
„Thunder is good, thunder is impressive; but it is lightning that does the work.“
I think it makes sense to distinguish between macro work in a studio or in the nature.
Macro in a studio
The adjustment of the flashes (one is hardly ever enough) is quite a piece of work itself. It can easily take a lot more time to adjust the flashes than all the rest.
It makes sense to build clamps and mounts to fix flashes, diffusors, etc.
It helps, if the flash control (trigger) is wireless. A flash exposure control or any of the enhanced flash features of modern cameras is typically not required at all (even though the camera marketing makes us believe it).
It is important to be able to control the power of the flash (to reduce the flahs power).
But all that is not the topic of this page, it could be a topic for another book.
Maco in nature
Also in nature the flash control does not require the fancy enhanced flash features. A simple manual power adjustment is enough.
Try out you own, and you will be surprised how simple the correct exposure with macro flash can be.
Especially if the subject distance is fix due to the given magnification, a pre-set flash will do it.
As long as magnification, aperture and ISO setting stay constant, the required flash power will stay constant, too.
When starting my photo session in the nature I set ISO, aperture and exposure time. Afterwards I choose a flash power level and take a photo from a trial subject. The result indicates, whether the power level of the flash is correct or not. I repeat this, until the flash power is set properly. And with this setting I go and take my photos.
It makes sense to reduce the flash power a lot:
First of all that reduces the amount of light to a reasonable level.
Secondly it allows the flash to recover much faster, and to be ready for the next photo.
And last but not least, the reduction of flash power reduces the duration of the flash, which helps to freeze movements (of insects).
My recommendation is to play with the overall exposure time:
the background gets dark, if it is very short. You can use that for your composing, and in some cases a dark background is even what you want.
The principle is - a bit simplified - this:
The camera exposure time is responsible for brightness of the environment, the flash power is responsible for the brightness of macro subject.