How does PRAGMA optimize or ANALYSE affect concurrency? Is there any
risk that it might leave the database locked for the full duration of
the scan?

On 4 October 2017 at 23:29, Jens Alfke <j...@mooseyard.com> wrote:
>
>
>> 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