change sqlite file size after "DELETE FROM table"

Question

I am working with sqlite3 file.

First, I entered relatively big database, file size was about 100 mb.

Than I made

$db->exec("DELETE FROM table");

and entered just a small part of that database. But file size remained 100 mb.

What should you do to change sqlite file size when deleting it's content?

Answer 1

The command you are looking for is vacuum. There is also a pragma to turn auto-vacuuming on.

From the documentation:

When an object (table, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

Answer 2

You can do this

$db->exec("DELETE FROM table");
$db->exec("vacuum");

and the file size will be changed.

Answer 3

Cleaning Databases SQLite has two commands designed for cleaning—reindex and vacuum.

reindex is used to rebuild indexes. It has two forms:

reindex collation_name;
reindex table_name|index_name;

vacuum has the form:

VACUUM;