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;

▶ Run

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;

▶ Run

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

PostgreSQL-style manual scans are requested with:

SQL

SELECT db9_refresh_storage_stats();

Not available in the current release

DB9_REFRESH_STORAGE_STATS() requires the background worker subsystem, which is sealed off in the current release. Calling it returns:

Output

ERROR: feature "storage_size_scan" is unavailable (PreActivationSeal)
SQLSTATE: 55000

There is currently no way to trigger a scan on demand. The virtual tables above are still populated by the automatic reconciliation cycle described below, so query them directly.

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,
       total_bytes,
       round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb,
       scanned_at
FROM _DB9_SYS_STORAGE_STATS;

▶ Run

DB9 Difference: pg_size_pretty() is not implemented

PostgreSQL’s size-formatting and size-inspection functions — pg_size_pretty(), pg_total_relation_size(), pg_relation_size(), pg_table_size(), pg_indexes_size() — do not exist in DB9 and fail with unknown function (0A000). The virtual tables report raw BIGINT byte counts; divide by 1024 as shown above to format them.

Find the largest tables:

SQL

SELECT table_name,
       round(data_bytes::numeric / 1024 / 1024, 2) AS data_mb,
       round(index_bytes::numeric / 1024 / 1024, 2) AS index_mb,
       round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC
LIMIT 10;

▶ Run

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;

▶ Run

Next Steps

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