auto_vacuum at full is slower: When you delete some data, SQLite would shrink the file size to the minimum. If, in the next transaction, you add new data, SQLite would again have to extend the file size by at least a page. This takes time. Without auto-vacuum=FULL, the Insert statement would reuse the page that became free.
File system fragmentation (how the blocks of your file are arranged on disk) also likely becomes worse. In-file fragmentation (how close related data is w.r.t. the logical file offset) might also become worse, this should not be worse than random inserts and deletes, though. --- auto_vacuum might be useful if disk space is very precious, or you have to be "always on" with guaranteed response times, where even an infrequent short "downtime" is problematic. If you are not sure, you can set auto_vacuum to INCREMENTAL to keep your options open. This allows you to add incremental vaccum later, without having to change database files. The overhead, AFAIK, is little. --- it makes more sense to trigger maintenance when it pays. You can always use pragma free_pages to query how many pages would be released by vacuuming. For example: - at some points - e.g. after a DELETE, after a while of inactivity or when the user closes the application, check if there is a significant (!) number of pages that would be freed by vacuuming. If yes, do an incremental vacuum. (Do not use icnremental vacuum after every delete! Only if there is significant space to be freed!) - you can put a time limit on the incremental vacuum: if there's a significant number of pages to be freed, run incremental_vacuum(1) repeatedly until there are no more free pages, but also stop after a short time (e.g. 50ms) has passed. This may not clear everything, but also does nto interrupt the user - at a suitable point e.g. when the user is closing the application, and you find there are really very very many pages that could be freed, start an incremental or full vacuum of everything, but allow the user to cancel this. (You might know something similar from closing outlook) - When doing a full vacuum, store the write counter in the database somewhere. if the current write cunter is far ahead the one of the last full vaccum, recommend a full rather than an incremental vacuum to the user. --- We use SQLite as applicaiton file format, and in some common use cases, we add and remove a lot of data. Furthermore, with the previosu solution, people have complained about "databases not getting smaller" (we are talking abotu hundreds of megabytes). So now I am using a combination of this: - when closing the app, and there's more than 1MB or more than 20% to reclaim, i release as many pages as possible for 50 ms. - when after this, there's still a lot to reclaim (50% or 10MB, IIRC), I continue calling incremental_vacuum, but allow the user to cancel the process. - There's a manual maintenance operation for integrity an validity check and other cleanup. This also includes an option for a full VACUUM. When the last full VACUUM was a long time ago (100k or a million writes or so), I pre-select the "full vacuum" option. -- View this message in context: http://sqlite.1065341.n5.nabble.com/auto-vacuum-default-setting-tp70765p70781.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
