HeliosDB Nano v2.3.0 PostgreSQL-Compatible System Views

HeliosDB Nano v2.3.0 PostgreSQL-Compatible System Views

Overview

Version 2.3.0 introduces comprehensive 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

Status: Replication monitoring (when sync enabled)

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

Schema:

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:

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

Status: Maintenance operation progress tracking

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

Schema:

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:

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

Status: v2.3.0 enhancement for distributed sync monitoring

Purpose: Comprehensive view of node synchronization status, replication lag, and performance metrics.

Schema:

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:

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

Status: v2.3.0 query performance tracking

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

Schema:

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:

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

Status: v2.3.0 per-table resource tracking

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

Schema:

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:

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

Status: v2.3.0 ACID compliance monitoring

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

Schema:

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:

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

PostgreSQL v2.0-v2.2 Compatibility Views

The following views continue to be available for backward compatibility:

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

HeliosDB v2.0 Features

• pg_branches - Database branches
• pg_matviews - Materialized views
• pg_snapshots - Time-travel snapshots

HeliosDB v2.1 Features

• 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

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

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

Backward Compatibility

All v2.3.0 views are fully compatible with PostgreSQL client libraries:

• psql
• JDBC
• Python psycopg2
• Node.js pg
• Ruby pg
• Go pq

Example with psql:

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 status, not historical
• Memory stats are estimates, not exact measurements
• Vacuum progress requires active maintenance operation

Planned Enhancements (v2.4+)

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

Troubleshooting

Empty Results

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

-- Reduce cache TTL for frequently changing data
SET system_view_cache_ttl = 1000; -- 1 second

-- Check cache statistics
SELECT * FROM helios_cache_performance;

Replication Status Empty

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

-- Check node connectivity
SELECT * FROM helios_cluster_nodes;

See Also

• FEATURE_DEVELOPMENT_PROTOCOL.md
• v2.1.0 Features
• v2.0.0 Features