I know sqlite3 is great, but I've always found it much more annoying to use it on a server for small side projects than just using Postgres where I could just connect to the prod instance from my local machine and run some queries to look at the data. With sqlite I'd always have to sync files, set up volumes, make sure permissions are correct vs. just setting an environment variable to a PG instance.
I do share your preference on using Postgres as the main database. Having that as the main db with sqlite3 used for auxiliary functionality sounds great.
In reality cloud is too convenient, and the tradeoffs for self-hosting just don't make sense for the majority of companies. The talent to run your own servers with modern HA and reliability expectations has largely been consolidated into the giant cloud providers and other large companies at this point. As much as I think a lot of cloud is wasteful and more diversity would be beneficial, I don't see any meaningful change to cloud dominance on the horizon.