SQLite Vacuum

Here we will learn what is vacuum command in SQLite and how to use SQLite Vacuum statement to clear unused memory space from the database to improve performance with example.

SQLite Vacuum

In SQLite when we perform operations like INSERT, UPDATE, and DELETE on database tables, the SQLite will create unused data blocks within the individual database pages. So when we try to insert more records in the table the SQLite will create more pages to hold table data then automatically the size of the database will increase and take more time to perform read or write operations on database tables and the cache performance will get reduced.

Generally in SQLite when we drop database objects like TABLES, TRIGGERS, VIEWS, functions, etc. it will free the space but reserve that space for the future. So as a result, the size of the database will not get changed and the size of databases will grow for further if we perform any other operations.

In SQLite when we deal with really large databases that contains multiple tables and data the database file size becomes too large when we are trying to perform INSERT, UPDATE and DELETE operations repeatedly, tables, and the associated indexes with table become fragmented. So it will take more space for storing data.

So to solve all the above stated problems SQLite offers command called VACUUM. The SQLite VACUUM command will remove the obsolete data by defragmenting the database structure. It will rebuild the database content by rewriting all the tables content into a new database file. This process will free all unused space and ensure that all the tables and indexes are stored contiguously.

The VACUUM command will not change the content of database and reset the rowid values in case if we use any unaliased rowid. If we use INTEGER PRIMARY KEY column, the VACUUM does not change values of that column.

Its good practice to run VACUUM command periodically especially when we delete a large table or index to minimize the database file size by removing unused space.

Here, we need to remember that the SQLite VACUUM will only work with main database file and not on any attached files & VACUUM will not work if there are any open transactions.

Syntax of SQLite VACUUM

Following is the syntax of using the SQLite VACUUM command.

The above SQLite command will run on all the tables of database to free or reclaim unused memory space.

Here, we need to make sure that no open transactions when we perform VACUUM operations because VACUUM will fail in case if any transaction is happing on that database.

Example of SQLite VACUUM

Following is the example of using the VACUUM command in SQLite.

The above statement will minimize database by freeing unused space of all the tables in the database.

SQLite Auto_VACUUM

Generally, in SQLite we need to run VACUUM command manually to free unused memory space. In case if you want to run VACUUM command automatically in SQLite we have a feature called auto_vacuum which will run automatically to free unused memory space of database files.

By default in the SQLite auto_vacuum feature is disabled we need to enable it manually. In SQLite, we have three modes: NONE (the default), FULL, and INCREMENTAL to enable auto_vacuum based on our requirements.

FULL – If we set this it property means after each transaction SQLite moves freed pages to end of the file and truncates them. 

NONE – It is used to disable the auto_vacuum mode and its default mode.

INCREMENTAL – In this mode, the reference data is maintained but free pages are not swapped or released.

The following statement enables full auto-vacuum mode.

To enable an incremental vacuum, you can use the following statement.

The following statement disables auto-vacuum mode.

This is how we can use VACUUM in SQLite to remove unused memory objects in database based on our requirements.