Verneuil: S3-backed asynchronous replication for SQLite | Hacker News Reader

Verneuil: S3-backed asynchronous replication for SQLite

engineering.backtrace.io

14 pointspkhuong8 comments

Happy to see some (more sane looking) implementations of distributed SQLite. For a completely different set of thinking on the problem check out BedrockDB (https://github.com/expensify/bedrock ; warning.... contains blockchain :D )

> This effort is incomparable with litestream: Verneuil is meant for asynchronous read replication, with streaming backups as a nice side effect. The replication approach is thus completely different. In particular, while litestream only works with sqlite databases in WAL mode, Verneuil only supports rollback journaling. See doc/DESIGN.md for details.

Can someone explain to me (as if I were a 5 year old) when to use Litestream versus Verneuil?

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

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.

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.

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?

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.

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?

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).

Reply on news.ycombinator.com