Comment by jadbox

12 hours ago

"But the performance probably sucks" The performance of what sucks? Replication? Why would you need to cache that?

The idea behind Litestream is that you have a “primary” which has the SQLite database on a local disk, and you use Litestream to replicate it asynchronously to (e.g.) object storage.

What's in object storage is not an SQLite file, but enough information to restore your SQLite file at various points in time. The way it's stored is optimized for frequent incremental updates and less frequent point restores.

The lightweight read replicas VFS allows you open an SQLite database read-only directly from the data in object storage, without having to first do a point restore, downloading all the data, and creating a local copy of the database.

You “stream” the data directly from object storage as needed, get a consistent view of it, and can query it while the “primary” is concurrently updating it.

It works, but SQLite kinda expects data to be local and low latency, which this might not be.

SQLite has a page cache, which you can use, but everytime there is a write, the entire cache is dropped.

Which means if you poll for updates regularly (which for various reasons you have to) you'll get frequent latency spikes.

One way to improve this is to add a page cache that has an understanding of which pages have changed and which have stayed the same. Prefetching would help to, but SQLite is really unhelpful there.

This is the piece I'm still missing. The rest mostly works already with Litestream v0.5.1.

  • That's a rabbit hole. Thank you for sharing so much context. I see clearly that SQLite needs a page-updated-aware page cache, even if it was optional.

    • To clarify, SQLite does this for local databases in WAL mode, that's what the shared memory WAL index file is for: it allows multiple processes to cooperate and figure out which pages changed since when, which also helps SQLite manage its page cache more efficiently.

      Rollback mode needs to drop its page cache whenever there is any change to the database, because what changed – and what didn't – is not stored anywhere.

      Confusingly, the read-replica VFS needs to pretend the database is in rollback mode even if it isn't. Otherwise, we'd have to fake a database file, a WAL and a WAL index, instead of just a database.