> On Oct 4, 2017, at 10:30 AM, Richard Hipp <d...@sqlite.org> wrote: > > The PRAGMA optimize command is our effort to move further in the > direction of a "smart" SQLite that always automatically "does the > right thing" with respect to gathering and using database statistics.
That’s a great move. Along the same lines, it would be nice if SQLite could vacuum once in a while without being asked (like a good housemate ;) What I’m doing now is based on some advice I read in a blog post*: // If this fraction of the database is composed of free pages, vacuum it static const float kVacuumFractionThreshold = 0.25; // If the database has many bytes of free space, vacuum it static const int64_t kVacuumSizeThreshold = 50 * MB; // After creating a new database: exec("PRAGMA auto_vacuum=incremental”); // Just before closing a database: exec("PRAGMA optimize"); int64_t pageCount = intQuery("PRAGMA page_count"); int64_t freePages = intQuery("PRAGMA freelist_count"); if ((pageCount > 0 && (float)freePages / pageCount >= kVacuumFractionThreshold) || (freePages * kPageSize >= kVacuumSizeThreshold)) { exec("PRAGMA incremental_vacuum"); } (To forestall any retorts that “you don’t need to vacuum because SQLite will reuse the free space later”: Yes, you do need to, on a space-constrained device like a phone. Otherwise your app never reclaims any storage back to the OS for use by other apps, and you get customer issues like “I deleted all my old junk from the app but it’s still using 10GB of storage, please help my phone is out of space”…) —Jens * https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users