How to see the size of individual layers in a geopackage

Question

How can we see the disk size of individual layers that are packed together in a geopackage?

I am a big fan of keeping all layers (and their styles) of a QGIS project inside one geopackage only. Its easier to keep everything together and to share with others.

However, as I sometimes work with many layers, it is important for me to be able to see and analyze the contribution of these layers to the total geopackage filesize. This allows me to quickly see which layers might take up an excessive amount of space.

When each layer is stored separately as a shapefile or geopackage file this its easy to just see the file size.

So far I have not found any method to see the size of the individual layers. The only method I have is exporting each layer to a separate geopackage file, which is time consuming for many layers, and defeats the purpose of keeping them together in the first place.

I am not an QGIS expert, but checking the size should be a relatively standard and easy operation I suppose. According to chat GPT it cannot be done inside QGIS... Please prove the robot wrong!

Answer 1

Use the SQLite utility "sqlite3_analyzer https://www.sqlite.org/sqlanalyze.html

Usage example and an excerpt of the very comprehensive report showing the data for one data layer. Notice that it is possible to save the report into a database and then use SQL for creating reports.

The output is also valid SQL. Most of the report text is contained within a header comment, with various SQL statements that create and initialize a database at the end of the report. The constructed database contains the raw data from which the report was extracted. Hence the original report can be read into an instance of the command-line shell and then the raw data can be queried to dig deeper into the space utilization of a particular database file.

sqlite3_analyzer states.gpkg

...

*** Table STATES **************************************************************

Percentage of total database......................  65.5%
Number of entries................................. 49
Bytes of storage consumed......................... 233472
Bytes of payload.................................. 195003      83.5%
Bytes of metadata................................. 894          0.38%
B-tree depth...................................... 2
Average payload per entry......................... 3979.65
Average unused bytes per entry.................... 767.08
Average metadata per entry........................ 18.24
Average fanout.................................... 28.00
Non-sequential pages.............................. 0            0.0%
Maximum payload per entry......................... 13842
Entries that use overflow......................... 25          51.0%
Index pages used.................................. 1
Primary pages used................................ 28
Overflow pages used............................... 28
Total pages used.................................. 57
Unused bytes on index pages....................... 3895        95.1%
Unused bytes on primary pages..................... 32319       28.2%
Unused bytes on overflow pages.................... 1373         1.2%
Unused bytes on all pages......................... 37587       16.1%

Answer 2

This works if your Sqlite has dbstat support built in, which someone in the comments says isn't so for the Windows OSGeo4W. Maybe submit a feature request for it?

Add one layer from your geopackage to your project and duplicate it.

With the duplicate layer selected in the Layers list, hit Ctrl-F to bring up the filter query builder. Normally you'd use this to filter existing layers, for example to only select all points from the layer with X greater than 123 or whatever, but we're going to use this to run the SQL command that gets the size information from the geopackage.

Put this text in the "Provider Specific Filter Expression" box.

select name, sum(pgsize) from dbstat 
   where name NOT LIKE 'rtree_%' 
     and name NOT LIKE 'gpkg_%' 
     and name NOT LIKE 'sqlite_%'
   group by name

Hit "Test" and it should be okay. If this fails then chances are your SQlite doesn't have dbstat support. Oh well. If it works, hit "Ok" and you've now got that duplicated layer with a little "filter" icon in the Layers list. You can uncheck this layer since it really doesn't have any points.

Now open the attribute table of this duplicated layer...

This is the name and sum of "compressed size on disk" reported by SQLite from its dbstat table (which is where the sqlite3_analyze utility gets its numbers from).

Rename the filter layer to "DB sizes" if you want. You can use "Update SQL Layer" on the layers right-click menu to update. I think.

Note if you do have genuinely spatial layers starting with any of the prefixes in the SQL then they'll get filtered out. It should be possible to select only spatial layers by joining with one of the gpkg system tables and filtering but this is 99% good enough for now I think!