STORAGE_INFO views

Admin Only Feature

This feature can only be used by Admin users.

Overview

MotherDuck provides two views to look at how much storage is used - a current snapshot (STORAGE_INFO) and the previous 30 days of history (STORAGE_INFO_HISTORY).

The MD_INFORMATION_SCHEMA.STORAGE_INFO view provides comprehensive storage information for all databases in your MotherDuck organization. This view is essential for understanding storage usage, billing calculations, and database lifecycle management.

The MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY view provides storage information for up to the past 30 days of usage.

If you're an admin, you can view your organization's storage breakdown on the databases page. Here, you'll find the total breakdown of current bytes across all your databases, as well as a breakdown for each database. You can also click on a row to get a lifecycle breakdown for a given database.

Syntax

To see the latest snapshot:

SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;

To see the history:

SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY;

Columns

The MD_INFORMATION_SCHEMA.STORAGE_INFO view returns one row for each database in your organization with the following columns:

Column Name	Data Type	Description
database_name	VARCHAR	Name of the database
database_id	UUID	Unique ID for the database
created_ts	TIMESTAMP	Time when the database was created
deleted_ts	TIMESTAMP	Time when the database was deleted (NULL if not deleted)
username	VARCHAR	Username of the database owner
active_bytes	BIGINT	Actively referenced bytes of the database
historical_bytes	BIGINT	Non-active bytes that are referenced by a share of this database
kept_for_cloned_bytes	BIGINT	Bytes referenced by other databases (via zero-copy clone) that are no longer referenced by this database as active or historical bytes
failsafe_bytes	BIGINT	Bytes that are no longer referenced by any database or share
computed_ts	TIMESTAMP	Time at which active_bytes, historical_bytes, etc. were computed

The MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY view has the same schema, but will return results from up to the past 30 days, so a single database might have multiple entries reflecting its state at different points in time.

Examples

Basic Usage

View storage information for all databases in your organization:

-- Get storage information for all databases
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;

Sample results:

database_name	database_id	created_ts	deleted_ts	username	active_bytes	historical_bytes	kept_for_cloned_bytes	failsafe_bytes	results_ts
test_db_1	7ed1baf3-e4ff-42c9-a37b-9f683905ce45	2024-12-02 20:18:36	NULL	bob	82063360	0	268496896	0	2025-06-25 16:46:16.37
test_db_2	fcc16e53-d761-4e40-84ec-15570fab363e	2024-11-12 03:38:52	NULL	jim	274432	0	0	0	2025-06-25 16:46:16.37

Filtering and Analysis

Find databases with high storage usage:

-- Find databases using more than 1GB of active storage
SELECT
    database_name,
    username,
    active_bytes,
    ROUND(active_bytes / 1024.0 / 1024.0 / 1024.0, 2) as active_gb
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
WHERE active_bytes > 1073741824  -- 1GB in bytes
ORDER BY active_bytes DESC;

Storage Cost Analysis

Analyze storage costs by user:

-- Calculate total storage usage per user
SELECT
    username,
    COUNT(*) as database_count,
    SUM(active_bytes) as total_active_bytes,
    SUM(historical_bytes) as total_historical_bytes,
    SUM(kept_for_cloned_bytes) as total_cloned_bytes,
    SUM(failsafe_bytes) as total_failsafe_bytes
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
GROUP BY username
ORDER BY total_active_bytes DESC;

Analyze active and failsafe storage footprint over the past week for a specific database:

SELECT active_bytes, failsafe_bytes, computed_ts
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY
WHERE database_name = "my_database"
AND computed_ts >= NOW - INTERVAL 7 DAYS
ORDER BY computed_ts DESC;

Notes

• Data Refresh: Information in this view refreshes every 1-6 hours
• Retention: STORAGE_INFO_HISTORY only returns one set of results per day, even though the latest results are re-computed multiple times per day
• Billing Data: This view returns the underlying data used to power MotherDuck storage billing
• Permissions: You must have appropriate permissions to access this view
• Organization Scope: Only shows databases within your current organization

Troubleshooting

Common Issues

Outdated information

• Data refreshes only happen periodically, so recent changes may not be immediately visible

Permission denied errors

• Contact your organization administrator to ensure you have the necessary permissions
  • This feature is only for Admins
• Verify your authentication token is valid and has the required scope