How to Check SQLite Table and Index Sizes With .diskused

TLDR

SQLite 3.54 adds .diskused to the shell for table and index space analysis.
Application code can query dbstat if the SQLite build enables it.
Allocated pages, stored content and unused space answer different questions.

Start with the database's space report

Run sqlite3 demo.db .diskused to see how tables and indexes occupy an SQLite database. Replace demo.db with the database path. This gives you an object-level view that the file's total size cannot provide.

SQLite 3.54.0, released October 9, adds the command and deprecates the separate sqlite3_analyzer utility. SQLite 3.54.1, released October 16, includes further fixes.

sqlite3 --version
sqlite3 demo.db .diskused

The CLI documentation explains that the readable report is a comment within SQL output containing the analysis data. Seeing SQL after the report is expected. If the shell does not recognize the command, check the version of the executable you actually invoked.

An embedded library does not automatically gain the command

The shell calls diskused('main') internally, but the extension is not part of the sqlite3.c amalgamation. Applications must compile and load it separately. Updating an embedded library alone does not make that function available.

For numeric results, use the DBSTAT virtual table in a build compiled with SQLITE_ENABLE_DBSTAT_VTAB. This example ranks objects by allocated bytes:

SELECT name, pgsize, payload, unused
FROM dbstat
WHERE aggregate = TRUE
ORDER BY pgsize DESC;

Allocated bytes are not the same as payload

Aggregation produces one row per table or index. pgsize measures its page space, payload measures stored content, and unused measures unused space inside those pages. All three are byte counts.

DBSTAT omits freelist pages and certain other database pages. Adding up its results therefore does not account for the whole database file. Use the ranking to identify large objects, then inspect how much of their allocation contains data.

A smaller dataset can still occupy the same file

SQLite can retain space freed by deletion for reuse; reclamation also depends on settings such as auto_vacuum=FULL. A space report measures that situation without rebuilding the file.

PRAGMA freelist_count; reports wholly unused pages, a different quantity from unused bytes inside occupied pages. Neither number alone promises a particular reduction in file size.

VACUUM performs a rebuild, requires additional disk space and is subject to write-lock constraints. Identify the large objects first, then decide whether the database needs data cleanup or space reclamation.