> On Feb 13, 2017, at 12:13 PM, Richard Hipp <d...@sqlite.org> wrote:
>
> Does anybody really care anymore that a
> database file might have a few dozen pages on its freelist? Or if
> they do care, does anybody lack the temp space sufficient to run a
> real VACUUM?
The issue of vacuuming has confused me for a long time. It’s unclear from the
docs how necessary it is, or when it should be run, and there’s a lot of
conflicting advice online.
A small amount of wasted space isn’t a problem, but without vacuuming the
database file _will never shrink_. ("When auto-vacuum is disabled and data is
deleted data from a database, the database file remains the same size.”[1])
This could lead to large amounts of waste. (We have had developers complain to
us that our library doesn’t reduce its disk usage after they delete data
through its API.) It also means that if a user deliberately tries to free up
storage by deleting app data, they won’t see any improvement, which can be
frustrating.
Running a full VACUUM is problematic for a mobile app because
* It can take quite a long time if the database is large or if I/O bandwidth is
low.
* It requires potentially a lot of storage space, at a time when it’s likely
that free space is low.
* IIRC it blocks access to the database file by all handles while it’s running,
so it can’t be done while the database is otherwise in use, only when the app
is quitting or being backgrounded.
* If the process is killed during this activity [as is entirely possible on
iOS, because the OS will terminate processes that are unresponsive or that keep
doing stuff too long after they’ve been backgrounded] it will leave large
amounts of wasted storage behind until it starts again, and all the incremental
progress of the vacuum is lost.
The strategy I’m using in my current codebase is to enable incremental
auto_vacuum, and then before closing the database check the free page count. If
it’s too high, I run an incremental_vacuum. This was suggested by someone’s
blog post that I neglected to bookmark. So far it seems to work well, but this
codebase is still in development and hasn’t been through a lot of stress
testing.
—Jens
[1]: http://www.sqlite.org/pragma.html#pragma_auto_vacuum
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
