Description
The VACUUM command recovers free space from the
database file and releases it to the filesystem. VACUUM can also defragment
database structures and repack individual database pages.
VACUUM can only be run
against the main database (the database used to create the
database connection). VACUUM
has no effect on in-memory databases.
When data objects (rows, whole tables, indexes, etc.) are deleted or dropped from a database, the file size remains unchanged. Any database pages that are recovered from deleted objects are simply marked as free and available for any future database storage needs. As a result, under normal operations the database file can only grow in size.
Additionally, as rows are inserted and deleted from the database, the tables and indexes can become fragmented. In a dynamic database that normally experiences a high number of inserts, updates, and deletes, it is common for free pages to be scattered all across the database file. If a table or index requires additional pages for more storage, these will first be allocated off the free list. This means the actual parts of the database file that hold a particular table or index may become scattered and mixed all across the database file, lowering seek performance.
Finally, as rows are inserted, updated, and deleted, unused data blocks and other “holes” may appear within the individual database pages. This reduces the number of records that can be stored in a single page, increasing the total number of pages required to hold a table. In effect, this increases the storage overhead for the table, increasing read/write times and decreasing cache performance.
The vacuum process addresses all three of these issues by copying all the data within a database file to a separate, temporary database. This data transfer is done at a fairly high level, dealing with the logical elements of the database. As a result, individual database pages are “repacked,” data objects are defragmented, and free pages are ignored. This optimizes storage space, reduces seek times, and recovers any free space from the database file. Once all this is done, the content of the temporary database file is copied back to the original file.
As the VACUUM command rebuilds the database file from
scratch, VACUUM can also be
used to modify many database-specific configuration parameters.
For example, you can adjust the page size, file format, default
encoding, and a number of other parameters that normally become
fixed once a database file is created. To change something, just
set the default new database pragma values to whatever you wish,
and vacuum the database.
Be warned that this behavior is not always desirable. For example, if you have a database with a nondefault page size or file format, you need to be sure that you explicitly set all the correct pragma values before you vacuum the database. If you fail to do this, the database will be re-created with the default configuration values, rather than the original values. If you work with database files that have any nonstandard parameters, it is best to explicitly set all of these configuration values before you vacuum the database.
Warning
VACUUM will re-create the database using
the current default values. For example, if you have a
database that uses a custom page size and you wish to
maintain that page size, you must issue the appropriate
PRAGMA page_size
command before issuing the VACUUM command. Failing to do so will
result in the database being rebuilt with the default page
size.
Logically, the database contents
should remain unchanged from a VACUUM. The one exception is ROWID values. Columns marked
INTEGER PRIMARY KEY
will be preserved, but unaliased ROWID values may be reset. Also, indexes are
rebuilt from scratch, rather than copied, so VACUUM does the equivalent of a
REINDEX for each index
in the database.
Generally, any reasonably dynamic
database should vacuumed periodically. A good rule of thumb is
to consider a full VACUUM any
time 30% to 40% of the database content changes. It may also be
helpful to VACUUM the
database after a large table or index is dropped.
Be warned that the VACUUM process requires
exclusive access to the database file and can take a significant
amount of time to complete. VACUUM also requires enough disk space to hold
the original file, plus the optimized copy of the database, plus
a rollback journal that can be as large as the original
file.
SQLite also supports an auto-vacuum
mode, which enables portions of the vacuum process to be done
automatically. It has some significant limitations, however, and
even if auto-vacuum is
enabled, it is still advisable to do a full manual VACUUM from time to time.
See Also
auto_vacuum [PRAGMA, Ap F], temp_store [PRAGMA, Ap F], temp_store_directory [PRAGMA, Ap F], REINDEX