I'm glad to hear I'm not the only one who has learned the hard way. At least we learn, no? I'm not a Lightroom user, but it appears that your experience confirms that the days of drag and drop any sort of application are long gone. I don't think you would see any great drop in performance when using external drives. As mentioned, USB is plenty fast for backups, but if you intend to work on files directly on an external, it might be worth the extra $ for a Thunderbolt-connected enclosure. I edit 4k video files that reside on a Thunderbolt enclosure, and I can discern no difference in speed compared to if they were on my Mac's primary internal drive. I'm no expert in such matters, but I suspect that processing might actually be a hair faster, since I'm not asking my internal drive to constantly search back and forth.Lol I know exactly what you mean I got into a real mess the first time I tried moving my Lightroom originals to an external disc. I was the better part of a week getting that mess straightened out. That’s one of the reasons I’ve been apprehensive about going to an external drive again honestly. I’m still contemplating getting an OWC raid system which I’m sure would be safer than what I’m doing now. I suppose as long as I left my catalog on my iMac, then I’d still have decent performance.
Your Lightroom experience is one reason I'm a big CCC fan. The cost is a pittance, and while as with any software there is a bit of a learning curve (minimal), the effort long-term is well worth it. This is because CCC, configured properly, makes an exact clone. You don't need to worry about hidden catalogs or nebulously-named files buried in the bowels of the library or OS. My startup drive could die tomorrow, and all I would have to do is replace it, start up from my external clone with CCC, and press "restore" while I drink a cup of coffee. Or scotch.
Yes, a good RAID from OWC is highly reliable and provides great peace of mind. You can specify whatever size and brand of drive you prefer, even "industrial-strength" models. I still prefer non-RAID individual disks for straight backups, but that might not be practical for everyone. By the way, most OWC enclosures let you specify the RAID system you prefer, and they include SoftRAID, their proprietary RAID management software which I've found to be extremely good and well supported.