Sqlite VACUUM command is used to clean the main Sqlite database. For this, it copies the content from main database to a temporary database file and then it reloads the original file from the copy. This is done in order to remove the free pages to make more space available in database. The importance of VACUUM command is to conserve disk space for better performance.
There can be different reasons for any application to perform this VACUUM operation. Some of these are mentioned below:
If the Sqlite is not running the “auto_vacuum=FULL” mode, deletion of large amount of data from the main database leaves empty space after deletion. This empty space is nothing but the free database pages. The database file can now have quite larger size than it should have. VACUUM operation rebuilds the databases in such situations and reduces the database file size.
Statements like; INSERTS, UPDATES, and DELETES used repeatedly can cause the Sqlite database file to be uneven with scattered data stored in the database. In such situation also, executing VACUUM command can reduce the unused space and partially filled pages in the database.
The VACUUM command can change the ROWID of entries of tables that do not have explicit INTEGER PRIMARY KEY. It will work only on the main database and not to the attached database file. In case any transaction is active, at that point of time VACUUM operation will not work at all.
The syntax for executing the VACUUM command for complete Sqlite database is mentioned below:
This command can be also run using Sqlite prompt as:
VACUUM command can be also executed on specific table using following syntax:
Auto-VACUUM is not similar in operation to VACUUM. What it does is it only moves the free pages to the end of the database, which ultimately reduces the database size. By performing this task, it fragments the database whereas VACUUM defragments it. Therefore, Auto-VACUUM just keeps the database small.
This Sqlite auto-vacuuming operation can be enabled or disabled. Below mentioned pragmas running at Sqlite prompt can be used for enabling or disabling:
Following command for checking auto-vacuum settings from command prompt can be used:
The below mentioned steps explains how this operation works and how the reduction of database file size can be achieved;