Query that returns the size of a table in a SQLite database

If SQLite is compiled with SQLITE_ENABLE_DBSTAT_VTAB, you can query the dbstat table. This should return the table size (in bytes) of table TABLENAME:

SELECT SUM("pgsize") FROM "dbstat" WHERE name='TABLENAME';

https://www.sqlite.org/dbstat.html

This is what is used by the sqlite3_anazlyer CLI tool, which can also be used for this purpose.

There's no easy way to query the size of a table. So what I ended up doing is coming up with an estimate of the table's size by multiplying the number of rows by an estimate of the row's size. I manually summed the length of the integer columns (4 bytes each), plus the sum of the length of the long columns (8 bytes each), plus an estimate of the average length of the string columns using a query. Something like this:

SELECT COUNT(*) *  -- The number of rows in the table
     ( 24 +        -- The length of all 4 byte int columns
       12 +        -- The length of all 8 byte int columns
       128 )       -- The estimate of the average length of all string columns
FROM MyTable

The only problems with this are that:

• It will overestimate the size of any row with an integer or long column that can be null, and that happens to be null
• It will overestimate or underestimate the length of the string columns.
• It does not take into account the size of the indexes.

This was good enough for our implementation. You might be able to do a better job computing the table's size with a more complicated query that takes nulls & the actual length of the string columns into account.

You can use the sqlite3_analyzer tool for this, which can be downloaded here. Just run it on your database and it will spit out a lot of interesting statistics, in a form that can also be piped straight into another SQLite database:

$ sqlite3_analyzer my_db.sqlite