Comment by kevincox | Hacker News Reader

Comment by kevincox | Hacker News Reader

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.

Reply on news.ycombinator.com