Using SQLite

Name

auto_vacuum — Configure automatic vacuum settings

Common Usage

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Description

The auto_vacuum pragma gets or sets the auto-vacuum mode. The mode can be any of the following:

Values	Meaning
0 or NONE	Auto-vacuum is disabled
1 or FULL	Auto-vacuum is enabled and fully automatic
2 or INCREMENTAL	Auto-vacuum is enabled but must be manually activated

The set mode can be either the name or the integer equivalent. The returned value will always be an integer.

By default, databases are created with an auto-vacuum mode of NONE. In this mode, when the contents of a database page are deleted, the page is marked as free and added to the free-page list. This is the only action that is taken, meaning that a database file will never shrink in size unless it is manually vacuumed using the VACUUM command.

Auto-vacuum allows a database file to shrink as data is removed from the database. In FULL auto-vacuum mode, free pages are automatically swapped with an active page at the end of the database file. The file is then truncated to release the unused space. In FULL mode, a database should never have pages on the free list.

The ability to move pages is key to the auto-vacuum system. In order to accomplish this, the database needs to maintain some extra data that allows a page to back-track references. In the event the page needs to be moved, references to the page can also be updated. Keeping all the reference data up to date consumes some storage ...