> It took me quite a few iterations to get to this recipe for compiling the tool itself
Hehe, I really feel this remark, having recently built a sqlite3 binary with Lua support[*]
The LLMs were no help here, either. Which is maybe a good thing for our collective future employment.
That said, I found the quality of the documentation and legibility of the sqlite source code to be absolutely fantastic. When you work with the code, you can almost feel the programming enlightenment rub off on you.
[*] why, for heavens' sake? so you can decouple your business logic from the database and test it a bit outside the DB, whilst still keeping the data all in one place without sending it over the wire. It lets you SELECT lua('some lua code that operates on your sqlite data') FROM some-table;, so you can put gnarly nested conditionals or whatever into Lua, build some comprehensive testing around that, and execute it on your data as if everything was a single, plain old SQL statement.
I got a little bit of assistance from Claude in figuring this out, but it pointed me in the right direction more than actually giving me the right sequence of commands. Claude got me as far as:
Which was enough for me to figure out I needed to get the sqlite3.c amalgamation to build and then run gcc in the tool/ directory. I landed on this after a bit more poking:
I did have a much more useful interaction with an LLM later on: I was curious about the protocol used over SSH, so I copied the whole of this 1800 line C file:
Lua as the language for SQLite procedures just sounds right for some reason. Did you try integrating at that level, or is it limited to expression-level evaluation?
sqlite3-rsync is in the makefile. I got sqlite-rsync to build by just checking out the main branch and going:
./configure
make sqlite3-rsync
I think the rsync branch has been merged?
I'm really excited for this new tool; I'm hoping to use it for nightly backups of a webapp I'm building that uses sqlite exclusively. Although I'm hesitant to rely on sqlite-rsync right away because it's so new.
I'm aware of the existing backup methods, but AFAIK they don't do a delta of only the changed pages (unless I use separate software), and "vacuum into" doesn't work over SSH.
I have no need to store a backup on the same physical server. Also, the backup server will keep snapshots, and with sqlite3-rsync I can just update a previous snapshot with the changed pages rather than redundantly transferring the whole database over every time.
Yeah, the unique benefit of sqlite-rsync looks to be efficiently backing up medium to large databases that don’t change enormously (so deltas are worthwhile) over an SSH connection. I particularly like that you don’t need double the disk space to create the temporary backup copy before downloading it.
I had a quick scan through the source and it gets the hash of each page on both sides and sends the page if it's different (so conceptually very similar to rsync). I don't think the cache is indexed anywhere normally, so that'd imply it needs to do a full read of the database on both sides, so it'll be slow and probably trash your disk cache on a large database.
In theory, it should be able to use the WAL file directly if it copies the WAL checksums, but that information would get lost every time the WAL is flushed and the data is moved into the main db file.
Litestream knows exactly which pages have been written via the fuse fs, so it doesn't need to do all that work for unchanged pages.
Ugh, yeah that's a good point: it looks like each time you run sqlite-rsync it calculates a hash for every database page on both the source and the replica side of things - that's a lot of disk access and CPU, way more than you would want to run on an every-ten-seconds basis.
I guess this is probably fast enough that you could run it on a schedule every 10 seconds or so (at least for databases measured in GBs, not TBs) to keep a warm copy updated elsewhere. [UPDATE: maybe not, it has to hash everything, see comment here https://news.ycombinator.com/item?id=41749288#41760395]
Litestream gives you a backup in S3 within less than a second of data being committed though, plus since it records the full WAL archive you get point-in-time recovery for previous timestamps too.
Hehe, I really feel this remark, having recently built a sqlite3 binary with Lua support[*]
The LLMs were no help here, either. Which is maybe a good thing for our collective future employment.
That said, I found the quality of the documentation and legibility of the sqlite source code to be absolutely fantastic. When you work with the code, you can almost feel the programming enlightenment rub off on you.
[*] why, for heavens' sake? so you can decouple your business logic from the database and test it a bit outside the DB, whilst still keeping the data all in one place without sending it over the wire. It lets you SELECT lua('some lua code that operates on your sqlite data') FROM some-table;, so you can put gnarly nested conditionals or whatever into Lua, build some comprehensive testing around that, and execute it on your data as if everything was a single, plain old SQL statement.