Tuesday, March 1, 2016

change sqlite file size after “delete from table” or "drop table tablename"

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.
The VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.
The VACUUM command will fail if there is an active transaction. The VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.


Following is simple syntax to issue a VACUUM command for the whole database from command prompt:
$sqlite3 database_name "VACUUM;"
You can run VACUUM from SQLite prompt as well as follows:
sqlite> VACUUM;
You can also run VACUUM on a particular table as follows:
sqlite> VACUUM table_name;

[root@server data]# du -sh Samsun_data.db

2.3M    Samsun_data.db

[root@server data]# sqlite3 Samsun_data.db "delete from data where ts<'$(date --date="2 days ago" "+%s")'"

[root@server data]# sqlite3 Samsun_data.db "VACUUM;"

[root@server data]# du -sh Samsun_data.db

1.2M    Samsun_data.db


SQLite Auto-VACUUM does not do the same as VACUUM rather it only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. So Auto-VACUUM just keeps the database small.
You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt:
sqlite> PRAGMA auto_vacuum = NONE;  -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL;  -- 1 means enable incremental vacuum
sqlite> PRAGMA auto_vacuum = FULL;  -- 2 means enable full auto vacuum
You can run following command from command prompt to check the auto-vacuum setting:
$sqlite3 database_name "PRAGMA auto_vacuum;"

No comments:

Post a Comment