Storage Accounting

DB9 provides built-in storage accounting through virtual tables that show how much space your data, indexes, and metadata consume. Storage stats are updated automatically in the background and can be refreshed on demand.

Virtual Tables

_DB9_SYS_STORAGE_STATS

Database-level storage breakdown.

SQL

SELECT database_name, data_bytes, index_bytes, metadata_bytes, total_bytes, scanned_at
FROM _DB9_SYS_STORAGE_STATS;

Column	Type	Description
database_id	TEXT	Database identifier.
database_name	TEXT	Database name.
data_bytes	BIGINT	Bytes used by table data.
index_bytes	BIGINT	Bytes used by indexes.
metadata_bytes	BIGINT	Bytes used by system metadata.
total_bytes	BIGINT	Sum of data + index + metadata bytes.
scanned_at	TIMESTAMP	When the last scan completed (NULL if never scanned).
scan_duration_ms	BIGINT	Duration of the last scan in milliseconds.

_DB9_SYS_TABLE_STORAGE_STATS

Per-table storage breakdown.

SQL

SELECT table_name, data_bytes, index_bytes, total_bytes
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC;

Column	Type	Description
database_id	TEXT	Database identifier.
table_id	TEXT	Internal table identifier.
table_name	TEXT	Table name.
data_bytes	BIGINT	Bytes used by this table’s data.
index_bytes	BIGINT	Bytes used by this table’s indexes.
total_bytes	BIGINT	Sum of data + index bytes.
scanned_at	TIMESTAMP	When the last scan completed.

Manual Refresh

Trigger an immediate storage scan:

SQL

SELECT db9_refresh_storage_stats();

Output

 db9_refresh_storage_stats
---------------------------
 storage scan enqueued

The scan runs asynchronously in the background. Results appear in the virtual tables once the scan completes (typically within a few seconds).

Automatic Reconciliation

Storage stats are automatically refreshed by a background worker on a 30-minute cycle. No configuration is required — the system keeps stats reasonably up-to-date without manual intervention.

Practical Examples

Check total database size:

SQL

SELECT database_name,
       pg_size_pretty(total_bytes) AS total_size,
       scanned_at
FROM _DB9_SYS_STORAGE_STATS;

Note: pg_size_pretty() formats byte counts as human-readable strings like 128 MB.

Find the largest tables:

SQL

SELECT table_name,
       pg_size_pretty(data_bytes) AS data,
       pg_size_pretty(index_bytes) AS indexes,
       pg_size_pretty(total_bytes) AS total
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC
LIMIT 10;

Monitor index-to-data ratio:

SQL

SELECT table_name,
       CASE WHEN data_bytes > 0
            THEN round(index_bytes::numeric / data_bytes, 2)
            ELSE 0
       END AS index_ratio
FROM _DB9_SYS_TABLE_STORAGE_STATS
WHERE total_bytes > 0
ORDER BY index_ratio DESC;

Related Docs

• Observability — query metrics and live inspection
• Limits & Quotas — storage limits per tenant
• Built-in Functions — db9_refresh_storage_stats() reference