---
title: "HeliosDB-Lite System Catalog (Dictionary Tables/Views)"
description: "HeliosDB Lite system catalog functions and views for branches, materialized-view staleness and vector indexes, with their PostgreSQL equivalents."
---

# HeliosDB-Lite System Catalog (Dictionary Tables/Views)

---

## Overview

HeliosDB-Lite provides PostgreSQL-compatible system views, also called dictionary tables or catalog views, for monitoring database branching, materialized views, and vector index statistics.

---

## Available System Views

### 1. pg_database_branches()

**Description**: Lists all database branches with metadata

**Schema**:
```sql
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**:
```sql
-- 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**:
```sql
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**:
```sql
-- 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;

-- Views with many unrefreshed changes
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**:
```sql
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**:
```sql
-- 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

With 8 PQ subquantizers each vector code takes 8 bytes instead of
dimensions × 4 bytes, but memory_bytes also counts the graph, the codebooks
and metadata, so the whole-index ratio is far lower than the per-code ratio.
```

---

## REPL Meta Commands

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

### \dS - List System Views

```bash
heliosdb> \dS

System Views:
──────────────────────────────────────────────────────────────────────
  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 <view_name> - Describe System View

```bash
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) | Branch catalog |
| pg_mv_staleness() | pg_stat_all_tables | Custom for MVs |
| pg_vector_index_stats() | pg_stat_all_indexes | Custom for vectors |

**Standard catalog views** are also available:
- `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

```sql
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

```sql
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

```sql
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;
```

---

### Query Behavior

- `\dS` lists the registered system views and `\dS <view_name>` shows schemas.
- `pg_database_branches()` reads branch metadata from storage and includes the `main` branch on a new database.
- `pg_mv_staleness()` returns materialized view staleness rows when materialized views exist.
- `pg_vector_index_stats()` returns vector index metadata and PQ compression metrics when vector indexes exist.

---

## Usage Examples

### Monitoring Script

```sql
-- 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

```sql
-- 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

### No Rows

**Problem**: A system view query succeeds but returns no rows.

**Cause**: Some system views are data-dependent. Materialized view and vector index views populate after creating the corresponding database objects.

**Check**:
```sql
SELECT * FROM pg_database_branches();
-- Includes at least the main branch
```

### 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

- [Features](/docs/lite/features/) - Branching, time-travel, materialized views and vector search

---

**Maintainer**: HeliosDB Engineering Team