---
title: "HeliosDB-Lite: HeliosCore & HTAP Features Guide"
---

# HeliosDB-Lite: HeliosCore & HTAP Features Guide

HeliosDB-Lite is a Rust database engine with MVCC, ACID transactions, full SQL support, vector search, encryption, branching, and HTAP capabilities. This guide covers every major feature with practical SQL examples.

---

## Table of Contents

1. [Columnar Storage](#1-columnar-storage)
2. [HTAP Workload Classification](#2-htap-workload-classification)
3. [Smart Scan / Predicate Pushdown](#3-smart-scan--predicate-pushdown)
4. [Parallel Execution](#4-parallel-execution)
5. [Materialized Views](#5-materialized-views)
6. [Partitioning](#6-partitioning)
7. [EXPLAIN ANALYZE](#7-explain-analyze)
8. [VACUUM / Maintenance](#8-vacuum--maintenance)
9. [Backup & Restore](#9-backup--restore)
10. [Point-in-Time Recovery (PITR)](#10-point-in-time-recovery-pitr)
11. [GRANT/REVOKE & RBAC](#11-grantrevoke--rbac)
12. [Row-Level Security (RLS)](#12-row-level-security-rls)
13. [Rate Limiting](#13-rate-limiting)
14. [SSL Enforcement](#14-ssl-enforcement)
15. [Plan Cache & Result Cache](#15-plan-cache--result-cache)
16. [Time-Travel Queries](#16-time-travel-queries)
17. [Database Branching](#17-database-branching)
18. [Full-Text Search](#18-full-text-search)
19. [Vector Search](#19-vector-search)
20. [Transparent Data Encryption (TDE)](#20-transparent-data-encryption-tde)
21. [Triggers & PL/pgSQL](#21-triggers--plpgsql)
22. [Prepared Statements](#22-prepared-statements)
23. [Transactions & Savepoints](#23-transactions--savepoints)
24. [UPSERT (ON CONFLICT)](#24-upsert-on-conflict)
25. [Window Functions](#25-window-functions)
26. [JSONB Support](#26-jsonb-support)
27. [Common Table Expressions (CTEs)](#27-common-table-expressions-ctes)
28. [System Views](#28-system-views)
29. [Connection Protocols](#29-connection-protocols)
30. [Graph Adjacency Lists](#30-graph-adjacency-lists)
31. [BM25 & Hybrid Search](#31-bm25--hybrid-search)
32. [Arena Allocation](#32-arena-allocation)
33. [Compiled Query Plans](#33-compiled-query-plans)
34. [SQL Cursors](#34-sql-cursors)
35. [Metrics & Health Endpoints](#35-metrics--health-endpoints)

---

## 1. Columnar Storage

HeliosDB supports per-column storage mode selection, allowing HTAP workloads to mix row-oriented (OLTP) and column-oriented (OLAP) storage within the same table.

**Storage modes available:**
- `DEFAULT` -- Standard row-oriented storage (best for OLTP)
- `DICTIONARY` -- Dictionary-encoded for low-cardinality strings (Response Codes, enums)
- `CONTENT_ADDRESSED` -- Hash-based dedup for large values (documents, blobs)
- `COLUMNAR` -- Column-grouped storage for analytics (aggregations, range scans)

### Creating Tables with Storage Modes

```sql
CREATE TABLE analytics (
    id INT PRIMARY KEY,
    status TEXT STORAGE DICTIONARY,
    bio TEXT STORAGE CONTENT_ADDRESSED,
    metric FLOAT8 STORAGE COLUMNAR
);
```

### Altering Column Storage on Existing Tables

```sql
ALTER TABLE events ALTER COLUMN payload SET STORAGE COLUMNAR;
```

**What happens:** HeliosDB migrates existing data to the new storage layout and logs the change. The EXPLAIN output reflects the storage mode.

### EXPLAIN Reflects Columnar Scan

When all columns in a table use `COLUMNAR` mode, EXPLAIN shows `Columnar Scan` instead of `Seq Scan`:

```sql
CREATE TABLE metrics (
    ts TIMESTAMP STORAGE COLUMNAR,
    value FLOAT8 STORAGE COLUMNAR,
    label TEXT STORAGE COLUMNAR
);

EXPLAIN SELECT * FROM metrics;
```

Example output:
```
Columnar Scan on metrics
  Output: ts, value, label
```

For partially columnar tables, the scan type remains `Seq Scan` but the storage mode is tracked per column.

---

## 2. HTAP Workload Classification

Every query is automatically classified as OLTP, OLAP, or HTAP based on plan features (point queries, aggregations, row access patterns). This classification drives resource allocation and appears in EXPLAIN output.

### Viewing Classification

```sql
EXPLAIN SELECT * FROM users WHERE id = 42;
```

Example output:
```
Filtered Scan on users
  Filter: id = 42
Workload Classification
  Type             : OLTP
  Confidence       : 90%
  OLTP Score       : 0.85
  OLAP Score       : 0.15
  Recommended Store: Row
```

```sql
EXPLAIN SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;
```

Example output:
```
Aggregate
  Group By: department
  -> Seq Scan on employees
Workload Classification
  Type             : OLAP
  Confidence       : 95%
  OLTP Score       : 0.10
  OLAP Score       : 0.90
  Recommended Store: Column
```

### Resource Management

The resource manager allocates separate OLTP and OLAP quotas. OLTP queries receive lower-latency paths; OLAP queries may use parallel execution and larger memory budgets. Classification is automatic -- no user intervention required.

---

## 3. Smart Scan / Predicate Pushdown

The optimizer pushes filter predicates down to the storage layer, creating filtered scan nodes that evaluate conditions during the scan rather than after reading all rows.

### How It Works

```sql
SELECT * FROM orders WHERE status = 'pending' AND amount > 1000;
```

Without pushdown:
```
Filter (status = 'pending' AND amount > 1000)
  -> Seq Scan on orders
```

With pushdown (automatic):
```
Filtered Scan on orders
  Storage Filter: status = 'pending' AND amount > 1000
```

**Supported pushdown predicates:** `=`, `!=`, `<`, `<=`, `>`, `>=`, `BETWEEN`, `LIKE`, `IS NULL`, `IS NOT NULL`, `IN`, `AND`, `OR`.

### Projection Pushdown

The optimizer also pushes column projections, reading only the requested columns from storage:

```sql
SELECT name, email FROM users WHERE active = true;
```

The storage layer reads only the `name`, `email`, and `active` columns, skipping all others.

### Aggregate Pushdown

For simple aggregations, the storage layer can compute results directly:

```sql
SELECT COUNT(*) FROM large_table;
```

The HeliosCore storage tier computes the count during the scan without materializing rows.

---

## 4. Parallel Execution

HeliosDB uses rayon-based work-stealing parallelism for large scans, hash joins, aggregations, and sorting.

### Automatic Parallel Scan

Large tables using default storage mode automatically use parallel scanning:

```sql
-- Automatic: no configuration needed for large tables
SELECT COUNT(*) FROM large_table;
```

The parallel scanner distributes data across rayon worker threads, achieves up to 2.3x scaling, and supports predicate filtering during the parallel phase.

### Parallel Operations

| Operation | Parallelism | Trigger |
|-----------|-------------|---------|
| Table Scan | Parallel scan | Automatic (large tables) |
| Hash Join | Parallel build + probe | Automatic |
| Aggregate | Parallel partial aggregation | With parallel scan |
| Sort | Parallel merge sort | With parallel scan |

### Monitoring

```sql
SELECT * FROM pg_parallel_exec_stats;
```

Example output:
```
 total_scans | parallel_scans | sequential_scans | tuples_scanned | avg_parallelism | peak_throughput
 ----------- | -------------- | ---------------- | -------------- | --------------- | ---------------
 1024        | 847            | 177              | 52000000       | 3.7             | 912000.0
```

---

## 5. Materialized Views

Materialized views store precomputed query results for fast reads. The optimizer can transparently rewrite queries to read from the materialized view data table instead of the base tables.

### Creating and Refreshing

```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    SUM(amount) AS total,
    COUNT(*) AS order_count
FROM orders
GROUP BY date_trunc('month', order_date);
```

```sql
-- Refresh with latest data
REFRESH MATERIALIZED VIEW monthly_sales;
```

### Transparent Query Rewrite

After creating the materialized view, the optimizer may rewrite matching queries to use it:

```sql
-- User writes:
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY date_trunc('month', order_date);

-- Optimizer rewrites to:
-- Seq Scan on __mv_monthly_sales
```

This is automatic. Use EXPLAIN to verify rewrite:

```sql
EXPLAIN SELECT date_trunc('month', order_date) AS month, SUM(amount)
FROM orders GROUP BY date_trunc('month', order_date);
```

### Listing Materialized Views

```sql
SELECT * FROM pg_matviews;
```

### Staleness Monitoring

```sql
SELECT * FROM pg_mv_staleness;
```

Returns the last refresh time and whether the underlying data has changed since.

---

## 6. Partitioning

HeliosDB supports HASH, RANGE, and LIST partitioning with partition-aware scanning and pruning in the optimizer.

### Partition by Hash

```sql
CREATE TABLE events (
    id INT,
    created_at TIMESTAMP,
    data TEXT
) PARTITION BY HASH (id);
```

### Partition by Range

```sql
CREATE TABLE logs (
    id INT,
    log_date DATE,
    message TEXT
) PARTITION BY RANGE (log_date);
```

### Partition by List

```sql
CREATE TABLE orders (
    id INT,
    region TEXT,
    amount FLOAT8
) PARTITION BY LIST (region);
```

### Partition Pruning

The optimizer prunes irrelevant partitions during query planning:

```sql
-- Only scans the partition containing region = 'EU'
SELECT * FROM orders WHERE region = 'EU';
```

---

## 7. EXPLAIN ANALYZE

HeliosDB provides query plan introspection with multiple formats and extensions beyond standard PostgreSQL.

### Basic EXPLAIN

```sql
EXPLAIN SELECT * FROM users WHERE age > 25;
```

Example output:
```
Filtered Scan on users
  Storage Filter: age > 25
  Estimated Rows: 150
  Estimated Cost: 25.0
Workload Classification
  Type             : OLAP
  Confidence       : 80%
  OLTP Score       : 0.30
  OLAP Score       : 0.70
  Recommended Store: Column
```

### EXPLAIN ANALYZE (with execution)

Runs the query and reports actual row counts and timing:

```sql
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
```

Example output:
```
Filtered Scan on users (actual rows=147, time=1.234ms)
  Storage Filter: age > 25
  Estimated Rows: 150
Workload Classification
  Type             : OLAP
  Confidence       : 80%
```

### Output Formats

```sql
-- JSON format
EXPLAIN (FORMAT JSON) SELECT * FROM users;

-- YAML format
EXPLAIN (FORMAT YAML) SELECT * FROM users;

-- Tree format
EXPLAIN (FORMAT TREE) SELECT * FROM users;

-- Text format (default)
EXPLAIN (FORMAT TEXT) SELECT * FROM users;
```

### Full Options

```sql
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM users WHERE id = 1;
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users;
```

### HeliosDB-Specific Extensions

```sql
-- Storage layer analysis (bloom filters, zone maps, compression)
EXPLAIN (STORAGE) SELECT * FROM orders;

-- AI-powered explanation
EXPLAIN (AI) SELECT * FROM users WHERE status = 'active';

-- Why-Not analysis: why certain optimizations were not applied
EXPLAIN (ANALYZE, STORAGE, WHY_NOT) SELECT * FROM orders;

-- Trace mode: full lifecycle tracing
EXPLAIN (TRACE) SELECT * FROM users;
```

---

## 8. VACUUM / Maintenance

VACUUM reclaims storage from deleted/updated rows and optionally updates table statistics.

### Basic VACUUM

```sql
-- Clean expired version data and empty tombstones across all tables
VACUUM;
```

Example output:
```
 command | versions_removed | tombstones_removed | bytes_freed | duration_ms | is_full | is_analyze
 ------- | ---------------- | ------------------ | ----------- | ----------- | ------- | ----------
 VACUUM  | 1203             | 45                 | 98304       | 12          | false   | false
```

### VACUUM FULL

Aggressive cleanup pass:

```sql
VACUUM FULL;
```

### VACUUM ANALYZE

Clean up and update statistics for the query optimizer:

```sql
-- All tables
VACUUM ANALYZE;

-- Specific table
VACUUM ANALYZE users;
```

When `ANALYZE` is included, HeliosDB scans each table to compute column histograms and cardinality estimates used by the optimizer.

### Maintenance Scheduler

HeliosDB includes a maintenance scheduler that can automatically dispatch:
- **Analyze** -- update statistics
- **Vacuum** -- reclaim space
- **Checkpoint** -- flush WAL to stable storage
- **Compaction** -- merge storage segments

---

## 9. Backup & Restore

HeliosDB provides SQL-level backup and restore with support for full and incremental backups.

### Full Backup

```sql
BACKUP DATABASE;
```

Example output:
```
 command | backup_id        | type | keys  | size_bytes | path                          | duration_ms
 ------- | ---------------- | ---- | ----- | ---------- | ----------------------------- | -----------
 BACKUP  | bk_1711234567890 | full | 15234 | 2457600    | /tmp/bk_1711234567890.hldb    | 45
```

### Incremental Backup

Only backs up data changed since the last backup:

```sql
BACKUP DATABASE INCREMENTAL;
```

### Backup to Specific Path

```sql
BACKUP DATABASE TO '/backups/mydb_20260324.hldb';
```

### Restore from Backup

```sql
RESTORE DATABASE FROM 'bk_1711234567890';
```

### Restore to a New Branch

Non-destructive restore -- creates a branch with the backup data:

```sql
RESTORE DATABASE FROM 'bk_1711234567890' TO BRANCH 'recovery';
```

### Incremental Chain Restore

When restoring an incremental backup, HeliosDB automatically walks the backup chain back to the base full backup and applies all deltas in order.

---

## 10. Point-in-Time Recovery (PITR)

Fork the database state from any past timestamp into a new branch.

### Recovery Syntax

```sql
RECOVER DATABASE TO TIMESTAMP '2026-03-21T10:00:00' AS BRANCH 'before_incident';
```

**What happens:**
1. Creates a new branch named `before_incident`
2. Scans every user table at the specified MVCC snapshot timestamp
3. Writes the historical rows into the new branch
4. Original data remains untouched

Example output:
```
 command | branch           | timestamp      | tables_recovered | rows_recovered | duration_ms
 ------- | ---------------- | -------------- | ---------------- | -------------- | -----------
 RECOVER | before_incident  | 1711018800000  | 8                | 45230          | 234
```

### Auto-Named Branch

If no branch name is specified, HeliosDB generates one:

```sql
RECOVER DATABASE TO TIMESTAMP '2026-03-20T12:00:00';
-- Creates branch: pitr_1710936000000
```

---

## 11. GRANT/REVOKE & RBAC

HeliosDB implements a full role-based access control system with table-level and column-level privilege grants, role hierarchy, and `WITH GRANT OPTION`.

### Creating Roles

```sql
CREATE ROLE analyst WITH LOGIN;
CREATE ROLE manager WITH LOGIN;
```

### Granting Privileges

```sql
-- Table-level
GRANT SELECT ON sales TO analyst;
GRANT INSERT, UPDATE ON sales TO manager;

-- All privileges
GRANT ALL ON orders TO manager;

-- With grant option (can re-grant to others)
GRANT SELECT ON reports TO manager WITH GRANT OPTION;
```

### Column-Level Grants

```sql
GRANT SELECT (name, email) ON users TO analyst;
```

### Role Hierarchy

```sql
GRANT analyst TO alice;   -- alice inherits analyst's privileges
GRANT manager TO bob;
```

### Revoking Privileges

```sql
REVOKE INSERT ON sales FROM analyst;
REVOKE ALL ON orders FROM manager;
```

### Introspection

```sql
-- View all grants
SELECT * FROM helios_grants;
```

Example output:
```
 table_name | grantee  | privilege | with_grant_option
 ---------- | -------- | --------- | -----------------
 sales      | analyst  | SELECT    | false
 sales      | manager  | INSERT    | false
 sales      | manager  | UPDATE    | false
 reports    | manager  | SELECT    | true
```

```sql
-- View all roles
SELECT * FROM helios_roles;
```

Example output:
```
 role_name | can_login | parent_roles   | rate_limit | ssl_required
 --------- | --------- | -------------- | ---------- | ------------
 analyst   | true      |                | 0          | false
 manager   | true      |                | 0          | false
 alice     | true      | analyst        | 0          | false
```

### Privilege Enforcement

Privileges are checked on every `query()` and `execute()` call. A user without `SELECT` on a table receives:
```
permission denied: SELECT privilege required on table 'sales' for user 'guest'
```

---

## 12. Row-Level Security (RLS)

RLS restricts which rows a user can see based on policy conditions.

### Enabling RLS

```sql
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
```

### Creating Policies

```sql
CREATE POLICY user_orders ON orders
FOR SELECT
USING (user_id = current_user);
```

This means each user can only see their own orders.

### Viewing Policies

```sql
SELECT * FROM pg_rls_policies;
```

Example output:
```
 table_name | policy_name | command | roles  | using_expr
 ---------- | ----------- | ------- | ------ | ----------------------------
 orders     | user_orders | SELECT  | public | (user_id = current_user)
```

### Dropping Policies

```sql
DROP POLICY user_orders ON orders;
```

---

## 13. Rate Limiting

Per-role request rate limiting prevents abuse and ensures fair resource distribution.

### Creating a Rate-Limited Role

```sql
CREATE ROLE api_user WITH LOGIN RATE_LIMIT 100;
```

This limits `api_user` to 100 requests per minute.

### Viewing Rate Limits

```sql
SELECT * FROM helios_user_quotas;
```

Example output:
```
 role_name | rate_limit | requests_used | window_start
 --------- | ---------- | ------------- | ----------------------
 api_user  | 100        | 47            | 2026-03-24T10:00:00Z
```

When the limit is exceeded, requests return:
```
rate limit exceeded: api_user is limited to 100 requests per minute
```

---

## 14. SSL Enforcement

Roles can require encrypted connections, rejecting any plaintext connection attempt.

### Creating an SSL-Required Role

```sql
CREATE ROLE secure_user WITH LOGIN SSL_REQUIRED;
```

### Behavior

When `secure_user` connects without TLS, the connection is refused:
```
SSL required: user 'secure_user' must connect via TLS
```

### Monitoring SSL Connections

```sql
SELECT * FROM pg_stat_ssl;
```

Example output:
```
 pid | ssl | version | cipher          | bits | compression
 --- | --- | ------- | --------------- | ---- | -----------
 1   | t   | TLSv1.3 | TLS_AES_256_GCM | 256  | off
```

---

## 15. Plan Cache & Result Cache

HeliosDB employs a 3-tier caching system to accelerate repeated queries.

### Tier 1: Plan Cache

Repeated SQL queries skip parsing and planning. The cache is keyed by a hash of the SQL text. DDL statements (CREATE, ALTER, DROP) automatically invalidate the plan cache.

### Tier 2: Result Cache

Query results are cached and returned directly for identical queries against unchanged data. DML operations (INSERT, UPDATE, DELETE) invalidate relevant cache entries:

- `INSERT INTO users` invalidates caches for the `users` table
- `REFRESH MATERIALIZED VIEW` invalidates caches for the base and MV tables
- DDL operations invalidate all caches

### Tier 3: Statistics Cache

Table statistics (histograms, cardinality estimates) are cached and invalidated on relevant DML.

### Cache Behavior

All caching is automatic and transparent. No SQL interface is needed. The caches are consulted on every `query()` call:

```
query("SELECT * FROM users WHERE id = 1")
  -> Check result cache (hit? return immediately)
  -> Check plan cache (hit? skip parse+plan)
  -> Execute and store results in cache
```

---

## 16. Time-Travel Queries

Query the state of any table at a past point in time using MVCC snapshots.

### Syntax

```sql
SELECT * FROM users AS OF TIMESTAMP '2026-03-20T12:00:00';
```

**What happens:** HeliosDB reads the MVCC version chain and returns the row versions visible at the specified timestamp.

### Comparing States

```sql
-- What did the data look like yesterday?
SELECT * FROM orders AS OF TIMESTAMP '2026-03-23T00:00:00';

-- Current data for comparison
SELECT * FROM orders;
```

### Use Cases

- Audit: "What was this row before it was updated?"
- Debugging: "What did the data look like when the bug was reported?"
- Compliance: Prove the state of data at a regulatory checkpoint

---

## 17. Database Branching

HeliosDB supports Git-like branching of the entire database state, enabling safe experimentation and parallel development.

### Creating a Branch

```sql
CREATE BRANCH dev;
```

Creates a copy-on-write branch from the current state. Data is shared until modified.

### Querying a Branch

```sql
SELECT * FROM users ON BRANCH dev;
```

### Merging Branches

```sql
MERGE BRANCH dev INTO main;
```

Applies changes from `dev` back into `main`.

### Dropping a Branch

```sql
DROP BRANCH old_feature;
```

### Listing Branches

```sql
SELECT * FROM pg_database_branches;
```

Example output:
```
 branch_name | branch_id | parent_id | created_at
 ----------- | --------- | --------- | ----------------------
 main        | 1         |           | 2026-01-01T00:00:00Z
 dev         | 2         | 1         | 2026-03-24T10:00:00Z
```

### Branch + Time-Travel Combo

```sql
-- Create a branch from a past snapshot
CREATE BRANCH recovery AS OF TIMESTAMP '2026-03-20T12:00:00';
```

---

## 18. Full-Text Search

HeliosDB supports PostgreSQL-compatible full-text search using `tsvector` and `tsquery`.

### Basic Full-Text Query

```sql
SELECT title, body
FROM articles
WHERE to_tsvector(body) @@ to_tsquery('database & performance');
```

**Operators:**
- `&` -- AND (both terms must appear)
- `|` -- OR (either term)
- `!` -- NOT (term must not appear)

### Combining with Other Filters

```sql
SELECT *
FROM articles
WHERE to_tsvector(body) @@ to_tsquery('rust | performance')
  AND published = true
ORDER BY created_at DESC
LIMIT 10;
```

---

## 19. Vector Search

HeliosDB provides native vector similarity search with HNSW indexes, Product Quantization, and SIMD acceleration.

### Creating a Vector Column

```sql
CREATE TABLE embeddings (
    id INT PRIMARY KEY,
    title TEXT,
    vec VECTOR(128)
);
```

### Inserting Vectors

```sql
INSERT INTO embeddings (id, title, vec)
VALUES (1, 'example', '[0.1, 0.2, 0.3, ...]');
```

### Creating an HNSW Index

```sql
CREATE INDEX idx_embeddings_vec ON embeddings USING hnsw (vec);
```

### K-Nearest Neighbor Search

```sql
-- L2 distance (Euclidean)
SELECT id, title, vec <-> '[0.1, 0.2, 0.3, ...]' AS distance
FROM embeddings
ORDER BY vec <-> '[0.1, 0.2, 0.3, ...]'
LIMIT 10;
```

### Distance Operators

| Operator | Distance Function |
|----------|-------------------|
| `<->` | L2 (Euclidean) distance |
| `<#>` | Negative inner product |
| `<=>` | Cosine distance |

### Distance Functions

```sql
SELECT cosine_similarity(vec, '[0.1, 0.2, ...]') FROM embeddings;
SELECT l2_distance(vec, '[0.1, 0.2, ...]') FROM embeddings;
SELECT inner_product(vec, '[0.1, 0.2, ...]') FROM embeddings;
```

### Vector Index Statistics

```sql
SELECT * FROM pg_vector_index_stats;
```

---

## 20. Transparent Data Encryption (TDE)

All data at rest is encrypted using AES-256-GCM when TDE is enabled.

### Configuration

TDE is configured at startup in `config.toml`:

```toml
[encryption]
enabled = true
```

### How It Works

- **Algorithm:** AES-256-GCM (authenticated encryption)
- **Key management:** Built-in key generation, rotation, and storage
- **Scope:** Table data in the storage backend is encrypted before writing and decrypted on read
- **PBKDF2:** 100,000 iterations (NIST SP 800-132 compliant) for key derivation
- **Performance:** Encryption/decryption is transparent to the SQL layer

### Monitoring

```sql
SELECT * FROM pg_encryption_status;
SELECT * FROM pg_table_encryption;
SELECT * FROM pg_segment_encryption;
```

---

## 21. Triggers & PL/pgSQL

HeliosDB supports triggers and PL/pgSQL stored functions with loops, cursors, conditionals, and exception handling.

### Creating a PL/pgSQL Function

```sql
CREATE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

### Creating a Trigger

```sql
CREATE TRIGGER audit_changes
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();
```

### Trigger Timing and Events

| Timing | Events |
|--------|--------|
| `BEFORE` | INSERT, UPDATE, DELETE, TRUNCATE |
| `AFTER` | INSERT, UPDATE, DELETE, TRUNCATE |
| `INSTEAD OF` | INSERT, UPDATE, DELETE (for views) |

### Trigger Granularity

```sql
-- Per-row trigger
CREATE TRIGGER row_trigger AFTER INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION my_func();

-- Per-statement trigger
CREATE TRIGGER stmt_trigger AFTER INSERT ON orders
    FOR EACH STATEMENT EXECUTE FUNCTION my_func();
```

### Constraint Triggers

```sql
CREATE CONSTRAINT TRIGGER check_balance
    AFTER UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION validate_balance();
```

### PL/pgSQL Features

```sql
CREATE FUNCTION calculate_bonus(emp_id INT) RETURNS FLOAT8 AS $$
DECLARE
    emp_salary FLOAT8;
    bonus FLOAT8;
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;

    IF emp_salary > 100000 THEN
        bonus := emp_salary * 0.10;
    ELSIF emp_salary > 50000 THEN
        bonus := emp_salary * 0.15;
    ELSE
        bonus := emp_salary * 0.20;
    END IF;

    RETURN bonus;
END;
$$ LANGUAGE plpgsql;
```

### Dropping Triggers

```sql
DROP TRIGGER audit_changes ON users;
DROP TRIGGER audit_changes ON users CASCADE;
```

---

## 22. Prepared Statements

Prepared statements separate parsing/planning from execution, improving performance for repeated queries and preventing SQL injection.

### PREPARE

```sql
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
```

### EXECUTE

```sql
EXECUTE get_user(42);
EXECUTE get_user(99);
```

### DEALLOCATE

```sql
-- Single statement
DEALLOCATE get_user;

-- All prepared statements
DEALLOCATE ALL;
```

### How It Works

Prepared statements persist for the session. The plan is compiled once and re-executed with different parameters.

---

## 23. Transactions & Savepoints

HeliosDB provides full ACID transactions with savepoint support.

### Basic Transaction

```sql
BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
```

### Rollback

```sql
BEGIN;
DELETE FROM important_data;
-- Oops, undo everything
ROLLBACK;
```

### Savepoints

Savepoints allow partial rollbacks within a transaction:

```sql
BEGIN;
INSERT INTO orders (id, amount) VALUES (1, 100);
SAVEPOINT before_discount;
UPDATE orders SET amount = 50 WHERE id = 1;
-- Undo only the update
ROLLBACK TO SAVEPOINT before_discount;
-- The INSERT is still intact
COMMIT;
```

### Release Savepoint

```sql
BEGIN;
SAVEPOINT sp1;
INSERT INTO log (msg) VALUES ('step 1');
RELEASE SAVEPOINT sp1;  -- sp1 no longer available for rollback
COMMIT;
```

---

## 24. UPSERT (ON CONFLICT)

Insert or update in a single atomic operation.

### ON CONFLICT DO UPDATE

```sql
INSERT INTO users (id, name, email, login_count)
VALUES (1, 'Alice', 'alice@example.com', 1)
ON CONFLICT (id) DO UPDATE
SET login_count = users.login_count + 1,
    name = EXCLUDED.name;
```

If `id = 1` already exists, the `login_count` is incremented and `name` is updated. Otherwise, the row is inserted.

### ON CONFLICT DO NOTHING

```sql
INSERT INTO tags (name)
VALUES ('rust')
ON CONFLICT (name) DO NOTHING;
```

Silently skips the insert if the tag already exists.

---

## 25. Window Functions

HeliosDB supports 10+ window functions for analytics queries.

### Available Functions

| Function | Description |
|----------|-------------|
| `ROW_NUMBER()` | Sequential number per partition |
| `RANK()` | Rank with gaps for ties |
| `DENSE_RANK()` | Rank without gaps |
| `LAG(expr, offset, default)` | Value from previous row |
| `LEAD(expr, offset, default)` | Value from next row |
| `FIRST_VALUE(expr)` | First value in window frame |
| `LAST_VALUE(expr)` | Last value in window frame |
| `NTH_VALUE(expr, n)` | Nth value in window frame |
| `NTILE(n)` | Distribute rows into n buckets |
| `PERCENT_RANK()` | Relative rank (0 to 1) |
| `CUME_DIST()` | Cumulative distribution |
| `SUM/AVG/COUNT/MIN/MAX` | Aggregate window functions |

### Examples

```sql
-- Rank employees by salary within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
```

```sql
-- Running total of sales
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
```

```sql
-- Compare each sale to the previous one
SELECT
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change
FROM orders;
```

---

## 26. JSONB Support

HeliosDB provides 20+ JSONB functions for document-style queries.

### Storing JSON Data

```sql
CREATE TABLE events (
    id INT PRIMARY KEY,
    data JSONB
);

INSERT INTO events (id, data)
VALUES (1, '{"type": "click", "page": "/home", "meta": {"browser": "Firefox"}}');
```

### Extraction Functions

```sql
-- Extract a nested value
SELECT jsonb_extract_path(data, 'meta', 'browser') FROM events;
-- Result: "Firefox"

-- Extract as text
SELECT jsonb_extract_path_text(data, 'type') FROM events;
-- Result: click
```

### Array Functions

```sql
SELECT jsonb_array_elements('[1, 2, 3]'::JSONB);
SELECT jsonb_array_elements_text('["a", "b", "c"]'::JSONB);
SELECT jsonb_array_length('[1, 2, 3]'::JSONB);
-- Result: 3
```

### Object Functions

```sql
SELECT jsonb_object_keys('{"a": 1, "b": 2}'::JSONB);
-- Results: a, b

SELECT jsonb_typeof('"hello"'::JSONB);
-- Result: string

SELECT jsonb_typeof('42'::JSONB);
-- Result: number
```

---

## 27. Common Table Expressions (CTEs)

HeliosDB supports both regular and recursive CTEs.

### Regular CTE

```sql
WITH active_users AS (
    SELECT * FROM users WHERE active = true
)
SELECT name, email FROM active_users WHERE created_at > '2026-01-01';
```

### Recursive CTE

```sql
WITH RECURSIVE hierarchy(id, name, manager_id, level) AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees under each manager
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy ORDER BY level, name;
```

### Number Sequence

```sql
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
```

---

## 28. System Views

HeliosDB provides 60+ system views for introspection.

### Core Catalog Views

| View | Description |
|------|-------------|
| `pg_tables` | List all user tables |
| `pg_views` | List views |
| `pg_indexes` | List indexes (including HNSW) |
| `pg_columns` | Column definitions |
| `pg_constraints` | Constraints (PK, FK, UNIQUE, CHECK) |
| `pg_settings` | Runtime configuration parameters |

### Session & Activity

| View | Description |
|------|-------------|
| `pg_stat_activity` | Active queries and sessions |
| `pg_stat_database` | Database-level statistics |
| `pg_stat_ssl` | SSL/TLS connection statistics |
| `pg_authid` | Authentication identities |

### MVCC & Branching

| View | Description |
|------|-------------|
| `pg_branches` / `pg_database_branches` | List all database branches |
| `pg_snapshots` | Active MVCC snapshots |
| `pg_transaction_map` | Transaction ID to timestamp mapping |
| `pg_scn_map` / `pg_current_scn` | System Change Number tracking |

### Optimizer & Execution

| View | Description |
|------|-------------|
| `pg_stat_optimizer` | Query optimizer statistics |
| `pg_parallel_exec_stats` | Parallel execution metrics |
| `pg_predicate_pushdown_stats` | Predicate pushdown performance |

### Materialized Views

| View | Description |
|------|-------------|
| `pg_matviews` | List materialized views |
| `pg_mv_staleness` | MV freshness tracking |
| `pg_mv_cpu_usage` | MV refresh CPU usage |

### Security & RBAC

| View | Description |
|------|-------------|
| `helios_grants` | All privilege grants |
| `helios_roles` | Role definitions and hierarchy |
| `helios_user_quotas` | Per-user rate limits and usage |
| `pg_rls_policies` | Row-Level Security policies |

### Storage & HTAP

| View | Description |
|------|-------------|
| `pg_htap_layout_stats` | HTAP storage layout statistics |
| `helios_workload_patterns` | Column access patterns for HTAP tuning |
| `pg_data_store_types` | Available storage types |
| `pg_compression_stats` | Compression ratios and statistics |
| `heliosdb_compression_stats` | Detailed compression metrics |
| `heliosdb_pattern_stats` | Data pattern analysis |
| `heliosdb_compression_events` | Compression event log |

### SMFI (Speculative Multi-Filter Indexing)

| View | Description |
|------|-------------|
| `helios_filter_metrics` | Filter performance metrics |
| `helios_filter_recommendations` | Suggested filter improvements |
| `helios_filter_budget` | Storage budget for filters |
| `helios_filter_build_queue` | Filter build queue status |
| `helios_filter_types` | Available filter types |
| `pg_smfi_status` | SMFI system status |
| `pg_smfi_table_stats` | Per-table SMFI statistics |
| `pg_speculative_filters` | Active speculative filters |

### High Availability & Proxy

| View | Description |
|------|-------------|
| `helios_topology` | Cluster topology with health info |
| `helios_node_aliases` | Node alias mappings |
| `helios_ha_status` | HA cluster status summary |
| `pg_stat_replication` | Replication status |
| `pg_stat_progress_vacuum` | Maintenance progress |
| `helios_sync_status` | HeliosDB sync metrics |
| `helios_proxy_pools` | Connection pool statistics |
| `helios_proxy_status` | Proxy status summary |
| `helios_proxy_cache_stats` | Query cache statistics |
| `helios_proxy_routing_stats` | Query routing statistics |
| `helios_proxy_lag_stats` | Replica lag statistics |
| `helios_proxy_rate_limit_stats` | Rate limiting statistics |
| `helios_proxy_circuit_breaker_stats` | Circuit breaker statistics |
| `helios_proxy_analytics_stats` | Query analytics |
| `helios_proxy_auth_stats` | Authentication statistics |
| `helios_proxy_rewriter_stats` | Query rewriting statistics |
| `helios_proxy_plugin_stats` | WASM plugin statistics |
| `helios_proxy_graphql_stats` | GraphQL gateway statistics |
| `helios_proxy_schema_routing_stats` | Schema-aware routing stats |

### Encryption

| View | Description |
|------|-------------|
| `pg_encryption_status` | TDE status overview |
| `pg_table_encryption` | Per-table encryption status |
| `pg_segment_encryption` | Per-segment encryption details |

### Operational

| View | Description |
|------|-------------|
| `helios_query_history` | Query execution history |
| `helios_table_memory_stats` | Per-table memory usage |
| `helios_transaction_stats` | Transaction statistics |
| `pg_maintenance_stats` | Maintenance task statistics |
| `pg_access_tracker_stats` | Access pattern tracking |

### HeliosCore Storage Tier

| View | Description |
|------|-------------|
| `pg_helioscore_api_stats` | HeliosCore API call statistics |
| `pg_block_file_stats` | Block file I/O statistics |
| `pg_wal_stats` | WAL write statistics |
| `pg_segment_stats` | Storage segment statistics |
| `pg_shard_stats` | Shard distribution statistics |
| `pg_staging_area_stats` | Staging area utilization |
| `pg_aux_structure_stats` | Auxiliary structure (bloom, zone map) stats |

### Multi-Tenancy

| View | Description |
|------|-------------|
| `pg_tenant_usage` | Per-tenant resource usage |
| `pg_cdc_events` | Change Data Capture events |
| `pg_distribcache_stats` | Distributed cache statistics |

### Example Usage

```sql
-- Check active sessions
SELECT * FROM pg_stat_activity;

-- View optimizer statistics
SELECT * FROM pg_stat_optimizer;

-- Check HTAP workload patterns
SELECT * FROM helios_workload_patterns;

-- Monitor compression
SELECT * FROM pg_compression_stats;

-- Check encryption status
SELECT * FROM pg_encryption_status;
```

---

## 29. Connection Protocols

HeliosDB supports three connection protocols.

### PostgreSQL Wire Protocol (Port 5432)

Full PostgreSQL wire protocol compatibility with SCRAM-SHA-256 authentication.

**Features:**
- Simple and extended query protocols
- SCRAM-SHA-256 authentication (PBKDF2 with 100,000 iterations)
- SSL/TLS support
- Per-role SSL enforcement

**Connect with psql:**
```bash
psql -h localhost -p 5432 -U myuser -d heliosdb
```

**Connect with any PostgreSQL driver** (Python, Go, Java, Node.js, etc.):
```python
import psycopg2
conn = psycopg2.connect(
    host="localhost", port=5432,
    user="myuser", password="mypassword",
    dbname="heliosdb"
)
```

### REST API

HTTP-based query interface with JSON request/response format. Includes rate limiting middleware, JWT authentication, and Supabase-compatible PostgREST endpoints.

**Endpoints:**
- `POST /query` -- Execute SQL queries
- `POST /execute` -- Execute DML statements
- `GET /tables` -- List tables
- PostgREST-compatible data endpoints

### gRPC (planned)

A Protocol Buffers interface for distributed storage operations between nodes is
planned, as is a gRPC client query API. HeliosDB Lite does not open a gRPC
listener; clients use the PostgreSQL wire protocol or the REST API.

---

## Architecture Summary

```
Clients
  |
  +-- PostgreSQL Wire Protocol (SCRAM-SHA-256)
  +-- REST API (JSON / JWT)
  +-- gRPC (planned)
  |
SQL Engine
  |
  +-- Parser (sqlparser-rs)
  +-- Planner (logical plan)
  +-- Optimizer (predicate pushdown, join reorder,
  |              MV rewrite, partition pruning, projection, subquery,
  |              storage pushdown, expression simplification)
  +-- Executor (Volcano model, instrumented operators for ANALYZE)
  |
Storage Engine
  |
  +-- MVCC (snapshot isolation, read-committed)
  +-- WAL (write-ahead log with checkpointing)
  +-- Native storage backend (O_DIRECT I/O)
  +-- ART Indexes (primary + secondary)
  +-- HNSW Vector Indexes (with Product Quantization)
  +-- Graph Adjacency Lists (BFS, Dijkstra, A*)
  +-- BM25 Inverted Index + Hybrid Search (RRF, MMR)
  +-- Bloom Filters + Zone Maps
  +-- Compression (Zstd, LZ4, Snappy, dictionary)
  +-- Columnar / Dictionary / Content-Addressed storage
  +-- Branching (Git-like, copy-on-write)
  +-- Time-Travel (MVCC version chains)
  |
Security
  |
  +-- TDE (AES-256-GCM, key rotation)
  +-- RBAC (GRANT/REVOKE, role hierarchy)
  +-- RLS (row-level security policies)
  +-- Rate Limiting (per-role)
  +-- SSL Enforcement (per-role)
  +-- SCRAM-SHA-256 (PBKDF2, 100K iterations)
  |
High Availability (feature-gated)
  |
  +-- WAL Streaming Replication
  +-- Multi-Primary Replication
  +-- Sharding
  +-- HeliosProxy (13 features: caching, routing, rate limiting,
                    circuit breaker, analytics, auth, query rewriting,
                    WASM plugins, GraphQL gateway, schema routing, etc.)
```

---

## 30. Graph Adjacency Lists

:::note[Planned]
Graph traversal from SQL is planned. The functions `graph_bfs(source, max_depth)`,
`graph_shortest_path(source, target)`, `graph_a_star(source, target)` and
`graph_neighbors(node)` are not yet available in the SQL layer. Until they ship, model
relationships as an edge table and traverse it with recursive CTEs.
:::

```sql
CREATE TABLE edges (src BIGINT, dst BIGINT, label TEXT, weight FLOAT8 DEFAULT 1.0);

WITH RECURSIVE reachable(node, depth) AS (
    SELECT 1::BIGINT, 0
    UNION
    SELECT e.dst, r.depth + 1
    FROM edges e JOIN reachable r ON e.src = r.node
    WHERE r.depth < 3
)
SELECT node, MIN(depth) FROM reachable GROUP BY node;
```

---

## 31. BM25 & Hybrid Search

:::note[Planned]
BM25 ranking and hybrid (BM25 + vector) search with RRF and MMR reranking are planned
for the SQL and REST surfaces. Today, use full-text search (`to_tsvector`,
`to_tsquery`, `@@`, see section 18) and vector search (section 19), and combine their
rankings in your application.
:::

---

## 32. Arena Allocation

HeliosDB uses per-request bump allocation for transient buffers, eliminating per-object allocator overhead. All allocations for a request are freed at once when the request completes.

### Use Cases

- HNSW candidate queues during vector search
- BM25 term hash buffers
- Row pointer scratch space during parallel scans
- Visited bitsets during graph traversal

---

## 33. Compiled Query Plans

The plan cache eliminates repeated parse and plan cost for frequently executed SQL statements.

### Components

| Component | Description |
|-----------|-------------|
| Plan fingerprint | Stable 64-bit hash of normalized SQL (comments stripped, literals replaced with `?`) |
| Compiled plan | Parsed + validated prepared statement with parameter slots |
| Plan cache | Bounded LRU (default 512 entries), thread-safe |

### How It Works

```text
1. fingerprint(sql) -> cache lookup
   hit?  -> bind parameters, execute directly
   miss? -> parse + plan + optimize + insert into cache
```

### Normalization

The fingerprint normalizer strips comments, collapses whitespace, lowercases keywords, and replaces literal values with positional placeholders. This ensures semantically identical queries with different literal values share a single cached plan.

---

## 34. SQL Cursors

HeliosDB supports server-side cursors for iterating over large result sets without loading all rows into memory.

### Syntax

```sql
-- Declare a cursor
DECLARE my_cursor CURSOR FOR SELECT * FROM large_table WHERE status = 'active';

-- Fetch rows in batches
FETCH 100 FROM my_cursor;
FETCH 100 FROM my_cursor;

-- Close when done
CLOSE my_cursor;
```

### Behavior

- Cursors are scoped to the current session
- FETCH returns the next N rows from the cursor position
- CLOSE releases the cursor resources
- Cursors are automatically closed on transaction rollback and session disconnect

---

## 35. Metrics & Health Endpoints

HeliosDB exposes a health check and Prometheus-compatible metrics on the HTTP port
(default 8080).

### /health

Returns HTTP 200 with `{"status": "ok"}` while the server is up. Callers that present
the monitoring key (`HELIOSDB_METRICS_KEY`, sent as `Authorization: Bearer <key>` or
`x-metrics-key: <key>`) receive detailed checks (disk, memory, WAL, connections), and
HTTP 503 when the server is unhealthy.

### /metrics

Returns Prometheus-format metrics to callers that present the monitoring key; others
receive HTTP 403. Metrics include:

- Query count and latency histograms
- Connection count (active, idle)
- Storage utilization
- Cache hit rates (plan, result, statistics)
- MVCC GC statistics

---

## Quick Reference: SQL Command Summary

| Category | Commands |
|----------|----------|
| **DDL** | CREATE/ALTER/DROP TABLE, CREATE INDEX, CREATE VIEW, CREATE MATERIALIZED VIEW |
| **DML** | SELECT, INSERT, UPDATE, DELETE, UPSERT (ON CONFLICT) |
| **Transactions** | BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT |
| **Branching** | CREATE BRANCH, DROP BRANCH, MERGE BRANCH, SELECT... ON BRANCH |
| **Time-Travel** | SELECT... AS OF TIMESTAMP |
| **Backup** | BACKUP DATABASE, RESTORE DATABASE, RECOVER DATABASE |
| **Maintenance** | VACUUM, VACUUM FULL, VACUUM ANALYZE |
| **Security** | CREATE ROLE, GRANT, REVOKE, ALTER TABLE... ENABLE ROW LEVEL SECURITY, CREATE POLICY |
| **Explain** | EXPLAIN, EXPLAIN ANALYZE, EXPLAIN (FORMAT JSON/YAML/TREE), EXPLAIN (STORAGE/AI/WHY_NOT/TRACE) |
| **Prepared** | PREPARE, EXECUTE, DEALLOCATE |
| **Triggers** | CREATE TRIGGER, DROP TRIGGER, CREATE FUNCTION |
| **MV** | CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW |
| **Vector** | CREATE INDEX... USING hnsw, `<->`, `<#>`, `<=>`, cosine_similarity(), l2_distance() |
| **FTS** | to_tsvector(), to_tsquery(), `@@` |
| **Graph** | Planned: graph_bfs(), graph_shortest_path(), graph_a_star(), graph_neighbors() |
| **BM25/Hybrid** | Planned: BM25 ranking, hybrid search, RRF and MMR reranking |
| **Cursors** | DECLARE, FETCH, CLOSE |
| **Monitoring** | /metrics (Prometheus, monitoring key), /health endpoint |
| **Storage** | ALTER TABLE... ALTER COLUMN... SET STORAGE COLUMNAR/DICTIONARY/CONTENT_ADDRESSED |