Basic SQLite Tutorial

Advanced SQLite Tutorial

SQLite Program Interface

SQLite Vacuum

The VACUUM command copies the contents of the main database to a temporary database file, then clears the main database, and reloads the original database file from the copy. This eliminates free pages, arranges the data in the table in a continuous manner, and also 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 entries in the table. The VACUUM command is only applicable to the main database, and it is impossible to use the VACUUM command on auxiliary database files.

If there is an active transaction, the VACUUM command will fail. The VACUUM command is any operation for a memory database. Since the VACUUM command creates a new database file from scratch, it can also be used to modify many database-specific configuration parameters.

Manual Vacuum

The following is a simple syntax for issuing the VACUUM command for the entire database from the command prompt-

$sqlite3　database_name "VACUUM;"

You can 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;

Automatic Vacuum(Auto-VACUUM)

SQLite's Auto-VACUUM is quite different from VACUUM, it simply moves free pages to the end of the database, thereby reducing the size of the database. By doing so, it can significantly fragment the database, while VACUUM is defragmentation. So Auto-VACUUM will only make the database smaller.

In the SQLite prompt, you can enable it by compiling and running the following/Disable SQLite's Auto-VACUUM：

sqlite> PRAGMA auto_vacuum = NONE;　--　0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL;　--　1　means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL;　--　2　means enable incremental vacuum

You can run the following command from the command prompt to check the Vacuum settings-

$sqlite3　database_name "PRAGMA auto_vacuum;"

SQLite Date and Time SQLite Explain