Are there drawbacks to sqlite as archivation format?

Question

I have a few huge folders of small files I want to archive for long term storage (about 200GB).

Instead of just using something like gzip/tar/dar/7zip I am considering to use sqlite

sqlite3 Backup.db ".ar -cv Data"

Disregarding the compression, am I going towards some pitfalls? Are there any advantages old simple tar would have?

Answer 1

The primary disadvantage: you need an sqlite3 executable with archive support when you need to recover your data. Depending on the circumstances, you may not have one handy, but you're far more likely to find a working gzip, tar et al.

Here's why I'm being particularly pedantic about this:

• macOS system sqlite3 versions are generally within spitting distance of up-to-date, but Apple only updates its software sporadically, and many Mac users may run backdated OSes for various reasons. Consequently, macOS system sqlite3 before Mojave does not support archiving, so for portability, you'll definitely want to ensure that the Homebrew sqlite3 package is installed and updated.
• Homebrew on Linux defaults to installing a bottled (i.e. precompiled binary) sqlite v3.26 that somehow manages to not support archiving. I had to brew install --build-from-source sqlite3 to fix that.

Those are just two examples that I have personal experience with. The platforms on which you plan to sqlar may have similar gotchas. Contrast all that with "it just works everywhere" gzip/tar/etc.

So, if you want to use the sqlar format in earnest, carefully consider all the circumstances and environments in which you'll be doing so. A rescue disk, for instance, may not have an archiving sqlite3, and you really don't want to build sqlite from scratch during disaster recovery.

---

Aside from the availability of an archive-capable sqlite3, you also need to consider the issue of file sizes.

Since an sqlar is basically an SQLite DB, it inherits all the limits of this file format. In particular, each file's data is stored as a BLOB, which has a default maximum size of 1 billion bytes. Also, SQLite query processing leads to each row being handled as a single BLOB, further lowering the file size limit.

Compare that with ZIP (2³² for "classic ZIP", 2⁶⁴ for ZIP64) or GNU tar (unlimited), and you may have yet another reason to take the traditional route instead.