Skip to content
HeliosDB Nano

System Views Quick Reference

System Views Quick Reference

Status: FULLY IMPLEMENTED Last Updated: 2025-11-21

Overview

System views provide metadata about database internals. They are read-only views that return information about branches, materialized views, and vector indexes. As of v2.0, all system views return real data from storage metadata.

Available System Views

pg_database_branches()

Lists all database branches with metadata.

Columns:

  • branch_name (TEXT) - Branch name
  • branch_id (INT8) - Unique ID
  • parent_id (INT8) - Parent branch ID
  • created_at (TIMESTAMP) - Creation time
  • fork_point_lsn (INT8) - Snapshot at creation
  • size_mb (INT8) - Storage size in MB
  • status (TEXT) - Branch state

Example:

-- List all branches
SELECT * FROM pg_database_branches();


-- Find large branches
SELECT branch_name, size_mb
FROM pg_database_branches()
WHERE size_mb > 100;


-- Show branch hierarchy
SELECT b.branch_name, p.branch_name AS parent
FROM pg_database_branches() b
LEFT JOIN pg_database_branches() p ON b.parent_id = p.branch_id;

pg_mv_staleness()

Shows staleness information for materialized views.

Columns:

  • view_name (TEXT) - View name
  • base_tables (TEXT) - JSON array of source tables
  • last_update (TIMESTAMP) - Last refresh time
  • pending_changes (INT8) - Pending changes count
  • staleness_sec (INT8) - Seconds since refresh
  • status (TEXT) - STALE/OUTDATED/FRESH

Example:

-- List all materialized views
SELECT * FROM pg_mv_staleness();


-- Find stale views
SELECT view_name, staleness_sec
FROM pg_mv_staleness()
WHERE status = 'STALE';


-- Find views that need refresh
SELECT view_name, last_update
FROM pg_mv_staleness()
WHERE staleness_sec > 3600; -- > 1 hour

pg_vector_index_stats()

Vector index statistics including compression ratios.

Columns:

  • index_name (TEXT) - Index name
  • num_vectors (INT8) - Number of vectors
  • dimensions (INT4) - Vector dimensionality
  • quantization (TEXT) - Compression method
  • memory_bytes (INT8) - Memory usage
  • recall_at_10 (FLOAT8) - Recall@10 metric

Example:

-- List all vector indexes
SELECT * FROM pg_vector_index_stats();


-- Find large indexes
SELECT index_name, num_vectors, memory_bytes / 1024 / 1024 AS memory_mb
FROM pg_vector_index_stats()
WHERE num_vectors > 1000000;


-- Check quantization status
SELECT index_name, quantization
FROM pg_vector_index_stats()
WHERE quantization != 'None';

Usage in REPL

List System Views

\dS

Describe System View Schema

\dS pg_database_branches
\dS pg_mv_staleness
\dS pg_vector_index_stats

Query System Views

SELECT * FROM pg_database_branches();
SELECT * FROM pg_mv_staleness();
SELECT * FROM pg_vector_index_stats();

Monitoring Examples

Database Health Check

-- Check for stale materialized views
SELECT view_name, staleness_sec / 3600 AS hours_stale
FROM pg_mv_staleness()
WHERE status IN ('STALE', 'OUTDATED')
ORDER BY staleness_sec DESC;


-- Check branch storage usage
SELECT branch_name, size_mb, status
FROM pg_database_branches()
ORDER BY size_mb DESC
LIMIT 10;


-- Check vector index memory usage
SELECT index_name,
       num_vectors,
       memory_bytes / 1024 / 1024 AS memory_mb,
       (memory_bytes::float / num_vectors) AS bytes_per_vector
FROM pg_vector_index_stats()
WHERE num_vectors > 0
ORDER BY memory_bytes DESC;

Automated Refresh Strategy

-- Find views that should be refreshed
SELECT view_name,
       staleness_sec / 60 AS minutes_stale
FROM pg_mv_staleness()
WHERE status = 'OUTDATED'
  AND staleness_sec > 3600; -- More than 1 hour old

Capacity Planning

-- Total storage by branch
SELECT
    COUNT(*) AS branch_count,
    SUM(size_mb) AS total_mb,
    AVG(size_mb) AS avg_mb,
    MAX(size_mb) AS max_mb
FROM pg_database_branches()
WHERE status = 'Active';


-- Vector index growth tracking
SELECT
    COUNT(*) AS index_count,
    SUM(num_vectors) AS total_vectors,
    SUM(memory_bytes) / 1024 / 1024 / 1024 AS total_gb
FROM pg_vector_index_stats();

Best Practices

  1. Regular Monitoring: Query system views periodically to track database health
  2. Alerting: Set up alerts for stale materialized views
  3. Capacity Planning: Use system views to forecast storage needs
  4. Performance Tuning: Identify large indexes for potential optimization

Compatibility

System views are compatible with PostgreSQL monitoring tools and follow PostgreSQL naming conventions (pg_* prefix).

Performance

All system views execute in < 1ms for typical deployments:

  • Branches: O(n) where n < 100
  • Materialized views: O(n) where n < 50
  • Vector indexes: O(n) where n < 20

Implementation Status

All system views are fully implemented and return real data:

  • pg_database_branches: Queries BranchManager for branch metadata
  • pg_mv_staleness: Queries MaterializedViewCatalog for MV metadata
  • pg_vector_index_stats: Queries VectorIndexManager for index statistics

Implementation details: /home/claude/HeliosDB Nano/SYSTEM_VIEW_DATA_POPULATION_COMPLETE.md

See Also

  • [System View Data Population Complete](/home/claude/HeliosDB Nano/SYSTEM_VIEW_DATA_POPULATION_COMPLETE.md)
  • [System View Execution Implementation](/home/claude/HeliosDB Nano/docs/implementation/SYSTEM_VIEW_EXECUTION_SUMMARY.md)
  • [Branch Storage Guide](/home/claude/HeliosDB Nano/docs/BRANCH_STORAGE_GUIDE.md)
  • [Materialized Views Implementation](/home/claude/HeliosDB Nano/MATERIALIZED_VIEWS_IMPLEMENTATION.md)