---
title: "HeliosDB Nano Monitoring System Views"
---

# HeliosDB Nano Monitoring System Views

## Overview

HeliosDB Nano provides PostgreSQL-compatible monitoring views alongside HeliosDB-specific system views. These views provide real-time insights into database activity, performance metrics, replication status, and resource utilization.

All views support:
- Full PostgreSQL naming and column compatibility
- 5-second caching for performance-critical views
- Empty result sets for disabled features (graceful degradation)
- Thread-safe access with proper error handling

## PostgreSQL-Compatible Views

### 1. pg_stat_replication

**Purpose**: Monitor active replication connections and synchronization state between primary and replica nodes.

**Schema**:
```sql
CREATE VIEW pg_stat_replication AS
SELECT
    client_id TEXT,                    -- Unique identifier for replication client
    application_name TEXT,             -- Application name for the replica
    state TEXT,                        -- 'streaming', 'catchup', 'offline'
    sent_lsn INT8,                     -- Log sequence number sent
    write_lsn INT8,                    -- LSN written by replica
    flush_lsn INT8,                    -- LSN flushed to disk
    replay_lsn INT8,                   -- LSN replayed on replica
    sync_priority INT4,                -- Priority for synchronous replication
    sync_state TEXT,                   -- 'sync' or 'async'
    connected_at TIMESTAMPTZ,          -- When connection was established
    last_sync_time TIMESTAMPTZ         -- Last successful sync time
FROM replication_clients;
```

**Example Usage**:
```sql
-- Monitor replication status
SELECT client_id, state, sync_state, last_sync_time
FROM pg_stat_replication;

-- Check replication lag
SELECT client_id, (sent_lsn - replay_lsn) as lag_bytes
FROM pg_stat_replication
WHERE state = 'streaming';
```

**Notes**:
- Returns empty result set if replication is not enabled
- LSN values are Log Sequence Numbers (WAL positions)
- Synchronous vs asynchronous replication affects durability guarantees

### 2. pg_stat_progress_vacuum

**Purpose**: Monitor the progress of ongoing maintenance operations (vacuum, compression, etc.).

**Schema**:
```sql
CREATE VIEW pg_stat_progress_vacuum AS
SELECT
    pid INT4,                          -- Process ID
    datid INT4,                        -- Database OID
    datname TEXT,                      -- Database name
    relid INT4,                        -- Relation OID
    relname TEXT,                      -- Relation name
    phase TEXT,                        -- 'scanning', 'vacuuming', 'cleaning'
    heap_blks_total INT8,              -- Total heap blocks
    heap_blks_scanned INT8,            -- Blocks scanned
    heap_blks_vacuumed INT8,           -- Blocks vacuumed
    index_vacuum_count INT8,           -- Number of index vacuums
    current_free_pages INT8            -- Free pages found
FROM vacuum_progress;
```

**Example Usage**:
```sql
-- Monitor active maintenance
SELECT relname, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- Track vacuum progress percentage
SELECT relname,
       ROUND(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0)) as progress_pct
FROM pg_stat_progress_vacuum;
```

**Notes**:
- Returns empty result set if no maintenance is in progress
- Used for tracking VACUUM, ANALYZE, and compression operations
- Real-time progress tracking useful for large maintenance tasks

## HeliosDB-Specific Views

### 3. helios_sync_status

**Purpose**: View of node synchronization status, replication lag, and performance metrics.

**Schema**:
```sql
CREATE VIEW helios_sync_status AS
SELECT
    node_id TEXT,                      -- Node identifier
    node_name TEXT,                    -- Human-readable node name
    is_primary BOOLEAN,                -- TRUE if this is primary node
    connected_replicas INT4,           -- Number of connected replica nodes
    total_changes_sent INT8,           -- Total changes sent to replicas
    total_changes_received INT8,       -- Total changes received from peers
    last_sync_time TIMESTAMPTZ,        -- Timestamp of last sync operation
    avg_sync_latency_ms FLOAT8,        -- Average sync latency (milliseconds)
    max_sync_latency_ms FLOAT8,        -- Maximum sync latency observed
    sync_errors INT8,                  -- Count of sync errors
    replication_lag_bytes INT8         -- Bytes of data pending replication
FROM sync_metrics;
```

**Example Usage**:
```sql
-- Check node status
SELECT node_id, is_primary, connected_replicas, replication_lag_bytes
FROM helios_sync_status;

-- Monitor sync latency
SELECT node_id, avg_sync_latency_ms, max_sync_latency_ms
FROM helios_sync_status;

-- Detect sync issues
SELECT node_id, sync_errors
FROM helios_sync_status
WHERE sync_errors > 0;
```

**Notes**:
- Returns one row per node in the cluster
- Replication lag indicates amount of data not yet synchronized
- Used for monitoring distributed setup health

### 4. helios_query_history

**Purpose**: Historical record of query executions with detailed performance metrics.

**Schema**:
```sql
CREATE VIEW helios_query_history AS
SELECT
    query_id INT8,                     -- Unique query execution identifier
    query_hash TEXT,                   -- Hash of normalized query
    query_text TEXT,                   -- Full query text (truncated if needed)
    start_time TIMESTAMPTZ,            -- Query start time
    end_time TIMESTAMPTZ,              -- Query completion time
    duration_ms FLOAT8,                -- Total execution time (milliseconds)
    rows_returned INT8,                -- Rows returned to client
    rows_examined INT8,                -- Rows examined during execution
    cpu_time_ms FLOAT8,                -- CPU time spent
    io_time_ms FLOAT8,                 -- I/O time spent
    status TEXT,                       -- 'success', 'error', 'timeout'
    error_message TEXT,                -- Error message if status='error'
    user_name TEXT                     -- User who executed query
FROM query_log
ORDER BY start_time DESC
LIMIT 1000;
```

**Example Usage**:
```sql
-- Find slowest queries
SELECT query_text, duration_ms, rows_returned
FROM helios_query_history
WHERE status = 'success'
ORDER BY duration_ms DESC
LIMIT 10;

-- Find problematic queries
SELECT query_hash, COUNT(*) as error_count, user_name
FROM helios_query_history
WHERE status = 'error'
GROUP BY query_hash, user_name;

-- Analyze query efficiency
SELECT query_text, AVG(duration_ms) as avg_duration,
       AVG(rows_examined::float / NULLIF(rows_returned, 0)) as efficiency
FROM helios_query_history
GROUP BY query_text
HAVING COUNT(*) > 5;
```

**Notes**:
- Limited to last 1000 queries for performance
- Includes detailed timing breakdown (CPU vs I/O)
- Query hash enables aggregation of similar queries
- Text is truncated for very long queries

### 5. helios_table_memory_stats

**Purpose**: Analyze memory usage, cache hit ratios, and storage footprint by table.

**Schema**:
```sql
CREATE VIEW helios_table_memory_stats AS
SELECT
    schemaname TEXT,                   -- Schema name
    tablename TEXT,                    -- Table name
    heap_size_bytes INT8,              -- Size of heap data
    cache_size_bytes INT8,             -- Amount of data in cache
    index_size_bytes INT8,             -- Size of all indexes
    total_size_bytes INT8,             -- Total table + index size
    cache_hit_ratio FLOAT8,            -- Cache hit ratio (0-1)
    cache_accesses INT8,               -- Total cache accesses
    cache_hits INT8,                   -- Successful cache hits
    last_analyzed TIMESTAMPTZ          -- Last statistics update
FROM table_memory_stats;
```

**Example Usage**:
```sql
-- Find largest tables
SELECT tablename, total_size_bytes / (1024*1024) as size_mb
FROM helios_table_memory_stats
ORDER BY total_size_bytes DESC
LIMIT 10;

-- Monitor cache efficiency
SELECT tablename, cache_hit_ratio, cache_accesses
FROM helios_table_memory_stats
WHERE cache_hit_ratio < 0.8;

-- Identify compression opportunities
SELECT tablename, heap_size_bytes
FROM helios_table_memory_stats
WHERE heap_size_bytes > 100*1024*1024
ORDER BY heap_size_bytes DESC;
```

**Notes**:
- Cache sizes are actual allocated memory
- Cache hit ratio indicates working set vs total data size
- Helps identify tables that need compression
- Statistics updated periodically, not in real-time

### 6. helios_transaction_stats

**Purpose**: Track transaction execution, isolation levels, and ACID properties.

**Schema**:
```sql
CREATE VIEW helios_transaction_stats AS
SELECT
    transaction_id INT8,               -- Unique transaction identifier
    start_time TIMESTAMPTZ,            -- When transaction started
    end_time TIMESTAMPTZ,              -- When transaction completed
    duration_ms FLOAT8,                -- Total duration (milliseconds)
    isolation_level TEXT,              -- 'READ_UNCOMMITTED', 'READ_COMMITTED', 'REPEATABLE_READ', 'SERIALIZABLE'
    operations_count INT8,             -- Number of operations in transaction
    rows_read INT8,                    -- Rows read
    rows_written INT8,                 -- Rows written (insert/update/delete)
    status TEXT,                       -- 'committed', 'aborted', 'active'
    is_distributed BOOLEAN             -- TRUE for distributed transactions
FROM transaction_log;
```

**Example Usage**:
```sql
-- Find long-running transactions
SELECT transaction_id, duration_ms, isolation_level, status
FROM helios_transaction_stats
WHERE status = 'active'
  AND duration_ms > 60000;

-- Analyze transaction throughput
SELECT DATE_TRUNC('minute', start_time) as minute,
       COUNT(*) as transaction_count,
       AVG(duration_ms) as avg_duration
FROM helios_transaction_stats
WHERE status = 'committed'
GROUP BY DATE_TRUNC('minute', start_time);

-- Monitor isolation level usage
SELECT isolation_level, COUNT(*) as usage_count
FROM helios_transaction_stats
GROUP BY isolation_level;
```

**Notes**:
- Tracks all transaction isolation levels
- Distributed transactions have additional overhead
- Used for ACID compliance verification
- Aborted transactions indicate rollback events

## Other Catalog and Feature Views

The following views are also available:

### Core Catalog Views
- `pg_tables` - All user tables
- `pg_views` - All views
- `pg_indexes` - All indexes
- `pg_attribute` - Column definitions
- `pg_database` - Database metadata
- `pg_namespace` - Schema information
- `pg_class` - Relations (tables, views, indexes)
- `pg_type` - Data type definitions

### Session & Activity Views
- `pg_stat_activity` - Active sessions
- `pg_stat_database` - Database statistics
- `pg_settings` - Configuration settings
- `pg_stat_ssl` - SSL/TLS connection info

### Branching, Materialized Views and Snapshots
- `pg_branches` - Database branches
- `pg_matviews` - Materialized views
- `pg_snapshots` - Time-travel snapshots

### Optimizer, Compression and Authentication
- `pg_stat_optimizer` - Query optimizer statistics
- `pg_compression_stats` - Compression metrics
- `pg_authid` - Authentication identities

## Performance Considerations

### Caching Strategy
- System views are cached with 5-second TTL
- Cache invalidated on DDL operations
- Use `ANALYZE` to refresh statistics

### Query Optimization
```sql
-- Good: Filter early, avoid full table scans
SELECT tablename, total_size_bytes
FROM helios_table_memory_stats
WHERE total_size_bytes > 100*1024*1024;

-- Less efficient: Full scan then filter
SELECT tablename, total_size_bytes
FROM helios_table_memory_stats
WHERE total_size_bytes / (1024*1024) > 100;
```

### Monitoring Queries
```sql
-- Monitor system view cache health
SELECT view_name, cache_size, cache_hit_count
FROM helios_cache_stats;

-- Check view execution times
SELECT view_name, AVG(execution_time_ms)
FROM helios_view_metrics
GROUP BY view_name;
```

## Integration with Tools

### Monitoring & Alerting
- Integrate with Prometheus/Grafana via pg_stat_* views
- Query history useful for APM (Application Performance Monitoring)
- Transaction stats for compliance reporting

### Backup & Recovery
- Use helios_sync_status to verify replication before backup
- Check transaction_stats for active transactions before recovery
- Monitor pg_stat_progress_vacuum during maintenance

### Capacity Planning
- Track helios_table_memory_stats trends over time
- Use helios_query_history for workload analysis
- Analyze cache_hit_ratio to size memory appropriately

## Client Compatibility

These views can be queried from any PostgreSQL client library:
- psql
- JDBC
- Python psycopg2
- Node.js pg
- Ruby pg
- Go pq

Example with psql:
```bash
psql -h localhost -d heliosdb -c "SELECT * FROM helios_table_memory_stats"
```

## Limitations & Future Enhancements

### Current Limitations
- Query history limited to 1000 most recent queries
- Replication views only show Current state, not historical
- Memory stats are estimates, not exact measurements
- Vacuum progress requires active maintenance operation

### Planned Enhancements
- Extended query history (configurable retention)
- Replication lag prediction
- Automatic slowlog generation
- Integration with query plan analysis
- Per-connection resource tracking

## Troubleshooting

### Empty Results
```sql
-- Check if view exists
SELECT COUNT(*) FROM information_schema.views
WHERE table_name = 'helios_sync_status';

-- Verify system view registration
SELECT * FROM pg_settings WHERE name = 'system_views_enabled';
```

### High Cache Miss Rate
```sql
-- Reduce cache TTL for frequently changing data
SET system_view_cache_ttl = 1000; -- 1 second

-- Check cache statistics
SELECT * FROM helios_cache_performance;
```

```sql
-- Verify replication is enabled
SELECT * FROM pg_settings WHERE name LIKE '%replication%';

-- Check node connectivity
SELECT * FROM helios_cluster_nodes;
```