VACUUM

Clean the main database file

Synopsis:

VACUUM

Description:

The VACUUM command is a QDB extension modeled after a similar command found in PostgreSQL.

When an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This makes the database file larger than it needs to be, but can speed up insertions. In time, insertions and deletions can leave the database file structure fragmented, which slows down disk access to the database contents.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

Note: It is not possible to perform these cleanup actions on attached databases using this SQL command. You can call qdb_vacuum() to clean up the files of the main database and any auto-attached databases.

This command fails if there is an active transaction and has no effect on an in-memory database.

An alternative to using the VACUUM command is to enable auto-vacuum mode, which means the database file is cleaned whenever a transaction that deletes data is committed. You can enable or disable this mode by setting the auto-vacuum flag using the PRAGMA SQL extension:

qdb_statement(&db, "PRAGMA auto_vacuum = 1;"); // on
qdb_statement(&db, "PRAGMA auto_vacuum = 0;"); // off