You can set a fee that will most likely ensure that your transaction will be in the next block, but that can still take up to 10 minutes to get confirmed, and even waiting for, like, 5 minutes at the counter when paying for groceries is such a long time. And you're right that the cost of the transaction is also likely to be significant, compared to the small amount a person is spending.
Waiting time is long and 10 minutes, the average block time is long but sometimes we will get a first confirmation within a few seconds, one or few minutes less than 10 but it is when we are very lucky.
Waiting 10 minutes even when we use high fee rate to buy something at groceries is painful.
I think a solution to that is centralized services that rely on custodial wallets, or just using Bitcoin only for big purchases. Both have their downsides, of course, and point at limitation of BTC on-chain transactions.
If you use custodial wallets, you will over pay transaction fee because they always charge you very high transaction fee to get profit from it. You only can save transaction fee if you use non custodial wallet. Sometimes it is more painful when you use custodial wallet like exchange account and they proceed your withdrawal with low fee rate. You can not do anything, just wait for them to re-proceed it for you.
Voila! You are right! Bitcoin is not capable of handling micro transactions and it is expensive to spend. Which makes Bitcoin a very bad currency system. It's more of an asset class than a currency system. It can not compete with the current payment solutions we have in the market. Bitcoin can not compete to become a currency system. It's an investment asset.
Some bitcoin puritans will still argue. But that won't change the truth!
I agree that Bitcoin transaction fee is expensive with many people even when Bitcoin mempools are normal, not too overloaded. When it is overloaded, congested, transaction fee will be very expensive and we can not compare Bitcoin blockchain with Ethereum blockchain and say it gives better transaction fee. It is better than Ethereum but nothing to be proud about it.
To have more adoption, Bitcoin transaction fee should be cheaper.