Summary: in this tutorial, we will explain why you need to use the SQLite VACUUM command and how to use it to optimize the database file.
Why do you need SQLite VACUUM command
First, when you drop database objects such as tables, views, indexes, and triggers or delete data from tables, the database file size remains unchanged. Because SQLite just marks the deleted objects as free and reserves it for the future uses. As a result, the size of the database file always grows in size.
Third, the insert, update, and delete operations create unused data block within individual database pages. It decreases the number of rows that can be stored in a single page. Therefore, it increases the number of pages to hold a table. Because of this, it increases storage overhead for the table, takes more time to read/write, and decreases the cache performance.
SQLite provides the VACUUM command to address all three issues above.
SQLite first copies data within a database file to a temporary database. This operation defragments the database objects, ignores the free spaces, and repacks individual pages. Then, SQLite copies the content of the temporary database file back to the original database file. The original database file is overwritten.
Because the VACUUM command rebuilds the database, you can use it to change some database-specific configuration parameters such as page size, page format, default encoding, etc. To do this, you set new values using pragma and then vacuum the database.
The SQLite VACUUM command
The VACUUM command does not change the content of the database except the rowid values. If you use INTEGER PRIMARY KEY column, the VACUUM does not change values of that column. However, if you use unaliased rowid, the VACUUM command will reset the rowid values. Besides changing the rowid values, the VACUUM command also builds the index from the scratch.
It is a good practice to perform the VACUUM command periodically, especially when you delete the large table or index.
It is important to note that the VACCUM command requires storage for hold the original file and also the copy.
Also, the VACUUM command requires exclusive access to the database file. In other words, the VACUUM command is not successful if there is one active SQL statement or an open transaction.
Currently, as of version 3.9.2, you can run the VACUUM command on the main database, not the attached database file.
Even though SQLite enables the auto-vacuum mode that triggers the vacuum process automatically with some limitations. It is a good practice to run the VACUUM command manually.
How to run the SQLite VACUUM command
To run the VACUUM command, you need to issue it as simple as the following statement:
Make sure that there is no open transaction while you run the command.
The following statement enables full auto-vacuum mode:
PRAGMA auto_vacuum = FULL;
To enable incremental vacuum, you use the following statement:
PRAGMA auto_vacuum = INCREMENTAL;
The following statement disables auto-vacuum mode:
PRAGMA auto_vacuum = NONE;
In this tutorial, you have learned why you need to use the SQLite VACUUM command and how to run it to optimize the database.