The Pis I installed are all first version, which uses SD card. I agree that things may improve quite a lot in recent iterations.
All Raspberry Pi versions (except the compute module) use SD cards.
Raspberry Pi 2 and 3 use microsd cards.
I had a lot of trouble with corrupt cards. Took care of it by keeping the file system in a USB stick, and using the SD card only for booting into the OS on the stick.
How did you get that working? I haven't had much luck with USB sticks so far. (not really new to Linux, but I'm very new to the Pi.)
Basically, you format the drive for ext4, then copy the filesystem over with async. Then edit the cmdline.txt on the sdcard to point to root=/dev/sda2. It's rather more problematic if you have multiple usb drives, and there's a RPi forum message somewhere with a lot more details...
The only downtime has been a result of power or Internet outages at home.
Anecdotal, but I now have some 36 different Raspberry Pis at my house, and while I've had two (cheap) MicroSD card failures, I haven't had an issue with any of the Pis themselves.
As long as you use quality 2A power supplies (1A for older Pis) and quality MicroSD cards, I don't think you should worry too much about reliability, and if you have a cluster with redundancy... That's kind of the point ;)