> 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 >= 
                        || (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”…)


* https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/
sqlite-users mailing list

Reply via email to