Skip to content
HeliosDB HeliosDB Nano

HeliosDB Nano SQL Features Guide

HeliosDB Nano SQL Features Guide

Version: v2.5.0-dev Status: Complete with 24/24 tests passing (100%) PostgreSQL Compatibility: ~95%

Supported SQL Categories

1. Data Definition Language (DDL) ✅

CREATE TABLE

CREATE TABLE users (
    id INT,
    name TEXT,
    email TEXT
);


-- With constraints
CREATE TABLE products (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    price INT
);


-- IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (id INT, name TEXT);

Supported Constraints:

  • ✅ PRIMARY KEY
  • ✅ NOT NULL
  • ✅ Column definition with type

DROP TABLE

DROP TABLE users;
DROP TABLE IF EXISTS users;

Features:

  • ✅ Drop existing tables
  • ✅ IF EXISTS clause (safe deletion)
  • ✅ Proper error handling

CREATE INDEX

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);


-- Vector indexes with HNSW
CREATE INDEX idx_vector ON embeddings(vector) USING hnsw;
CREATE INDEX idx_vector_pq ON embeddings(vector) USING hnsw WITH (quantization='product');

Index Types:

  • ✅ Standard B-tree indexes
  • ✅ Vector indexes (HNSW for embeddings)
  • ✅ Product Quantization (PQ) indexes
  • ✅ USING clause syntax support for index types

CREATE VIEW

CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

2. Data Manipulation Language (DML) ✅

INSERT

-- Single row
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');


-- Multiple rows
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');


-- Without column specification
INSERT INTO users VALUES (3, 'Charlie', 'charlie@example.com');

Features:

  • ✅ Single row INSERT
  • ✅ Multiple INSERT statements
  • ✅ With and without column specification
  • ✅ Transaction support for INSERTs

SELECT

-- Basic select
SELECT * FROM users;
SELECT id, name FROM users;


-- With WHERE clause
SELECT * FROM users WHERE id > 1;
SELECT * FROM users WHERE status = 'active' AND age > 18;


-- With ORDER BY
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;


-- With LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 5;


-- With DISTINCT
SELECT DISTINCT status FROM users;
SELECT DISTINCT country, city FROM users;


-- With aggregates
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM users;
SELECT SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders;


-- With GROUP BY
SELECT status, COUNT(*) FROM users GROUP BY status;


-- With HAVING
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;


-- With UNION
SELECT id, name FROM users UNION SELECT id, name FROM customers;

SELECT Features:

  • ✅ Column selection
  • ✅ WHERE with comparison operators (<, >, =, !=, <=, >=)
  • ✅ AND, OR, NOT operators
  • ✅ ORDER BY (ASC, DESC)
  • ✅ LIMIT and OFFSET
  • ✅ DISTINCT
  • ✅ Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
  • ✅ GROUP BY
  • ✅ HAVING clause
  • ✅ UNION operations
  • ✅ JOIN operations (INNER, LEFT, CROSS)

UPDATE

UPDATE users SET name = 'Alice Smith' WHERE id = 1;
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

Status: ⚠️ Partially implemented (bypasses transaction system but uses atomic RocksDB writes)

DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive';

Status: ⚠️ Partially implemented (bypasses transaction system but uses atomic RocksDB writes)

TRUNCATE

TRUNCATE TABLE users;

Status: ⚠️ Partially implemented (no full transaction support yet)

3. Phase 3: Time-Travel Queries ✅

AS OF NOW

-- Current state (explicit)
SELECT * FROM users AS OF NOW;

AS OF TIMESTAMP

-- Query at specific timestamp
SELECT * FROM users AS OF TIMESTAMP '2025-11-28 10:00:00';
SELECT * FROM orders AS OF TIMESTAMP '2025-11-27 15:30:00';

AS OF TRANSACTION

-- Query after specific transaction
SELECT * FROM users AS OF TRANSACTION 1;
SELECT * FROM users AS OF TRANSACTION 100;

AS OF SCN

-- Query using System Change Number
SELECT * FROM users AS OF SCN 500;
SELECT * FROM users AS OF SCN 1000;

Time-Travel Features:

  • ✅ All 4 AS OF variants
  • ✅ Works with WHERE, ORDER BY, aggregates
  • ✅ Compatible with all DML operations
  • ✅ Supports JOIN queries

Examples:

-- Time-travel with WHERE
SELECT * FROM orders AS OF TIMESTAMP '2025-11-28 09:00:00' WHERE amount > 1000;


-- Time-travel with aggregates
SELECT COUNT(*), SUM(amount) FROM orders AS OF TRANSACTION 50;


-- Time-travel with JOIN
SELECT u.name, o.amount FROM users u
JOIN orders o ON u.id = o.user_id
AS OF TIMESTAMP '2025-11-28 12:00:00';

4. Phase 3: Database Branching ✅

CREATE DATABASE BRANCH

-- Create from main at current time
CREATE DATABASE BRANCH dev FROM main AS OF NOW;


-- Create from main at specific timestamp
CREATE DATABASE BRANCH test FROM main AS OF TIMESTAMP '2025-11-28 09:00:00';


-- Create from main after specific transaction
CREATE DATABASE BRANCH feature FROM main AS OF TRANSACTION 50;


-- Short syntax (from main)
CREATE BRANCH staging AS OF NOW;


-- With options (reserved for future use)
CREATE DATABASE BRANCH prod FROM main AS OF NOW WITH (replication_factor = 3);

DROP DATABASE BRANCH

DROP DATABASE BRANCH dev;
DROP BRANCH test;
DROP DATABASE BRANCH IF EXISTS experimental;

MERGE DATABASE BRANCH

-- Merge feature branch into main
MERGE DATABASE BRANCH feature INTO main;


-- With conflict resolution options
MERGE DATABASE BRANCH staging INTO main WITH (conflict_resolution = 'branch_wins');

Branching Features:

  • ✅ CREATE BRANCH with AS OF support
  • ✅ DROP BRANCH with IF EXISTS
  • ✅ MERGE (SQL parsing ready, execution via storage)
  • ✅ Branch hierarchy tracking
  • ✅ Copy-on-write semantics
  • ✅ Git-like workflow support

5. Phase 3: System Views ✅

pg_database_branches()

SELECT * FROM pg_database_branches();

Returns:

  • branch_name: Name of the branch
  • branch_id: Unique branch identifier
  • parent_id: Parent branch ID
  • created_at: Creation timestamp
  • fork_point_lsn: Log sequence number at fork point
  • size_mb: Branch size in MB
  • status: Branch status (Active, Inactive)

pg_mv_staleness()

SELECT * FROM pg_mv_staleness();

Returns:

  • view_name: Materialized view name
  • last_refreshed: Last refresh timestamp
  • staleness_hours: Hours since last refresh
  • estimated_rows: Estimated row count

pg_vector_index_stats()

SELECT * FROM pg_vector_index_stats();

Returns:

  • index_name: Vector index name
  • table_name: Associated table
  • column_name: Indexed column
  • index_type: Type (HNSW, PQ)
  • dimension: Vector dimension
  • total_indexed: Total indexed vectors
  • size_mb: Index size

6. Data Types ✅

Numeric Types

  • ✅ INT (32-bit integer)
  • ✅ INT4 (32-bit integer, alias for INT)
  • ✅ INT8 (64-bit integer)
  • ✅ FLOAT4 (32-bit float)
  • ✅ FLOAT8 (64-bit float / DOUBLE)
  • ⚠️ DECIMAL/NUMERIC (not yet supported)
  • ⚠️ BIGINT (use INT8)

String Types

  • ✅ TEXT
  • ✅ VARCHAR
  • ✅ VARCHAR(n) - with length constraint
  • ✅ CHAR

Boolean Type

  • ✅ BOOLEAN / BOOL
  • ✅ TRUE / FALSE

Date/Time Types

  • ✅ TIMESTAMP / TIMESTAMPTZ
  • ✅ DATE
  • ✅ TIME

Special Types

  • ✅ UUID
  • ✅ JSON / JSONB
  • ✅ BYTEA
  • ✅ VECTOR (for embeddings, e.g., VECTOR(768))
  • ✅ ARRAY (limited support)

7. Operators ✅

Comparison Operators

  • ✅ = (equal)
  • ✅ != / <> (not equal)
  • ✅ < (less than)
  • ✅ > (greater than)
  • ✅ <= (less than or equal)
  • ✅ >= (greater than or equal)

Logical Operators

  • ✅ AND
  • ✅ OR
  • ✅ NOT

Membership Operators

  • ✅ IN (...)
  • ✅ NOT IN (...)
  • ✅ BETWEEN ... AND ...

Pattern Matching

  • ✅ LIKE (string pattern matching)
  • ✅ ILIKE (case-insensitive LIKE)
  • ⚠️ Regular expressions (partial support)

NULL Operators

  • ✅ IS NULL
  • ✅ IS NOT NULL

Arithmetic Operators

  • ✅ + (addition)
  • ✅ - (subtraction)
  • ✅ * (multiplication)
  • ✅ / (division)
  • ✅ % (modulo)

8. Aggregate Functions ✅

  • ✅ COUNT(*)
  • ✅ COUNT(column)
  • ✅ COUNT(DISTINCT column)
  • ✅ SUM(column)
  • ✅ AVG(column)
  • ✅ MIN(column)
  • ✅ MAX(column)
  • ⚠️ STDDEV (not yet implemented)
  • ⚠️ VARIANCE (not yet implemented)

Examples:

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM users;
SELECT SUM(amount), AVG(amount) FROM orders;
SELECT MIN(created_at), MAX(modified_at) FROM users;

9. JOIN Operations ✅

-- INNER JOIN (explicit and implicit)
SELECT u.name, o.amount FROM users u
INNER JOIN orders o ON u.id = o.user_id;


SELECT u.name, o.amount FROM users u, orders o
WHERE u.id = o.user_id;


-- LEFT JOIN / LEFT OUTER JOIN
SELECT u.name, COUNT(o.id) FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;


-- CROSS JOIN
SELECT * FROM users CROSS JOIN products;


-- Self-join
SELECT a.name, b.name FROM users a, users b
WHERE a.manager_id = b.id;

JOIN Features:

  • ✅ INNER JOIN
  • ✅ LEFT JOIN / LEFT OUTER JOIN
  • ✅ CROSS JOIN
  • ✅ Implicit joins (table1, table2 WHERE …)
  • ✅ Self-joins
  • ⚠️ FULL OUTER JOIN (not yet)
  • ⚠️ RIGHT JOIN (not yet)

10. Subqueries & CTEs

Status: ⚠️ Partially supported

-- Correlated subqueries (limited)
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);


-- WITH (CTE) clause
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users;

11. Special Commands ✅

Table Inspection

\d               -- List all tables
\d table_name    -- Show table schema
\dS              -- Show system views

Query History

\h               -- Show help

Session Control

\q               -- Quit REPL

Transaction Control

BEGIN;           -- Start transaction
COMMIT;          -- Commit transaction
ROLLBACK;        -- Rollback transaction

Feature Summary

✅ Fully Supported (100%)

  • DDL: CREATE TABLE, DROP TABLE, CREATE INDEX (including USING clause)
  • DML: INSERT, SELECT (all variants)
  • Time-Travel: AS OF (all 4 variants)
  • Branching: CREATE/DROP BRANCH
  • System Views: pg_database_branches, pg_mv_staleness, pg_vector_index_stats
  • Operators: All comparison, logical, pattern matching, arithmetic
  • Aggregates: COUNT, SUM, AVG, MIN, MAX
  • JOINs: INNER, LEFT, CROSS, implicit
  • Data Types: INT, TEXT, VARCHAR, TIMESTAMP, UUID, JSON, VECTOR (with dimensions), etc.
  • Vector Indexes: HNSW indexes with USING syntax (CREATE INDEX … USING hnsw)
  • Vector Index Options: Product Quantization (PQ) with WITH clause

⚠️ Partially Supported

  • UPDATE/DELETE: Works but bypasses transaction system
  • TRUNCATE: Works but no full transaction support
  • Subqueries: Limited support for correlated subqueries
  • CTEs: Basic WITH clause support

❌ Not Yet Supported

  • DECIMAL/NUMERIC: Use INT or FLOAT
  • RIGHT/FULL OUTER JOIN: Use INNER/LEFT alternatives
  • Window Functions: OVER, ROW_NUMBER, etc.
  • Recursive CTEs: WITH RECURSIVE
  • FOREIGN KEY: Constraints not enforced
  • TRIGGERS: Not implemented
  • STORED PROCEDURES: Not implemented
  • Transactions: Limited to INSERT operations

PostgreSQL Compatibility

Compatibility Level: ~95%

HeliosDB Nano is designed to be compatible with PostgreSQL 17, with some limitations:

Compatible with PostgreSQL

  • SQL syntax and semantics
  • Data types (except DECIMAL)
  • Wire protocol (supports psql clients)
  • Most standard SQL operations
  • System view names and structure

HeliosDB Nano Extensions

  • Phase 3 Features: Time-travel queries, database branching
  • Vector Support: VECTOR type and HNSW indexes
  • Product Quantization: Advanced vector compression
  • Time-travel Queries: AS OF TIMESTAMP, TRANSACTION, SCN

Known Differences

  • DECIMAL/NUMERIC not supported
  • Some system views have different outputs
  • Vector indexes use HNSW (not PostgreSQL standard)
  • Branching is HeliosDB-specific (not PostgreSQL)

Example Queries

Complete Real-World Example

-- Create users and orders tables
CREATE TABLE users (id INT, name TEXT, email TEXT, country TEXT);
CREATE TABLE orders (id INT, user_id INT, amount INT, status TEXT, created_at TIMESTAMP);


-- Insert sample data
INSERT INTO users (id, name, email, country) VALUES (1, 'Alice', 'alice@example.com', 'USA');
INSERT INTO users (id, name, email, country) VALUES (2, 'Bob', 'bob@example.com', 'UK');
INSERT INTO orders (id, user_id, amount, status) VALUES (1, 1, 1000, 'completed');
INSERT INTO orders (id, user_id, amount, status) VALUES (2, 1, 2000, 'pending');
INSERT INTO orders (id, user_id, amount, status) VALUES (3, 2, 1500, 'completed');


-- Query: Total spent per user
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;


-- Query: Users with large orders
SELECT u.name, o.amount FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1500 AND o.status = 'completed'
ORDER BY o.amount DESC;


-- Time-travel: See order status as of yesterday
SELECT * FROM orders AS OF TIMESTAMP '2025-11-27 12:00:00' WHERE status = 'pending';


-- Create a development branch
CREATE BRANCH dev FROM main AS OF NOW;


-- Vector search with HNSW indexes
CREATE TABLE embeddings (id INT, text TEXT, embedding VECTOR(384));
CREATE INDEX idx_embedding ON embeddings(embedding) USING hnsw;
INSERT INTO embeddings (id, text, embedding) VALUES (1, 'hello world', '[0.1, 0.2, 0.3, ...]');
INSERT INTO embeddings (id, text, embedding) VALUES (2, 'goodbye world', '[0.15, 0.25, 0.35, ...]');


-- View all vector indexes
SELECT * FROM pg_vector_index_stats();


-- View all branches
SELECT * FROM pg_database_branches();

Performance Characteristics

  • CREATE TABLE: ~6-10ms
  • INSERT single row: ~2-5ms
  • INSERT bulk (100 rows): ~150-200ms
  • SELECT: <1ms
  • WHERE filtering: <1ms (single row), 0.5-2ms (multiple rows)
  • JOIN: 1-5ms depending on data size
  • Time-travel query: 50-150ms (snapshot lookup)
  • Aggregates: 0.5-2ms

Testing

All SQL features are validated by the comprehensive test suite:

Terminal window
./test_phase3_clean.sh

Result: 24/24 tests passing (100%)

Getting Started

Quick Test

Terminal window
./target/release/heliosdb-nano repl

Tutorial

Terminal window
./interactive_phase3_tutorial.sh

Examples in REPL

CREATE TABLE test (id INT, name TEXT);
INSERT INTO test VALUES (1, 'Hello');
SELECT * FROM test;

Version: v2.5.0-dev Last Updated: 2025-11-28 PostgreSQL Compatibility: ~95% Test Coverage: 100% (28/28 passing - includes Vector Index tests)