Determine sqlite table size on disk
René Dohmen
May 28, 2014
1 min

I took me a while to find out that it’s not very easy to find out how big a sqlite table is on disk. But luckily the sqlite creators have a simple tool for that. It’s called sqlite3_analyzer and is available in precompiled format for Linux, Windows and OSX on SQLite’s Download section

Confusing: There also seems to be a commercial graphical sqlite tool in a windows only variant that has the same name, but it doesn’t have functionality to see how big tables are. http://www.kraslabs.com/sqlite_analyzer.php

When you run sqlite3_analyzer it will create a .sql file with info about the database. You can insert the .sql file in a new database to get more detailed info about the tables sizes and some other metrics.

It will produce a sql file with a big sql comment on top. After the comment some SQL statements are printed that will create a table “space_used” with more detailed info.

Example: the example DB is 381Mb on disk. The Binary table is used to store some binary data.

I used it to clean up some DB’s with a lot of tables. After deleting a lot of records I noticed that the file size didn’t shrink, so as a reminder for myself; you have to execute one SQLite SQL statement to free up disk space after a lot of delete statements: VACUUM;

Related Posts

Deploy FastAPI on Amazon Serverless
May 15, 2021
5 min
© 2021, All Rights Reserved.
Powered by formatics

Quick Links

Advertise with usAbout UsContact Us

Social Media