#readwise # SQLite VACUUM  ## Metadata - Author: [[sqlite.org]] - Full Title: SQLite VACUUM - URL: https://www.sqlite.org/lang_vacuum.html ## Summary The `VACUUM` command rebuilds an SQLite database file to save disk space. It removes empty space left after data is deleted and reduces fragmentation. `VACUUM` can also securely erase deleted content to prevent recovery. The command requires additional disk space and may fail if there are open transactions on the database. ## Highlights **The `VACUUM` command rebuilds the database file, repacking it into a minimal amount of disk space.** ^ahlp1y There are several reasons an application might do this: - Unless SQLite is running in `auto_vacuum=FULL` mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running `VACUUM` to rebuild the database reclaims this space and reduces the size of the database file. - Frequent inserts, updates, and deletes can cause the database file to become fragmented, where data for a single table or index is scattered around the database file. Running `VACUUM` ensures that each table and index is largely stored contiguously within the database file. In some cases, `VACUUM` may also reduce the number of partially filled pages in the database, reducing the size of the database file further. - When content is deleted from an SQLite database, the content is not usually erased but rather the space used to hold the content is marked as being available for reuse. This can allow deleted content to be recovered by a hacker or by forensic analysis. Running `VACUUM` will clean the database of all traces of deleted content, thus preventing an adversary from recovering deleted content. Using `VACUUM` in this way is an alternative to setting [PRAGMA secure_delete=ON](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_secure_delete). - Normally, the database [page_size](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_page_size) and whether or not the database supports [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) must be configured before the database file is actually created. However, when not in [[SQLite Write-Ahead Logging|write-ahead log]] mode, the [page_size](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_page_size) and/or [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) properties of an existing database may be changed by using the [page_size](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_page_size) and/or [pragma auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) pragmas and then immediately `VACUUM`ing the database. When in [[SQLite Write-Ahead Logging|write-ahead log]] mode, only the [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) support property can be changed using `VACUUM`. ([View Highlight](https://read.readwise.io/read/01jjh5st979c9x9y0jvg2m9wzf)) --- **If the `INTO` clause is included**, then the original database file is unchanged and **a new database is created in a file named by the argument to the `INTO` clause**. The argument is a scalar [[SQLite Language Expressions|expression]], such as a text literal. The new database will contain the same logical content as the original database, fully vacuumed. ^zgss1s The `VACUUM` command with an `INTO` clause is an alternative to the [[Using the SQLite Online Backup API|backup API]] for generating backup copies of a live database. The advantage of using `VACUUM INTO` is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced. Also, all deleted content is purged from the backup, leaving behind no forensic traces. On the other hand, the [[Using the SQLite Online Backup API|backup API]] uses fewer CPU cycles and can be executed incrementally. ([View Highlight](https://read.readwise.io/read/01jjh5zpwrmecwva51exevedt6)) ^bjff9z --- The `VACUUM` command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or [[SQLite Write-Ahead Logging|write-ahead log]] WAL file is used just as it would be for any other database transaction. This means that when `VACUUM`ing a database, as much as twice the size of the original database file is required in free disk space. ([View Highlight](https://read.readwise.io/read/01jjh63fje1m6y15zkj2g0vgt0)) --- The `VACUUM` command may change the [ROWIDs](https://www.sqlite.org/lang_vacuum.html/lang_createtable.html#rowid) of entries in any tables that do not have an explicit [INTEGER PRIMARY KEY](https://www.sqlite.org/lang_vacuum.html/lang_createtable.html#rowid). ([View Highlight](https://read.readwise.io/read/01jjh63t2yckeyd1ga15yag1pa)) --- An alternative to using the `VACUUM` command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) pragma. When [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using `VACUUM`. However, using [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) can lead to extra database file fragmentation. And [auto_vacuum](https://www.sqlite.org/lang_vacuum.html/pragma.html#pragma_auto_vacuum) does not compact partially filled pages of the database as `VACUUM` does. ([View Highlight](https://read.readwise.io/read/01jjh64gzgrqzq0jbhp55wwvnb)) ---