Oh my god so much this. One of my friends has been getting into civ because of a hand injury from more intensive games, and we had a session where he was playing singleplayer while I watched over his back and advised him on things he could be doing. In this game it looked like china was on track to win a culture victory if he didn't do anything, so after trying a bunch of things that weren't working (such as buying great works off of them), so I suggested he put together an army to take out some of china's cities. That war became so painful for him to play and for me to watch because the loyalty mechanic was taking a big dump on any city he captured. I already somewhat disliked the mechanic going into it, but him going into this without any opinions and coming out finding it completely unfun (at least in the context of intercontinental war; I want to see his opinion in more charitable scenarios) made me considerably more negative.
During this, another one of our friends joined the call who does like the mechanic. She says she's never run into any issues like what we were having, to which we both asked "literally how??" She suggested putting in a governor (we did, victor w/ garrison commander), building/repairing a monument (we tried, but it won't build because loyalty is low and 1 lpt is minuscule anyway), policies (we had every single one slotted that could possibly have an effect in this situation), culture output (dunno what this was about, nowhere does the game say this is an effect), great works (specific to eleanor when he was playing trajan, purely offensive anyways), spies (also loyalty offense against cities that would be effectively invulnerable to it), and razing (we eventually did this which solved it, but issues with razing have been discussed earlier in this thread that I generally agree with). Looking at the wiki after the fact, the only thing that seems like was possible for us to try was getting more amenities, but that couldn't possibly counteract the -15 to -25 loyalty per turn the typical city we captured had. Especially annoying because of the loop of 'take city -> lose city to loyalty -> take city back' which reduces population and generates grievances each time making it more and more untenable.
I generally think that cities that you conquer should require some kind of occupation force to hold on to, as it's both somewhat immersion breaking for it not to do so and it potentially makes conquest a much more engaging investment. However, the loyalty mechanic doesn't help with either. For one, because it so heavily weights population, that makes conquering high pop cities easier than low pop (which makes very little sense to me). For two, during the invasion described above, the free city units we had to fight easily outnumbered the chinese units by an order of magnitude, which is frankly ridiculous.
Every time I find myself having to think about the loyalty mechanic, it feels like the game effectively saying "no, you're not allowed to have this city you settled/captured" with very little recourse, which is just straight up not fun.