Minimal News_

Saving to text file is inefficient. I save sqlite databases using VACUUM INTO, like this:

  sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"

From https://sqlite.org/lang_vacuum.html :

  The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database. The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced.

> If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file. The first half of the file is pre-update, the second half file is post-update, and they don’t match. When I try to open the database locally, I get an error

Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption.

For replicating sqlite databases safely there is

https://github.com/benbjohnson/litestream

How to copy databases between computers? Just send a circle and forget about the rest of the owl.

As others have mentioned an incremental rsync would be much faster, but what bothers me the most is that he claims that sending SQL statements is faster than sending database and COMPLETELY omiting the fact that you have to execute these statements. And then run /optimize/. And then run /vacuum/.

Currently I have scenario in which I have to "incrementally rebuild *" a database from CSV files. While in my particular case recreating the database from scratch is more optimal - despite heavy optimization it still takes half an hour just to run batch inserts on an empty database in memory, creating indexes, etc.

The recently released sqlite_rsync utility uses a version of the rsync algorithm optimized to work on the internal structure of a SQLite database. It compares the internal data pages efficiently, then only syncs changed or missing pages.

Nice tricks in the article, but you can more easily use the builtin utility now :)

I blogged about how it works in detail here: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-w...

I was surprised that he didn't try to use on the flight compression, provided by rsync:

  -z, --compress              compress file data during the transfer
      --compress-level=NUM    explicitly set compression level

Probably it's faster to compress to gzip and later transfer. But it's nice to have the possibility to improve the transfer with a a flag.

In DuckDB you can do the same but export to Parquet, this way the data is an order of magnitude smaller than using text-based SQL statements. It's faster to transfer and faster to load.

https://duckdb.org/docs/stable/sql/statements/export.html

SQLite has an session extension, which will track changes to a set of tables and produce a changeset/patchset which can patch previous version of an SQLite database.

https://www.sqlite.org/sessionintro.html

If you're regularly syncing from an older version to a new version, you can likely optimize further using gzip with "--rsyncable" option. It will reduce the compression by ~1% but make it so differences from one version to the next are localized instead of cascading through the full length of the compression output.

Another alternative is to skip compression of the dump output, let rsync calculate the differences from an previous uncompressed dump to the current dump, then have rsync compress the change sets it sends over the network. (rsync -z)

Great example of Cunningham's Law: https://en.wikipedia.org/wiki/Ward_Cunningham#:~:text=%22Cun...

Does the author not know that rsync can use compression (rsync -z | --compress | --compress-level=<n> ), or does he not think it worthwhile to compare that data point?

I just tried some comparisons (albeit with a fairly small sqlite file). The text compressed to only about 84% of the size of the compressed binary database, which isn't negligible, but not necessarily worth fussing over in every situation. (The binary compressed to 7.1%, so it's 84% relative to that).

bzip2 performed better on both formats; its compression of the binary database was better than gzip's compression of the text (91.5%) and bzip2's text was better than binary (92.5).

Though that is not available inside rsync, it indicates that if you're going with an external compression solution, maybe gzip isn't the best choice if you care about every percentage reduction.

If you don't care about every percentage reduction, maybe just rsync compression.

One thing worth mentioning is that if you are updating the file, rsync will only compress what is sent. To replicate that with the text solution, you will have to be retaining the text on both sides to do the update between them.

Wait... why would you even think about rsyncing a database that can get changed while being copied?

Isn't this a case for proper database servers with replication?

Or if it's an infrequent process done for dev purposes just shut down the application doing writes on the other side?

sqlite provide the sqlite3_rsync command to safely copy databases https://sqlite.org/rsync.html

I am sure you can just pipe all this so you don't have to use an intermediate gunzip file.

Just ssh the machine, dump the SQL and load it back into SQLite locally.

I think this could be a single pipeline?

ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

I used to work at a company that had a management interface that used sqlite as database, its multi-node / fallover approach was also just... copying the file and rsyncing it. I did wonder about data integrity though, what if the file is edited while it's being copied over? But there's probably safeguards in place.

Anyway I don't think the database file size was really an issue, it was a relatively big schema but not many indices and performance wasn't a big consideration - hence why the backend would concatenate query results into an XML file, then pass it through an xml->json converter, causing 1-2 second response times on most requests. I worked on a rewrite using Go where requests were more like 10-15 milliseconds.

But, I still used sqlite because that was actually a pretty good solution for the problem at hand; relatively low concurrency (up to 10 active simultaneous users), no server-side dependencies or installation needed, etc.

That makes zero sense. Incremental backup via rsync/sqlite3_rsync should always be faster.

I have recently discovered a tool called mscp which opens open multiple scp threads to copy down large files. It works great for speeding up these sorts of downloads.

https://github.com/upa/mscp

isn't this rather obvious? doesn't everyone do this when it makes sense? obviously, it applies to other DBs, and you don't even need to store the file (just a single ssh from dumper to remote undumper).

if retaining the snapshot file is of value, great.

I'd be a tiny bit surprised if rsync could recognize diffs in the dump, but it's certainly possible, assuming the dumper is "stable" (probably is because its walking the tables as trees). the amount of change detected by rsync might actually be a useful thing to monitor.

zstd would be a better choice. It’s bonkers fast (especially when used with multithreading) and still compresses better than gzip. Alternatively, I’d recommend looking into bzip3, but I’m not sure if it would save time.

???

Why not just compress the whole database using `gzip` or `lz4` before rsyncing it instead? `zstd` works too but seems like it had a bug regarding compressing file with modified content.

better yet, split your sqlite file to smaller piece. it is not like it needs to contain all the app data in a single sqlite file.

I guess for me it is obvious you don't try to copy running DB only a backup.

So I see basic stuff needs to be repeated as people still miss those kinds of things.

But I learned that you can easily dump SQLite to a text file - neat!

One of the coolest things you can do with Postgresql is pipe pg_dump straight into psql connected to another cluster on another host.

You can save time by using `zcat` instead of `cat` and skip the `gunzip my_local_database.db.txt.gz` step.

I wonder if there's a way to export to parquet files? They are designed to be extremely compact.

How long does this procedure take in comparison to the network transfer?

My first try would've been to copy the db file first, gzip it and then transfer it but I can't tell whether compression will be that useful in binary format.

What technologies we have in 2025!

In curious how your indices are twice the data. Sounds like you just put indices in anything you see.

Doesn't this just push the runtime into index recomputation on the destination database?

how well does just the sqlite database gzip, the indexes are a lot of redundant data so your going to get some efficiencies there, probably less locality of data then the text file though so maybe less?

Since sqlite is just a simple file-level locking DB, I'm pretty shocked they don't have an option to let the indexes be stored in separate files for all kinds of obvious and beneficial reasons, like the fact that you can easily exclude them from backups if they were, and you can make them "rebuild" just by deleting them. Probably their reason for keeping all internal has to do with being sure indexes are never out of sync, but that could just as easily be accomplished with hashing algos.

I’ve been looking into a way to replicate a SQLite database and came across the LiteFS project by Fly.io. Seems like a solid drop-in solution backed by FUSE and Consul. Anybody used it in production? My use case is high availability between multiple VMs.

I usually use scp for this case, sometimes rsync version is not compatible between 2 machines

Nice!

I'm surprised sqlite is duplicating data to make indexes? Surely it would just be manipulating groups of pointers?

Pretty good point. I just wonder if databases in generally can be perfectly reconstructed from a text dump. For instance, do the insertion orders change in any of the operations between dumping and importing?

Very neat walkthrough, clear commands and I appreciate the explanations as to why this may help in OPs case

This is basically the way every other database is moved around.

it's a file - what am I missing? scp host:path .

All this obsession with making processes like this faster

When is a guy supposed to get a coffee and stretch his legs anymore?