Litestream replication to clients

[switz]

I'm sorry if this is the wrong place for this, but I have a unique situation and question.

We operate a popular mobile app (react native) in which all of our data is public and accessible to the user. The user makes zero mutation/writes to the database and only reads from it. Currently we store everything in a postgres db fronted by a REST API. We make updates to this database once every 24h, however the changes are less than a percent of a percent. Almost ALL off it is completely static.

We're exploring some other solutions for our app that we feel may be better, to avoid dealing with network requests, cache misses, and to improve offline support. One crazy idea is to put everything in a sqlite database (20->50MB or so) and just have the user download the entire database on app launch.

This would effectively allow us to get rid of our API and, on the upside, give our users full offline support with no changes.

The only thing stopping us from doing this is figuring out if we can possibly have our users query for delta updates so they can get updates without needing to download a 20->50mb file every time.

Could litestream possibly support replication to clients, perhaps in a pull manner rather than push? Is that anything that would be achievable with this library or is it out of the scope of this library entirely?

And since you all are sqlite experts, is this something you feel we could solve with WAL outside of this library? Is this doable or are we crazy?

Thanks for taking the time.

[hifi]

Because the database is read only, relatively small and the updates are low frequency I'd explore not using WAL and use some diff algorithm to see how compact the updates would be.

If you can dump the raw SQL commands to update the database you could just patch an existing one like that by always keeping the latest database available to download and a daily compressed SQL text file for updates that the client would download keeping track how many days/updates it is lagging behind. It all depends on the relative size if it makes sense or not. This would be like doing database migrations that also contain data or only data and can be applied on-the-fly by the running application to a live database.

You could also use xdelta or similar binary diff tool to compare two databases and get the updates bit level accurate but that is unlikely online safe for SQLite and would need to be done before the database is accessed during startup.

If something fails in the client that an update wouldn't work it could fall back to download the full database. Both approaches would be easy to host over HTTP without needing any server side logic for distribution other than the daily generator to publish a new set of files (full db and a patch).

Litestream itself doesn't sound like the right tool even if it implemented this because even when doing WAL updates you'll eventually need a full snapshot as the updates would end up being larger than the whole database.

I don't think it's crazy at all if users do a lot of read operations against a relative static set of data that doesn't need to update live.