Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Truncate Table Sqlite Perl ❯

SQLite Vacuum

The VACUUM command copies the contents of the main database to a temporary database file, then empties the main database and reloads the original database file from the copy. This eliminates free pages, arranges data in tables contiguously, and cleans up the database file structure.

If the table does not have an explicit integer primary key (INTEGER PRIMARY KEY), the VACUUM command may change the row ID (ROWID) of entries in the table. The VACUUM command only applies to the main database; it cannot be used on attached database files.

The VACUUM command will fail if there is an active transaction. It is not applicable 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

Below 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 follows:

sqlite> VACUUM;

You can also run VACUUM on a specific table as follows:

sqlite> VACUUM table_name;

Automatic VACUUM (Auto-VACUUM)

SQLite's Auto-VACUUM is different from VACUUM; it simply 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 defragments it. Therefore, Auto-VACUUM only makes the database smaller.

At the SQLite prompt, you can enable/disable SQLite's Auto-VACUUM by running the following commands:

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 from the command prompt with the following command:

$ sqlite3 database_name "PRAGMA auto_vacuum;"
❮ Sqlite Truncate Table Sqlite Perl ❯