Scaling with SQLite and a datastore-per-user model is trivial. If you build your app as if it's using Postgres when you're using SQLite, then, yes, you'll suffer. If you pick SQLite, you should pick it because you will actually build your application in the way that makes most sense with SQLite.
Datastore-per-user makes sharding/scaling trivial, and tends to drive an architecture that is easier to reason about. You're never going to e.g. accidentally leak data between users because of a broken query, for example.
Datastore-per-user makes sharding/scaling trivial, and tends to drive an architecture that is easier to reason about. You're never going to e.g. accidentally leak data between users because of a broken query, for example.