[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  : 23 points
       Date   : 2024-05-10 11:02 UTC (11 hours 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.
        
       ___________________________________________________________________
       (page generated 2024-05-10 23:01 UTC)