What Are Sqlite Vacuum And AUTO Vacuum Commands?
Introduction
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.
Reasons to Perform Sqlite VACUUM Operation
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.
VACUUM Command: Manual & Auto Vacuum
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.
Manual VACUUM
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:
Sqlite Auto-VACCUM
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:
How Sqlite VACUUM Operation Works?
The below mentioned steps explains how this operation works and how the reduction of database file size can be achieved;
- As the VACUUM operation is executed, Sqlite will create an empty database that is stored in the same directory where original file is located.
- Schema of the database is read and applied to the new database creating tables, indices, etc. Few of the PRAGMA settings are also copied to new database.
- Sqlite reads each row of the table serially and writes to the new database.
- Original database is deleted by Sqlite.
- Then the new database is renamed as the original file name.
