4 ms·

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

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.

tptacek 3y ago
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.
Mertax 3y ago
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?
- benbjohnson 3y ago
  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. [1]: https://github.com/vlcn-io/cr-sqlite https://github.com/vlcn-io/cr-sqlite [2]: https://github.com/mycelial/mycelite https://github.com/mycelial/mycelite
felipeccastro 3y ago
Doesn't the WAL mode solve the high concurrency write situation? If it can't be relied on busy season, why the push for Sqlite in production?
- tmpz22 3y ago
  > If it can't be relied on busy season, why the push for Sqlite in production? I think its less about proving sqlite is awesome for everything then it is about proving it can be awesome and practical for some projects.
- benbjohnson 3y ago
  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.

tptacek 3y ago

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.

Mertax 3y ago

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?

benbjohnson 3y ago
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. [1]: https://github.com/vlcn-io/cr-sqlite https://github.com/vlcn-io/cr-sqlite [2]: https://github.com/mycelial/mycelite https://github.com/mycelial/mycelite

felipeccastro 3y ago

Doesn't the WAL mode solve the high concurrency write situation? If it can't be relied on busy season, why the push for Sqlite in production?

tmpz22 3y ago
> If it can't be relied on busy season, why the push for Sqlite in production? I think its less about proving sqlite is awesome for everything then it is about proving it can be awesome and practical for some projects.
benbjohnson 3y ago
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.