Dear SQLiters,

Vacuuming seems to belong to a different thread, but let me say that it is not 
always warranted. Vacuuming may change/reassign ROWIDs. If you have two 
databases (backup and production?) that used to be linked via such a key, it 
will break.

Roman

________________________________________
From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jens Alfke [j...@mooseyard.com]
Sent: Wednesday, October 04, 2017 6:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?

> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to