The practical problem is that users have to download the entire dataset. There are a lot of queries that cannot be done exclusively through an index and require downloading a substantial amount of data. Bitcoin (and any decentralized app) is exactly the same. It's just that they replaced the central server with a proof of work consensus mechanism that makes it truly decentralized.
It's not a practical user experience. Lightning is just a fancy federated paypal. The average user won't care once Paypal and Mastercard support Bitcoin. The inability to scale causes Bitcoin to give more power to established financial institutions. It's ironic. Bitcoin will just be relegated to a settlement layer.
Not ironic at all. Bitcoin is supposed to be a robust, decentralised, censorship resistant settlement layer. That's literally the point. It's the base protocol.
We don't, after all, expect users to care about TCP/IP whenever they post to TikTok.
But it's reasonable, based on current trends, to predict that the number of full nodes will continue to increase all over the world.
Please learn how the Nakamoto Consensus works and how nodes protect the Bitcoin network while miners are slaves to the network. There's a rich history of this being tested by adversaries.
https://news.ycombinator.com/item?id=26089898