[HN Gopher] Experience with SQLite as a Store of Files and Images
___________________________________________________________________
Experience with SQLite as a Store of Files and Images
Author : thunderbong
Score : 60 points
Date : 2024-05-10 11:02 UTC (1 days ago)
(HTM) web link (sqlite.org)
(TXT) w3m dump (sqlite.org)
| evnix wrote:
| 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.
| ectospheno wrote:
| Set the auto_vacuum pragma before creating any tables?
| kevincox wrote:
| The db size generally won't shrink under default settings, but
| the space will be reused.
| kevincox wrote:
| 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.
| knighthack wrote:
| 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
| Repulsion9513 wrote:
| > 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...
| gfv wrote:
| You can use streaming WALs, like Litestream does. It is a
| pretty simple way to do incremental backups.
| password4321 wrote:
| 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.
| forinti wrote:
| 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.
| fforflo wrote:
| 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
___________________________________________________________________
(page generated 2024-05-11 23:02 UTC)