If you're serious about macro photography with the R5, the Canon RF 100mm f/2.8L Macro IS is unbeatable. The 1:1 magnification, hybrid IS (which pairs perfectly with the R5’s IBIS), and the spherical aberration control ring make it a dream for detail-oriented work. I’ve used it for insect photography, and the sharpness is jaw-dropping. Yes, it’s pricey, but the weather sealing and dual nano USM for smooth focusing are worth every penny for pros.

ngl i am in the exact same boat and it has been driving me crazy for months. I have been obsessing over the technical specs and diffraction limits for the r5 sensor but i still cant find a setup that actually meets my requirements. I have even been trying to diy some macro bellows and custom lighting rigs because i want more control over the working distance, but i am still very cautious about committing to anything. You really have to be careful with the technical trade-offs at high magnification. It is really frustrating when you are looking for that specific level of data-backed performance and nothing seems to fit perfectly... still searching for an answer myself.

If you need more reach, consider the RF 70-200mm F4 Macro G OSS II, although it's not a dedicated macro lens;

RF 85mm f/2 Macro IS STM is a good choice.

Wait, what are you actually planning to shoot most of the time? The answer changes a lot depending on if you need working distance for skittish bugs or just want high magnification for static objects. I would suggest being careful with the native RF options if you need more than 1.4x magnification. Here is what I have noticed from the technical side:

• The Canon RF 100mm f/2.8L Macro IS USM is technically impressive with the 1.4x, but the spherical aberration control ring can actually mess with your bokeh if you aren't precise. It is a bit of a learning curve.
• If you are okay with manual focus, you might want to consider the Laowa 100mm f/2.8 2X Ultra Macro APO. It offers 2:1 magnification which Canon doesnt currently match natively. For tiny insects, that extra 1x is massive.
• Be careful about using adapted EF glass like the Sigma 105mm f/2.8 EX DG OS HSM Macro. It works, but the AF can pulse a bit more on the R5 compared to native RF nano-USM motors. The R5 sensor is so high-res that any vibration or missed focus is instantly visible, so make sure to look at how these lenses handle focus shift when stopping down. Personally, I find the 1.4x on the Canon a bit niche tho.

Of course the best choice is the RF 100mm Macro.