PostgreSQL-Compatible System Views Reference

PostgreSQL-Compatible System Views Reference

Overview

HeliosDB Nano provides comprehensive PostgreSQL-compatible system views (pg_catalog) for introspecting all database features. This document describes all 18 system views, their schemas, and usage examples.

Categories

System views are organized into four categories:

1. Core Catalog Views - Tables, columns, schemas, types
2. Session & Activity Views - Active connections, queries, configuration
3. v2.0 Feature Views - Branches, materialized views, snapshots
4. v2.1 Feature Views - SSL stats, authentication, optimizer stats, compression

---

Core Catalog Views

1. pg_tables

Lists all user tables in the database.

Schema:

pg_tables (
    schemaname TEXT,
    tablename TEXT,
    tableowner TEXT,
    tablespace TEXT,
    hasindexes BOOLEAN,
    hasrules BOOLEAN,
    hastriggers BOOLEAN,
    rowsecurity BOOLEAN
)

Example:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

Output:

 tablename | tableowner
-----------+------------
 users     | heliosdb
 products  | heliosdb

---

2. pg_views

Lists all views in the database (excluding materialized views).

Schema:

pg_views (
    schemaname TEXT,
    viewname TEXT,
    viewowner TEXT,
    definition TEXT
)

Example:

SELECT viewname, definition
FROM pg_views
WHERE schemaname = 'public';

Note: Regular views are not yet implemented in HeliosDB Nano. Use pg_matviews for materialized views.

---

3. pg_indexes

Lists all indexes in the database, including vector indexes.

Schema:

pg_indexes (
    schemaname TEXT,
    tablename TEXT,
    indexname TEXT,
    tablespace TEXT,
    indexdef TEXT
)

Example:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'documents';

Output:

 tablename  |      indexname       |                indexdef
------------+----------------------+-----------------------------------------
 documents  | doc_embedding_idx    | CREATE INDEX doc_embedding_idx ON documents USING hnsw (embedding)

---

4. pg_attribute

Lists all table columns with detailed attributes.

Schema:

pg_attribute (
    attrelid INT4,          -- Table ID
    attname TEXT,           -- Column name
    atttypid INT4,          -- Data type OID
    attnum INT2,            -- Column position
    attlen INT2,            -- Storage length
    attnotnull BOOLEAN,     -- NOT NULL constraint
    atthasdef BOOLEAN       -- Has default value
)

Example:

SELECT attname, atttypid, attnotnull
FROM pg_attribute
WHERE attrelid = (
    SELECT oid FROM pg_class WHERE relname = 'users'
)
ORDER BY attnum;

Output:

 attname | atttypid | attnotnull
---------+----------+------------
 id      |       23 | true
 name    |       25 | false
 email   |       25 | true

---

5. pg_database

Lists database information.

Schema:

pg_database (
    datname TEXT,           -- Database name
    datdba INT4,            -- Owner ID
    encoding INT4,          -- Character encoding
    datcollate TEXT,        -- Collation
    datctype TEXT,          -- Character classification
    datistemplate BOOLEAN,  -- Is template database
    datallowconn BOOLEAN    -- Allow connections
)

Example:

SELECT datname, encoding, datallowconn
FROM pg_database;

Output:

  datname  | encoding | datallowconn
-----------+----------+--------------
 heliosdb  |        6 | true

---

6. pg_namespace

Lists database schemas/namespaces.

Schema:

pg_namespace (
    nspname TEXT,       -- Namespace name
    nspowner INT4       -- Owner ID
)

Example:

SELECT nspname FROM pg_namespace;

Output:

 nspname
---------
 public

---

7. pg_class

Lists all relations (tables, indexes, views, materialized views).

Schema:

pg_class (
    relname TEXT,           -- Relation name
    relnamespace INT4,      -- Namespace ID
    relkind CHAR(1),        -- Relation kind: 'r'=table, 'i'=index, 'm'=matview
    relowner INT4,          -- Owner ID
    relam INT4,             -- Access method
    relpages INT4,          -- Size in pages
    reltuples FLOAT4        -- Number of tuples
)

Example:

SELECT relname, relkind
FROM pg_class
WHERE relkind IN ('r', 'm')
ORDER BY relname;

Output:

   relname    | relkind
--------------+---------
 users        | r
 mv_sales     | m
 products     | r

---

8. pg_type

Lists all data types.

Schema:

pg_type (
    typname TEXT,           -- Type name
    typnamespace INT4,      -- Namespace ID
    typowner INT4,          -- Owner ID
    typlen INT2,            -- Storage length
    typtype CHAR(1),        -- Type category: 'b'=base
    typcategory CHAR(1)     -- General category: 'N'=numeric, 'S'=string, etc.
)

Example:

SELECT typname, typcategory
FROM pg_type
WHERE typcategory = 'N'
ORDER BY typname;

Output:

 typname | typcategory
---------+-------------
 int2    | N
 int4    | N
 int8    | N
 float4  | N
 float8  | N

---

Session & Activity Views

9. pg_stat_activity

Shows information about current database sessions and active queries.

Schema:

pg_stat_activity (
    datid INT4,                 -- Database ID
    datname TEXT,               -- Database name
    pid INT4,                   -- Process/session ID
    usesysid INT4,              -- User system ID
    usename TEXT,               -- Username
    application_name TEXT,      -- Client application/protocol
    client_addr TEXT,           -- Client IP address
    client_port INT4,           -- Client port
    backend_start TIMESTAMPTZ,  -- Connection start time
    state_change TIMESTAMPTZ,   -- Last state change
    state TEXT,                 -- Session state: active, idle, etc.
    query TEXT                  -- Current or last query
)

Example:

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state = 'active';

Output:

 pid  | usename  | state  |              query
------+----------+--------+---------------------------------
 1234 | appuser  | active | SELECT * FROM products WHERE...
 1235 | analyst  | active | REFRESH MATERIALIZED VIEW...

---

10. pg_stat_database

Shows database-wide statistics.

Schema:

pg_stat_database (
    datid INT4,             -- Database ID
    datname TEXT,           -- Database name
    numbackends INT4,       -- Active connections
    xact_commit INT8,       -- Committed transactions
    xact_rollback INT8,     -- Rolled back transactions
    blks_read INT8,         -- Blocks read from disk
    blks_hit INT8,          -- Blocks found in cache
    tup_returned INT8,      -- Rows returned by queries
    tup_fetched INT8,       -- Rows fetched by queries
    tup_inserted INT8,      -- Rows inserted
    tup_updated INT8,       -- Rows updated
    tup_deleted INT8        -- Rows deleted
)

Example:

SELECT datname, numbackends, xact_commit, xact_rollback
FROM pg_stat_database;

Output:

  datname  | numbackends | xact_commit | xact_rollback
-----------+-------------+-------------+---------------
 heliosdb  |          12 |       45678 |            23

---

11. pg_settings

Shows current database configuration settings.

Schema:

pg_settings (
    name TEXT,          -- Setting name
    setting TEXT,       -- Current value
    unit TEXT,          -- Unit of measurement
    category TEXT,      -- Configuration category
    short_desc TEXT,    -- Short description
    context TEXT,       -- When setting can be changed
    vartype TEXT,       -- Variable type
    source TEXT,        -- Configuration source
    min_val TEXT,       -- Minimum value (if applicable)
    max_val TEXT        -- Maximum value (if applicable)
)

Example:

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE category = 'Write-Ahead Logging'
ORDER BY name;

Output:

       name        | setting | unit |           short_desc
-------------------+---------+------+---------------------------------
 wal_enabled       | true    |      | Enables write-ahead logging
 wal_sync_mode     | sync    |      | WAL synchronization mode

Common Settings:

• wal_enabled - Write-ahead logging enabled
• time_travel_enabled - Automatic time-travel versioning
• query_timeout_ms - Maximum query execution time
• cache_size - Memory cache size

---

v2.0 Feature Views

12. pg_branches

Lists all database branches (HeliosDB extension).

Schema:

pg_branches (
    branch_id INT8,             -- Branch ID
    branch_name TEXT,           -- Branch name
    parent_id INT8,             -- Parent branch ID
    parent_name TEXT,           -- Parent branch name
    created_at TIMESTAMPTZ,     -- Creation timestamp
    fork_point_lsn INT8,        -- Fork point log sequence number
    state TEXT,                 -- Branch state: Active, Merged, Dropped
    size_bytes INT8,            -- Storage size in bytes
    num_commits INT8            -- Number of commits/snapshots
)

Example:

SELECT branch_name, parent_name, state, size_bytes
FROM pg_branches
ORDER BY created_at;

Output:

  branch_name   | parent_name | state  | size_bytes
----------------+-------------+--------+------------
 main           | NULL        | Active |   10485760
 feature_auth   | main        | Active |    2097152
 hotfix_bug123  | main        | Merged |          0

Use Cases:

• Monitor branch hierarchy
• Track branch storage usage
• Identify merged or stale branches

---

13. pg_matviews

Lists all materialized views with status and metadata.

Schema:

pg_matviews (
    schemaname TEXT,            -- Schema name
    matviewname TEXT,           -- Materialized view name
    matviewowner TEXT,          -- Owner
    definition TEXT,            -- SQL query definition
    ispopulated BOOLEAN,        -- Has been refreshed
    created_at TIMESTAMPTZ,     -- Creation time
    last_refresh TIMESTAMPTZ,   -- Last refresh time
    row_count INT8,             -- Number of rows
    refresh_strategy TEXT,      -- Refresh strategy: manual, auto
    base_tables TEXT            -- Dependent base tables
)

Example:

SELECT
    matviewname,
    ispopulated,
    last_refresh,
    row_count,
    refresh_strategy
FROM pg_matviews
ORDER BY matviewname;

Output:

   matviewname    | ispopulated |      last_refresh       | row_count | refresh_strategy
------------------+-------------+-------------------------+-----------+------------------
 mv_daily_sales   | true        | 2025-11-23 08:00:00+00 |      1250 | manual
 mv_user_summary  | false       | NULL                   |      NULL | manual

Use Cases:

• Check which materialized views need refreshing
• Monitor materialized view freshness
• Track materialized view size

---

14. pg_snapshots

Lists all time-travel snapshots (HeliosDB extension).

Schema:

pg_snapshots (
    snapshot_id INT8,           -- Snapshot ID
    created_at TIMESTAMPTZ,     -- Creation timestamp
    scn INT8,                   -- System Change Number
    transaction_id INT8,        -- Transaction ID
    description TEXT,           -- Snapshot description
    size_bytes INT8,            -- Storage size
    is_automatic BOOLEAN        -- Auto-created by time-travel
)

Example:

SELECT
    snapshot_id,
    created_at,
    description,
    is_automatic
FROM pg_snapshots
ORDER BY created_at DESC
LIMIT 10;

Output:

 snapshot_id |      created_at         |     description      | is_automatic
-------------+-------------------------+----------------------+--------------
        1523 | 2025-11-23 10:15:00+00 | auto_snapshot_1523   | true
        1520 | 2025-11-23 10:00:00+00 | pre_migration_backup | false
        1515 | 2025-11-23 09:45:00+00 | auto_snapshot_1515   | true

Use Cases:

• List available snapshots for AS OF queries
• Monitor automatic snapshot creation
• Track storage usage by snapshots

---

v2.1 Feature Views

15. pg_stat_ssl

Shows SSL/TLS connection information.

Schema:

pg_stat_ssl (
    pid INT4,               -- Session ID
    ssl BOOLEAN,            -- SSL enabled for connection
    version TEXT,           -- SSL/TLS version
    cipher TEXT,            -- Cipher suite
    bits INT4,              -- Encryption strength in bits
    client_dn TEXT,         -- Client certificate DN
    client_serial TEXT,     -- Client certificate serial
    issuer_dn TEXT          -- Certificate issuer DN
)

Example:

SELECT pid, ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE ssl = true;

Output:

 pid  | ssl  |  version  |        cipher         | bits
------+------+-----------+-----------------------+------
 1234 | true | TLSv1.3   | TLS_AES_256_GCM_SHA384| 256
 1235 | true | TLSv1.3   | TLS_AES_128_GCM_SHA256| 128

Note: SSL/TLS functionality is planned for future release.

---

16. pg_authid

Lists authentication identities (users and roles).

Schema:

pg_authid (
    rolname TEXT,               -- Role/user name
    rolsuper BOOLEAN,           -- Is superuser
    rolinherit BOOLEAN,         -- Inherits privileges
    rolcreaterole BOOLEAN,      -- Can create roles
    rolcreatedb BOOLEAN,        -- Can create databases
    rolcanlogin BOOLEAN,        -- Can login
    rolconnlimit INT4,          -- Connection limit (-1 = unlimited)
    rolvaliduntil TIMESTAMPTZ   -- Password expiration
)

Example:

SELECT rolname, rolsuper, rolcanlogin, rolconnlimit
FROM pg_authid
ORDER BY rolname;

Output:

  rolname  | rolsuper | rolcanlogin | rolconnlimit
-----------+----------+-------------+--------------
 appuser   | false    | true        |           10
 heliosdb  | true     | true        |           -1
 readonly  | false    | true        |           50

Note: Advanced authentication features are planned for future releases.

---

17. pg_stat_optimizer

Shows query optimizer statistics (HeliosDB extension).

Schema:

pg_stat_optimizer (
    query_hash TEXT,            -- Query hash/fingerprint
    plan_type TEXT,             -- Execution plan type
    execution_count INT8,       -- Number of executions
    total_time_ms FLOAT8,       -- Total execution time
    avg_time_ms FLOAT8,         -- Average execution time
    min_time_ms FLOAT8,         -- Minimum execution time
    max_time_ms FLOAT8,         -- Maximum execution time
    rows_estimate INT8,         -- Estimated rows
    rows_actual INT8,           -- Actual rows processed
    last_execution TIMESTAMPTZ  -- Last execution time
)

Example:

SELECT
    query_hash,
    execution_count,
    avg_time_ms,
    rows_estimate,
    rows_actual
FROM pg_stat_optimizer
ORDER BY total_time_ms DESC
LIMIT 10;

Note: Optimizer statistics tracking is planned for future release.

---

18. pg_compression_stats

Shows compression statistics per table (HeliosDB extension).

Schema:

pg_compression_stats (
    schemaname TEXT,            -- Schema name
    tablename TEXT,             -- Table name
    compression_type TEXT,      -- Compression algorithm: FSST, ALP, ZSTD
    uncompressed_bytes INT8,    -- Original size
    compressed_bytes INT8,      -- Compressed size
    compression_ratio FLOAT8,   -- Compression ratio
    num_chunks INT8,            -- Number of compressed chunks
    avg_chunk_size INT8,        -- Average chunk size
    last_updated TIMESTAMPTZ    -- Last statistics update
)

Example:

SELECT
    tablename,
    compression_type,
    compression_ratio,
    (uncompressed_bytes - compressed_bytes) / 1024 / 1024 AS saved_mb
FROM pg_compression_stats
ORDER BY compression_ratio DESC;

Output:

  tablename  | compression_type | compression_ratio | saved_mb
-------------+------------------+-------------------+----------
 text_logs   | FSST             |              8.2  |      156
 metrics     | ALP              |              4.5  |       89
 documents   | ZSTD             |              3.1  |       45

Use Cases:

• Monitor compression effectiveness
• Identify tables that would benefit from compression
• Track storage savings

---

Usage Examples

Example 1: Find All Tables and Their Sizes

SELECT
    t.tablename,
    c.reltuples::bigint AS estimated_rows,
    pg_size_pretty(c.relpages * 8192) AS table_size
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename
WHERE t.schemaname = 'public'
ORDER BY c.relpages DESC;

Example 2: Monitor Active Sessions

SELECT
    pid,
    usename,
    application_name,
    state,
    query,
    now() - backend_start AS connection_age
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY backend_start;

Example 3: Check Materialized View Freshness

SELECT
    matviewname,
    ispopulated,
    CASE
        WHEN last_refresh IS NULL THEN 'Never refreshed'
        WHEN now() - last_refresh > interval '1 day' THEN 'Stale (>1d)'
        WHEN now() - last_refresh > interval '1 hour' THEN 'Outdated (>1h)'
        ELSE 'Fresh'
    END AS freshness,
    last_refresh
FROM pg_matviews
ORDER BY last_refresh NULLS FIRST;

Example 4: Branch Storage Usage

SELECT
    branch_name,
    pg_size_pretty(size_bytes) AS size,
    state,
    created_at
FROM pg_branches
ORDER BY size_bytes DESC;

Example 5: Compression Effectiveness Report

SELECT
    tablename,
    compression_type,
    pg_size_pretty(uncompressed_bytes) AS original_size,
    pg_size_pretty(compressed_bytes) AS compressed_size,
    round(compression_ratio::numeric, 2) AS ratio,
    pg_size_pretty(uncompressed_bytes - compressed_bytes) AS saved
FROM pg_compression_stats
WHERE compression_ratio > 1.0
ORDER BY compression_ratio DESC;

Example 6: Database Configuration Audit

SELECT
    name,
    setting,
    unit,
    category,
    context
FROM pg_settings
WHERE category IN ('Write-Ahead Logging', 'Time Travel', 'Query Execution')
ORDER BY category, name;

---

Integration with SQL Executor

System views are automatically recognized by the SQL executor and query planner. No special syntax is required:

-- Works like any regular table
SELECT * FROM pg_tables WHERE tablename LIKE 'user%';

-- Can be joined with other views
SELECT
    t.tablename,
    c.compression_ratio
FROM pg_tables t
LEFT JOIN pg_compression_stats c ON t.tablename = c.tablename
ORDER BY c.compression_ratio DESC NULLS LAST;

-- Can be used in subqueries
SELECT tablename
FROM pg_tables
WHERE tablename IN (
    SELECT matviewname FROM pg_matviews WHERE ispopulated = false
);

---

Performance Considerations

1. Lightweight Queries: System views query metadata, not data, so they’re fast
2. Real-Time Data: All views return real-time information from the catalog
3. No Caching: Results are computed on each query (no stale data)
4. Efficient Metadata Access: Uses optimized catalog lookups

---

Compatibility Notes

PostgreSQL Compatibility

HeliosDB Nano system views are designed to be PostgreSQL-compatible:

• Column names match PostgreSQL conventions
• Data types map to PostgreSQL types
• View names follow pg_* naming convention
• Behavior mimics PostgreSQL where applicable

Differences from PostgreSQL

1. Simplified OIDs: Object IDs are simplified (sequential integers)
2. Limited Metrics: Some statistics (like tuple counts) are not yet tracked
3. Custom Extensions: HeliosDB-specific views (branches, compression) have no PostgreSQL equivalent
4. Single Database: Only one database per instance (no catalog-wide views)

---

Future Enhancements

Planned improvements for system views:

1. Query Performance Tracking - Populate pg_stat_optimizer with real metrics
2. Enhanced Statistics - Track tuple counts, block hits/misses
3. SSL/TLS Integration - Populate pg_stat_ssl when encryption is implemented
4. Role-Based Access - Filter views based on user permissions
5. Partition Information - Add pg_partitions for future partitioning support
6. Index Usage Stats - Add pg_stat_user_indexes for index efficiency
7. Replication Views - Add pg_stat_replication when replication is added

---

Troubleshooting

View Returns Empty Results

Check that the feature is enabled and has data:

-- Check if tables exist
SELECT COUNT(*) FROM pg_tables;

-- Check if branches are created
SELECT COUNT(*) FROM pg_branches;

-- Check if sessions are active
SELECT COUNT(*) FROM pg_stat_activity;

View Not Found

Ensure the system view registry is initialized:

use heliosdb_nano::sql::SystemViewRegistry;

let registry = SystemViewRegistry::new();
assert!(registry.is_system_view("pg_tables"));

Incorrect Data in Views

System views reflect real-time catalog state. If data seems incorrect:

1. Verify the underlying catalog is correct
2. Check that the storage engine is properly initialized
3. Ensure no concurrent modifications are interfering

---

Summary

HeliosDB Nano provides 18 comprehensive PostgreSQL-compatible system views across four categories:

Category	Views	Purpose
Core Catalog	8	Tables, columns, schemas, types
Session & Activity	3	Connections, queries, settings
v2.0 Features	3	Branches, matviews, snapshots
v2.1 Features	4	SSL, auth, optimizer, compression

All views are accessible via standard SQL queries and integrate seamlessly with the query executor and planner.