SQLite Vacuum

The VACUUM command copies the contents of the main database into a temporary database file, then clears the main database and reloads the original database file from the copy. This eliminates free pages, arranges the data in tables into contiguous order, and also cleans up the database file structure.

If the table does not have an explicit integer primary key (INTEGER PRIMARY KEY), the VACUUM command may change the row IDs (ROWID) of entries in the table. The VACUUM command only applies to the main database; it is impossible to use the VACUUM command on attached database files.

The VACUUM command will fail if there is an active transaction. The VACUUM command is a no-op for in-memory databases. Since the VACUUM command recreates the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.

Manual VACUUM

The following is the syntax for issuing the VACUUM command on the entire database at the command prompt:

$sqlite3 database_name "VACUUM;"

You can also run VACUUM in the SQLite prompt, as shown below:

sqlite> VACUUM;

You can also run VACUUM on a specific table, as shown below:

sqlite> VACUUM table_name;

Automatic VACUUM (Auto-VACUUM)

SQLite's Auto-VACUUM is not quite the same as VACUUM; it only moves free pages to the end of the database, thereby reducing the database size. By doing so, it can significantly fragment the database, whereas VACUUM is defragmenting. So Auto-VACUUM only makes the database smaller.

In the SQLite prompt, you can enable/disable SQLite's Auto-VACUUM via the following pragma:

sqlite> PRAGMA auto_vacuum = NONE;  -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL;  -- 1 means enable incremental vacuum
sqlite> PRAGMA auto_vacuum = FULL;  -- 2 means enable full auto vacuum

You can run the following command from the command prompt to check the auto-vacuum setting:

$sqlite3 database_name "PRAGMA auto_vacuum;"
Other Extensions