---
title: "System Views Quick Reference"
---

# System Views Quick Reference


## Overview

System views provide metadata about database internals. They are read-only views that return information about branches, materialized views, and vector indexes. 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
-- 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
```sql
SELECT * FROM pg_database_branches();
SELECT * FROM pg_mv_staleness();
SELECT * FROM pg_vector_index_stats();
```

## Monitoring Examples

### Database Health Check

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

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

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


These views return live data:

- **pg_database_branches**: branch metadata
- **pg_mv_staleness**: materialized view metadata and staleness
- **pg_vector_index_stats**: vector index statistics


## See Also