OS-X & macOS limits are in the exabyte realm which you’ll have a hard time hitting! What Comes After Terabyte?
So your real limit is the limits of your ports and the limits of power your system can offer. Your system offers 4 - Internal 3.5” drive bays running SATA II (3.0 Gb/s) so you really need to look at installing a SATA III (6.0 Gb/s) PCIe bd as the main logic board ports will limit you.
If you do use the current SATA interfaces you’ll need to install SATA II (3.0 Gb/s) fixed speed drives or ones that offer auto sense technology like Sansumg 870 EVO which will auto sense the systems limits and match it running as SATA II (3.0 Gb/s) which is the limit of your system. You’ll also need a 3.5” to 2.5” adaptor frame
While I aimed you to a SSD Vs HDD which was you asked about, large HD’s are all now fixed speed SATA III (6.0 Gb/s) which is why you’ll need to swap out the SATA interfaces your Bay 1 &2 (as well as 3 & 4 as it just makes sense to do all of them). You can’t use these HDD’s without upgrading your systems SATA interface to SATA III
Then you can jump to any current 3.5” HDD. As an example the WD Black series would work WD_BLACK Performance Desktop Hard Drive spec sheet and WD_BLACK Performance Desktop Hard Drive Product page
I would change your approach if you want both speed as well as more data reliability. HDD’s are cheaper but as they are mechanical they tend to fail mechanically! They also are slower than a SSD drive and consume more power.
You also have the ability to add a PCIe eSATA or USB-C port board. So you can then use an external drive as your backup drive. As an example I have a 2013 Mac Pro and I use four external SSD drives for my data backup drives which I rotate to a safety deposit box so I have an off site backup.
If you have a good internet connection you could use an offsite site like BackBlaze. Do keep in mind you want a symmetrical high speed internet connection as the upload link of most ISP’s is much slower than the downlink speed (which they advertise as being so good). I find them to be one of the better services. there are others.