SQLite: VACUUM Statement

This SQLite post explains how to use the VACUUM statement with syntax and examples.

Description

As you insert, update, and delete data, the SQLite database file will grow in size and become fragmented. Deletes will cause empty space that is not returned to the operating system. Wherease, inserts and updates will cause data to be scattered within the database file.

The VACUUM statement is used to reclaim storage by removing obsolete data and reducing the size of the database file. It does this by writing the full contents of all tables into a new database file. This process frees all unused space and ensures that all tables and indexes are stored contiguously.

Syntax

The syntax for the VACUUM statement in SQLite is:

VACUUM;

Parameters or Arguments

There are no parameters or arguments for the VACUUM statement.

Note

• You can only VACUUM the main database file and not any attached files.
• You can not run a VACUUM command when there is an open transaction.

Example

In SQLite, the process of vacuuming is a regular activity that must be performed to minimize the size of your database file and ensure that data and indexes are stored contiguously.

Let's look at how to use the VACUUM statement in SQLite.

For example:

VACUUM;

This example would vacuum all tables within the database. The database file would be minimized as all of the unused space is reclaimed back to the operating system.