SQLite VACUUM Command for Cleaning/Backing Up
The VACUUM command does not change the content of the database except the rowid values. If you use INTEGER PRIMARY KEY column, the VACUUM does not change the values of that column. However, if you use unaliased rowid, the VACUUM command will reset the rowid values. Besides changing the rowid values, the VACUUM command also builds the index from scratch.
It is a good practice to perform the VACUUM command periodically, especially when you delete large tables or indexes from a database.
It is important to note that the VACCUM command requires storage to hold the original file and also the copy. Also, the VACUUM command requires exclusive access to the database file. In other words, the VACUUM command will not run successfully if the database has a pending SQL statement or an open transaction.
Currently, as of version 3.9.2, you can run the VACUUM command on the main database, not the attached database file.
Even though SQLite enables the auto-vacuum mode that triggers the vacuum process automatically with some limitations, still it is a good practice to run the VACUUM command manually.
How to run the SQLite VACUUM command
The following shows how to run the VACUUM command:
VACUUM;
Make sure that there is no open transaction while you're running the command!
The following statement enables full auto-vacuum mode:
PRAGMA auto_vacuum = FULL;
To enable incremental vacuum, you use the following statement:
PRAGMA auto_vacuum = INCREMENTAL;
The following statement disables auto-vacuum mode:
PRAGMA auto_vacuum = NONE;
VACUUM with an INTO clause
Here is syntax of the VACUUM with INTO clause:
VACUUM schema-name INTO filename;
The VACUUM statement with an INTO clause keeps the original database file unchanged and creates a new database with the file name specified. The new database will contain the same logical content as the original database, but fully vacuumed.
The filename in the INTO clause can be any SQL expression that evaluates to a string. It must be a path to a file that does not exist or to an empty file, or the VACUUM INTO command will result in an error.
The VACUUM command is very useful for generating backup copies of a live database. It is transactional safe, with the generated database being a consistent snapshot of the original database. However, if a unplanned shutdown or power lose interupts the command, the generated database might be corrupted.
The following statement uses the VACUUM INTO command to generate a new database with the file name new_world.db, whose data is copied from of the main schema of the new_world.db database:
VACUUM main INTO '~/Documents/sqlite/db/new_world.db';