---
title: "PostgreSQL-Compatible System Views Reference"
---

# PostgreSQL-Compatible System Views Reference

## Overview

HeliosDB-Lite provides 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. **Branching and Materialized View Views** - Branches, materialized views, snapshots
4. **Security and Optimizer Views** - SSL stats, authentication, optimizer stats, compression

---

## Core Catalog Views

### 1. pg_tables

Lists all user tables in the database.

**Schema:**
```sql
pg_tables (
    schemaname TEXT,
    tablename TEXT,
    tableowner TEXT,
    tablespace TEXT,
    hasindexes BOOLEAN,
    hasrules BOOLEAN,
    hastriggers BOOLEAN,
    rowsecurity BOOLEAN
)
```

**Example:**
```sql
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:**
```sql
pg_views (
    schemaname TEXT,
    viewname TEXT,
    viewowner TEXT,
    definition TEXT
)
```

**Example:**
```sql
SELECT viewname, definition
FROM pg_views
WHERE schemaname = 'public';
```

**Note:** Regular views are not yet implemented in HeliosDB-Lite. Use `pg_matviews` for materialized views.

---

### 3. pg_indexes

Lists all indexes in the database, including vector indexes.

**Schema:**
```sql
pg_indexes (
    schemaname TEXT,
    tablename TEXT,
    indexname TEXT,
    tablespace TEXT,
    indexdef TEXT
)
```

**Example:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
SELECT datname, encoding, datallowconn
FROM pg_database;
```

**Output:**
```
  datname  | encoding | datallowconn
-----------+----------+--------------
 heliosdb  |        6 | true
```

---

### 6. pg_namespace

Lists database schemas/namespaces.

**Schema:**
```sql
pg_namespace (
    nspname TEXT,       -- Namespace name
    nspowner INT4       -- Owner ID
)
```

**Example:**
```sql
SELECT nspname FROM pg_namespace;
```

**Output:**
```
 nspname
---------
 public
```

---

### 7. pg_class

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

**Schema:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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

---

## Branching and Materialized View Views

### 12. pg_branches

Lists all database branches (HeliosDB extension).

**Schema:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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

---

## Security and Optimizer Views

### 15. pg_stat_ssl

Shows SSL/TLS connection information.

**Schema:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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:**
```sql
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

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

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

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

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

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

```sql
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:

```sql
-- 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-Lite 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)

---

## Troubleshooting

### View Returns Empty Results

Check that the feature is enabled and has data:

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

Check the view name against the catalog of system views, for example:

```sql
SELECT * FROM pg_tables LIMIT 1;
```

If this query fails, confirm you are connected to a HeliosDB Lite server and that the
view name is spelled as listed on this page.

### 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-Lite provides 18 PostgreSQL-compatible system views across four categories:

| Category | Views | Purpose |
|-----------------------|-------|-----------------------------------|
| Core Catalog | 8 | Tables, columns, schemas, types |
| Session & Activity | 3 | Connections, queries, settings |
| Branching & MV | 3 | Branches, matviews, snapshots |
| Security & Optimizer | 4 | SSL, auth, optimizer, compression |

All views are queried with standard SQL.