HeliosDB-Lite System Catalog (Dictionary Tables/Views)

Version: 2.0.0 Phase: Phase 3 v2.0 Status: Production Ready

---

Overview

HeliosDB-Lite provides PostgreSQL-compatible system views (also called dictionary tables or catalog views) for monitoring and managing Phase 3 features. These views provide insights into database branching, materialized views, and vector index statistics.

---

Available System Views

1. pg_database_branches()

Description: Lists all database branches with metadata

Schema:

CREATE VIEW pg_database_branches AS (
  branch_name       TEXT NOT NULL,
  branch_id         BIGINT NOT NULL,
  parent_id         BIGINT,
  created_at        TIMESTAMP NOT NULL,
  fork_point_lsn    BIGINT NOT NULL,
  size_mb           BIGINT NOT NULL,
  status            TEXT NOT NULL
);

Columns: - branch_name - Name of the database branch - branch_id - Unique identifier for the branch - parent_id - ID of parent branch (NULL for main branch) - created_at - Timestamp when branch was created - fork_point_lsn - Log Sequence Number at fork point - size_mb - Current branch size in megabytes - status - Branch status: 'active', 'merged', 'archived'

Usage:

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

-- Find active branches
SELECT branch_name, size_mb FROM pg_database_branches()
WHERE status = 'active';

-- Get branch hierarchy
SELECT
  b.branch_name,
  p.branch_name as parent_name,
  b.created_at
FROM pg_database_branches() b
LEFT JOIN pg_database_branches() p ON b.parent_id = p.branch_id
ORDER BY b.created_at;

Example Output:

 branch_name | branch_id | parent_id |      created_at      | fork_point_lsn | size_mb | status
-------------+-----------+-----------+----------------------+----------------+---------+--------
 main        |         1 |      NULL | 2025-11-01 10:00:00  |           1000 |     150 | active
 dev         |         2 |         1 | 2025-11-15 14:30:00  |           2500 |      75 | active
 staging     |         3 |         1 | 2025-11-16 09:00:00  |           2600 |      80 | active

---

2. pg_mv_staleness()

Description: Shows staleness information for all materialized views

Schema:

CREATE VIEW pg_mv_staleness AS (
  view_name         TEXT NOT NULL,
  base_tables       TEXT NOT NULL,     -- JSON array
  last_update       TIMESTAMP NOT NULL,
  pending_changes   BIGINT NOT NULL,
  staleness_sec     BIGINT NOT NULL,
  status            TEXT NOT NULL
);

Columns: - view_name - Name of the materialized view - base_tables - JSON array of underlying base tables - last_update - Timestamp of last refresh - pending_changes - Number of pending DML operations - staleness_sec - Seconds since last update - status - View status: 'fresh', 'stale', 'refreshing'

Usage:

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

-- Find stale views (> 5 minutes old)
SELECT view_name, staleness_sec, pending_changes
FROM pg_mv_staleness()
WHERE staleness_sec > 300;

-- Check auto-refresh candidates
SELECT view_name, pending_changes
FROM pg_mv_staleness()
WHERE status = 'stale' AND pending_changes > 1000;

Example Output:

   view_name   |      base_tables       |     last_update      | pending_changes | staleness_sec | status
---------------+------------------------+----------------------+-----------------+---------------+--------
 user_stats    | ["users", "orders"]    | 2025-11-18 10:00:00  |            1250 |           300 | stale
 product_stats | ["products", "sales"]  | 2025-11-18 10:04:30  |              50 |            30 | fresh

---

3. pg_vector_index_stats()

Description: Vector index statistics including Product Quantization compression ratios

Schema:

CREATE VIEW pg_vector_index_stats AS (
  index_name        TEXT NOT NULL,
  num_vectors       BIGINT NOT NULL,
  dimensions        INTEGER NOT NULL,
  quantization      TEXT NOT NULL,
  memory_bytes      BIGINT NOT NULL,
  recall_at_10      FLOAT8
);

Columns: - index_name - Name of the vector index - num_vectors - Number of vectors in index - dimensions - Vector dimensionality - quantization - Quantization type: 'none', 'pq', 'scalar' - memory_bytes - Total memory usage in bytes - recall_at_10 - Search accuracy (recall@10) or NULL

Usage:

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

-- Calculate compression ratios
SELECT
  index_name,
  num_vectors,
  dimensions,
  memory_bytes,
  (num_vectors * dimensions * 4.0 / memory_bytes) as compression_ratio
FROM pg_vector_index_stats()
WHERE quantization = 'pq';

-- Find indexes with low accuracy
SELECT index_name, recall_at_10
FROM pg_vector_index_stats()
WHERE recall_at_10 < 0.95 AND recall_at_10 IS NOT NULL;

Example Output:

     index_name      | num_vectors | dimensions | quantization | memory_bytes | recall_at_10
---------------------+-------------+------------+--------------+--------------+--------------
 embeddings_idx      |      100000 |        768 |           pq |     78643200 |         0.97
 product_vectors_idx |       50000 |        384 |           pq |     19660800 |         0.96
 user_vectors_idx    |       25000 |        128 |         none |     12800000 |         NULL

Compression Ratio Calculation:

Uncompressed size = num_vectors × dimensions × 4 bytes (float32)
Compression ratio = Uncompressed size / memory_bytes

Example (embeddings_idx):
Uncompressed = 100,000 × 768 × 4 = 307,200,000 bytes
Compressed   = 78,643,200 bytes
Ratio        = 307,200,000 / 78,643,200 = 3.9x

For PQ with 8 subquantizers:
Expected ratio = (dimensions × 4) / 8 = 768 × 4 / 8 = 384x (vectors only)
Actual includes graph structure overhead

---

REPL Meta Commands

HeliosDB-Lite REPL provides convenient commands for exploring system views:

\dS - List System Views

heliosdb> \dS

System Views (Phase 3):
──────────────────────────────────────────────────────────────────────
  pg_database_branches - Lists all database branches with metadata
  pg_mv_staleness - Shows staleness info for materialized views
  pg_vector_index_stats - Vector index statistics (PQ compression)

Use \dS <view_name> to see schema details
Example: \dS pg_database_branches

\dS - Describe System View

heliosdb> \dS pg_vector_index_stats

System View: pg_vector_index_stats
──────────────────────────────────────────────────────────────────────
Column                    Type            Nullable
──────────────────────────────────────────────────────────────────────
index_name                Text            NO
num_vectors               Int8            NO
dimensions                Int4            NO
quantization              Text            NO
memory_bytes              Int8            NO
recall_at_10              Float8          YES

Usage:
  SELECT * FROM pg_vector_index_stats();

---

PostgreSQL Compatibility

HeliosDB-Lite system views follow PostgreSQL naming conventions:

HeliosDB-Lite View	PostgreSQL Equivalent	Notes
pg_database_branches()	N/A (custom)	Phase 3 feature
pg_mv_staleness()	pg_stat_all_tables	Custom for MVs
pg_vector_index_stats()	pg_stat_all_indexes	Custom for vectors

Future Compatibility (planned for v2.1+): - pg_tables - Standard catalog of tables - pg_indexes - Standard catalog of indexes - pg_views - Standard catalog of views - information_schema.tables - ANSI SQL standard - information_schema.columns - ANSI SQL standard

---

Advanced Queries

Monitor Database Growth by Branch

SELECT
  branch_name,
  size_mb,
  ROUND(size_mb::FLOAT / SUM(size_mb) OVER () * 100, 2) as pct_of_total
FROM pg_database_branches()
WHERE status = 'active'
ORDER BY size_mb DESC;

Find Materialized Views Needing Refresh

SELECT
  view_name,
  staleness_sec / 60 as staleness_min,
  pending_changes,
  CASE
    WHEN pending_changes > 10000 THEN 'urgent'
    WHEN pending_changes > 1000 THEN 'high'
    WHEN staleness_sec > 600 THEN 'medium'
    ELSE 'low'
  END as priority
FROM pg_mv_staleness()
WHERE status = 'stale'
ORDER BY
  CASE priority
    WHEN 'urgent' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    ELSE 4
  END;

Calculate Vector Storage Efficiency

WITH storage_metrics AS (
  SELECT
    index_name,
    num_vectors,
    dimensions,
    memory_bytes,
    (num_vectors * dimensions * 4.0) as uncompressed_bytes,
    (num_vectors * dimensions * 4.0 / memory_bytes) as compression_ratio
  FROM pg_vector_index_stats()
  WHERE quantization = 'pq'
)
SELECT
  index_name,
  num_vectors,
  ROUND(compression_ratio, 1) as compression_ratio,
  ROUND((uncompressed_bytes - memory_bytes) / 1024.0 / 1024.0, 2) as saved_mb,
  ROUND((1.0 - memory_bytes::FLOAT / uncompressed_bytes) * 100, 2) as savings_pct
FROM storage_metrics
ORDER BY saved_mb DESC;

---

Implementation Status

Phase 3 v2.0 (Current)

Feature	Status	Notes
System view schemas	✅ Complete	All views defined
REPL commands (\dS)	✅ Complete	List and describe
SQL query support	⚠️ Partial	Returns empty results
Storage backend	❌ Not implemented	Planned for v2.2

Planned Enhancements (v2.2+)

1. Full Storage Integration
2. Actual data retrieval from storage backend
3. Real-time statistics computation

4. Automatic view refresh

5. Additional System Views

6. pg_stat_database - Database-wide statistics
7. pg_stat_activity - Current queries and sessions
8. pg_locks - Lock monitoring

9. pg_settings - Configuration settings

10. Performance Monitoring

11. Query execution statistics
12. Index usage statistics
13. Cache hit ratios

---

Usage Examples

Monitoring Script

-- Check database health
DO $$
DECLARE
  stale_views INT;
  large_branches INT;
BEGIN
  -- Count stale materialized views
  SELECT COUNT(*) INTO stale_views
  FROM pg_mv_staleness()
  WHERE staleness_sec > 3600;

  -- Count large branches
  SELECT COUNT(*) INTO large_branches
  FROM pg_database_branches()
  WHERE size_mb > 1000;

  -- Report
  RAISE NOTICE 'Stale views: %', stale_views;
  RAISE NOTICE 'Large branches: %', large_branches;
END $$;

Automated Refresh

-- Refresh stale materialized views
DO $$
DECLARE
  view_rec RECORD;
BEGIN
  FOR view_rec IN
    SELECT view_name FROM pg_mv_staleness()
    WHERE status = 'stale' AND pending_changes > 1000
  LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || view_rec.view_name;
  END LOOP;
END $$;

---

Troubleshooting

Empty Results

Problem: System views return no data

Cause: Storage backend not yet implemented (v2.0 limitation)

Workaround: Views are currently scaffolding - full implementation in v2.2

Check:

SELECT * FROM pg_database_branches();
-- Returns empty set in v2.0

View Not Found

Problem: ERROR: System view 'xxx' does not exist

Solution: Check available views with \dS

Valid Views: - pg_database_branches - pg_mv_staleness - pg_vector_index_stats

---

See Also

• Phase 3 User Guide - Complete Phase 3 feature documentation
• Database Branching - Branching details
• Materialized Views - MV implementation
• Product Quantization - PQ details

---

Document Version: 1.0 Last Updated: November 18, 2025 Maintainer: HeliosDB Engineering Team