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.
The sqlite file format (https://www.sqlite.org/fileformat.html) does not talk about compression, so I would wager unless you are storing already compressed content (media maybe?) or random numbers (encrypted data), it should compress reasonably well.
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.
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.
Also I wonder how big your test database is and it's schema. For large tables Parquet is way more efficient than a 20% reduction.
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.
Valve has different needs then most. Their files are rarely change so they only need to do expensive compression once and they save a ton in bandwidth/storage along with fact that their users are more tolerant of download responsiveness.
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.
If working from files on disk that happen not to be cached, the speed differences are likely to disappear, even on many NVMe disks.
(It just so happens that the concatenation of all text-looking .tar files I happen to have on this machine is roughly a gigabyte (though I did the math for the actual size)).
Looks like it depends heavily on choice of file, but I see good performance on both compressible and uncompressible files. Small files tend to perform (relatively) bad though. Here is a sample of 3 large files with different compression ratios:
Ain't no way zstd compresses at 5+, even at -1. That's the sort of throughputs you see on lz4 running on a bunch of core (either half a dozen very fast, or 12~16 merely fast).
This will always be something you have to determine for your own situation. At least at my work, CPU cores are plentiful, IO isn't. We rarely have apps that need more than a fraction of the CPU cores (barring garbage collection). Yet we are often serving fairly large chunks of data from those same apps.
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.
Whether or not to compress data before transfer is VERY situationally dependent. I have seen it go both ways and the real-world results do not not always match intuition. At the end of the day, if you care about performance, you still have to do proper testing.
(This is the same spiel I give whenever someone says swap on Linux is or is not always beneficial.)
The main point is to skip the indices, which you have to do pre-compression.
When I do stuff like this, I stream the dump straight into gzip. (You can usually figure out a way to stream directly to the destination without an intermediate file at all.)
Plus this way it stays stored compressed at its destination. If your purpose is backup rather than a poor man's replication.
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.
The point of the article is that it does matter if the indices are there. And indices generally don't compress very well anyways. What compresses well are usually things like human-readable text fields or booleans/enums.
He absolutely should be doing this, because by using rsync on a compressed file he's passing by the whole point of using rsync, which is the rolling-checksum based algorithm that allows to transfer diffs.
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.
SQLite has a write-ahead log (WAL). You can use Litestream on top of that. You get single RW, multiple readers (you lose the C in CAP), and can promote a reader when the writer fails.
I recently set up some scripts to do this and it wasn't quite as simple as I had hoped. I had to pass some extra flags to pg_restore for --no-owner --no-acl, and then it still had issues when the target db has data in it, even with --clean and --create. And sometimes it would leave me in a state where it dropped the database and had trouble restoring, and so I'd be totally empty.
What I ended up doing is creating a new database, pg_restore'ing into that one with --no-owner and --no-acl, forcibly dropping the old database, and then renaming the new to the old one's name. This has the benefit of not leaving me high and dry should there be an issue with restoring.
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)
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.
30 minutes seems long. Is there a lot of data? I’ve been working on bootstrapping sqlite dbs off of lots of json data and by holding a list of values and then inserting 10k at a time with inserts, Ive found a good perf sweet spot where I can insert plenty of rows (millions) in minutes. I had to use some tricks with bloom filters and LRU caching, but can build a 6 gig db in like 20ish minutes now
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).
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.
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.
Would it be useful for you to have a SQL database that’s like SQLite (single file but not actually compatible with the SQLite file format) but can do 100M/s instead?
I tested couple different approaches, including pglite, but node finally shipped native sqlite with version 23 and it's fine for me.
I'm a huge fan of serverless solutions and one of the absolute hidden gems about sqlite is that you can publish the database on http server and query it extremely efficitent from a client.
I even have a separate miniature benchmark project I thought I might publish, but then I decided it's not worth anyones time. x]
I have been looking for replacement of SQLite for years -- admittedly, not very actively, embedded databases are just a hobby obsession and my life did not allow me much leisure time in the last years -- and still couldn't find one.
The written-in-Rust `sled` database is more like a key-value store and I had partial successes with it but it's too much work making a KV store a relational database.
Honestly I don't see much use for yet-another-sqlite.
The premise of having 100M/s writes instead of 500k/s sounds bit unrealistic, but at the same time, while simply importing tuples and completely ignoring stuff like foreign keys, I'm only utilizing one core. I had on my todo list an experiment to run these imports in paralell into different databases and then merging them somehow, but I ran out of time. Again, 10Gb sqlite is quite large.
On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore. I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother.
And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count(*) on main table and it will only take like 40kb of bandwidth.
> Honestly I don't see much use for yet-another-sqlite.
Agreed. I want something better than SQLite, something that learns from it and upgrades it further.
> On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore.
Absolutely. That's why I am soon finishing my Elixir -> Rust -> SQLite library since I happen to believe most apps don't even need a dedicated DB server.
> I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother.
I would probably easily pay 1000 EUR next month if I could have SQLite with PostgreSQL's strict schema. That's the one weakness of SQLite that I hate with a passion. I know about strict mode. I am using it. Still not good enough. I want "type affinity" gone forever. It's obviously a legacy feature and many people came to rely on it.
Hence I concluded that SQLite will never change and something newer should arrive at one point. Though how do you beat the (likely) millions of tests that SQLite has? You don't... but we have to start somewhere.
> And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count() on main table and it will only take like 40kb of bandwidth.*
Admittedly I never saw the value in that, to me that just seems like you are having a remote database again, at which point why not just go for PostgreSQL which is stricter and has much less surprises. But that's my bias towards strictness and catching bugs at the door and not 10 km down the road.
I hear you. As someone who lost rest of his hair during last 10 years talking to frontend kids claiming that types are for grannies - I'm on your side.
But having that said, sqlite has a niche application and you can enforce types on app layer, the same way you do it with web apps. Trust, but verify. Better - don't trust at all. At the end of the day the way I see it - it's just like protobuffers et al. - you put some data into a black box stream of bytes and it's your responsiblity to ensure correctness on both ends.
@serverless
It's twofold. On one hand you have the ability to move faster. On the other you have less moving parts that need maintenance and can break. Plus, for me personally, it's the default mindset. Let me give you an example - in 2025 still most online shops have filters that will trigger a full reload of the web page.
When I'm clicking on TVs in a shop I don't need to reload the webpage everytime I click on something, the app could easily got the whole stock in single json and filter results on the fly while I'm fiddling with filters. Sure it doesn't work for amazon, but it works for 95% of shops online. Yet no one is doing it. Why?
My point - I'm looking for a way to simplify processes, and for some niche applications it's just more convenient.
RE: serverless, I think I understand the use-case and I get the homogeneity argument, it's just that to me SQLite mostly wins for being embedded; the rest of its traits are a nice bonus but using stuff e.g. Litestream I don't view as super important. And I would change a good amount of its internals if it were up to me. But! It helps you, you are using it, you are happy with it -- cool!
RE: stricter types, oh, I am adding a ton of code to verify types, absolutely. My upcoming FFI library (Elixir -> Rust -> SQLite) will have a lot of "raw" FFI bridges to SQLite with some good sensible default checks but ultimately I'd leave it to the users (programmers) of the library to f.ex. make sure that the field `price` in each result from the set is in fact a `REAL` (float / double) value. That's going to be the next game though, the "raw" FFI stuff will just make sure nothing ever crashes / panics (as much as I can guarantee; obviously I can't stop the OS killing the process or running out of disk or memory) and return errors as detailed and as machine-readable as they can be (major selling point, at least for me when I start dogfooding it). Just today I started working on using interruptible SQLite operations (via its progress handler mechanism) and it's almost done and I'll release it via a PR. Which will also make the library near-real-time friendly (I am aiming at 1-10 ms pauses between each check-in at most even if you are fetching a million records). Etc.
So yeah, no trust and a lot of verification indeed. But I would still like to have some more safety around executing raw SQL (not talking injection here) where e.g. you are never allowed to insert a string into an integer column.
It's hard to complain though. SQLite is one of the very very best softwares ever made. If the price for using it is to write some more conservative validation code at the edges then that's still a fantastic deal and I am happy to do it.
I have a PoC KV store that does > 150M write/s (Rust) using a single core (for 8 bytes of data inserted - it gets bandwidth limited on disk quite quickly even latest NVME PCIE5 disks). The plan is to have it support application RAID0 out of the box so that you could spread the data across multiple disks, but of course that's something you have to setup up-front when you create the DB.
I then would add a SQL engine on top - not sure how much SQL would slow things down but hopefully not much. I haven't found anyone who's interested in anything like that though.
And yes I realize this is several orders of magnitude more performance than any other DB out there.
kv doesn't cut my use case. After import I'm running several queries to remove unwanted data. I was shortly contemplaiting filtering while I was importing but (a) I couldn't really handle relationships - hence two distinct steps - remove and remove orphans, and (b) even if I could it's really much cleaner code having a single simple function to import a table, and then having a clean one liners to remove weed. And (c) I need sql queries later on.
Similar to @zeroq, I don't really need a K/V store. I need a full relational database that I can also use for analytics and time series, and I want it embedded. And as strict as possible -- PostgreSQL is doing this really well.
I don't mind that there are databases that can be forced into all that and work alright but I admit I am not willing to put the plumbing work if I can avoid it. If I can't avoid it at one point then, well, we'll cross that bridge when we get to it.
DuckDB and ClickHouse(-Local) are amazing candidates but I have never evaluated their normal OLTP performance. For now.
(EDIT: let that not stop you however. Please publish it and announce your thing on HN as well.)
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.
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?
As with any optimization, it matters where your bottleneck is here. Sounds like theirs is bandwidth but CPU/Disk IO is plentiful since they mentioned that downloading 250MB database takes minute where I just grabbed 2GB SQLite test database from work server in 15 seconds thanks to 1Gbps fiber.
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.
I have yet to see a single SQLite binding supporting this, so it’s quite useless unless you’re writing your application in C, or are open to patching the language binding.
In one of my projects I have implemented my own poor man’s session by writing all the statements and parameters into a separate database, then sync that and replay. Works well enough for a ~30GB database that changes by ~0.1% every day.
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.
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.
Quite simply, I have a table with 4 columns -- A, B, C, D. Each column is just an 8-byte integer. It has hundreds of millions of rows. It has an index on B+C+D, an index on C+D, and one on D.
All of these are required because the user needs to be able to retrieve aggregate data based on range conditions around lots of combinations of the columns. Without all the indices, certain queries take a couple minutes. With them, each query takes milliseconds to a couple seconds.
I thought of every possible way to avoid having all three indices, but it just wasn't possible. It's just how performant data lookup works.
You shouldn't assume people are being careless with indices. Far too often I see the opposite.
--no-whole-file is the standard, except for local paths (on the same system, the delta-transfer algorithm would be a waste of work, as it reads src and dst to find the delta with rolling checksums -- over the network it can save lots of time and bandwidth)
--inplace is independent of that: it specifies writing directly to target files (instead of write to temp file and rename to target after completion)
I've seen a suggestion several times to compress the data before sending. If remote means in the same data center, there's a good chance compressing the data is just slowing you down. Not many machines can gzip/bzip2/7zip at better than the 1 gigabyte per second you can get from 10 Gbps networks.
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?
> 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.
> Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption
Do people really not understand how file storage works? I cannot rightly apprehend the confusion of ideas that would produce an attempt to copy a volatile database without synchronization and expect it to work.
The confusion of ideas here is understandable IMO: people assume everything is atomic. Databases of course famously have ACID guarantees. But it's easy for people to assume copying is also an atomic operation. Honestly if someone works too much with databases and not enough with filesystems it's a mistake easily made.
It was early days... very early days. He didn't have the benefit of trying to help his (metaphorical) grandparents get their emails or worked under a manager who thinks 2023-era ChatGPT is only slightly less reliable than the Standard Model of Physics, if not slightly more.
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.
>You can't copy the file of a running, active db receiving updates, that can only result in corruption
There is a slight 'well akshully' on this. A DB flush and FS snapshot where you copy the snapshotted file will allow this. MSSQL VSS snapshots would be an example of this.
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.
For moving DBs where I'm allowed minutes of downtime I do rsync (slow) first from the live, while hot, then just stop that one, then rsync again (fast) then make the new one hot.
Works a treat when other (better) method are not available.
> You can't copy the file of a running, active db receiving updates, that can only result in corruption
To push back against "only" -- there is actually one scenario where this works. Copying a file or a subvolume on Btrfs or ZFS can be done atomically, so if it's an ACID database or an LSM tree, in the worst case it will just rollback. Of course, if it's multiple files you have to take care to wrap them in a subvolume so that all of them are copied in the same transaction, simply using `cp --reflink=always` won't do.
Possibly freezing the process with SIGSTOP would yield the same result, but I wouldn't count on that
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?
Taking an LVM snapshot and then copying the sqlite database from that is sufficient to keep it from being corrupted, but you can have incomplete transactions that will be rolled back during crash recovery.
The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata.
To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot.
PRAGMA wal_checkpoint(FULL);
BEGIN IMMEDIATE; -- locks out writers
. /* trigger your LVM snapshot here */
COMMIT;
You can also use fsfreeze to get the same level of safety:
What does "losing transactions" mean? Some transactions will have committed before your backup and some will have committed after and therefore won't be included. I don't see the problem you are trying to solve?
Whether a transaction had started and gets transparently rolled back, or you had prevented from starting, what is the difference to you? Either way, you have a point-in-time snapshot, that time is the latest commit before the LVM snapshot.
You're discussing this in terms of "safety" and that doesn't seem right to me.
This isn't really a personal or controversial take on the issue. There are easier ways to back up a sqlite database, but if you want to use LVM snapshots you need to understand how to do it correctly if you want to have useful backups.
Here's a scenario for a note-taking app backed by sqlite:
1. User A is editing a note and the app writes the changes to the database.
2. SQLite is in WAL mode, meaning changes go to a -wal file first.
3. You take the LVM snapshot while SQLite is:
- midway through a write (to the WAL or the main db file)
- or the WAL hasn’t been checkpointed back into the main DB file
4. The snapshot includes:
- a partial write to notes.db
- or a notes.db and notes.db-wal that are out of sync
Result: The backup is inconsistent. Restoring this snapshot later might:
- Cause sqlite3 to throw errors like database disk image is malformed
- Lose recent edits
- Require manual recovery or loss of WAL contents
In order to get a consistent, point-in-time recovery where your database is left in state A and your backup from the LVM snapshot is in state B with _no_ intermediate states (like rolled back transactions or partial writes to the db file), you have to first either:
- Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes
- Or, flush and block all writes to the filesystem using fsfreeze
You are wrong, the whole point of the WAL is to make SQLite crash-consistent. Same as the rollback journal. SQLite will safely rollback partial writes. I don't know where you got the idea that in WAL mode, SQLite ditches its consistency guarantees somehow.
If you can corrupt a SQLite database by pulling the power or simulating it by taking a block-device snapshot, this is a serious SQLite bug and you should report it.
Sorry but your point is just completely wrong and I am not sure why you have this belief. It is extremely normal and safe to backup SQLite via block device or filesystem snapshots. Are you under the impression that SQLite cannot recover from a power loss...? The whole point of using a log is that you can recover from crashes mid-way through updating the database file by replaying the writes from the log.
If it will convince you, I went and asked ChatGPT like you recommended and it agrees:
> If you want to use snapshots:
> Use a filesystem or block-level snapshot tool that guarantees a point-in-time, atomic snapshot (e.g., LVM snapshots, ZFS snapshots, Btrfs snapshots, or VSS on Windows).
If you were going to "Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes" as you suggest is necessary, why even bother with a snapshot at that point?
I'm sorry you took "you're wrong" as aggressive. I am providing sources though and other users are agreeing with me, will you please look into it? Or ask ChatGPT if SQLite is crash-consistent? Please consider the possibility you are spreading misinformation...
The last time I had to call fsfreeze (or xfs_freeze) before creating an LVM snapshot was maybe 17-18 years ago on a system with rather old (even for the time) "stable" versions of everything, where it was not yet integrated.
Since decades, lvm triggers a freeze before snapshot creation and an unfreeze afterwards, if the filesystem supports it.
> fsfreeze is unnecessary for device-mapper devices. The device-mapper (and LVM) automatically freezes a filesystem on the device when a snapshot creation is requested. For more details see the dmsetup(8) man page.
> freeze_fs
>
> called when VFS is locking a filesystem and forcing it into a consistent state. This method is currently used by the Logical Volume Manager (LVM) and ioctl(FIFREEZE). Optional.
> List of these filesystems include (2016-12-18) btrfs, ext2/3/4, f2fs, jfs, nilfs2, reiserfs, and xfs. Previous list may be incomplete, as more filesystems get support.
Using LVM snapshots is absolutely fine for creating consistent and complete copies of sqlite databases. You have to to copy the rollback-journal or WAL, too: they are part the db, it will checkpoint to the db file when you open it or its copy. Every committed transaction before the snapshot is in it. It is complete.
And even if you do use a mode below PRAGMA synchronous=FULL (
https://sqlite.org/pragma.html#pragma_synchronous ) -- FULL is needed to ensure durability as it fsync()s the WAL on every commit -- you are better of than with a system crash: the freeze writes out every dirty page to disk, which is not guaranteed in case of a crash, where in NORMAL mode, you might lose the transactions written to the WAL file, but not to synced to disk yet.
I would assume cp uses ioctl (with atomic copies of individual files on filesystems that support CoW like APFS and BTRFS), whereas sqlite probably uses mmap?
I was trying to find evidence that reflink copies are atomic and could not and LLMs seem to think they are not. So at best may be a btrfs only feature?
> Clones are atomic with regards to concurrent writes, so no locks need to be taken to obtain a consistent cloned copy.
I'm not aware of any of the filesystems that use it (Btrfs, XFS, Bcachefs, ZFS) that deviate from expected atomic behavior, at least with single files being the atom in question for `FICLONE` operation.
If the corruption is detectable and infrequent enough for your purposes, then it does work, with a simple “retry until success” loop. (That’s how TCP works, for example.)
Not all corruption is detectable. You could make a copy during a transaction where only a subset of the transactions saved pages are persisted but all branch & leaf pages are pointed to correctly. That would give you a state of the database that never actually existed and break atomicity.
Well, I don't know rsync that well. If you're saying it doesn't detect changes to files while it's being copied, then I'll believe you.
And, as far as I know, it's impossible to detect absolutely all corruption.
But you can pretty easily detect, e.g., that a file has or has not changed since before you copied it to after, on a system with a basically functioning filesystem and clock, with a reasonable/useful level of confidence.
While I run and love litestream on my own system, I also like that they have a pretty comprehensive guide on how to do something like this manually, via built-in tools: https://litestream.io/alternatives/cron/
Litestream is really cool! I'm planning to use it to backup and restore my SQLite in the container level, just like what that ex-google guy who started a startup of a small KVM and had a flood in his warehouse while on vacation did. If I'm not mistaken. I would link here the perfect guide he wrote but there's 0 chance I'll find it. If you understand the reference please post the link.
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.
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 :)
sqlite_rsync can only be used in WAL mode. A further constraint of WAL mode is the database file must be stored on local disk. Clearly, you'd want to do this almost all the time, but for the times this is not possible this utility won't work.
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.
I'm not sure understand your comment. Regardless of WAL or network filesystem usage, the sqlite file cannot be written to from multiple processes simultaneously. Am I missing something here, or did you misstate?
You can have multiple writer connections from multiple writer processes (on one host at a time). Their transactions will be safely serialized by SQLite.
So, sure, from a low-level point of view, the write transactions are not really simultaneous (they never are with SQLite3), but from the user perspective they are in the sense that you don't need to serialize them yourself.
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.
200 comments
[ 4.2 ms ] story [ 202 ms ] threadgzip is a legacy algorithm that imo only gets used for compatibility with legacy software that understands nothing but gzip.
sqlite3 data/database.db < “{backup_file}"
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.
https://sqlite.org/com/cerod.html
https://duckdb.org/docs/stable/sql/statements/export.html
You'd want to do this:
Also I wonder how big your test database is and it's schema. For large tables Parquet is way more efficient than a 20% reduction.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.
sqlite transaction- and WAL-aware rsync with inflight compression.
(It just so happens that the concatenation of all text-looking .tar files I happen to have on this machine is roughly a gigabyte (though I did the math for the actual size)).
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.
(This is the same spiel I give whenever someone says swap on Linux is or is not always beneficial.)
When I do stuff like this, I stream the dump straight into gzip. (You can usually figure out a way to stream directly to the destination without an intermediate file at all.)
Plus this way it stays stored compressed at its destination. If your purpose is backup rather than a poor man's replication.
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db
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.
You could do a filesystem snapshot and copy from that, but neither a cp or rsync is atomic.
And then there is also https://www.sqlite.org/rsync.html
What I ended up doing is creating a new database, pg_restore'ing into that one with --no-owner and --no-acl, forcibly dropping the old database, and then renaming the new to the old one's name. This has the benefit of not leaving me high and dry should there be an issue with restoring.
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)
> Downloading a 250MB database from my web server takes about a minute over my home Internet connection
So for the original 3.4GB database that's nearly 15mn waiting for the download.
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.
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):
It's a very good writeup on how to do fast inserts in sqlite3
CREATE INDEX then INSERT vs. INSERT then CREATE INDEX
i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.
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.
I tested couple different approaches, including pglite, but node finally shipped native sqlite with version 23 and it's fine for me.
I'm a huge fan of serverless solutions and one of the absolute hidden gems about sqlite is that you can publish the database on http server and query it extremely efficitent from a client.
I even have a separate miniature benchmark project I thought I might publish, but then I decided it's not worth anyones time. x]
I have been looking for replacement of SQLite for years -- admittedly, not very actively, embedded databases are just a hobby obsession and my life did not allow me much leisure time in the last years -- and still couldn't find one.
The written-in-Rust `sled` database is more like a key-value store and I had partial successes with it but it's too much work making a KV store a relational database.
The premise of having 100M/s writes instead of 500k/s sounds bit unrealistic, but at the same time, while simply importing tuples and completely ignoring stuff like foreign keys, I'm only utilizing one core. I had on my todo list an experiment to run these imports in paralell into different databases and then merging them somehow, but I ran out of time. Again, 10Gb sqlite is quite large.
On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore. I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother.
And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count(*) on main table and it will only take like 40kb of bandwidth.
Agreed. I want something better than SQLite, something that learns from it and upgrades it further.
> On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore.
Absolutely. That's why I am soon finishing my Elixir -> Rust -> SQLite library since I happen to believe most apps don't even need a dedicated DB server.
> I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother.
I would probably easily pay 1000 EUR next month if I could have SQLite with PostgreSQL's strict schema. That's the one weakness of SQLite that I hate with a passion. I know about strict mode. I am using it. Still not good enough. I want "type affinity" gone forever. It's obviously a legacy feature and many people came to rely on it.
Hence I concluded that SQLite will never change and something newer should arrive at one point. Though how do you beat the (likely) millions of tests that SQLite has? You don't... but we have to start somewhere.
> And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count() on main table and it will only take like 40kb of bandwidth.*
Admittedly I never saw the value in that, to me that just seems like you are having a remote database again, at which point why not just go for PostgreSQL which is stricter and has much less surprises. But that's my bias towards strictness and catching bugs at the door and not 10 km down the road.
I hear you. As someone who lost rest of his hair during last 10 years talking to frontend kids claiming that types are for grannies - I'm on your side. But having that said, sqlite has a niche application and you can enforce types on app layer, the same way you do it with web apps. Trust, but verify. Better - don't trust at all. At the end of the day the way I see it - it's just like protobuffers et al. - you put some data into a black box stream of bytes and it's your responsiblity to ensure correctness on both ends.
@serverless
It's twofold. On one hand you have the ability to move faster. On the other you have less moving parts that need maintenance and can break. Plus, for me personally, it's the default mindset. Let me give you an example - in 2025 still most online shops have filters that will trigger a full reload of the web page. When I'm clicking on TVs in a shop I don't need to reload the webpage everytime I click on something, the app could easily got the whole stock in single json and filter results on the fly while I'm fiddling with filters. Sure it doesn't work for amazon, but it works for 95% of shops online. Yet no one is doing it. Why?
My point - I'm looking for a way to simplify processes, and for some niche applications it's just more convenient.
RE: stricter types, oh, I am adding a ton of code to verify types, absolutely. My upcoming FFI library (Elixir -> Rust -> SQLite) will have a lot of "raw" FFI bridges to SQLite with some good sensible default checks but ultimately I'd leave it to the users (programmers) of the library to f.ex. make sure that the field `price` in each result from the set is in fact a `REAL` (float / double) value. That's going to be the next game though, the "raw" FFI stuff will just make sure nothing ever crashes / panics (as much as I can guarantee; obviously I can't stop the OS killing the process or running out of disk or memory) and return errors as detailed and as machine-readable as they can be (major selling point, at least for me when I start dogfooding it). Just today I started working on using interruptible SQLite operations (via its progress handler mechanism) and it's almost done and I'll release it via a PR. Which will also make the library near-real-time friendly (I am aiming at 1-10 ms pauses between each check-in at most even if you are fetching a million records). Etc.
So yeah, no trust and a lot of verification indeed. But I would still like to have some more safety around executing raw SQL (not talking injection here) where e.g. you are never allowed to insert a string into an integer column.
It's hard to complain though. SQLite is one of the very very best softwares ever made. If the price for using it is to write some more conservative validation code at the edges then that's still a fantastic deal and I am happy to do it.
I then would add a SQL engine on top - not sure how much SQL would slow things down but hopefully not much. I haven't found anyone who's interested in anything like that though.
And yes I realize this is several orders of magnitude more performance than any other DB out there.
I don't mind that there are databases that can be forced into all that and work alright but I admit I am not willing to put the plumbing work if I can avoid it. If I can't avoid it at one point then, well, we'll cross that bridge when we get to it.
DuckDB and ClickHouse(-Local) are amazing candidates but I have never evaluated their normal OLTP performance. For now.
(EDIT: let that not stop you however. Please publish it and announce your thing on HN as well.)
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.
https://www.sqlite.org/sessionintro.html
Idea for an offline first app, where each app install call pull a changeset and apply it to their local db.
In one of my projects I have implemented my own poor man’s session by writing all the statements and parameters into a separate database, then sync that and replay. Works well enough for a ~30GB database that changes by ~0.1% every day.
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.
Every extra bit makes AOT compiling the Wasm slower (impacting startup time).
I also wanna keep the number of variants reasonable, or my repo blows up.
Add your votes for additional features to this issue: https://github.com/ncruces/go-sqlite3/issues/126
It's not carelessness, it's performance.
Quite simply, I have a table with 4 columns -- A, B, C, D. Each column is just an 8-byte integer. It has hundreds of millions of rows. It has an index on B+C+D, an index on C+D, and one on D.
All of these are required because the user needs to be able to retrieve aggregate data based on range conditions around lots of combinations of the columns. Without all the indices, certain queries take a couple minutes. With them, each query takes milliseconds to a couple seconds.
I thought of every possible way to avoid having all three indices, but it just wasn't possible. It's just how performant data lookup works.
You shouldn't assume people are being careless with indices. Far too often I see the opposite.
Set this on the connection:
PRAGMA mmap_size=…
https://www.sqlite.org/mmap.html
I think this works as it removes data copying between the os and your process.
Your process reads data from the os to execute the query.
With mmap the data reads have less overhead, so a full table scan may be fast enough.
Just ssh the machine, dump the SQL and load it back into SQLite locally.
--inplace is independent of that: it specifies writing directly to target files (instead of write to temp file and rename to target after completion)
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?
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
Do people really not understand how file storage works? I cannot rightly apprehend the confusion of ideas that would produce an attempt to copy a volatile database without synchronization and expect it to work.
I see you are a man of culture.
But things haven't improved much. Today we have "prompt engineers" whose only job is to input the right question in order to get the right answer.
Is Litestream still an active project?
There is a slight 'well akshully' on this. A DB flush and FS snapshot where you copy the snapshotted file will allow this. MSSQL VSS snapshots would be an example of this.
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.
Works a treat when other (better) method are not available.
https://learn.microsoft.com/en-us/sql/relational-databases/b...
To push back against "only" -- there is actually one scenario where this works. Copying a file or a subvolume on Btrfs or ZFS can be done atomically, so if it's an ACID database or an LSM tree, in the worst case it will just rollback. Of course, if it's multiple files you have to take care to wrap them in a subvolume so that all of them are copied in the same transaction, simply using `cp --reflink=always` won't do.
Possibly freezing the process with SIGSTOP would yield the same result, but I wouldn't count on that
The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata.
To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot.
You can also use fsfreeze to get the same level of safety: Bonus - validate the snapshotted db file with:Whether a transaction had started and gets transparently rolled back, or you had prevented from starting, what is the difference to you? Either way, you have a point-in-time snapshot, that time is the latest commit before the LVM snapshot.
You're discussing this in terms of "safety" and that doesn't seem right to me.
Here's a scenario for a note-taking app backed by sqlite:
1. User A is editing a note and the app writes the changes to the database.
2. SQLite is in WAL mode, meaning changes go to a -wal file first.
3. You take the LVM snapshot while SQLite is:
4. The snapshot includes: Result: The backup is inconsistent. Restoring this snapshot later might: In order to get a consistent, point-in-time recovery where your database is left in state A and your backup from the LVM snapshot is in state B with _no_ intermediate states (like rolled back transactions or partial writes to the db file), you have to first either: Then take the LVM snapshot.If you can corrupt a SQLite database by pulling the power or simulating it by taking a block-device snapshot, this is a serious SQLite bug and you should report it.
https://sqlite.org/transactional.html & https://sqlite.org/atomiccommit.html have more details
If it will convince you, I went and asked ChatGPT like you recommended and it agrees:
> If you want to use snapshots: > Use a filesystem or block-level snapshot tool that guarantees a point-in-time, atomic snapshot (e.g., LVM snapshots, ZFS snapshots, Btrfs snapshots, or VSS on Windows).
If you were going to "Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes" as you suggest is necessary, why even bother with a snapshot at that point?
Since decades, lvm triggers a freeze before snapshot creation and an unfreeze afterwards, if the filesystem supports it.
> fsfreeze is unnecessary for device-mapper devices. The device-mapper (and LVM) automatically freezes a filesystem on the device when a snapshot creation is requested. For more details see the dmsetup(8) man page.
https://man7.org/linux/man-pages/man8/fsfreeze.8.html
It is part of the VFS:
> freeze_fs > > called when VFS is locking a filesystem and forcing it into a consistent state. This method is currently used by the Logical Volume Manager (LVM) and ioctl(FIFREEZE). Optional.
https://docs.kernel.org/filesystems/vfs.html
All relevant filesystems implement it.
> List of these filesystems include (2016-12-18) btrfs, ext2/3/4, f2fs, jfs, nilfs2, reiserfs, and xfs. Previous list may be incomplete, as more filesystems get support.
Using LVM snapshots is absolutely fine for creating consistent and complete copies of sqlite databases. You have to to copy the rollback-journal or WAL, too: they are part the db, it will checkpoint to the db file when you open it or its copy. Every committed transaction before the snapshot is in it. It is complete.
And even if you do use a mode below PRAGMA synchronous=FULL ( https://sqlite.org/pragma.html#pragma_synchronous ) -- FULL is needed to ensure durability as it fsync()s the WAL on every commit -- you are better of than with a system crash: the freeze writes out every dirty page to disk, which is not guaranteed in case of a crash, where in NORMAL mode, you might lose the transactions written to the WAL file, but not to synced to disk yet.
https://man7.org/linux/man-pages/man2/ioctl_ficlone.2.html
> Clones are atomic with regards to concurrent writes, so no locks need to be taken to obtain a consistent cloned copy.
I'm not aware of any of the filesystems that use it (Btrfs, XFS, Bcachefs, ZFS) that deviate from expected atomic behavior, at least with single files being the atom in question for `FICLONE` operation.
Well, I don't know rsync that well. If you're saying it doesn't detect changes to files while it's being copied, then I'll believe you.
And, as far as I know, it's impossible to detect absolutely all corruption.
But you can pretty easily detect, e.g., that a file has or has not changed since before you copied it to after, on a system with a basically functioning filesystem and clock, with a reasonable/useful level of confidence.
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.
A reminder that litestream can run over plain old SFTP[1] which means you can stream database replication to just about any UNIX endpoint over SSH.
I have a favorite[2] but any SFTP server will do ...
[1] https://github.com/benbjohnson/litestream/issues/140
[2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_tech...
[1] https://www.postgresql.org/docs/current/warm-standby.html [2] https://www.postgresql.org/docs/current/logical-replication....
Wot? There are multiple ways of snapshotting/checkpointing, starting at the virty level and working on down the stack through the application level.
https://www.sqlite.org/lang_vacuum.html
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.
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...
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.
So, sure, from a low-level point of view, the write transactions are not really simultaneous (they never are with SQLite3), but from the user perspective they are in the sense that you don't need to serialize them yourself.
sqlite3_rsync is now built into the rsync.net platform.
… just added last week and not rolled out in all regions but … all initial users reported it worked exactly as they expected it to.https://github.com/upa/mscp