What's happening with unusually large geopackage file size?

Question

I have been working with a Geopackage in QGIS and R for a few weeks, storing ~ 12 layers of various geometry types.

Needing to email it to a colleague, I discovered the size of the file had grown to > 230MB

l *.gpkg
-rw-r--r--  1 user  staff   236M 24 Nov 12:37 AerialSurvey.gpkg

I was sure that there was nowhere near that amount of data in the system - so copied everything to a new geopackage:

ogr2ogr -f GPKG AerialSurvey-2.gpkg AerialSurvey.gpkg

The new copy is 1/30 the size!

l -h *.gpkg
-rw-r--r--  1 user  staff   236M 24 Nov 12:37 AerialSurvey.gpkg
-rw-r--r--  1 user  staff   6.7M 25 Nov 10:13 AerialSurvey-2.gpkg

My workflow involves analysis and re-saving of layers from time to time, from both QGIS and R.

Obviously this increase in file size is quick to fix with ogr2ogr, but why does this happen and how can it be prevented in future?

Answer 1

SQLite database is stored into a single file and when user does updates/deletes it is much faster just to mark the old data as removed. Nothing gets physically removed from the data file and the size of it grows as you have noticed until the database file is compressed.

Copying GeoPackage into a new GeoPackage with ogr2ogr is a good option but there is a native SQLite function VACUUM for that purpose https://www.sqlite.org/lang_vacuum.html. It is also possible to run VACUUM from QGIS or with GDAL as documented in https://gdal.org/drivers/vector/gpkg.html. Documentation does not show a usage example but the syntax is simple:

ogrinfo -sql "VACUUM" my_geopackage.gpkg

Vacuum is actually also writing all data into a new SQLite database which means that it is not any faster than making a new copy with ogr2ogr. The advantage in using VACUUM is that you do not need to care about deleting and renaming the files.