> On Oct 4, 2017, at 10:30 AM, Richard Hipp <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
