Hi and welcome to the forum:
If we are just considering that focal range (which is what I assume you are considering), then there are many aspects to this, starting off with budget. The weight, bulk, complexity and thus the cost of lenses with very wide apertures are great, so one must have a good reason for wanting to accept those characteristics, and that depends on what one photographs. So, for a start, one lens type does not match all needs. Just taking the Rf 24-105 range you refer to:
Lens variant------ Weight ------- length -------Width
f/2.8----------------- 1330g -------- 199mm ----- 89mm ..... $2,999
f/4 ------------------ 700g --------- 107mm ---- 84mm ..... $1.299
f4-7.1----------------- 395g -------- 89mm --- 77mm ..... $ 400
In financial terms alone, the difference is stark, so unless one has a pressing need to pay large amounts for an f/2.8 constant aperture, then the cost is not a great value proposition.
So What are the Benefits?
The wide aperture has two practical benefits:
First, it will allow more light in where it is limited, thus keeping the shutter speed and ISO potentially lower. However, if one is not shooting in such conditions then the benefit and thus the value is not there. If the light is low, then can move into better light, or provide more light with reflectors, strobes, etc. Using a slower shutter speed with a tripod or other steadying device is a traditional method. The pressing need to keep the ISO down is much less with modern sensors, not to mention the magic wrought in post-processing by apps that reduce or remove noise. The days when one had a very limited ISO range are long gone.
Second, it offers one method to allow one to achieve a very shallow depth of field.
But that alone is not the only characteristic that does so. There is another one of those 'trinities' at play here:
* The larger the aperture, the shallower the DoF
* The closer the subject, the shallower the DoF
* The longer the focal length, the shallower the DoF
Thus a shallow DoF can be achieve by multiple effects in combination. We can explore how just focal length works by using a Depth of Field Calculator:
The three examples above demonstrate how close the depth of field can be using those lenses, simply by changing the focal length slightly - this will create a slightly cropped image, but the cost difference is massive, so where is the value.
Simply put, for the vast majority of people (since you do not specify who would be making this choice) who would be in the market for that focal range, most of them will be perfectly happy with the results from either of the much, much cheaper alternatives.
A fixed aperture does not, in itself, make an image sharp. The steadiness of the camera, the movement of the subject relative to the lens, the depth of field required to get the subject in focus, and the quality of the camera sensor itself all have an impact.
Simply buying a prime lens or a more expensive zoom does not make for great images - it is far more down to the skill of the photographer. For example, how one holds a camera can make a huge difference to the image quality. A lot of people come to photography with a dedicated camera having been used to cell phones, which are extremely compact and light, and use significant computational algorithms to improve the quality of the images. They use those cell phones by holding them out in front of them, supported only by two arms, which is not a stable platform, but one can get away with it because the cell phone is so light and the physical focal lengths are absolutely tiny.
However, dedicated cameras are bulkier, heavier and use much longer focal lengths, thus having profound impact on the image. The correct method for holding a dedicated camera is demonstrated in this illustration:
The above method seeks to create a tripod effect with one point of support with the EVF against the face and two others via the arms tucked tight to the torso. Yet, I see photographers holding a fairly heavy camera as if it was a cell phone to take still images. This is a very unstable platform, so the weight of a heavy f/2.8 lens will dramatically increase the risk of a shaky image. In the examples below the photographer was shooting stills with an EOS R5 (738g) and an EF 24-105Lf/4, plus EF-RF adapter (with a combined weight of 905g) - total weight 1,643g).
