So currently there are a quarter or half a million transactions per day for 100 $ each. If Bitcoin was primarily used for long term storage, that would probably no longer be true. The current market capitalization of 1T $ is a million people holding a million dollar, everyone would have to do one transaction every two, three days to maintain that transaction rate, something I would not call long term storage.
In the end the details don't really matter, currently running Bitcoin seems to cost about 1B $ per month and the users have to pay for that one way or another. Whether there are 1M users with 1M $ each paying 1k $ per month or 1B users with 1k $ and paying 1 $ in fees per month or no one paying any fees and the newly mined coins just diluting the value, that are all details.
Bitcoin fees are cheapest when used with large values (because the fee doesn't increase with transaction size) and for long periods (because there is no fee related to time held), but that doesn't mean it's only useful for that, and it's a wide spectrum on both scales.
It's extremely important to remember that when the fees make up the vast majority of the miner's income, there will be far more people using bitcoin. Ultimately, if bitcoin fulfils it's promise as the best store of value mankind has ever seen, everyone in the world will want some. In this situation, the demand for the 7 tn/sec will be enormous. As humans we're just not used to seeing hard limits on supply of a liquid asset, and it's easy to overlook it's effects. Just as the hard limit on the bitcoin supply issuance is fundamental to it holding its value against essentially anything else (even the gold supply doubles every 30-50 years) and will lead to enormous growth in demand against a falling supply, the fixed supply of transactions will lead to similar increases in the price of transactions due to fixed transaction supply and increasing overall demand.
Once hundred's of millions to billions of people are fighting over transaction space that can only service 150 million transactions a year, the supply/demand ratio will be plenty to support a high price. By that point people would _ideally_ be using it every day/week, and so the potential demand would be enormous, and the transaction price will increase until only their larger transactions are economical.
The real question is: how much hash-rate is really needed?
That increasing demand drives the price up does not look like a feature to me, if something becomes popular, then I want the supply to increase, ideally even the price to go down due to economy of scale and investments in innovation. For people that got their share of the limited coin supply early, this might look like a feature as they can make money for nothing, for people considering to join later, that is a barrier to entry. Why would people willingly spend more instead of looking for alternatives?
The hash rate itself is irrelevant, what matters is the cost of achieving it. If that cost becomes too low so that someone can afford to control a substantial fraction of the hash rate, they could decide to mess with the system, for example perform a denial of service attack. As said before, it seems that running Bitcoin currently costs about a billion dollar per month. That makes the network probably quite safe but it certainly is still in reach of some actors. Lowering the costs substantially - say ten times or more - would increase the circle of entities capable of messing with the systems quite a bit.
The question is of course why someone would want to do this, but I can imagine some scenarios, not at last that it seems quite possible to make some money if Bitcoin experiences issues.
It doesn't matter that a store of value is engineered to go up in price forever if it's infinitely divisible.
For a store of value it doesn't matter how much you own in absolute terms, it matters how well the amount you bought holds it's value relative to things you might want to buy.
Think of Amazon shares. When looking to invest today, I don't care that some people bought shares at $0.25 (I'd argue they deserved to), I care about what the price is now relative to what it's likely to be when I sell - the actual number of shares I buy is irrelevant.
Bitcoin's price goes up for two reasons - because more and more value is being stored in it, and because it's engineered such that each unit retains it's value better than a unit of anything else. So assuming I'm right about the engineering, even when no new value is being stored in it, the price will still increase relative to any alternative. The units of everything else leak value, be it through supply increase, or instead through maintenance costs, poor liquidity, increased risk etc.
Regarding people buying "cheaper" alternatives instead - even ignoring network effect, any cheap bitcoin clone won't have the same potential because bitcoin already exists. There are thousands of cheap copies already - clone the bitcoin repo and have at it. To beat bitcoin, network effect means your copy will need to be substantially better and in a way that bitcoin can't adapt to (including via adding additional network layers such as Lightning, Paypal etc) - otherwise like the rest, including fiat, it's going to 0.