I'm a Fusion Drive fan, have been running a Late 2013 iMac with Fusion for just about 4 years with nary a hitch (the only problem came while beta testing macOS, so I don't think that counts).
From my viewpoint, I've encountered few true horror stories. There are, however, some people who disagree vehemently with the concept behind Fusion Drive and do their best to warn people away from it. Few, if any of those individuals have personal experience with Fusion Drive, their horror stories tend to be speculation on the worst that might happen, and/or they make the assumption that, with two drives involved, the chances of catastrophic failure are higher than if a single drive was involved. Their speculations tend assume that the chance of catastrophic failure is exponentially greater than if a single drive was involved, but they have no statistics to back that up.
Fact is, yes, a Fusion Drive can fail. The HDD portion will fail at the same rate as any HDD-only system would fail. Since the SSD portion is likely to be far more durable, it's just as possible that a Fusion Drive is no less reliable than an HDD-only system. The one thing it can't be is more reliable than an HDD-only system. The HDD is the weakest link.
I also have an Early 2008 iMac that is still in active use, still using the original spinning HDD (a statistical outlier, I concede). Perhaps if this machine was also a Fusion Drive, the SSD portion might not have lasted as long as the HDD, but we'll never know - it predates the existence of Fusion.
Regardless, it's more critical to have a good backup of a Fusion Drive than of either an HDD-only or Flash/SSD-only system. The reason is that there's no reliable way to perform data recovery on a failed Fusion Drive. (Well, I'm sure someone would take on the task for the right price; the question is whether that price is remotely affordable or justifiable.)
I also have an all-Flash late 2013 iMac. On a day to day basis, it's hard to tell the difference in performance between the Late 2013 Fusion Drive iMac and the Late 2013 all-Flash iMac. I don't doubt that it'd be a measurable difference in benchmark testing, but since I spend a lot more time thinking between keystrokes than the system spends actively computing between keystrokes, I seriously doubt I'm losing a significant amount of time. If I were performing different kinds of tasks, I might have a different assessment. Different keystrokes for different folks.
Yes, if you can afford it, a Flash/SSD-only system of the same capacity would be preferable. Fusion Drive exists for those of us who want/need a large amount of internal storage that performs at near-Flash speeds at a far more affordable price than all-Flash, with no need for active management. I think that it accomplishes that goal admirably.