On 29 Mar 2017, at 6:14pm, Jens Alfke <j...@mooseyard.com> wrote: > * 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?
Blobs are stored in the same pages that other values are stored in. If a blob is bigger than a page it needs to be split, with the continuation on another page. So yes, this can lead to internal defragmentation of the database. However, with modern storage systems fragmentation causes very little decrease in access time. Access to 'sectors' of solid state storage are not related to the order in which sectors are stored. And even on rotating storage so much use is made of virtual sectors for wear-balancing purposes that consecutive sectors are often not consecutive. Any advice to users to run defragmentation software I have to consider as an obsolete hang-over from obsolete storage systems, merely causing additional pointless wear. > * 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? All the above is correct, as far as I know. Pages and parts of pages are not released from filespace, they’re just listed as 'free' and will be used when SQLite needs more storage space for that database. In your situation you should take a look at <https://sqlite.org/pragma.html#pragma_auto_vacuum> use FULL mode or perhaps use INCREMENTAL mode and perform the incremental vacuums as your app quits. Note that both of these modes are misnamed. Read the descriptions, don’t depend on the names. > * 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? I think that the answer here is negative (unless you play C-type pointer games with memory blocks) but I have to let the devs answer this one. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
