Latest web development tutorials

SQLite Vacuum

VACUUM command by copying the contents of the primary database to a temporary database file, and then empty the primary database, and reload the original from the copy of the database file. This eliminates free pages, the data in the table are arranged in a row, and the other will clean up the database file structure.

If the table is not explicit integer primary key (INTEGER PRIMARY KEY), VACUUM command may change the entries in the table row ID (ROWID). VACUUM command is only applicable to the primary database, additional database file is not possible to use the VACUUM command.

If there is an active transaction, VACUUM command will fail. VACUUM command is nothing for a memory database. Since VACUUM command to re-create the database files from scratch, so VACUUM can also be used to modify a number of database-specific configuration parameters.


Here is the syntax for the command prompt issued VACUUM commands the entire database:

$ Sqlite3 database_name "VACUUM;"

You can also run SQLite prompt VACUUM, as follows:

sqlite> VACUUM;

You can also run VACUUM on a particular table, as follows:

sqlite> VACUUM table_name;


SQLite's Auto-VACUUM and VACUUM is not the same, it's just a free page to the end of the database, thereby reducing the size of the database. By doing so, it can significantly to fragment the database, which is the anti-VACUUM fragmented. Auto-VACUUM so will only make the database smaller.

In SQLite prompt, you can use the following compile and run, enable / disable the SQLite Auto-VACUUM:

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

You can run the following command from a command prompt to check the auto-vacuum settings:

$ Sqlite3 database_name "PRAGMA auto_vacuum;"