Small. Fast. Reliable.
Choose any three.
VACUUM

1. Syntax

vacuum-stmt:

VACUUM schema-name INTO filename

2. Description

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 operates 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.

2.1. VACUUM with an INTO clause

If the INTO clause is included, then the original database file is unchanged and a new database is created in a file named by the argument to the INTO clause. The new database will contain the same logical content as the original database, fully vacuumed.

The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database. The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced. Also, all deleted content is purged from the backup, leaving behind no forensic traces. On the other hand, the backup API uses fewer CPU cycles and can be executed incrementally.

The filename in the INTO clause can be an arbitrary SQL expression that evaluates to a string. The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error.

The argument to INTO can be a URI filename if URI filenames are enabled. URL filenames are enabled if any of the following are true:

The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database. However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt. Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.

3. How VACUUM works

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 INTO command works the same way except that it uses the file named on the INTO clause in place of the temporary database and omits the step of copying the vacuumed database back over top of the original database.

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 on the database connection that is attempting to run the VACUUM. Unfinalized SQL statements typically hold a read transaction open, so the VACUUM might fail if there are unfinalized SQL statements on the same connection. VACUUM (but not VACUUM INTO) is a write operation and so if another database connection is holding a lock that prevents writes, then the VACUUM will fail.

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.