3 ms·

Litestream author here. I haven't used Verneuil but I read through some of their design docs. I'd say there are two places where the tools are different in sign

Litestream author here. I haven't used Verneuil but I read through some of their design docs. I'd say there are two places where the tools are different in significant ways to the end user: 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 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.

pkhuong 5y ago
You can also ask sqlite to load Verneuil as a runtime extension, without otherwise changing your application. That's how backtrace uses it for ad hoc query of read replicas in the sqlite shell. 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.
- harryvederci 5y ago
  Thanks for your reply! > 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?
  - pkhuong 5y ago
    The blog post has some numbers. We use Verneuil at backtrace to replicate small to medium sized databases (one per user), with up to a few hundred replicated databases per process. We see a few million write transactions a day (so not a super heavy write load), and our periodic polls for replication lag only notes lag > 5 seconds ~100 times per day, and > 1 minute <= 1 time a day. 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.
harryvederci 5y ago
That's very helpful, thanks! 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?
- benbjohnson 5y ago
  I did have a report of someone trying to run thousands of SQLite databases with Litestream, however, Litestream is currently implemented using a polling mechanism so it gets expensive with a lot of databases. I'm going to switch it to use fsnotify so it will be feasible to run a ton of small databases but it doesn't work well right now. 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).

pkhuong 5y ago

You can also ask sqlite to load Verneuil as a runtime extension, without otherwise changing your application. That's how backtrace uses it for ad hoc query of read replicas in the sqlite shell. 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.

harryvederci 5y ago
Thanks for your reply! > 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?
- pkhuong 5y ago
  The blog post has some numbers. We use Verneuil at backtrace to replicate small to medium sized databases (one per user), with up to a few hundred replicated databases per process. We see a few million write transactions a day (so not a super heavy write load), and our periodic polls for replication lag only notes lag > 5 seconds ~100 times per day, and > 1 minute <= 1 time a day. 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.

harryvederci 5y ago

That's very helpful, thanks! 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?

benbjohnson 5y ago
I did have a report of someone trying to run thousands of SQLite databases with Litestream, however, Litestream is currently implemented using a polling mechanism so it gets expensive with a lot of databases. I'm going to switch it to use fsnotify so it will be feasible to run a ton of small databases but it doesn't work well right now. 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).