35

I have a SQLite database that contains a number of tables. We are writing a maintenance tool that will remove "stale" data from a table until that table's size is a certain percentage of the total database file or smaller.

I know how to determine the size of the database file -- I do it by executing

PRAGMA PAGE_SIZE;

and

PRAGMA PAGE_COUNT;

in two separate queries and multiplying the two to get the file size in bytes. Yes, I know I can just take the size of the file in bytes, but this is similar to the way I've done it in other databases and I want to stick with it, at least for now.

My problem is I don't know how to get the size of a TABLE. There has to be some way to do this. Can anyone point me in the right direction?

Tony Vitabile
  • 8,298
  • 15
  • 67
  • 123
  • 8
    I don't know of a query, but check out sql_analize tool on http://www.sqlite.org/download.html `./sqlite3_analyze path_to_db/database.sqlite > dbinfo.sql` will give you detail info on each table's size on disk. – NotGaeL Dec 19 '14 at 19:33
  • 3
    (and if it's for cleanup purposes, don't forget to `VACUUM;` after you finish deleting records) – NotGaeL Dec 19 '14 at 19:35
  • 3
    Instead of watching for table size, you can trigger the action based on number of records stored in that particular table. Number of records you can easily get. – NewStackUser Dec 20 '14 at 07:55
  • See the accepted answer to similar/duplicate question http://stackoverflow.com/questions/5900050/sqlite-table-disk-usage which suggests using sqlite3_analyze. – Dirk Bergstrom Jan 12 '17 at 02:59

6 Answers6

39

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.

mbh86
  • 6,078
  • 3
  • 18
  • 31
Christopher K.
  • 1,015
  • 12
  • 18
  • Interesting. Is SQLite compiled with `SQLITE_ENABLE_DBSTAT_VTAB` by default? I believe most folks just use the DLLs that NuGet installs. – Tony Vitabile Oct 06 '19 at 03:24
  • I didn't try NuGet DLLs, but I believe it is normally not compiled this way. I tried PHP's SQLite3 PDO driver and there it is not enabled. – Christopher K. Oct 06 '19 at 14:45
  • 1
    This works with SQLite as installed from the Ubuntu 19.10 repository packages. :-) – tanius Jul 12 '20 at 01:17
  • 1
    This didn't work for me. btw you can check which compile options were used with [`PRAGMA compile_options`](https://www.sqlite.org/pragma.html#pragma_compile_options) and info about default options [here](https://www.sqlite.org/compile.html#defaults), – ashleedawg Nov 01 '20 at 13:11
  • @ashleedawg What's happening for you? Does it produce an error even though `SQLITE_ENABLE_DBSTAT_VTAB` is enabled? Or does it result in wrong results? Have you tried `SELECT * FROM "dbstat"` to check what the table `dbstat` contains? – Christopher K. Nov 01 '20 at 20:08
  • 3
    `SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name;` to get the file size of every table in the db –  Feb 24 '22 at 02:17
  • Works for me on MacOS at command line. Does not work in DataGrip. – narmaps Apr 07 '22 at 17:00
26

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
Thomas
  • 174,939
  • 50
  • 355
  • 478
5

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.

Tony Vitabile
  • 8,298
  • 15
  • 67
  • 123
  • How should that work? I always only get the columns multiplied with 164 (tha is 24+12+128) – rubo77 Aug 21 '16 at 22:20
  • 1
    The COUNT(*) function returns the number of rows that match the criteria. The rest of the numbers are indeed a constant equal to 164. It won't return the actual size of the rows, just an estimate of how much space is occupied by the table. – Tony Vitabile Aug 22 '16 at 19:03
  • Can't you select the field Types, so you make a reusable function out of this? – rubo77 Aug 22 '16 at 22:11
  • 1
    The problem with SQLite is that you can't tell what is stored in the columns of a row based on the types specified in the table definition. It depends more on the code that you use to write the data to the table. I didn't have the time or the need to go that far, so I just put together something that worked for me. I'm no longer in that job and I don't have to use SQLite any more, so if you want to give it a shot, go for it. – Tony Vitabile Aug 23 '16 at 13:07
4

Try this:

SELECT name ,SUM(pgsize)/1024 table_size  FROM "dbstat" GROUP BY name ORDER BY table_size desc;
Hakan
  • 240
  • 3
  • 4
0

simply

select sum(1) as "MyTableSize" from my_table;

SUM will sum the entire column and applying 1 adds one value per column. COUNT also works instead of SUM

to also get the width you can count the column names as follows:

select count(*) from pragma_table_info('str_b');
Daniel Olson
  • 73
  • 1
  • 3
-1

This is not be possible for an existing table but if you can alter the table definition, a solution is to add a integer field to store the total size of fields (based on field types, string length, etc.) when inserting records in the table. Then a simple query like this will return the table size:

select sum(RecordSize) from TableName
JnLlnd
  • 165
  • 1
  • 14