4 ms·
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
by M95D 1y ago
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 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.
- gwbas1c 1y agoDoes that preserve the indexes? As the TFA mentioned, the indexes are why the sqlite files are huge.
- M95D 1y agoYou're right. It does. I never thought about it until you asked.
- 4silvertooth 1y agoI think it won't preserve the index but it will recreate the index while running the text sql.
- nine_k 1y agoIt's cool but it does not address the issue of indexes, mentioned in the original post. Not carrying index data over the slow link was the key idea. The VACUUM INTO approach keeps indexes. 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.
- vlovich123 1y ago> 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
- dunham 1y agoBelow I'm discussing compressed size here rather than how "fast" it is to copy databases. Yeah there are indexes. And even without indexes there is an entire b-tree sitting above the data. So we're weighing the benefits of having a domain dependent compression (binary format) vs dropping all of the derived data. I'm not sure how that will go, but lets try one. Here is sqlite file containing metadata for apple's photo's application: 767979520 May 1 07:28 Photos.sqlite Doing a VACUUM INTO: 719785984 May 1 08:56 photos.sqlite gzip -k photos.sqlite (this took 20 seconds): 303360460 May 1 08:56 photos.sqlite.gz sqlite3 -readonly photos.sqlite .dump > photos.dump (10 seconds): 1277903237 May 1 09:01 photos.dump gzip -k photos.dump (21 seconds): 285086642 May 1 09:01 photos.dump.gz About 6% smaller for dump vs the original binary (but there are a bunch of indexes in this one). For me, I don't think it'd be worth the small space savings to spend the extra time doing the dump. With indexes dropped and vacuumed, the compressed binary is 8% smaller than compressed text (despite btree overhead): 566177792 May 1 09:09 photos_noindex.sqlite 262067325 May 1 09:09 photos_noindex.sqlite.gz About 13.5% smaller than compressed binary with indices. And one could re-add the indices on the other side.
- ForOldHack 1y agoBrilliant. >60% savings. 700mb? wow.
- conradev 1y agoSQLite 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.
- nine_k 1y agoThis should be the accepted answer.