Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
Good question. LiteFS is just a replication layer so it's probably better to answer your question just from a SQLite standpoint. One of the biggest limitations of SQLite is that it only allows a single writer at a time so you can't have long-running write transactions in your application. Writes can be incredibly fast though—sometimes a millisecond or less depending on your hardware and settings. Speaking of which, make sure you use the "WAL" journaling mode if you're using SQLite it improves concurrency & write performance.
There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.
Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.
For compression and encryption, commercial SQLite extensions are available - https://sqlite.org/support.html ... I also recall coming across some free open source projects that had implemented compression and encryption for SQLite databases though I have no idea if they were production ready.
Technical requirements for a SaaS app have a tendency to become something like a Jenga tower over time. You'll be able to sail through launch and initial customers but then slam hard into difficult architectural challenges as you suddenly onboard larger customers or unique use cases.
For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the onboarding of a large client.
Its not that SQLite can't handle these situations with careful architectural decisions. Its that out-of-the-box solutions, like the kind people depend on to solve business-issues in short time frames, won't support it as readily as more mainstream options.
I agree with everything the OP said above. Typically if you need to scale writes in SQLite, you'll want to look at sharding. The "single writer" restriction is per database so you can split your SaaS customers across multiple databases.
If your SaaS is in the hundreds or thousands of customers then you could split each customer into their own database. That also provides nice tenant isolation. If you have more customers than that you may want to look at something like a consistent hash to distribute customers across multiple databases.
I'm flinching a bit at using the word "sharding" here, because I think people do sleep on how straightforward it is to break up a SQL schema into multiple sqlite3 databases, but when people think about "sharding" they tend to be thinking things like range-partitioned keys, with each shard hosting a portion of the keyspace of the entire schema, which is not necessarily how you'd want to design a sqlite3 system.
In scenarios where an individual customer/tenant can have isolated data this makes sense.
Is there any reason why the client application itself can't be one of the nodes in the distributed system? Does LiteFS support a more peer-2-peer distribution model (similar to a git repo) where the client/customer's SQLite database is fully distributed to them and then it's just a matter of merging diffs?
No, LiteFS just does physical page replication. We don't really have a way to do merge conflict resolution between two nodes. You may want to look at either vlcn[1] or Mycelite[2] as options for doing that approach.
WAL solves the high concurrency read situation. Not the writes. SQLite can do thousands of writes per second in WAL mode which is more than enough for the vast majority of applications out there. It's not like most businesses could fulfill thousands of orders per second even if their database could write them.
