Can someone explain to me (as if I were a 5 year old) when to use Litestream versus Verneuil?
1. Litestream uses WAL mode & Verneuil uses the rollback journal. WAL mode enables a lot of concurrency benefits for applications and is probably what you want to use if you need concurrent access (e.g. a web app). Backtrace states that rollback journal is better for large writes (although I haven't verified that). SQLite provides some pros & cons on their site: https://www.sqlite.org/wal.html
2. Litestream runs as a separate process whereas Verneuil runs as a virtual file system (VFS) which needs to be compiled into your application. If you have access to the code then adding a VFS isn't usually too bad. However, Litestream will work with legacy applications as the application doesn't need any special knowledge of Litestream.
Finally, "Litestream" is easier to spell and pronouce than Verneuil.
The availability of read replica is probably the other big differentiator. If your code could benefit from slightly stale read-only snapshots on remote nodes, Verneuil VFS on a single writer node combined with the Verneuil snapshot VFS on multiple read-only nodes might be a good fit.
> If your code could benefit from slightly stale read-only snapshots on remote nodes [...]
How "slightly" stale are we talking?
Could you give an example scenario of where this would be a good approach?
In practice, the main source of data staleness is often the period at which readers can poll for changes (a blind S3 GET of one blob, for each database). With a background thread to refresh data once a second, lag should usually be the order of 2-3 seconds. As to when that makes sense... I think it's good for data that doesn't see changes too often, and for data that's not directly generated and consumed interactively. For Backtrace, that's mostly one of:
1. metadata that's updated programmatically (e.g., after analysing crashes), and displayed to interactive users
2. data that's updated interactively (e.g., analysis configuration), and then propagated to worker processes
It wouldn't make sense to use the replication capability to display the current crash analysis configuration back to the user: when I change some configuration and hit save, I expect to see the changes I made. We want to service both interactive reads and writes from the local (source of truth) sqlite db.
Depending on the domain, I may or may not be OK with a propagation delay for the changes to impact behaviour; for example, we can let the analysis code fetch its configuration from a read replica, in order to improve isolation and scalability. When propagation delays are acceptable, read replicas help build more reliable and scalable systems.
I'm not sure yet what I'll use for my side project: either one small (less than 1 MB) SQLite file per user, or one gigantic one for all users.
I guess in the first case WAL (and thus Litestream) would be more suitable, and in the one-huge-file scenario, Verneuil might be best.
Do you know if Litestream is used anywhere with the "one small db per user" scenario?
Litestream should work fine on larger databases. The long-running tests for Litestream run against databases between 1 to 10 GB. It uploads the full snapshot periodically (once a day by default) but it mostly just uploads the WAL changes which are typically quite small (~1KB for an LZ4 compressed page).