SQLite Vacuum

  Total Views : 403
  Comments : 0
  Keywords : sqlite vacuum to free unused space of database with example, how to execute sqlite vacuum command on database with example, sqlite vacuum and auto_vacuum with examples

Here we will learn what is vacuum command in SQLite and how to use SQLite Vacuum statement to clear unused memory space from database to improve performance with example.

SQLite Vacuum

In SQLite when we perform operations like INSERTUPDATE and DELETE on database tables, the SQLite will create unused data blocks within the individual database pages. So when we try to insert more records in table the SQLite will create more number of pages to hold table data then automatically the size of database will increase and take more time to perform read or write operations on database tables and the cache performance will get reduced.

 

Generally in SQLite when we drop database objects like TABLES, TRIGGERS, VIEWS, functions, etc. it will free the space but reserve that space for future. So as a result, the size of database will not get changed and the size of databases will grow for further if we perform any other operations.

 

In SQLite when we deal with really large databases which contains multiple tables and data the database file size becomes too large when we are trying to perform INSERT, UPDATE and DELETE operations repeatedly, tables and the associated indexes with table becomes fragmented. So it will take more space for storing data.

 

So to solve all the above stated problems SQLite offers command called VACUUM. The SQLite VACUUM command will remove the obsolete data by defragmenting the database structure. It will rebuild the database content by rewriting all the tables content into a new database file. This process will free all unused space and ensure that all the tables and indexes are stored contiguously.

 

The VACUUM command will not change the content of database and reset the rowid values in case if we use any unaliased rowid. If we use INTEGER PRIMARY KEY column, the VACUUM does not change values of that column.

 

Its good practice to run VACUUM command periodically especially when we delete large table or index to minimize the database file size by removing unused space.

 

Here, we need to remember that the SQLite VACUUM will only work with main database file and not on any attached files & VACUUM will not work if there are any open transactions.

Syntax of SQLite VACUUM

Following is the syntax of using SQLite VAUUM command.

 

VACUUM;

The above SQLite command will run on all the tables of database to free or reclaim unused memory space.

 

Here, we need to make sure that no open transactions when we perform VACUUM operations because VACUUM will fail in case if any transaction is happing on that database.

Example of SQLite VACUUM

Following is the example of using VACUUM command in SQLite.

 

BEGIN TRANSACTION

 

VACUUM

 

END TRANSACTION

The above statement will minimize database by freeing unused space of all the tables in database.

SQLite Auto_VACUUM

Generally, in SQLite we need to run VACUUM command manually to free unused memory space. In case if you want to run VACUUM command automatically in SQLite we have feature called auto_vacuum which will run automatically to free unused memory space of database files.

 

By default in SQLite auto_vacuum feature is disabled we need to enable it manually. In SQLite we have three modes: NONE (the default), FULL and INCREMENTAL to enable auto_vacuum based on our requirements.

 

FULL – If we set this it property means after each transaction SQLite moves freed pages to end of the file and truncates them. 

 

NONE – It is used to disable the auto_vacuum mode and its default mode.

 

INCREMENTAL – In this mode, the reference data is maintained but free pages are not swapped or released.

 

The following statement enables full auto-vacuum mode.

 

PRAGMA auto_vacuum FULL;

To enable incremental vacuum, you can use the following statement.

 

PRAGMA auto_vacuum INCREMENTAL;

The following statement disables auto-vacuum mode.

 

PRAGMA auto_vacuum NONE;

This is how we can use VACUUM in SQLite to remove unused memory objects in database based on our requirements.

 

Note: The auto_vacuum will reduce the size of database file by freeing unused memory space but it will not perform any defragmentation for tables and indexes. Still we need to run VACUUM statement in case if we want to defragment database file.
 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User