How to truncate table in SQLite and shrink the database

For my pet projects, I’m using SQLite a lot. The database engine is fast, small and serves well for small amounts of data. What is most important for me – it is easy to use and it only takes a file copy to perform the database backup.

In some of my projects, the database tables are getting bigger and bigger. In the typical engine such as MySQL or Postgres, once the data is not needed anymore I use truncate to remove the data from the table. Of course, if the table is no longer needed, I can delete the whole table. In most cases, however, I only want to get rid of data but leave the empty table in place.

Background

Surprisingly there is no truncate statement in the SQLite. What is the difference between truncate and delete? In the databases such as MySQL, Postgres or Oracle, the delete operation saves all of the removed data to the logs. In case of any issue, the operation can be reverted by the engine. On the other hand, truncate removes the data and in the logs, there is only the information that such an operation took place.

So, if the delete is safer, why would I use truncate? Mostly for the sake of performance – the delete operation takes longer, especially if there is serious amount of data to remove. This is why I’m using truncate to deal with not important data

So, how to truncate in SQLite?

There is no truncate in SQLite. But on the other hand, there is also no operation log as in “big SQL engines”. The deleted data is not saved to the logs. This means that if you want to remove all data from the table use:

DELETE FROM [table_name]

Simple, isn’t it? In the SQLite it will work fast enough and it will do what it should.

My database file is big…

Even if you remove all of your data from the SQLite database, the database file will not change the size. This is because the data is not removed from the disk itself, they are simply marked as removed and the engine is not taking care of them anymore.

If you want the SQLite to free up the drive space by shrinking the database file, here is the command for you:

VACUUM

Yes, a single word. This command will rebuild the database file and repack it to occupy the minimal amount of disk space. As an additional benefit, it will also put together the data which is fragmented in the database file – if you inserted or updated the data in various tables, the data is saved in different places. Vacuum will take care of such fragmented data.