Small. Fast. Reliable.
Choose any three.
Choose any three.
The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space. There are several reasons an application might do this:
R-51469-36013:[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. R-60541-63059:[Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file. ]
R-50943-18433:[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. ] R-05791-54928:[Running VACUUM ensures that each table and index is largely stored contiguously within the database file. ] R-52747-41357:[In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further. ]
R-20474-44465:[Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created. ] R-08570-19916:[However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database. ] R-48521-51450:[When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM. ]
R-55119-57913:[By default, VACUUM only works only on the main database. ] R-36598-60500:[Attached databases can be vacuumed by appending the appropriate schema-name to the VACUUM statement. ]
Compatibility Warning: The ability to vacuum attached databases was added in version 3.15.0 (2016-10-14). Prior to that, a schema-name added to the VACUUM statement would be silently ignored and the "main" schema would be vacuumed.
R-27809-56550:[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. ] R-48974-49065:[When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. ] R-13903-46567:[This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space. ]
R-17495-17419:[The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY. ]
R-49563-33883:[A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run. ]
R-55138-13241:[An alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma. ] R-64844-34873:[When 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. ] R-41548-42669:[However, using auto_vacuum can lead to extra database file fragmentation. ] R-49389-26640:[And auto_vacuum does not compact partially filled pages of the database as VACUUM does. ]
]