All cooling solutions (passive, air, AIO, custom liquid, more exotic methods) are compromises. It's just like any choice you have (and not just PC cooling).
In this instance: thermal performance, acoustics, maintenance overhead, risk tolerance, computational performance and of course cost. And yes, also aesthetics for some.
It's also worth noting that today's AIOs are far more reliable than the ones from 10-15 years ago. If they were still really bad, there would be people reciting horror stories of tubes bursting, ruined PCs, etc. on a daily basis at Reddit, here, elsewhere. But no.
For sure, water as a coolant has more thermal capacity than air. That's physics, not opinion. Ever splash water on your face on a hot day? Same physics.
It's important to note that one can mix cooling solutions in the same build and choose different solutions for another build. Just because I prefer a custom cooling loop on a high-wattage GPU like my 3080Ti and 2070 Super doesn't mean I necessarily want it for an RTX 3050 or RX 550.
I currently have four builds running, all of them using liquid is some format. One build (my primary gaming PC described in System Specs) is a full custom cooling loop for both the CPU and GPU. The other three have AIOs on the CPU, one with a custom cooling loop for the GPU, the other two have the stock air coolers for the graphics cards.
One thing I do notice with AIOs and custom liquid is that I can delay the fan response time and still get great thermals. I do this because my ears are sensitive to fan speed changes. This is something that doesn't show up in test results in hardware reviews. Reviewers only record sustained noise levels from peak loads via a benchmark which is not my usage case. My typical everyday usage makes CPU and GPU temperatures bounce up and down.
If I can cut the number of fan speed changes by 50% by using a water-based cooling solution versus air, that's a win for me because it's a far better experience from a total ownership perspective. Again, that's directly related to water's superior thermal capacity. I am willing to pay more for better acoustics.
For my daily driver PC (using an RTX 3050 with the stock cooler), I set the GPU fan curve to always have the fans at a minimal speed. That's because going from 0 rpm to 800 rpm is an audible annoyance.
In my primary gaming PC, the waterblocked graphics card is vertically mounted and an air cooler cannot fit on the CPU.
Another noteworthy detail: using AIOs on CPU sockets provides easy physical access to the adjacent m.2 slot. More than any other component in my PCs, I move the boot m.2 SSDs around. I can reach these without disassembling anything. This is the main reason I have stopped using CPU air coolers in my builds.
I still keep a couple of air coolers around when I have a motherboard I'm testing on a table, but I won't use them in a case anymore.
