- SQLite Tutorial
- SQLite - Home
- SQLite - Overview
- SQLite - Installation
- SQLite - Commands
- SQLite - Syntax
- SQLite - Data Type
- SQLite - CREATE Database
- SQLite - ATTACH Database
- SQLite - DETACH Database
- SQLite - CREATE Table
- SQLite - DROP Table
- SQLite - INSERT Query
- SQLite - SELECT Query
- SQLite - Operators
- SQLite - Expressions
- SQLite - WHERE Clause
- SQLite - AND & OR Clauses
- SQLite - UPDATE Query
- SQLite - DELETE Query
- SQLite - LIKE Clause
- SQLite - GLOB Clause
- SQLite - LIMIT Clause
- SQLite - ORDER By Clause
- SQLite - GROUP By Clause
- SQLite - HAVING Clause
- SQLite - DISTINCT Keyword
- Advanced SQLite
- SQLite - PRAGMA
- SQLite - Constraints
- SQLite - JOINS
- SQLite - UNIONS Clause
- SQLite - NULL Values
- SQLite - ALIAS Syntax
- SQLite - Triggers
- SQLite - Indexes
- SQLite - INDEXED By Clause
- SQLite - ALTER Command
- SQLite - TRUNCATE Command
- SQLite - Views
- SQLite - Transactions
- SQLite - Subqueries
- SQLite - AUTOINCREMENT
- SQLite - Injection
- SQLite - EXPLAIN
- SQLite - VACUUM
- SQLite - Date & Time
- SQLite - Useful Functions
- SQLite Interfaces
- SQLite - C/C++
- SQLite - Java
- SQLite - PHP
- SQLite - Perl
- SQLite - Python
- SQLite Useful Resources
- SQLite - Quick Guide
- SQLite - Useful Resources
- SQLite - Discussion
SQLite - VACUUM
VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.
VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.
VACUUM command will fail if there is an active transaction. VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.
Manual VACUUM
Following is a simple syntax to issue a VACUUM command for the whole database from command prompt −
$sqlite3 database_name "VACUUM;"
You can run VACUUM from SQLite prompt as well as follows −
sqlite> VACUUM;
You can also run VACUUM on a particular table as follows −
sqlite> VACUUM table_name;
Auto-VACCUM
SQLite Auto-VACUUM does not do the same as VACUUM rather 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 while VACUUM ensures defragmentation. Hence, Auto-VACUUM just keeps the database small.
You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt −
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 auto-vacuum setting −
$sqlite3 database_name "PRAGMA auto_vacuum;"