SQLite VACUUM

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. SQLite works on various platforms like Windows, Mac, Linux, iOS and Android. This makes it a popular choice for developers to develop applications for several platforms or Operating Systems.

As the name itself suggests, the VACUUM command of SQLite is especially used to clean the extra space that is either left after the deletion of tables or schemas from a Database, or unused data blocks that are left behind due to the use of commands like INSERT, UPDATE or DELETE. They decrease the space of each Page of the database.

In this article, we will see how we can use the VACUUM command to clean up extra space and increase the performance of our SQLite queries.

VACUUM Command in SQLite

• Whenever Database objects like Table, INDEX, Schema, TRIGGER, or reloads. VIEWS get dropped/deleted from a certain Database, the space cleared due to the removal of them is marked as “Cleared” or “Empty”, but SQLite keeps that “Empty” space reserved for any future use. When the user creates any other table or anything else it occupies some extra space, even though there is an “Empty” space available. Due to this, the size of the Database keeps on increasing, The VACUUM command can be used to clean up that “empty” space so that the size of the Database doesn’t keep increasing and no empty spaces are kept.
• The VACUUM command of SQLite copies the content of the Database to a separate database, this content only holds the objects which are present actively in the database. It avoids the free spaces and reloads the individual database pages. Then again copies the content of that temporary database and overwrites the existing database so that no extra space is, kept behind. The main database gets rewritten with the “Space Free” contents.
• The VACUUM commands don’t change/update any database/values apart from the Row_ID, but it rebuilds every content of the Database such as the Table, INDEX, Triggers, etc, to remove any unused space associated with them. It is always recommended to use the VACUUM command frequently to keep the Database clutter-free and increase the data processing and retrieval performance of the database.

Syntax of the SQLite VACUUM command:

VACUUM;

It is to be noted that no SQLite command is currently executing or any transactions are kept as open. Otherwise the VACUUM will not work. There are 3 variations of the SQLite VACUUM command.

• FULL
• INCREMENTAL
• NONE

How to Run the SQLite VACUUM Command

1. FULL VACUUM

The FULL VACUUM command is a deep cleaning process which needed to be done manually by the developer or user. If no user is doing it manually, SQLite also offers an Auto-VACUUM process in which the FULL VACUUM will be done automatically, but the user will have no control over it. The FULL VACUUM does a deep cleaning over the entire database, and it takes much more time and required much more resources as compared to the other VACUUMs. The entire database should in idle state during the entire process and no changes can be done on the database. It removes all the unused and unwanted blank spaces from the database by copying the entire content to a temporary location and re-creating the INDEX, VIEWS etc from scratch.

The command to enable FULL AUTO VACUUM is given below.

Query:

PRAGMA auto_vacuum = FULL;

Explanation: As we can see in the syntax, we will use the PRAGMA command to set the auto_vacuum to FULL. We have to use the statement auto_vacuum and assign it’s value to FULL.

2. INCREMENTAL VACUUM

INCREMENTAL VACUUM is a lightweight kind of VACUUM which doesn’t do deep cleaning like FULL VACUUM. Unlike FULL VACUUM, INCREMENTAL VACUUM can work in the background, it is like a regular and automatic cleaning process which happens periodically in the background. During the process, if the user wants, they can make any kind of change to the database during the execution of the VACUUM.

Syntax:

 PRAGMA auto_vacuum = INCREMENTAL;

Explanation: As we can see in the syntax, we will use the PRAGMA command to set the auto_vacuum to INCREMENTAL. Here also, the statement auto_vacuum is common, but here the value provided is INCREMENTAL, signifying the INCREMENTAL type of Auto-Vacuum.

3. NONE

This is the command used to stop AUTO VACUUM. This statement is used to STOP the both INCREMENTAL and FULL Auto-VACUUM over a Database. Below is the syntax of it.

PRAGMA auto_vacuum = NONE;

Explanation: In this statement, we are using the PRAGMA command with the same auto_vacuum, but the value assigned to it is NONE, signifying that there is no auto–vacuum turned on.

VACUUM with INTO Clause

The INTO clause with VACUUM is used to provide an user selected file as the temporary database in which all the contents of the original Database will be copied and stored, but here, all the contents will be “vacuumed“, meaning that every blank pages or clutters will be cleared and only the contents which are present in the database will be copied.

The main catch about VACUUM INTO is that, the filename or the file path passes as the argument of INTO clause, should either be non-existent (SQLite will create the file and store the content) or it must be an empty file. If any of the criteria doesn’t satisfy, the statement will result to an error.

Syntax:

VACUUM <database_schema_name> INTO <path_with_filename_extension>;

The generic syntax is pretty simple, here we will use the VACUUM command and then pass the name of the Database schema which we want to copy and save in a temporary location. Then we will use the INTO command and pass the filename or the full path of the file alongside it’s name and extension, where VACUUM command will temporarily store the contents of the Database.

Generally, in case of SQLite, all the “schemas” are being stored in the “main”, so VACUUM works with only the main schema name, as of now, SQLite doesn’t support the VACUUM command with a particular table name present in the database.

Example of VACUUM with INTO Clause

We will VACUUM the main schema of our database into a file named “mydb_backup.db” using the following command.

Query:

VACUUM main INTO 'mydb_backup.db';

If the file doesn’t exists, then SQLite will create the mydb_backup.db file and dump the main schema into it.

User may now use some advanced SQLite command like ATTACH DATABASE and DETACH DATABASE to transfer all the tables and peripherals into the mydb_backup. That part is out of scope of this Article.

Output:

Explanation: As we can see, the Database named mydb_backup now holds every content of the mydb Database, from which the main schema was vacuumed into it.

Conclusion

We discusses in this article that what is the VACUUM command in SQLite and why do we need it. We also saw the benefits of using the VACUUM command as well as certain instructions to be kept in mind before executing the VACUUM command. We also saw the types of the VACUUM, What and how we can use the Auto-Vacuum in SQLite, alongside their syntax. Use of the VACUUM command becomes very much important when the Database has a lots of tables and useless vacant spaces and unnecessarily long pages, which are decreasing the overall processing speed of the entire Database