Skip to content

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-Lite/SYSTEM_VIEW_DATA_POPULATION_COMPLETE.md

See Also