VACUUM — Recover free space and optimize database
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
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 ...