Name
VACUUM — Recover free space and optimize database
Syntax
Common Usage
VACUUM;
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 ...
Get Using SQLite now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
