SQLite: Auto_Vacuum Process

This SQLite post explains how to use the auto_vacuum process with syntax and examples.

Description

Introduced in SQLite 3.1, auto_vacuum is an optional feature that automatically vacuums the database to minimize the size of the database file. With this feature, you don't have to manually run the VACUUM statement. By default, the auto_vacuum feature is disabled.

CURRENT SETTINGS

To view the current mode for the auto-vacuum process, you can run the following PRAGMA statement:

PRAGMA [database.]auto_vacuum;

This statement will return the mode for the auto-vacuum process. See the table below for an explanation of the different modes.

Update Settings

The syntax to change the mode for the auto_vacuum process in SQLite is:

PRAGMA [database.]auto_vacuum = mode;

Parameters or Arguments

mode

Determines how the auto_vacuum process will behave. It can be one of the following values:

Mode	Explanation
0 or NONE (default)	The auto_vacuum featured is disabled. This is the default configuration in SQLite. With this mode, you must run the VACUUM statement to reduce the size of the database and perform any defragmentation.
1 or FULL	The auto_vacuum feature is enabled and will run automatically. With this mode, the freelist pages are moved to the end of the file and then truncated after every commit. This reduces the size of the database but it does not defragment the database file. To defragment the file, you must run the VACUUM statement.
2 or INCREMENTAL	The auto_vacuum feature is enabled but the vacuum is not performed automatically. With this mode, the database size is not reduced until the freelist pages are removed using incremental_vacuum PRAGMA statement. This mode does not defragment the database file. To defragment the file, you must run the VACUUM statement.

Examples

Next, let's look at examples of how to change the mode for the auto_vacuum feature in SQLite.

Enable Full

You can turn on the auto_vacuum feature so that it automatically reduces the size of the database file.

For example:

PRAGMA main.auto_vacuum = 1;

In this example, the auto_vacuum feature would be enabled and all vacuuming would be handled automatically.

Enable Incremental

You can also enable incremental auto_vacuum in SQLite so that the database file is prepared but the freelist pages must be manually removed from the database file with an additional PRAGMA statement.

For example:

PRAGMA main.auto_vacuum = 2;

In this example, the auto_vacuum feature would be enabled but you would need to run the PRAGMA incremental_vacuum statement to reduce the database file size.

The syntax for PRAGMA incremental_vacuum in SQLite is:

PRAGMA [database.]incremental_vacuum(<strong>N</strong>);

Where N is the maximum number of pages to remove from the freelist.

For example:

PRAGMA main.incremental_vacuum(5);

This PRAGMA statement would remove up to 5 freelist pages from the database file.

Disable

To turn off the auto_vacuum feature in SQLite, you could run the following PRAGMA statement:

PRAGMA main.auto_vacuum = 0;

In this example, the auto_vacuum feature would be disabled.