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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
