SQLite Vacuum
The VACUUM command copies the contents of the primary database to a temporary database file, then clears the primary database and reloads the original database file from the copy. This eliminates free pages, arranges the data in the table to be contiguous, and cleans up the database file structure.
If there is no explicit integer primary key (INTEGER PRIMARY KEY) in the table, the VACUUM command may change the row ID (ROWID) of the entry in the table. The VACUUM command only works on the master database, it is not possible to use the VACUUM command on attached database files.
The VACUUM command will fail if there is an active transaction. The VACUUM command is an operation for any in-memory database. Because the VACUUM command re-creates the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.
MANUAL VACUUM
Here is the syntax for issuing the VACUUM command on the entire database from the command prompt:
$sqlite3 database_name "VACUUM;"
You can also run VACUUM from the SQLite prompt , as shown below:
sqlite> VACUUM;
You can also run VACUUM on a specific table, as shown below:
sqlite> VACUUM table_name;
Auto-VACCUM (Auto-VACUUM)
Auto of SQLite -VACUUM is not the same as VACUUM. It just moves free pages to the end of the database, thereby reducing the database size. By doing this, it visibly fragments the database, while VACUUM is anti-fragmentation. So Auto-VACUUM just makes the database smaller.
In the SQLite prompt, you can enable/disable SQLite's Auto-VACUUM by running the following compilation:
sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 1 means enable incremental vacuum sqlite> PRAGMA auto_vacuum = FULL; -- 2 means enable full auto vacuum
You can run the following command from the command prompt to check auto -vacuum settings:
$sqlite3 database_name "PRAGMA auto_vacuum;"
