Calculating the size of a SQLite database file using SQL#

I learned this trick today while browsing the code of Blacklite, a neat Java library for writing diagnostic logs to a SQLite database.

To calculate the size in bytes of a SQLite database file using a SQL query, run this:

1
select page_size * page_count from pragma_page_count(), pragma_page_size();

I ran this against my content.db database and it returned:

1
21086208

And sure enough, ls -l confirms it:

1
ls -l content.db

1
-rw-r--r--@ 1 simon  staff  21086208 Aug 15 09:24 content.db

It works using two pragma function. Explored using the sqlite3 content.db tool:

1
sqlite> .headers on
2
sqlite> select * from pragma_page_count();
3
page_count
4
5148
5
sqlite> select * from pragma_page_size();
6
page_size
7
4096

The page_size defaults to 4096 for most databases, but can be changed.

The page_count is the number of pages in the current file.

It turns out SQLiite databases are always an exact multiple of the page_size. So multiplying that by the page count gives the size of the database in bytes!

Confirming that with awk#

I got GPT-4 to write me a shell script to confirm that all of my .db files were a multiple of 4096:

1
find . \
2
  -name "*.db" \
3
  -exec stat -f "%z %N" {} \; | \
4
awk '{
5
  if ($1 % 4096 == 0) {
6
    print $2 " has size " $1 " which is a multiple of 4096"
7
  } else {
8
    print $2 " has size " $1 " which is NOT a multiple of 4096"
9
  }
10
}'

The output, truncated, looked like this:

1
./datasette-extract/content.db has size 21086208 which is a multiple of 4096
2
./sf-tree-history/tree-history-ord.db has size 288354304 which is a multiple of 4096
3
./sf-tree-history/tree-history.db has size 619749376 which is a multiple of 4096
4
./ca-fires-history/ca-fires.db has size 8364032 which is a multiple of 4096
5
./webvid-datasette/webvid.db has size 2692648960 which is a multiple of 4096
6
./cbsa-datasette/core.db has size 112439296 which is a multiple of 4096
7
./nicar-2023/nicar2023.db has size 978944 which is a multiple of 4096
8
...
9
./wedding/weddingsite/data.db has size 37888 which is NOT a multiple of 4096

And sure enough:

1
$ sqlite3 wedding/weddingsite/data.db
2
SQLite version 3.41.1 2023-03-10 12:13:52
3
Enter ".help" for usage hints.
4
sqlite> select * from pragma_page_size();
5
1024

That’s a SQLite file created November 8th 2009, so presumably the default page size was smaller back then!