YouTip

# SQLite Vacuum The VACUUM command works by copying the contents of the main database into a temporary database file, then clearing the main database and reloading the original database file from the temporary copy. This eliminates free pages, aligns table data to be contiguous, and also cleans up the database file structure. If there is no explicit integer primary key (INTEGER PRIMARY KEY) defined for a table, the VACUUM command may change the row ID (ROWID) of entries in the table. The VACUUM command only applies to the main database; it is not possible to use the VACUUM command on attached database files. If there is an active transaction, the VACUUM command will fail. The VACUUM command is a no-op for in-memory databases. Since the VACUUM command recreates the database file from scratch, it can also be used to modify many database-specific configuration parameters. ## Manual VACUUM The syntax for issuing the VACUUM command on the entire database from the command prompt is: $sqlite3 database_name "VACUUM;" You can also run VACUUM from the SQLite prompt as follows: sqlite> VACUUM; You can also run VACUUM on a specific table as follows: sqlite> VACUUM table_name; ## Auto-VACUUM SQLite's Auto-VACUUM is different from VACUUM; it only moves free pages to the end of the database, thereby reducing the database size. By doing so, it can significantly fragment the database, whereas VACUUM performs defragmentation. Therefore, Auto-VACUUM only makes the database smaller. From the SQLite prompt, you can enable/disable SQLite's Auto-VACUUM by running 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 setting by running the following command from the command prompt: $sqlite3 database_name "PRAGMA auto_vacuum;"