Experience with SQLite as a Store of Files and Images | Hacker News Reader

Experience with SQLite as a Store of Files and Images

sqlite.org

74 pointsthunderbong11 comments

I think the biggest issue with storing large "blobs" in the DB is that you often want a different backup strategy. The DB is a mutating file so you will want to do some form of incremental backup but blobs are often unchanging. So it is often easier to pub blobs in a different file (possibly content-addressed) and avoid scanning through them checking for changes on every backup run.

Of course depending on how you do backups this may be a non-issue (ex: filesystem snapshots with native delta snapshot transfer)

It maybe also be easier if you want to launch a photo viewer for example. You can just point it at the regular file rather than extracting a temporary file from the database, launching the viewer and making sure to clean up the file when the viewer is done.

The biggest benefit is that you get consistency for free. However this is often fairly easy to manage if you have a dedicated table for external blobs and common code to read and write them.

One caveat: if the blobs (e.g. images) in the DBs are static, and transactions are read only/writes happen infrequently, then having all your blobs in one SQLite DB makes for an insanely easy backup strategy, compared to having thousands of smaller files on the filesystem.

And it only requires two tables - one table for blob storage with an indexable column; and another table where you're storing the metadata associated to the rows in the blob table.

For a small number of users and simple apps, it can be an ideal solution. I know, because I've tried.

---

This is an interesting read from the official site as well - '35% Faster Than The Filesystem' / https://sqlite.com/fasterthanfs.html

> if the blobs (e.g. images) in the DBs are static, and transactions are read only/writes happen infrequently, then having all your blobs in one SQLite DB makes for an insanely easy backup strategy

Does it? Suddenly instead of just backing up the whole database you have to do delta backups which can make it quite a bit more difficult...

You can use streaming WALs, like Litestream does. It is a pretty simple way to do incremental backups.

I ran I to this backing up a SQLite DB where I wasn't deleting records: it would have been easier to sync a separate .ZIP of the files because it would have then been append-only.

You can store a changelog as part of a transaction to modify the files. Very easy to know what changed and when.

If you want to go more advanced, you can also store the deltas similar to source control. Then you can get a snapshot of the files at any point in time.

I recently decommissioned an Oracle Portal installation. I kept a backup of the database, but in order to keep a version I could easily lookup, I also copied it into an sqlite file.

As I expected, I later had to recover various files that had been stored there. It was a lot easier than restoring the backup.

Kudos to SQLite.

A different approach to backups is an important aspect here. Also, writing-heavy things can get trickier with concurrency and locks. BLOBs usually have a more archive nature. In a scenario where I had a 5-10MB pdf document (BLOB) for each 4KB JSON document, I had to store these in separate db files. When I want to use them within the same query, I would use ATTACH https://www.sqlite.org/lang_attach.html

Had this exact same idea but I wonder how does it handle deletes.

Is it actually going to release deleted unused space or would be a manual defragmentation after some deletes.

The db size generally won't shrink under default settings, but the space will be reused.

Set the auto_vacuum pragma before creating any tables?

Reply on news.ycombinator.com