Small. Fast. Reliable.
Choose any three.

SQL As Understood By SQLite

[Top]

VACUUM

vacuum-stmt:

syntax diagram vacuum-stmt

The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space. There are several reasons an application might do this:

By default, VACUUM only works only on the main database. Attached databases can be vacuumed by appending the appropriate schema-name to the VACUUM statement.

Compatibility Warning: The ability to vacuum attached databases was added in version 3.15.0 (2016-10-14). Prior to that, a schema-name added to the VACUUM statement would be silently ignored and the "main" schema would be vacuumed.

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.

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.