HeliosDB Nano v2.3.0 System Views - Quick Start Guide

HeliosDB Nano v2.3.0 System Views - Quick Start Guide

Overview

Six new system views are now available in HeliosDB Nano v2.3.0 for monitoring database activity, replication status, and performance metrics.

Available Views Summary

View	Purpose	Example Query
pg_stat_replication	Monitor replication status	SELECT client_id, state FROM pg_stat_replication;
pg_stat_progress_vacuum	Track maintenance progress	SELECT relname, phase FROM pg_stat_progress_vacuum;
helios_sync_status	Node sync metrics	SELECT node_id, connected_replicas FROM helios_sync_status;
helios_query_history	Query performance history	SELECT query_text, duration_ms FROM helios_query_history;
helios_table_memory_stats	Table memory usage	SELECT tablename, total_size_bytes FROM helios_table_memory_stats;
helios_transaction_stats	Transaction statistics	SELECT transaction_id, status FROM helios_transaction_stats;

Quick Examples

1. Check Node Synchronization Status

-- View current node status
SELECT node_id, is_primary, connected_replicas,
       replication_lag_bytes, avg_sync_latency_ms
FROM helios_sync_status;

-- Expected output (single node):
--  node_id | is_primary | connected_replicas | replication_lag_bytes | avg_sync_latency_ms
-- ---------+------------+--------------------+-----------------------+---------------------
--  local   | t          |                  0 |                     0 |                   0

2. Monitor Table Memory Usage

-- Find largest tables
SELECT tablename, total_size_bytes / (1024*1024) as size_mb,
       cache_hit_ratio
FROM helios_table_memory_stats
ORDER BY total_size_bytes DESC
LIMIT 10;

-- Expected: Sorted list of tables with memory stats

3. Check Replication Status

-- Monitor connected replicas
SELECT client_id, state, sync_state, last_sync_time
FROM pg_stat_replication;

-- Returns empty if replication not enabled
-- When enabled, shows each replica's status

4. Analyze Query Performance

-- Find slowest queries
SELECT query_text, duration_ms, rows_returned
FROM helios_query_history
WHERE status = 'success'
ORDER BY duration_ms DESC
LIMIT 5;

-- Returns up to 1000 most recent queries

5. Review Maintenance Progress

-- Check ongoing maintenance operations
SELECT relname, phase, heap_blks_scanned, heap_blks_total,
       ROUND(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0)) as progress_pct
FROM pg_stat_progress_vacuum;

-- Returns empty if no maintenance in progress

6. Monitor Transaction Activity

-- Check active transactions
SELECT transaction_id, duration_ms, isolation_level, status
FROM helios_transaction_stats
WHERE status = 'active';

-- Returns details of any active transactions

Common Use Cases

Capacity Planning

-- Estimate disk usage by table
SELECT
    tablename,
    total_size_bytes / (1024*1024*1024) as size_gb,
    CASE
        WHEN total_size_bytes > 100*1024*1024 THEN 'Large'
        WHEN total_size_bytes > 10*1024*1024 THEN 'Medium'
        ELSE 'Small'
    END as category
FROM helios_table_memory_stats
ORDER BY total_size_bytes DESC;

Performance Troubleshooting

-- Find queries with poor cache efficiency
SELECT query_text, duration_ms, rows_examined,
       rows_returned,
       ROUND(100.0 * rows_returned / NULLIF(rows_examined, 0)) as efficiency_pct
FROM helios_query_history
WHERE status = 'success'
  AND rows_examined > rows_returned * 10
ORDER BY duration_ms DESC;

Replication Monitoring

-- Check replica synchronization
SELECT
    client_id,
    state,
    CASE WHEN state = 'streaming' THEN 'Healthy'
         WHEN state = 'catchup' THEN 'Catching up'
         ELSE 'Offline'
    END as health_status,
    (sent_lsn - replay_lsn) as lag_bytes,
    last_sync_time
FROM pg_stat_replication;

Cache Health Analysis

-- Monitor cache effectiveness
SELECT
    tablename,
    cache_hit_ratio,
    CASE
        WHEN cache_hit_ratio > 0.9 THEN 'Excellent'
        WHEN cache_hit_ratio > 0.7 THEN 'Good'
        WHEN cache_hit_ratio > 0.5 THEN 'Fair'
        ELSE 'Poor'
    END as cache_health,
    total_size_bytes / (1024*1024) as size_mb
FROM helios_table_memory_stats
WHERE cache_hit_ratio < 0.8
ORDER BY total_size_bytes DESC;

Integration with Tools

psql (Command Line)

# Check sync status
psql -c "SELECT * FROM helios_sync_status"

# Monitor memory usage
psql -c "SELECT tablename, total_size_bytes FROM helios_table_memory_stats"

# Export to CSV
psql -c "COPY (SELECT * FROM helios_query_history) TO '/tmp/queries.csv' CSV"

Python with psycopg2

import psycopg2

conn = psycopg2.connect("dbname=heliosdb user=postgres")
cur = conn.cursor()

# Get table memory stats
cur.execute("SELECT tablename, total_size_bytes FROM helios_table_memory_stats")
for tablename, size_bytes in cur.fetchall():
    print(f"{tablename}: {size_bytes / (1024*1024):.1f} MB")

conn.close()

JavaScript with node-postgres

const { Client } = require('pg');

const client = new Client();
await client.connect();

// Get sync status
const res = await client.query('SELECT * FROM helios_sync_status');
console.log('Current node status:', res.rows[0]);

await client.end();

Monitoring Dashboard Example (SQL)

-- Dashboard view combining all key metrics
WITH sync_info AS (
    SELECT node_id, connected_replicas, avg_sync_latency_ms
    FROM helios_sync_status
),
table_stats AS (
    SELECT COUNT(*) as table_count,
           SUM(total_size_bytes) / (1024*1024*1024) as total_size_gb
    FROM helios_table_memory_stats
),
recent_queries AS (
    SELECT COUNT(*) as query_count,
           COUNT(CASE WHEN status = 'error' THEN 1 END) as error_count,
           AVG(duration_ms) as avg_duration_ms
    FROM helios_query_history
    WHERE start_time > NOW() - INTERVAL '1 hour'
)
SELECT
    'System Status' as metric,
    node_id as value
FROM sync_info
UNION ALL
SELECT 'Connected Replicas', connected_replicas::text FROM sync_info
UNION ALL
SELECT 'Avg Sync Latency (ms)', avg_sync_latency_ms::text FROM sync_info
UNION ALL
SELECT 'Total Tables', table_count::text FROM table_stats
UNION ALL
SELECT 'Total Size (GB)', total_size_gb::text FROM table_stats
UNION ALL
SELECT 'Recent Queries (1h)', query_count::text FROM recent_queries
UNION ALL
SELECT 'Errors (1h)', error_count::text FROM recent_queries;

Troubleshooting

Views Not Visible

-- Check if views exist
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 'helios_%' OR table_name LIKE 'pg_stat_%'
ORDER BY table_name;

-- Should show 26 total views (20 existing + 6 new)

Empty Results

For pg_stat_replication:

• Normal when replication is disabled
• Enable replication to see data

For pg_stat_progress_vacuum:

• Normal when no maintenance is running
• Appears only during active vacuum/compress operations

For helios_query_history:

• Returns up to 1000 most recent queries
• Empty when database just started
• Data populated as queries execute

For helios_transaction_stats:

• Normal when no transaction history available
• Data populated during transaction execution

Performance Issues

-- Check what tables are consuming memory
SELECT tablename, total_size_bytes / (1024*1024) as size_mb
FROM helios_table_memory_stats
WHERE total_size_bytes > 50*1024*1024
ORDER BY total_size_bytes DESC;

-- Consider:
-- - Compression for large tables
-- - Archiving old data
-- - Increasing cache size

Performance Tips

1. Cache Utilization

   • Views are cached for 5 seconds
   • Repeated queries use cache for fast response
   • Cache automatically invalidates on changes

2. Query Optimization

   • Filter early (WHERE clauses)
   • Limit results with LIMIT
   • Aggregate before returning large results

3. Monitoring Frequency

   • Query system views every 5-10 seconds for monitoring
   • Avoid querying more frequently (uses cache anyway)
   • Use external tools (Prometheus) for frequency > 1/sec

Next Steps

1. Setup Monitoring

   • Create dashboard with preferred tool
   • Set up alerts for key metrics
   • Monitor sync_latency and replication_lag

2. Performance Analysis

   • Identify slow queries in query_history
   • Analyze cache hit ratios
   • Right-size table compression

3. Capacity Planning

   • Track memory_stats trends over time
   • Plan for growth
   • Adjust cache and buffer settings

More Information

• User Guide: V2_3_SYSTEM_VIEWS.md
• Developer Guide: V2_3_SYSTEM_VIEWS_INTEGRATION.md
• Technical Details: V2_3_VIEWS_CODE_STRUCTURE.md

Reference Card

-- List all available system views
SELECT table_name FROM information_schema.views
WHERE table_schema = 'pg_catalog' OR table_schema = 'public'
ORDER BY table_name;

-- Get view schema
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'helios_sync_status'
ORDER BY ordinal_position;

-- Quick status check
SELECT 'Sync Status' as metric FROM helios_sync_status
UNION ALL
SELECT 'Tables' as metric FROM helios_table_memory_stats
UNION ALL
SELECT 'Replicas' as metric FROM pg_stat_replication;

Support

• Check documentation for detailed information
• Review code comments in implementation
• Enable debug logging for troubleshooting
• Report issues with specific view behavior

---

Version: HeliosDB Nano v2.3.0 Last Updated: November 24, 2025 Status: Production Ready