SQLite table disk usage
SqliteSqlite Problem Overview
How can I find out the disk usage of a single table inside a SQLite database without copying it in a new empty database?
Sqlite Solutions
Solution 1 - Sqlite
You can use sqlite3_analyzer from https://www.sqlite.org/download.html.
It's a really cool tool. It shows you the number of pages used by each table with and without indexes (each page, by default, is 1024 bytes).
This is a sample sqlite3_analyzer output for the Northwind database:
*** Page counts for all tables with their indices ********************
EMPLOYEES............................. 200 34.4%
ORDERS................................ 152 26.2%
CATEGORIES............................ 90 15.5%
ORDER DETAILS......................... 81 13.9%
CUSTOMERS............................. 17 2.9%
SQLITE_MASTER......................... 11 1.9%
PRODUCTS.............................. 7 1.2%
SUPPLIERS............................. 7 1.2%
TERRITORIES........................... 6 1.0%
CUSTOMERCUSTOMERDEMO.................. 2 0.34%
CUSTOMERDEMOGRAPHICS.................. 2 0.34%
EMPLOYEETERRITORIES................... 2 0.34%
REGION................................ 2 0.34%
SHIPPERS.............................. 2 0.34%
It also generates SQL statements which can be used to create a database with usage statistics, which you can then analyze.
Solution 2 - Sqlite
If you are on linux or OSX, or otherwise have the unix utilities awk (and optionally, sort) available, you can do the following to get counts and estimated size via dump analysis:
# substitute '.dump' for '.dump mytable' if you want to limit to specific table
sqlite3 db.sqlite3 '.dump' | awk -f sqlite3_size.awk
which returns:
table count est. size
my_biggest_table 1090 60733958
my_table2 26919 7796902
my_table3 10390 2732068
and uses awk script:
/INSERT INTO/ { # parse INSERT commands
split($0, values, "VALUES"); # extract everything after VALUES
split(values[1], name, "INSERT INTO"); # get tablename
tablename = name[2]; #
gsub(/[\047\042]/, "", tablename); # remove single and double quotes from name
gsub(/[\047,]/, "", values[2]); # remove single-quotes and commas
sizes[tablename] += length(values[2]) - 3; # subtract 3 for parens and semicolon
counts[tablename] += 1;
}
END {
print "table\tcount\test. size"
for(k in sizes) {
# print and sort in descending order:
print k "\t" counts[k] "\t" sizes[k] | "sort -k3 -n -r";
# or, if you don't have the sort command:
print k "\t" counts[k] "\t" sizes[k];
}
}
The estimated size is based on the string length of the "INSERT INTO" command, and so is not going to equal the actual size on disk, but for me, count plus the estimated size is more useful than other alternatives such as page count.
Solution 3 - Sqlite
I realize that this answer totally violates the spirit of the question, but it does get you the size without copying the file...
$ ls -lh db.sqlite
-rw-r--r-- 1 dude bros 44M Jan 11 18:44 db.sqlite
$ sqlite3 db.sqlite
sqlite> drop table my_table;
sqlite> vacuum;
sqlite> ^D
$ ls -lh db.sqlite
-rw-r--r-- 1 dude bros 23M Jan 11 18:44 db.sqlite
Solution 4 - Sqlite
It is possible to get details about all the pages used by each table or index from the dbstat table, and it is also possible to aggregate that to get the disk usage of each table or index.
For example, it is possible get the 10 tables using more disk space like this:
sqlite> select name, sum(pgsize) as size from dbstat group by name order by size desc limit 10;
Based on https://www.sqlite.org/dbstat.html
Solution 5 - Sqlite
I ran into issues with the other answers here (namely sqlite_analyzer not working on Linux). 'Ended up creating the following Bash function to (temporarily) write out each table to disk as a way of assessing the on-disk size. Technically this is copying the db, which is not in the spirit of OP's question, but it gave me the information I was after.
function sqlite_size() {
TMPFILE="/tmp/__sqlite_size_tmp"
DB=$1
IFS=" " TABLES=`sqlite3 $DB .tables`
for i in $TABLES; do
\rm -f "$TMPFILE"
sqlite3 $DB ".dump $i" | sqlite3 $TMPFILE
echo $i `cat $TMPFILE | wc -c`
\rm -f "$TMPFILE"
done
}
Example:
$ sqlite_size sidekick.sqlite
SequelizeMeta 12288
events 16384
histograms 20480
programs 20480