Branches, Time-Travel, and Advanced Features Tutorial

A comprehensive guide to understanding and using HeliosDB Nano time-travel, branching, and advanced database features with practical examples.

Concepts
Time-Travel Queries
Database Branches
Branch Management
Merging Branches
Advanced Scenarios
Interactive Tutorial Script

Concepts

What is Time-Travel?

Time-travel allows you to query your database as it existed at any point in the past. Every data modification creates a new version, and you can look back at previous versions.

Timeline:
┌──────────────┬──────────────┬──────────────┬──────────────┐
│ T0: Initial  │ T1: Update   │ T2: Insert   │ T3: Current  │
│ Data v1      │ Data v2      │ Data v3      │ Data v4      │
└──────────────┴──────────────┴──────────────┴──────────────┘
                                                    ↑
                    You can query any point in time

What are Branches?

Branches allow you to create independent copies of your data for experimentation without affecting the main branch.

Main Branch (Production):
┌──────────────┬──────────────┬──────────────┐
│ T0: v1       │ T1: v2       │ T2: v3       │
└──────────────┴──────────────┴──────────────┘
                    ↓
         Create Branch at T1
                    ↓
Feature Branch:     ┌──────────────┬──────────────┐
                    │ F0: v2 (fork)│ F1: v2'      │
                    └──────────────┴──────────────┘

Later: Merge Feature Branch back to Main

Time-Travel + Branches

Combine both features to:

Travel back in time within any branch
Create branches from past snapshots
Merge different versions

Time-Travel Queries

Supported Syntax

Time-travel queries work with four variants:

1. AS OF NOW (Current State)

-- Query the current state of the database
SELECT * FROM orders AS OF NOW;

Use case: Explicitly specify you want current data (useful in scripts)

Example:

CREATE TABLE sales (id INT, amount INT, date TEXT);
INSERT INTO sales VALUES (1, 100, '2025-11-28');
INSERT INTO sales VALUES (2, 200, '2025-11-28');

-- These are equivalent:
SELECT * FROM sales;
SELECT * FROM sales AS OF NOW;

-- Both return:
┌────┬────────┬─────────────┐
│ id │ amount │ date        │
├────┼────────┼─────────────┤
│ 1  │ 100    │ 2025-11-28  │
│ 2  │ 200    │ 2025-11-28  │
└────┴────────┴─────────────┘

2. AS OF TIMESTAMP (Specific Point in Time)

-- Query the database at a specific timestamp
SELECT * FROM orders AS OF TIMESTAMP '2025-11-28 09:00:00';

Use case: Look at data as it was at a specific date/time

Example:

CREATE TABLE inventory (id INT, stock INT);
INSERT INTO inventory VALUES (1, 50);  -- 09:00
-- Later...
INSERT INTO inventory VALUES (2, 30);  -- 10:00

-- Query as it was at 9:30 AM (after first insert, before second)
SELECT * FROM inventory AS OF TIMESTAMP '2025-11-28 09:30:00';

-- Returns only:
┌────┬───────┐
│ id │ stock │
├────┼───────┤
│ 1  │ 50    │
└────┴───────┘

3. AS OF TRANSACTION (After Specific Transaction)

-- Query the database after a specific transaction executed
SELECT * FROM orders AS OF TRANSACTION 5;

Use case: Revert to state after a known transaction ID

Example:

CREATE TABLE accounts (id INT, balance INT);
-- Transaction 1: INSERT (1, 1000)
-- Transaction 2: INSERT (2, 2000)
-- Transaction 3: UPDATE id=1, balance=1500
-- Transaction 4: INSERT (3, 3000)

-- Query after transaction 2 (before transaction 3 update)
SELECT * FROM accounts AS OF TRANSACTION 2;

-- Returns:
┌────┬─────────┐
│ id │ balance │
├────┼─────────┤
│ 1  │ 1000    │ (not yet updated to 1500)
│ 2  │ 2000    │
└────┴─────────┘

-- Query after transaction 4
SELECT * FROM accounts AS OF TRANSACTION 4;

-- Returns:
┌────┬─────────┐
│ id │ balance │
├────┼─────────┤
│ 1  │ 1500    │ (updated)
│ 2  │ 2000    │
│ 3  │ 3000    │ (newly inserted)
└────┴─────────┘

4. AS OF SCN (System Change Number)

-- Query using System Change Number (internal version number)
SELECT * FROM orders AS OF SCN 1000;

Use case: Use internal database version numbers for precise recovery

Example:

CREATE TABLE events (id INT, type TEXT);
INSERT INTO events VALUES (1, 'login');   -- SCN ~100
INSERT INTO events VALUES (2, 'click');   -- SCN ~200
INSERT INTO events VALUES (3, 'logout');  -- SCN ~300

-- Query at SCN 150 (between first and second insert)
SELECT * FROM events AS OF SCN 150;

-- Returns:
┌────┬────────┐
│ id │ type   │
├────┼────────┤
│ 1  │ login  │
└────┴────────┘

Advanced Time-Travel Queries

Combine with WHERE Clause

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

Use with Aggregates

-- What was the total sales at 3 PM?
SELECT SUM(amount) FROM sales
AS OF TIMESTAMP '2025-11-28 15:00:00';

-- How many orders existed after transaction 10?
SELECT COUNT(*) FROM orders
AS OF TRANSACTION 10;

Audit Trail Pattern

-- Compare same data at different points in time
SELECT id, name, 'Current' as version
FROM users
AS OF NOW

UNION ALL

SELECT id, name, 'Yesterday' as version
FROM users
AS OF TIMESTAMP '2025-11-27 00:00:00';

-- Shows changes between today and yesterday

Point-in-Time Recovery

-- Export data to recover from accidental deletion
SELECT * FROM deleted_data
AS OF TIMESTAMP '2025-11-28 08:00:00'
INTO recovered_backup;

Database Branches

Supported SQL Workflow

Database branching is available through SQL, REPL commands, and the embedded Rust API.

-- Create a branch from the current state of main
CREATE DATABASE BRANCH feature_dev FROM main AS OF NOW;

-- Create a branch from a past snapshot
CREATE DATABASE BRANCH historical FROM main
AS OF TIMESTAMP '2025-11-15 00:00:00';

-- Switch the active branch
USE BRANCH feature_dev;

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

-- Merge branches back
MERGE DATABASE BRANCH feature_dev INTO main;

-- Delete a branch
DROP DATABASE BRANCH feature_dev;

Branch Concepts

1. Creating a Branch

Main Branch:
┌─────────────────────────────────────────┐
│ History: v1 → v2 → v3 → v4 → v5        │
│ (Production data)                       │
└─────────────────────────────────────────┘
                    ↓
        CREATE BRANCH feature_dev
                    ↓
Feature Branch:     ┌──────────────────────┐
                    │ Copy of v5            │
                    │ Ready for changes     │
                    └──────────────────────┘

2. Independent Changes

Main:    v1 → v2 → v3 (stable production)
Feature:    ├→ f1 → f2 → f3 (experimental changes)

Each branch evolves independently without affecting the other.

3. Branching from Past

Original at T=0:  ┌────────────────────────┐
                  │ v1 → v2 → v3 → v4 → v5 │
                  │ (Current: May 28)       │
                  └────────────────────────┘
                          ↓
            CREATE BRANCH from May 15 (v2)
                          ↓
May 15 Branch:           ┌──────────────────┐
                         │ v2 (May 15)       │
                         │ f1 → f2 (new)     │
                         └──────────────────┘

Branch Management

Branch management operations are available from SQL and the interactive REPL.

List All Branches

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

-- Output:
┌─────────────────┬───────────┬───────────┬────────────────────┐
│ branch_name     │ branch_id │ parent_id │ created_at         │
├─────────────────┼───────────┼───────────┼────────────────────┤
│ main            │ 1         │ NULL      │ 2025-11-01 00:00:00│
│ feature_dev     │ 2         │ 1         │ 2025-11-28 10:00:00│
│ hotfix_v2.4     │ 3         │ 1         │ 2025-11-27 14:00:00│
└─────────────────┴───────────┴───────────┴────────────────────┘

Switch Branches

-- Route subsequent reads and writes to the branch
USE BRANCH feature_dev;

-- Return to the main branch
USE BRANCH main;

# REPL shortcuts
\branches
\use feature_dev
\show branch

Delete a Branch

-- Remove a branch
DROP DATABASE BRANCH feature_dev;

-- Only drop if it exists (no error if missing)
DROP DATABASE BRANCH IF EXISTS feature_dev;

Check Branch Size

-- See which branches are largest
SELECT branch_name, size_mb
FROM pg_database_branches()
ORDER BY size_mb DESC;

Merging Branches

Simple Merge

-- Merge feature branch back to main
MERGE DATABASE BRANCH feature_dev INTO main;

What happens:

Before:
Main:    v1 → v2 → v3 → v4
Feature:    ├→ f1 → f2 → f3

After MERGE:
Main:    v1 → v2 → v3 → v4 → merged(f3) → v5
Feature: marked merged, or deleted when delete_branch_after is true

Merge with Conflict Resolution

-- If conflicts occur, choose resolution strategy
MERGE DATABASE BRANCH feature_dev INTO main
WITH (conflict_resolution='branch_wins');

-- Other options:
-- conflict_resolution='target_wins' -- Main branch wins
-- conflict_resolution='fail'        -- Stop on conflict

Delete After Merge

-- Automatically delete branch after successful merge
MERGE DATABASE BRANCH feature_dev INTO main
WITH (delete_branch_after=true);

Three-Way Merge

The system performs smart three-way merge:

       Common Ancestor (v2)
       /              \
      /                \
   Main (v2→v3→v4)   Feature (v2→f1→f2→f3)
      \                /
       \              /
        Merged Result

Advanced Scenarios

Scenario 1: Experiment with Rollback

-- Main branch - production
CREATE TABLE customers (id INT, name TEXT, status TEXT);
INSERT INTO customers VALUES (1, 'Alice', 'active');
INSERT INTO customers VALUES (2, 'Bob', 'active');

-- Create experiment branch
CREATE DATABASE BRANCH test_new_logic FROM main AS OF NOW;
USE BRANCH test_new_logic;

-- Simulate experimental changes
UPDATE customers SET status = 'inactive' WHERE id = 1;
DELETE FROM customers WHERE id = 2;

-- Oops! This was a mistake. Revert to before the experiment:
-- Drop the branch and start over
USE BRANCH main;
DROP DATABASE BRANCH test_new_logic;

-- OR query the original state:
SELECT * FROM customers AS OF TIMESTAMP '2025-11-28 09:00:00';
-- Shows the original data before the mistake

Scenario 2: A/B Testing

-- Create two experimental branches
CREATE DATABASE BRANCH variant_a FROM main AS OF NOW;
CREATE DATABASE BRANCH variant_b FROM main AS OF NOW;

-- In variant_a: Apply algorithm A
USE BRANCH variant_a;
-- INSERT/UPDATE/DELETE for algorithm A

-- In variant_b: Apply algorithm B
USE BRANCH variant_b;
-- INSERT/UPDATE/DELETE for algorithm B

-- Compare results:
USE BRANCH variant_a;
SELECT COUNT(*) FROM metrics;

USE BRANCH variant_b;
SELECT COUNT(*) FROM metrics;

-- Keep the winning variant, merge it back:
USE BRANCH main;
MERGE DATABASE BRANCH variant_a INTO main;

Scenario 3: Historical Analysis

-- Create branch from 30 days ago
CREATE DATABASE BRANCH analysis_30d_ago FROM main
  AS OF TIMESTAMP '2025-10-29 00:00:00';

-- Compare metrics:
SELECT 'Current' as period, COUNT(*) as users FROM users AS OF NOW
UNION ALL
SELECT '30 Days Ago', COUNT(*) FROM users
  AS OF TIMESTAMP '2025-10-29 00:00:00';

-- Result shows user growth over time

Scenario 4: Bug Root Cause Analysis

-- Find when a problem started
SELECT transaction_id, COUNT(*) as errors
FROM error_log
AS OF TRANSACTION 1000

UNION ALL

SELECT transaction_id, COUNT(*) as errors
FROM error_log
AS OF TRANSACTION 2000

UNION ALL

SELECT transaction_id, COUNT(*) as errors
FROM error_log
AS OF TRANSACTION 3000

-- Find the transaction where errors increased

Scenario 5: Compliance & Audit Trail

-- Keep immutable audit trail via time-travel
SELECT
    user_id,
    action,
    timestamp,
    'Current' as when_viewed
FROM audit_log
AS OF NOW

UNION ALL

SELECT
    user_id,
    action,
    timestamp,
    'As of Audit (2025-01-01)' as when_viewed
FROM audit_log
AS OF TIMESTAMP '2025-01-01 00:00:00'

-- Proves what data was visible at each audit date

Interactive Tutorial Script

Here’s a bash script with a pause function for an interactive tutorial:

#!/bin/bash

# Color codes
GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
NC='\033[0m' # No Color

# Database binary
DB="./target/release/heliosdb-nano"

# Pause function for interactive learning
pause() {
    echo ""
    echo -e "${BLUE}Press ENTER to continue...${NC}"
    read -r
}

# Function to show a section
section() {
    echo ""
    echo "════════════════════════════════════════════════════"
    echo -e "${YELLOW}$1${NC}"
    echo "════════════════════════════════════════════════════"
    pause
}

# Function to explain a concept
explain() {
    echo ""
    echo -e "${GREEN}ℹ️  $1${NC}"
    pause
}

# Function to run SQL and show results
run_sql() {
    local title="$1"
    local sql="$2"

    echo ""
    echo -e "${BLUE}>>> $title${NC}"
    echo -e "${YELLOW}SQL:${NC}"
    echo "  $sql"
    echo ""
    echo -e "${YELLOW}Result:${NC}"

    timeout 5 "$DB" repl << EOF 2>&1 | grep -A 50 "Lite"
$sql
\q
EOF
    pause
}

# Main Tutorial
clear
echo "═══════════════════════════════════════════════════════════════"
echo -e "${GREEN}    HeliosDB Nano Tutorial${NC}"
echo -e "${GREEN}    Time-Travel & Branch Management${NC}"
echo "═══════════════════════════════════════════════════════════════"
pause

section "Introduction to Time-Travel"
explain "Time-travel allows you to query your database AS IT WAS at any point in the past."
explain "Every change creates a new version. You can look back at previous versions."

section "Example 1: Query Current Data"
run_sql "Create a table and insert data" \
    "CREATE TABLE products (id INT, name TEXT, price INT);"

run_sql "Insert first product" \
    "INSERT INTO products VALUES (1, 'Laptop', 1000);"

run_sql "Query with AS OF NOW (current state)" \
    "SELECT * FROM products AS OF NOW;"

explain "AS OF NOW explicitly queries the current state - useful in scripts"

section "Example 2: Simulate Historical Query"
run_sql "Insert another product" \
    "INSERT INTO products VALUES (2, 'Phone', 500);"

run_sql "Query all products (current)" \
    "SELECT * FROM products AS OF NOW;"

explain "Now we have 2 products. But with time-travel, we can see the state before the second insert."

run_sql "Query as it was before the phone insert (using timestamp)" \
    "SELECT * FROM products AS OF TIMESTAMP '2025-11-28 08:00:00';"

explain "The timestamp shows only the Laptop, not the Phone (which was inserted later)"

section "Example 3: Transaction-Based Time Travel"
run_sql "Create an accounts table" \
    "CREATE TABLE accounts (id INT, balance INT);"

run_sql "Insert initial account" \
    "INSERT INTO accounts VALUES (1, 1000);"

run_sql "Query after first transaction" \
    "SELECT * FROM accounts AS OF TRANSACTION 1;"

explain "Transaction 1 shows the initial account with 1000 balance"

section "Example 4: SCN-Based Time Travel"
explain "SCN (System Change Number) is an internal version number"

run_sql "Query using SCN" \
    "SELECT * FROM accounts AS OF SCN 500;"

explain "SCN provides precise version-based time-travel queries"

section "Example 5: Combining Time-Travel with Filters"
run_sql "Create sales data" \
    "CREATE TABLE sales (id INT, amount INT, region TEXT);"

run_sql "Insert sales data" \
    "INSERT INTO sales VALUES (1, 1000, 'North');
INSERT INTO sales VALUES (2, 2000, 'South');
INSERT INTO sales VALUES (3, 1500, 'North');"

run_sql "Query specific region at specific time (with WHERE)" \
    "SELECT * FROM sales AS OF TIMESTAMP '2025-11-28 09:00:00' WHERE region = 'North';"

explain "Time-travel works with WHERE clauses, JOINs, aggregates, and other SQL features"

section "Example 6: Aggregates with Time-Travel"
run_sql "How many sales existed at 9 AM?" \
    "SELECT COUNT(*) FROM sales AS OF TIMESTAMP '2025-11-28 09:00:00';"

run_sql "What was the total amount at 9 AM?" \
    "SELECT SUM(amount) FROM sales AS OF TIMESTAMP '2025-11-28 09:00:00';"

explain "Aggregate functions (COUNT, SUM, AVG, etc.) work with time-travel"

section "System Views"
explain "System views show metadata about your database"

run_sql "View all database branches" \
    "SELECT * FROM pg_database_branches();"

explain "pg_database_branches() shows information about all branches"

run_sql "View materialized view staleness" \
    "SELECT * FROM pg_mv_staleness();"

explain "pg_mv_staleness() shows how up-to-date materialized views are"

section "Database Branching"
explain "Database branching is available through SQL and REPL commands."
echo -e "${GREEN}
  • CREATE DATABASE BRANCH tutorial_dev FROM main AS OF NOW;
  • USE BRANCH tutorial_dev;
  • MERGE DATABASE BRANCH tutorial_dev INTO main;
  • DROP DATABASE BRANCH tutorial_dev;
${NC}"
pause

run_sql "Create a branch from current main" \
    "CREATE DATABASE BRANCH IF NOT EXISTS tutorial_dev FROM main AS OF NOW;"

run_sql "List branches" \
    "SELECT * FROM pg_database_branches();"

run_sql "Switch to the branch" \
    "USE BRANCH tutorial_dev;"

run_sql "Return to main" \
    "USE BRANCH main;"

run_sql "Merge and delete the branch" \
    "MERGE DATABASE BRANCH tutorial_dev INTO main WITH (delete_branch_after = true);"

section "Tutorial Complete!"
echo ""
echo -e "${GREEN}✓ You now understand:${NC}"
echo "  • Time-travel queries (AS OF NOW, TIMESTAMP, TRANSACTION, SCN)"
echo "  • System views for database metadata"
echo "  • Database branch creation, switching, merge, and cleanup"
echo "  • Real-world use cases"
echo ""
echo -e "${YELLOW}Next steps:${NC}"
echo "  1. Try more complex queries in the REPL"
echo "  2. Read the Database Branching guide"
echo "  3. Combine branch workflows with time-travel recovery points"
echo ""
pause

Complete Interactive Tutorial (All-in-One)

Create and save as interactive_tutorial.sh:

#!/bin/bash
# See the bash script section above - save it as interactive_tutorial.sh
# Then run: chmod +x interactive_tutorial.sh && ./interactive_tutorial.sh

Key Takeaways

Feature	Result	Example
AS OF NOW	Query current state explicitly	`SELECT * FROM orders AS OF NOW;`
AS OF TIMESTAMP	Query a human-readable recovery point	`SELECT * FROM orders AS OF TIMESTAMP '2025-11-28 09:00:00';`
AS OF TRANSACTION	Query after a specific transaction	`SELECT * FROM orders AS OF TRANSACTION 5;`
AS OF SCN	Query an internal change number	`SELECT * FROM orders AS OF SCN 1000;`
System Views	Inspect branch metadata	`SELECT * FROM pg_database_branches();`
Create Branch SQL	Create an isolated database branch	`CREATE DATABASE BRANCH feature FROM main AS OF NOW;`
Merge Branch SQL	Merge a branch back to its target	`MERGE DATABASE BRANCH feature INTO main;`

Common Patterns

Audit Trail

-- Show all versions of a record
SELECT * FROM users AS OF NOW
UNION ALL
SELECT * FROM users AS OF TRANSACTION 10
UNION ALL
SELECT * FROM users AS OF TRANSACTION 5;

Data Recovery

-- Recover accidentally deleted data
SELECT * FROM backup_data
AS OF TIMESTAMP '2025-11-28 08:00:00'
WHERE id = 123;

Compliance Reporting

-- Show data as it was on specific compliance date
SELECT * FROM financial_records
AS OF TIMESTAMP '2025-12-31 23:59:59';

Debugging

-- Find when something changed
SELECT 'Before', * FROM events AS OF TRANSACTION 100
UNION ALL
SELECT 'After', * FROM events AS OF TRANSACTION 101;

Performance Tips

Time-travel queries are fast: <100ms for most queries
Use TIMESTAMP for human-readable dates: More intuitive than SCN
Use TRANSACTION IDs for reproducibility: Exact recovery points
Combine with WHERE to limit data: AS OF TIMESTAMP '...' WHERE...
System views are ultra-fast: <1ms for metadata queries

Next Steps

Run the interactive tutorial: ./interactive_tutorial.sh
Test in REPL: ./target/release/heliosdb-nano repl
Read branch details: Database Branching
Plan recovery workflows: Time-Travel Queries

Branches, Time-Travel, and Advanced Features Tutorial

Branches, Time-Travel, and Advanced Features Tutorial

Table of Contents

Concepts

What is Time-Travel?

What are Branches?

Time-Travel + Branches

Time-Travel Queries

Supported Syntax

1. AS OF NOW (Current State)

2. AS OF TIMESTAMP (Specific Point in Time)

3. AS OF TRANSACTION (After Specific Transaction)

4. AS OF SCN (System Change Number)

Advanced Time-Travel Queries

Combine with WHERE Clause

Use with Aggregates

Audit Trail Pattern

Point-in-Time Recovery

Database Branches

Supported SQL Workflow

Branch Concepts

1. Creating a Branch

2. Independent Changes

3. Branching from Past

Branch Management

List All Branches

Switch Branches

Delete a Branch

Check Branch Size

Merging Branches

Simple Merge

Merge with Conflict Resolution

Delete After Merge

Three-Way Merge

Advanced Scenarios

Scenario 1: Experiment with Rollback

Scenario 2: A/B Testing

Scenario 3: Historical Analysis

Scenario 4: Bug Root Cause Analysis

Scenario 5: Compliance & Audit Trail

Interactive Tutorial Script

Complete Interactive Tutorial (All-in-One)

Key Takeaways

Common Patterns

Audit Trail

Data Recovery

Compliance Reporting

Debugging

Performance Tips

Next Steps