Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from 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:

ValuesMeaning
0 or NONEAuto-vacuum is disabled
1 or FULLAuto-vacuum is enabled and fully automatic
2 or INCREMENTALAuto-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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content