@Jens; I'd take what David suggested one step further and just remove the BLOB from the tables of concern and keep the blob on a 1:1 PK:FK relationship and only look at the BLOB tables when required. That way if you do an occasional [select * from ...] you're not reading the BLOB because it's living somewhere else.
I make this suggestion not just because of SQLite, but, any DBMS I've developed for. Keep the really big data somewhere else, with a relationship to the primary resource required, and then only query that massive chunk when required through a specialized view or select. On Wed, Mar 29, 2017 at 2:11 PM, David Raymond <david.raym...@tomtom.com> wrote: > Remember to make the blob field the very last field in your table schema, > and avoid "select * from", otherwise you can create performance problems. > The data for a record is stored in the same order as the fields are > defined, and overflow pages are a linked list. So if you have your 100 MB > blob first, and your ID second, then you will have to read through all 100 > MB of the blob to get to your ID. > > For the freeing up of space check out the incremental version of auto > vacuum. Rather than freeing it all up at once it might be good to run it > "incrementally" to avoid 1 big performance hit. Maybe have something run > every so often to free up a set amount of pages, similar to a scheduled WAL > checkpointer. > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jens Alfke > Sent: Wednesday, March 29, 2017 1:14 PM > To: SQLite mailing list > Subject: [sqlite] Questions on big blobs and blob I/O > > I’m thinking of transitioning from external to internal storage of large > blobs. That is, currently I store these as individual files in a directory > next to the SQLite database. But it sounds like today’s new improved SQLite > is better at managing big blobs, and supports streaming reads; and it would > simplify my code to not have to deal with all those files. > > (These blobs are created by a “document attachments” API of the library I > work on. What’s in them is up to the developer. They’re often fairly small, > e.g. a thumbnail JPEG, but they could be large media files like movies, in > the tens or hundreds of megabytes. There’s no upper limit.) > > I have two questions: > > * Do big blobs cause any performance problems for the db as a whole, like > fragmentation? In particular, is the blob data stored contiguously in the > file? > > * After a big blob is deleted, my understanding is that the db file won’t > shrink, so the space isn’t reclaimed to the filesystem. Instead the freed > pages in the db file will be reused later on. If I urgently need to shrink > the file, do I have to run a full (expensive) vacuum? (The problem with > vacuum is that it requires lots of free space to work, but blob deletions > may be happening on behalf of the user, to free up storage when the > [mobile] device is running out of free space, creating a catch-22…) > > * Is there any way to avoid a sqlite3_blob handle being invalidated when > the blob’s row is deleted (via a DELETE on another db handle?) I’d prefer > to have behavior like file deletion in Unix, where open file handles keep > working even after a file is deleted. I’m wondering whether I can > accomplish this by opening a read transaction before opening the blob, then > leaving it open until the blob is closed. Since I’m using WAL, will this db > handle keep a snapshot of the time before the blob’s row was deleted, > allowing the blob handle to keep working? > > Thanks! > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
