Small. Fast. Reliable.
Choose any three.

[Top]

VACUUM

vacuum-stmt:

syntax diagram vacuum-stmt

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

As of SQLite version 3.1, an alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma. When auto_vacuum is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using VACUUM. However, using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.