Is a store of value that requires a significant fraction of it be eaten up by transaction fees to maintain security going to be actually useful in the long term?

With regards to transaction fees, bitcoin is already not particularly useful today. It can make sense to be used as an alternative to wire transfers where you only occasionally send a transaction, but it isn't useful as a currency and any day to day transactions have to happen off chain and not use bitcoin at all.