Personally, I buy the drives with the best $/storage ratio. Right now that seems to be ~3-6TB drives. Many PC enclosures and motherboards can fit 8-12 drives, fill it up with the cheapest stuff you're willing to spend money on. It will probably break even or be cheaper than the larger drives.

I'm not 100% up to speed with the current standing of things, but tapes (specifically the LTO technology) is still being relied on very heavily by the enterprise, both in data centers for things like cold storage or critical backups, and other corporate uses. Archival use is also very strong with libraries and other such institutions having large tape libraries with autoloaders and all that automation jazz. The LTO-5 generation I mentioned was released in 2010, and the first LTO generation was released in 2000 I believe. The current generation is LTO-10, with an uncompressed capacity of 30TB. New LTO tapes are still being produced, the last batch I purchased was made in 2023.

The LTO consortium consists of HP, IBM and one other company I believe. Now, in my opinion, none of this guarantees the longevity of the medium any more than any other medium, but when I initially looked into it, this was enough to convince me to buy a drive and a couple of tapes.

My reasoning was that with the advertised longevity of 30 years under "ideal archival conditions", if I can get 10 years of mileage from tapes that are just sitting on my non-environmentally-controlled shelf, that means I'll only have to hunt down new tapes 3 times in my remaining lifetime, and after that it will be someone else's problem.