SQLite - VACUUM

As INSERT, UPDATE, or DELETE statements are used, the SQLite database file grows in size and become fragmented. DELETE statement causes empty space that is not returned to the operating system. Whereas, INSERT and UPDATE statement causes data to be scattered within the database file.

The VACUUM command is used to reclaim storage by removing obsolete data and reducing the size of the database file. It does this by writing the full contents of all tables into a new database file. This process frees all unused space and ensures that all tables and indexes are stored contiguously.

VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.

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

Manual VACUUM

The syntax for issuing a VACUUM command for the whole database from command prompt is as follows:

$sqlite3 database_name 'VACUUM;'

To run VACUUM command from SQLite prompt, the following syntax can be used:

sqlite> VACUUM;

To run VACUUM command on a particular table, the syntax will be:

sqlite> VACUUM table_name;

Example

In SQLite, the process of vacuuming is a regular activity that must be performed to minimize the size of the database file and ensure that data and indexes are stored contiguously.

The command below will vacuum all tables within the database. The database file would be minimized as all of the unused space is reclaimed back to the operating system.

sqlite> VACUUM;

AUTO_VACUUM

AUTO_VACUUM is an optional feature that automatically vacuums the database to minimize the size of the database file. By default, the AUTO_VACUUM feature is disabled.

To view the current mode for the AUTO_VACUUM process, the following PRAGMA statement can be used:

PRAGMA [database.]auto_vacuum;

OR

$sqlite3 database_name 'PRAGMA auto_vacuum;'

This statement above will return the mode for the AUTO_VACUUM process. See the table below to learn about AUTO_VACUUM modes.

To change the current mode of the AUTO_VACUUM process, the following PRAGMA statement can be used:

PRAGMA [database.]auto_vacuum = mode;

The mode determines how the AUTO_VACUUM process will behave. It can be one of the following values:

Example

The command below can be used to enable AUTO_VACUUM process to handle all vacuuming automatically.

PRAGMA auto_vacuum = 1;