Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by tptacek on Wednesday July 05, 2023 @04:22PM
(#36606556)
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.
Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by Mertax on Wednesday July 05, 2023 @05:27PM
(#36607397)
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?
Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by benbjohnson on Wednesday July 05, 2023 @05:37PM
(#36607519)
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.
Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by felipeccastro on Wednesday July 05, 2023 @05:36PM
(#36607505)
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?
Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by tmpz22 on Wednesday July 05, 2023 @05:47PM
(#36607630)
> 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.
Re:LiteFS Cloud: Distributed SQLite with Managed Backups
by benbjohnson on Wednesday July 05, 2023 @06:06PM
(#36607841)
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.
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.