How to prevent gpkg size increase when I delete attributes?

Question

I have a file, polygons.gpkg that I am using in QGIS. Its original file size is 48MB.

When I do nothing except delete a handful of the attribute table's fields, I expect the file size to decrease. However, it instead increases to 85MB.

After deleting the fields, if I export as a new gpkg (Export > Save Features As), the new file is only 38MB. This result makes sense to me.

Is there a way to "clean up" whatever extra data is getting stored in the original polygons.gpkg so that its size on disk is reduced? That would be a more convenient solution than exporting anew...

Answer 1

** EDIT: You can now do this in the QGIS file browser by right clicking on the .gpkg and selecting vacuum **

You should VACUUM your database to reduce the size of the database after deleting rows/ dropping tables.

You can use the DB Manager core plugin. Open DB Manager and select your GeoPackage connection from the tree on the left. Select the spanner icon and input the SQL command.

Alternatively you can use ogrinfo from the command line:

ogrinfo your_db_file.gpkg -sql "VACUUM"

or sqlite

sqlite3 your_db_file.gpkg 'VACUUM;'

The latter solutions are useful to create a cron job (Linux) or scheduled task (Windows) to VACUUM your database automatic.