A faster way to copy SQLite databases between computers

Whats the closest counterpart in Postgres ecosystem?

It can't be done without fs specific snapshots - otherwise how would it distinguish between a cp/rsync needing consistent reads vs another sqlite client wanting the newest data?

This leverages a dedicated on-line backup API: https://sqlite.org/backup.html

Haha, that sounds like me. Here's the writeup you're talking about:

https://mtlynch.io/litestream/

And here's the flooding story:

https://mtlynch.io/solo-developer-year-6/#the-most-terrifyin...

Sidenote: I still use Litestream in every project where I use SQLite.

Despite the beta label and lack of a 1.x release, I would consider the project pretty stable. We've used it in production for over 18 months to support an offline-first point of sale system. We haven't had any issues with Litestream.

seems like a new release is being worked on: https://github.com/benbjohnson/litestream/pull/636

Similarly you can rsync a Postgres data directory safely while the db is running, with the caveat that you likely lose any data written while the rsync is running. And if you want that data, you can get it with the WAL files.

It’s been years since I needed to do this, but if I remember right, you can clone an entire pg db live with a `pg_backup_start()`, rsync the data directory, pg_backup_stop() and rsync the WAL files written since backup start.

MSSQL also offers a virtual backup device interface that 3rd party tools can implement.

https://learn.microsoft.com/en-us/sql/relational-databases/b...

Yes! That was actually quite helpful.

For my use case (recreating in-memory from scratch) it basically boils down to three points: (1) journal_mode = off (2) wrapping all inserts in a single transaction (3) indexes after inserts.

For whatever it's worth I'm getting 15M inserts per minute on average, and topping around 450k/s for trivial relationship table on a stock Ryzen 5900X using built-in sqlite from NodeJS.

It's worth noting that the data in that benchmark is tiny (28MB). While this varies between database engines, "one transaction for everything" means keeping some kind of allocations alive.

The optimal transaction size is difficult to calculate so should be measured, but it's almost certainly never beneficial to spend multiple seconds on a single transaction.

There will also be weird performance changes when the size of data (or indexed data) exceeds the size of main memory.

yes, but they punt on this issue:

CREATE INDEX then INSERT vs. INSERT then CREATE INDEX

i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.

Hilarious, 3000+ votes for a Stack Overflow question that's not a question. But it is an interesting article. Interesting enough that it gets to break all the rules, I guess?

It's roughly 10Gb across several CSV files.

I create a new in-mem db, run schema and then import every table in one single transaction (in my testing it showed that it doesn't matter if it's a single batch or multiple single inserts as long are they part of single transaction).

I do a single string replacement per every CSV line to handle an edge case. This results in roughly 15 million inserts per minute (give or take, depending on table length and complexity). 450k inserts per second is a magic barrier I can't break.

I then run several queries to remove unwanted data, trim orphans, add indexes, and finally run optimize and vacuum.

Here's quite recent log (on stock Ryzen 5900X):

   08:43 import
   13:30 delete non-essentials
   18:52 delete orphans
   19:23 create indexes
   19:24 optimize
   20:26 vacuum

Millions of rows in minutes sounds not ok, unless your tables have a large number of columns. A good rule is that SQLite's insertion performance should be at least 1% of sustained max write bandwidth of your disk; preferably 5%, or more. The last bulk table insert I was seeing 20%+ sustained; that came to ~900k inserts/second for an 8 column INT table (small integers).

Saying that 30 minutes seems long is like saying that 5 miles seems far.

Yeah, that’s a bummer. It does appear to be in nixpkgs, though:

  nix-shell -p sqlite-rsync

Realistically, are you using SQLite if you can’t compile and source control your rev of the codebase? Is that really a big deal?

I just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin database will block all writers while the sync is going on, and the replicate database will block both reads and writers during the sync, because to do otherwise requires WAL-mode. Nevertheless, being able to sync DELETE-mode databases might well be useful, as you observe.

If you are able, please try out this enhancement and let me know if it solves your problem. See <https://sqlite.org/src/info/2025-05-01T16:07Z> for the patch.

WAL mode works on many network filesystems provided it's being written from a single host at a time.

The main point was decreasing the transfer time - if rsync -z makes it short enough, it doesn't matter if the indices are there or not, and you also skip the step of re-creating the DB from the text file.

It would have to be one really fast network... zstd compresses and decompresses at 5+ GB (bytes, not bits) per second.

Valve tends to take a different view...

Is the network only doing an rsync? Then you are probably right.

For every other network, you should compress as you are likely dealing with multiple tenants that would all like a piece of your 40Gbps bandwidth.

Depends. Run a benchmark on your own hardware/network. ZFS uses in-flight compression because CPUs are generally faster than disks. That may or may not be the case for your setup.

What? Compression is absolutely essential throughout computing as a whole, especially as CPUs have gotten faster. If you have compressible data sent over the network (or even on disk / in RAM) there's a good chance you should be compressing it. Faster links have not undercut this reality in any significant way.

SQLite tosses out the SQL once it is parsed into bytecode. Using text is just going to take longer, even though I’m sure it works great.

You can modify the database before vacuuming by making a new in-memory database, copying selected tables into it, and then vacuuming that to disk.

> A text file may be inefficient as is, but it's perfectly compressible, even with primitive tools like gzip. I'm not sure the SQLite binary format compresses equality well, though it might.

I hope you’re saying because of indexes? I think you may want to revisit how compression works to fix your intuition. Text+compression will always be larger and slower than equivalent binary+compression assuming text and binary represent the same contents? Why? Binary is less compressible as a percentage but starts off smaller in absolute terms which will result in a smaller absolute binary. A way to think about it is information theory - binary should generally represent the data more compactly already because the structure lived in the code. Compression is about replacing common structure with noise and it works better if there’s a lot of redundant structure. However while text has a lot of redundant structure, that’s actually bad for the compressor because it has to find that structure and process more data to do that. Additionally, is using generic mathematical techniques to remove that structure which are genetically optimal but not as optimal as removing that structure by hand via binary is.

There’s some nuance here because the text represents slightly different things than the raw binary SQLite (how to restore data in the db vs the precise relationships + data structures for allowing insertion/retrieval. But still I’d expect it to end up smaller compressed for non trivial databases

You're right. It does. I never thought about it until you asked.

That's not it. This only exports the table's data, not the database. You lose the index, comments, schemas, partitioning, etc... The whole point of OP's article is how to export the indices in an efficient way.

You'd want to do this:

     duckdb -c "ATTACH 'sqlite-database.db' (READ-ONLY); EXPORT DATABASE 'target_directory' (FORMAT parquet, COMPRESSION zstd)"

If there's UUIDs, they're 36 bits each in text mode and 16 bits as binary in Parquet. And then if they repeat you can use a dictionary in your Parquet to save the 16 bits only once.

It's also worth trying to use brotli instead of zstd if small files is your goal.

Well, my upcoming Elixir wrapper of a Rust wrapper of SQLite (heh, I am aware how it sounds) will support it. I am pretty sure people do find it useful and would use it. If not, the 1-2 days of hobby coding to deliver it are not something I would weep over.

I have updated the Lua binding to support the session extension (http://lua.sqlite.org/home/timeline?r=session) and it's been integrated into the current version of cosmopolitan/redbean. This was partially done to support application-level sync of SQLite DBs, however this is still a work in progress.

There are atleast two SQLite bindings for Go.

https://github.com/crawshaw/sqlite

https://github.com/eatonphil/gosqlite/

Ended up with the latter, but did have to add one function binding in C, to inspect changesets.

I have, atleast to confirm it does what it says on the tin.

Idea for an offline first app, where each app install call pull a changeset and apply it to their local db.