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.

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