With Postgres, you might have one server, or one cluster of servers that are coordinated, and then inside there you have tables with users and the users' data with foreign keys tying them together.

With SQLite, you would instead have one database (one file) per user as close to the user as possible that has all of the user's data and you would just read/write to that database. If your application needs to aggregate multiple user's data, then you use something like Litestream to routinely back it up to S3, then when you need to aggregate data you can just access it all there and use a distributed system to do the aggregation on the SQLite database files.