May 16, 2021 SQLite
The VACUUM command copies the contents of the primary database to a temporary database file, then emptys the primary database and reloads the original database file from the replica. This eliminates idle pages, arranges the data in the table as continuous, 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 applies only to the primary database, and it is not possible to use the VACUUM command for attached database files.
If there is an active transaction, the VACUUM command fails. T he VACUUM command is any operation used for a 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.
Here is the syntax for issuing VACUUM commands to the entire database in the command prompt:
$sqlite3 database_name "VACUUM;"
You can also run VACUUM in the SQLite prompt, as follows:
sqlite> VACUUM;
You can also run VACUUM on a specific table, as follows:
sqlite> VACUUM table_name;
SQLite's Auto-VACUUM is not much like VACUUM, it simply moves idle pages to the end of the database, reducing the database size. B y doing so, it can significantly fragment the database, while VACUUM is de-fragmented. So Auto-VACUUM only makes the database smaller.
In the SQLite prompt, you can enable/disable SQLite's Auto-VACUUM by compiling the following:
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 check the auto-vacuum settings by running the following command from the command prompt:
$sqlite3 database_name "PRAGMA auto_vacuum;"