System Views: Monitoring and Observability

HeliosDB-Lite exposes over 60 system views for monitoring, diagnostics, and introspection. These views are queried with standard SELECT statements and require no special setup. This tutorial organizes them by category with example queries for each.

Prerequisites

HeliosDB-Lite v3.5+

Categories at a Glance

Category	Views	Purpose
Core Catalog	10	Tables, views, indexes, columns, types
Session	1	Active sessions and queries
Statistics	20+	Optimizer, disk, cache, compression
Security	4	Grants, roles, quotas, RLS policies
Features	10+	Branches, snapshots, materialized views
HTAP	3	Workload classification, resource usage
Proxy	12	Connection pools, routing, rate limiting
Storage	6+	Bloom filters, zone maps, indexes
Observability	5+	Audit log, filter advisor, prefetch

Core Catalog Views

pg_tables — List all user tables

SELECT tablename, hasindexes, hastriggers, rowsecurity
FROM pg_tables;

tablename	hasindexes	hastriggers	rowsecurity
orders	true	false	false
products	true	false	false

pg_views — List all views

SELECT viewname, definition FROM pg_views;

pg_indexes — List all indexes

SELECT tablename, indexname, indexdef FROM pg_indexes;

tablename	indexname	indexdef
orders	idx_orders_cust	CREATE INDEX idx_orders_cust ON …

pg_attribute — Column details

SELECT attname, atttypid, attnotnull FROM pg_attribute;

pg_database — Database info

SELECT datname, encoding, datcollate FROM pg_database;

pg_settings — Configuration parameters

SELECT name, setting, short_desc FROM pg_settings WHERE category = 'Storage';

name	setting	short_desc
time_travel_enabled	true	Enables automatic time-travel versioning
wal_sync_mode	flush	WAL synchronization mode

pg_class — All relations

SELECT relname, relkind, reltuples FROM pg_class;

Where relkind: ‘r’ = table, ‘i’ = index, ‘v’ = view, ‘m’ = materialized view.

pg_type — Data types

SELECT typname, typtype, typcategory FROM pg_type;

pg_namespace — Schemas

SELECT nspname FROM pg_namespace;

heliosdb_config — HeliosDB settings

SELECT key, value, description FROM heliosdb_config;

Session and Activity

pg_stat_activity — Active sessions

SELECT pid, usename, state, query, application_name
FROM pg_stat_activity;

pid	usename	state	query	application_name
1001	admin	active	SELECT * FROM pg_stat_activity	helios-repl

Statistics Views

pg_stat_database — Database-wide stats

SELECT datname, xact_commit, xact_rollback, tup_inserted, tup_updated, tup_deleted
FROM pg_stat_database;

pg_stats — Column-level statistics

SELECT tablename, attname, null_frac, n_distinct FROM pg_stats;

Run VACUUM ANALYZE first to populate these statistics.

pg_stat_optimizer — Optimizer performance

SELECT * FROM pg_stat_optimizer;

Shows optimizer rules applied, planning times, and cache effectiveness.

pg_stat_ssl — SSL connection info

SELECT pid, ssl, version, cipher, bits FROM pg_stat_ssl;

pg_stat_replication — Replication status

SELECT * FROM pg_stat_replication;

pg_stat_progress_vacuum — Vacuum progress

SELECT * FROM pg_stat_progress_vacuum;

Useful for monitoring long-running VACUUM operations.

Security Views

helios_grants — Active privileges

SELECT table_name, grantee, privilege, is_grantable
FROM helios_grants;

table_name	grantee	privilege	is_grantable
orders	analyst	SELECT	false
orders	dba	ALL	true

helios_roles — Defined roles

SELECT role_name, options, can_login, is_superuser
FROM helios_roles;

helios_user_quotas — Rate limiting status

SELECT username, requests_per_minute, current_count, window_seconds_remaining
FROM helios_user_quotas;

pg_rls_policies — Row-Level Security policies

SELECT policy_name, table_name, command, using_expr, enabled
FROM pg_rls_policies;

Feature Views

pg_branches — Database branches

SELECT branch_name, parent_name, state, size_bytes FROM pg_branches;

pg_matviews — Materialized views

SELECT matviewname, ispopulated, last_refresh, row_count
FROM pg_matviews;

pg_snapshots — Time-travel snapshots

SELECT snapshot_id, created_at, scn, is_automatic FROM pg_snapshots;

pg_transaction_map / pg_scn_map — ID mappings

SELECT transaction_id, snapshot_timestamp FROM pg_transaction_map;
SELECT scn, snapshot_timestamp FROM pg_scn_map;

HTAP Views

helios_resource_usage — Workload resource consumption

SELECT workload_type, active_queries, memory_mb, cpu_pct
FROM helios_resource_usage;

workload_type	active_queries	memory_mb	cpu_pct
OLTP	12	256.0	15.3
OLAP	2	1024.0	45.7

pg_htap_layout_stats — Dual storage layout

SELECT * FROM pg_htap_layout_stats;

helios_workload_patterns — Detected workload patterns

SELECT * FROM helios_workload_patterns;

Storage and Index Views

helios_bloom_filters — Bloom filter status

SELECT * FROM helios_bloom_filters;

helios_zone_maps — Zone map ranges

SELECT * FROM helios_zone_maps;

helios_index_stats — Index metadata and sizes

SELECT index_name, table_name, column_name, index_type, size_bytes
FROM helios_index_stats;

helios_column_compression — Per-column compression

SELECT * FROM helios_column_compression;

helios_dedup_stats — Deduplication statistics

SELECT * FROM helios_dedup_stats;

helios_prefetch_stats — Prefetch cache

SELECT enabled, window_size, total_requests, cache_hits
FROM helios_prefetch_stats;

Observability Views

helios_audit_log — Audit trail

SELECT * FROM helios_audit_log;

helios_advisor_recommendations — Index and query suggestions

SELECT recommendation_type, target_table, suggestion
FROM helios_advisor_recommendations;

helios_query_history — Recent query performance

SELECT * FROM helios_query_history;

helios_table_memory_stats — Per-table memory usage

SELECT * FROM helios_table_memory_stats;

helios_transaction_stats — Transaction counters

SELECT * FROM helios_transaction_stats;

Proxy Views (when HeliosProxy is active)

These views are populated when the HA proxy feature is enabled.

SELECT * FROM helios_proxy_pools;         -- Connection pool stats
SELECT * FROM helios_proxy_status;        -- Proxy health summary
SELECT * FROM helios_proxy_cache_stats;   -- Query cache hit rates
SELECT * FROM helios_proxy_routing_stats; -- Query routing decisions
SELECT * FROM helios_proxy_lag_stats;     -- Replication lag
SELECT * FROM helios_proxy_rate_limit_stats;  -- Rate limiter metrics
SELECT * FROM helios_proxy_circuit_breaker_stats; -- Circuit breaker state
SELECT * FROM helios_proxy_analytics_stats; -- Query analytics
SELECT * FROM helios_proxy_auth_stats;    -- Authentication metrics
SELECT * FROM helios_proxy_rewriter_stats;-- Query rewriter metrics
SELECT * FROM helios_proxy_plugin_stats;  -- Plugin subsystem
SELECT * FROM helios_proxy_graphql_stats; -- GraphQL endpoint stats

Practical Monitoring Queries

Health check dashboard

-- How many tables and total rows?
SELECT COUNT(*) AS table_count FROM pg_tables;
SELECT datname, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database;

-- Any RLS policies active?
SELECT COUNT(*) AS rls_policies FROM pg_rls_policies WHERE enabled = true;

-- Index coverage
SELECT tablename, COUNT(*) AS index_count FROM pg_indexes GROUP BY tablename;

Security audit

-- Who has what?
SELECT grantee, table_name, privilege FROM helios_grants ORDER BY grantee;

-- Roles with superuser access
SELECT role_name FROM helios_roles WHERE is_superuser = true;

-- Rate limit status
SELECT username, requests_per_minute, current_count FROM helios_user_quotas;

Performance investigation

-- Check column statistics (run VACUUM ANALYZE first)
SELECT tablename, attname, null_frac, n_distinct FROM pg_stats;

-- Optimizer stats
SELECT * FROM pg_stat_optimizer;

-- Index sizes
SELECT index_name, table_name, size_bytes FROM helios_index_stats
ORDER BY size_bytes DESC;

Common Pitfalls

Views return current state — System views are computed on demand. Values like current_count in helios_user_quotas change between queries.
Empty results are normal — Views like pg_stat_replication or helios_proxy_pools return empty results if the corresponding feature (replication, proxy) is not active.
VACUUM ANALYZE populates pg_stats — Column statistics in pg_stats are empty until you run VACUUM ANALYZE on the target tables.
Three system view registries — HeliosDB has main, Phase 3, and Extensions view registries. Some views delegate between registries. If a view returns unexpected results, check which registry handles it.
Case sensitivity — System view names are lowercase. Use lowercase in your SELECT queries: SELECT * FROM pg_tables (not PG_TABLES).