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 space and processing time, but it is reasonably small.
Swapping free pages and updating
references also consumes processing time, and in FULL mode this is done at the
end of every transaction. In INCREMENTAL mode, the reference data is
maintained, but free pages are not swapped or released—they are
simply put on the free list. The pages can be recovered using
the incremental_vacuum
pragma. This is much faster than a full VACUUM, and allows for partial recovery of
space on demand. It can also be done without any additional free
space.
The mode can be changed between
FULL and INCREMENTAL at any time. If the
database is in INCREMENTAL
mode and is switched to FULL
mode, any pages on the free list will automatically be
recovered. Because the FULL
and INCREMENTAL modes require
the extra data that NONE does
not maintain, it is only possible to move from NONE to FULL or INCREMENTAL before the database is initialized.
Once the database has been initialized, the only way to move
away from NONE is to set the
pragma and do a VACUUM.
Similarly, the only way to move from FULL or INCREMENTAL to NONE is with a VACUUM. In this case, the VACUUM is required even if the
database is still uninitialized.
Auto-vacuum has some significant
limitations. Although auto-vacuum is capable of releasing free
pages, it does so by swapping them with active pages. This can
lead to higher levels of fragmentation within the database.
Unlike the traditional VACUUM
command, auto-vacuum makes
no attempt to defragment the database, nor does it repack
records into the individual pages. This can lead to inefficient
use of space and performance degradation.
Because of these limitations, it is recommended any database with a moderate transaction rate is occasionally vacuumed, even if auto-vacuum is enabled.