Skip to content

db size: Allow displaying data and index size separately #349

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We鈥檒l occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
swissspidy wants to merge 3 commits into
base: main
Choose a base branch
from
Open

db size: Allow displaying data and index size separately #349

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
88d2b18
db size: Allow displaying data and index size separately
swissspidy Sep 15, 2026
5362d5a
db size: Accept megabytes in the human readable precision test
swissspidy Sep 15, 2026
bd815e3
db size: Collect the SQLite sizes in a single pass over dbstat
swissspidy Sep 15, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
db size: Collect the SQLite sizes in a single pass over dbstat 
Every dbstat query walks all pages of the database file, so asking for
the table size and the index size separately doubled that work for each
table. One grouped query over dbstat joined to sqlite_master returns
both, which also means the database-wide breakdown costs one query
instead of two.

The dbstat virtual table is not part of every SQLite build. Instead of
letting a failed query pass a silent 0 B on to the output, fail with a
message that names the missing extension.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01WA7GLFtiJMyt4ZwaDq194v
  • Loading branch information
@swissspidy @claude
swissspidy and claude committed Sep 15, 2026
commit bd815e39a22e2356fc63f792d4b9c267245f0f0f
26 changes: 11 additions & 15 deletions src/DB_Command.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1301,19 +1301,10 @@ public function size( $args, $assoc_args ) {

// Get the table size.
if ( $is_sqlite ) {
$data_bytes = (int) $wpdb->get_var(
$wpdb->prepare(
'SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name = %s LIMIT 1',
$table_name
)
);
$sqlite_sizes = $this->sqlite_size_breakdown( $table_name );

$index_bytes = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name IN ( SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = %s )",
$table_name
)
);
$data_bytes = $sqlite_sizes['data'];
$index_bytes = $sqlite_sizes['index'];
} else {
$table_sizes = $wpdb->get_row(
$wpdb->prepare(
Expand Down Expand Up @@ -1345,9 +1336,14 @@ public function size( $args, $assoc_args ) {
$db_path = $this->get_sqlite_db_path();
$db_name = $db_path ? basename( $db_path ) : '';

// Unlike the file size, this does not cover overhead such as free pages.
$data_bytes = $show_details ? $this->sqlite_type_size( 'table' ) : 0;
$index_bytes = $show_details ? $this->sqlite_type_size( 'index' ) : 0;
// Unlike the file size, these do not cover overhead such as free pages.
$sqlite_sizes = $show_details ? $this->sqlite_size_breakdown() : [
'data' => 0,
'index' => 0,
];

$data_bytes = $sqlite_sizes['data'];
$index_bytes = $sqlite_sizes['index'];
} else {
$db_sizes = $wpdb->get_row(
$wpdb->prepare(
Expand Down
62 changes: 52 additions & 10 deletions src/DB_Command_SQLite.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -553,20 +553,62 @@ protected function sqlite_size() {
}

/**
* Get the size of all SQLite objects of a given type.
* Get the data and the index size of the SQLite database, or of a single table.
*
* @param string $type Object type, either `table` or `index`.
* @return int Size in bytes.
* Both sizes are collected in a single pass over `dbstat`, which walks every
* page of the database file.
*
* @param string $table_name Optional. Table to limit the sizes to. Defaults to the whole database.
* @return array{data: int, index: int} Sizes in bytes.
*/
protected function sqlite_type_size( $type ) {
protected function sqlite_size_breakdown( $table_name = '' ) {
global $wpdb;

return (int) $wpdb->get_var(
$wpdb->prepare(
'SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name IN ( SELECT name FROM sqlite_master WHERE type = %s )',
$type
)
);
// The dbstat virtual table is not part of every SQLite build.
$suppress_errors = $wpdb->suppress_errors( true );

if ( '' !== $table_name ) {
$rows = $wpdb->get_results(
$wpdb->prepare(
"SELECT sqlite_master.type as type, SUM(dbstat.pgsize) as size_in_bytes FROM dbstat JOIN sqlite_master ON sqlite_master.name = dbstat.name WHERE sqlite_master.name = %s OR ( sqlite_master.type = 'index' AND sqlite_master.tbl_name = %s ) GROUP BY sqlite_master.type",
$table_name,
$table_name
),
ARRAY_A
);
} else {
$rows = $wpdb->get_results(
'SELECT sqlite_master.type as type, SUM(dbstat.pgsize) as size_in_bytes FROM dbstat JOIN sqlite_master ON sqlite_master.name = dbstat.name GROUP BY sqlite_master.type',
ARRAY_A
);
}

$last_error = $wpdb->last_error;

$wpdb->suppress_errors( $suppress_errors );

if ( '' !== $last_error ) {
WP_CLI::error( 'Could not determine the data and index size of the database. This requires the dbstat extension, which is not available for this SQLite installation.' );
}

$sizes = [
'data' => 0,
'index' => 0,
];

foreach ( (array) $rows as $row ) {
if ( ! is_array( $row ) || ! isset( $row['type'], $row['size_in_bytes'] ) || ! is_numeric( $row['size_in_bytes'] ) ) {
continue;
}

if ( 'table' === $row['type'] ) {
$sizes['data'] = (int) $row['size_in_bytes'];
} elseif ( 'index' === $row['type'] ) {
$sizes['index'] = (int) $row['size_in_bytes'];
}
}

return $sizes;
}

/**
Expand Down
Loading