STORAGE_INFO view

Overview

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.

Syntax

SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;

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
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

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	created_ts	deleted_ts	username	active_bytes	historical_bytes	kept_for_cloned_bytes	failsafe_bytes
test_db_1	2024-12-02 20:18:36	NULL	bob	82063360	0	268496896	0
test_db_2	2024-11-12 03:38:52	NULL	jim	274432	0	0	0

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;

Notes

• Data Refresh: Information in this view refreshes every 6 hours
• 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

No results returned

• Ensure you have the correct permissions to access the information schema
• Verify you're connected to the correct MotherDuck organization

Outdated information

• Data refreshes every 6 hours, so recent changes may not be immediately visible

Permission denied errors

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