Analyzing disk space usage¶

A database file is usually larger than the data in it, and where the rest of it went is not obvious: indexes, overflow pages, pages the freelist is holding on to, and space lost to fragmentation all take their share. Tools → Run sqlite3_analyzer measures all of it and reports what each table and index actually costs.

The command runs SQLite's own sqlite3_analyzer against the selected database and opens the result in a window of three tabs.

Report¶

The analyzer's own report, as it writes it: the page size, how many pages the file holds and how many of those store data, how much of the file is user payload, and then a breakdown per table and index.

The disk space report

The percentages are the useful part. A table whose payload is far below its page count is carrying overhead — long rows spilling into overflow pages, or space freed by deletes that has not been reused. Vacuum is what recovers it; see Performing maintenance operations.

Data¶

The same measurements as a grid, one row per table and index, which is easier to sort and to copy elsewhere than the report text.

The measurements as a grid

is_index marks the rows that are indexes rather than tables, nentry counts the entries stored, depth is the height of the b-tree, and payload is the bytes of your data — as against everything else the pages hold.

Chart¶

The same figures drawn, which is the quickest way to see which objects a file is being spent on.

The chart

Chart chooses the measure — payload is the default — and Preview opens the drawing for printing.

Only the twenty largest objects get a slice of their own; everything below them is added together into one, labelled with how many it stands for. A pie of a thousand slices is neither readable nor quick to draw, and the objects that matter are the ones at the top.

While it is measuring¶

The analyzer reads every page of the file, so it takes as long as the file is large — an eleven gigabyte database takes about a minute and a half. A strip across the top of the window says so while it works, and carries a Stop button; the window stays usable throughout, and stopping takes effect at once. Nothing is reported for a run that was stopped, since a partial measurement is not a measurement.

If it cannot be measured¶

The analyzer writes text; turning that text into the Data and Chart tabs is a second step, and it can fail on its own. When it does, the reason is printed at the top of the report, above the analyzer's own output, and the two other tabs are left empty. If the report loaded but the chart could not be drawn, the reason appears where the chart's title belongs.

So an empty Data or Chart tab always comes with an explanation. A tab that is empty and says nothing means the database genuinely has nothing to show.