Optimizing database file usage

By default, when deleting a large amount of data in an SQLite database it leaves behind empty space, causing the database file to be larger than stricly necessary.

This might be an issue with some mobile applications, when the disk space of the mobile device is limited.

Solution

Execute the VACUUM SQL command, to truncate the database file and reduce the disk usage.

According to the application, the VACUUM command can be executed:

when starting the application,
after doing a large db operation (like a synchronization with a central db),
as a manual option that the user can trigger.

Note that SQLite also supports "PRAGMA auto_vacuum", but it appears that it's not as efficient as the VACUUM command, regarding page fragmentation.

Pay attention to the fact that VACUUM needs twice the disk space of the actual database file, because it rebuilds totally the db file.

VACUUM is not Informix SQL syntax, thus you can't write it directly in the BDL code: You must use EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE "VACUUM"