SQLite | Database | Reclaiming Free Space (VACUUM)

When data is repeatedly added to and deleted from a SQLite database, the file size can become large compared with the amount of data stored. This page explains how to reclaim unused space by using the VACUUM statement.

Using VACUUM

When you create a SQLite database, one file is created, and tables and stored data are saved in that file. As you add data to tables, the database file gradually grows. However, even if you delete data from a table, the database file size does not immediately become smaller. This is because the space used in the file is not removed immediately, but is kept so it can be reused when new data is added.

Normally you do not need to worry about this, but if the file size is a concern, you can run the VACUUM statement to reclaim unused space and reduce the file size.

Run the VACUUM statement as follows.

VACUUM;

When you run the VACUUM statement, SQLite temporarily moves the database contents to a temporary database and then moves them back. This removes free space and rewrites the data sequentially.

One point to note is that VACUUM applies only to the main database. Attached databases are not processed by VACUUM. Also, for tables that do not have a column set as INTEGER PRIMARY KEY, the ROWID assigned to stored data may change.

Practice

Now try it in practice. Suppose there is a sample.sqlite3 file whose database file size is 16,384 bytes.

$ ls -al
total 5048
drwxr-xr-x@ 7 kimkc  staff      238 10 19 23:20 .
drwxr-xr-x  9 kimkc  staff      306 10 17 23:49 ..
drwxr-xr-x  6 kimkc  staff      204 10 19 23:20 bak
-rw-r--r--  1 kimkc  staff    16384 10 19 23:20 sample.sqlite3
-rwxr-xr-x@ 1 kimkc  staff   691768 10 11 18:31 sqldiff
-rwxr-xr-x@ 1 kimkc  staff  1152260 10 11 18:32 sqlite3
-rwxr-xr-x@ 1 kimkc  staff   719796 10 11 18:31 sqlite3_analyzer

Connect to the database and delete one of the tables in it.

$ sqlite3 sample.sqlite3 
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> .tables
select    user      username
sqlite> drop table user;
sqlite> 

After deleting the table, check the database file size. It is still 16,384 bytes, the same as before the deletion.

$ ls -al
total 5048
drwxr-xr-x@ 7 kimkc  staff      238 10 19 23:24 .
drwxr-xr-x  9 kimkc  staff      306 10 17 23:49 ..
drwxr-xr-x  6 kimkc  staff      204 10 19 23:20 bak
-rw-r--r--  1 kimkc  staff    16384 10 19 23:24 sample.sqlite3
-rwxr-xr-x@ 1 kimkc  staff   691768 10 11 18:31 sqldiff
-rwxr-xr-x@ 1 kimkc  staff  1152260 10 11 18:32 sqlite3
-rwxr-xr-x@ 1 kimkc  staff   719796 10 11 18:31 sqlite3_analyzer

In this way, deleting a table or data does not immediately reduce the database file size. Now connect to the database again and run the VACUUM statement.

$ sqlite3 sample.sqlite3 
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> vacuum;
sqlite> 

After running the VACUUM statement, check the database file size. You can see that the file size has been reduced to 12,288 bytes.

$ ls -al
total 5040
drwxr-xr-x@ 7 kimkc  staff      238 10 19 23:29 .
drwxr-xr-x  9 kimkc  staff      306 10 17 23:49 ..
drwxr-xr-x  6 kimkc  staff      204 10 19 23:20 bak
-rw-r--r--  1 kimkc  staff    12288 10 19 23:29 sample.sqlite3
-rwxr-xr-x@ 1 kimkc  staff   691768 10 11 18:31 sqldiff
-rwxr-xr-x@ 1 kimkc  staff  1152260 10 11 18:32 sqlite3
-rwxr-xr-x@ 1 kimkc  staff   719796 10 11 18:31 sqlite3_analyzer
kimkcui-MacBook-Pro:sqlite-tools-osx-x86-3300100 kimkc$ 

Running VACUUM reclaims unused space and reduces the file size. If the file size is a concern, run VACUUM.