Database Size

Database size can be fetched with PRAGMA database_size;. It contains attributes to allow insights into the sizes of MotherDuck databases.

Alternative invocations

This Pragma can also be invoked as a tabular function with:

• FROM pragma_database_size();

Schema

PRAGMA database_size has the following schema:

Column Name	Data Type	Value
database_name	VARCHAR	name of the database
database_size	VARCHAR	database size in 1024 byte increments (i.e. Kibibytes)
block_size	BIGINT	not currently returned
used_blocks	BIGINT	not currently returned
total_blocks	BIGINT	not currently returned
free_blocks	BIGINT	not currently returned
wal_size	VARCHAR	not currently returned
memory_usage	VARCHAR	not currently returned
memory_limit	VARCHAR	not currently returned

Example Usage

PRAGMA database_size;

Example result:

database_name	database_size	block_size	used_blocks	total_blocks	free_blocks	wal_size	memory_usage	memory_limit
my_db	153.9 GiB	NULL	NULL	NULL	NULL	NULL	NULL	NULL
another_database	3.1 TiB	NULL	NULL	NULL	NULL	NULL	NULL	NULL

In some cases, you may want to filter the dataset, in which case you can use a tabular function in your FROM clause. An example is shown below:

SELECT * 
FROM pragma_database_size() 
WHERE database_name = 'my_db'