This decision tree doesn't make much sense to me. Why you someone forego performance today in favor of adding a completely unnecessary network layer to every DB query in order to "satisfy" future imaginary "scaling concerns"?
replyBecause you don't add a network layer by running a database locally.
replyThat's still orders of magnitude more complexity for no real benefit. A migration from sqlite to postgres, if really required, is not that hard.
replyYes, postgres should support a superset of SQLite functionality.
replyNow you've added a substantial dependency, and annoying setup requirements. Good luck doing this for a native app on mobile or desktop.
replyIf someone is talking about "spinning up a separate machine" for Postgres, they're not talking about a desktop or mobile app...
replyObviously SQLite is the best choice for a mobile or desktop app, that's not what's being discussed here.
reply