In-Memory Mode User Guide

In-Memory Mode User Guide

Version: 3.1.0 Status: Production-Ready Last Updated: 2025-12-08

Table of Contents

1. Overview
2. When to Use In-Memory Mode
3. Getting Started
4. Multi-User Setup
5. Session Management
6. Isolation Levels
7. Performance Characteristics
8. Resource Quotas
9. Best Practices
10. Example Scenarios
11. Troubleshooting

Overview

In-memory mode allows HeliosDB Nano to operate entirely in RAM without persisting data to disk during normal operations. This mode provides:

• Ultra-fast performance: No disk I/O bottlenecks
• Full ACID guarantees: Complete transaction support with MVCC
• Multi-user concurrency: Multiple concurrent sessions with isolation
• User-triggered persistence: Manual dump/restore for data durability
• Session management: Per-user transaction contexts and resource limits

In-memory mode is ideal for:

• Development and testing environments
• Caching layers with periodic snapshots
• Real-time analytics with high throughput requirements
• Temporary data processing pipelines
• Session storage for web applications

When to Use In-Memory Mode

Use In-Memory Mode When:

1. Performance is Critical

   • Sub-millisecond query latency required
   • High transaction throughput (10,000+ TPS)
   • Real-time data processing workloads

2. Data is Temporary

   • Session data with short TTL
   • Cache invalidation patterns
   • Test data that can be regenerated

3. Controlled Persistence

   • You want explicit control over when data is saved
   • Scheduled backups are sufficient
   • Point-in-time snapshots meet requirements

Use Persistent Mode When:

1. Data Durability is Critical

   • Financial transactions
   • User-generated content
   • Audit logs and compliance data

2. Large Datasets

   • Working set exceeds available RAM
   • Cost of memory vs disk is prohibitive

3. Automatic Recovery Required

   • Unplanned restarts must preserve data
   • No manual intervention on startup

Getting Started

Starting In-Memory Server

# Start in-memory server on default port (5432)
heliosdb-nano start --memory

# Start with custom port and bind address
heliosdb-nano start --memory --port 5433 --listen 0.0.0.0

# Start with configuration file
heliosdb-nano start --memory --config /path/to/config.toml

Starting In-Memory REPL

# Launch interactive REPL in memory-only mode
heliosdb-nano repl --memory

# With custom configuration
heliosdb-nano repl --memory --config ./config.toml

# With automatic dump on shutdown for persistence
heliosdb-nano repl --memory --dump-on-shutdown

# With custom dump file location
heliosdb-nano repl --memory --dump-on-shutdown --dump-file /backups/session_$(date +%s).sql

Data Persistence in In-Memory Mode

While in-memory mode doesn’t persist data by default, you can enable persistence using the --dump-on-shutdown flag:

Option 1: Manual Dump During Session

heliosdb-nano repl --memory

# In REPL, when ready to save:
heliosdb> \dump /path/to/backup.sql
heliosdb> \q  # Exit

Option 2: Automatic Dump on Exit

# Start REPL with automatic shutdown dump
heliosdb-nano repl --memory --dump-on-shutdown --dump-file mydb.sql

# Make changes, then exit - dump happens automatically
heliosdb> CREATE TABLE users (id INT, name TEXT);
heliosdb> INSERT INTO users VALUES (1, 'Alice');
heliosdb> \q
# Automatically dumps to mydb.sql

# Restore in new session
heliosdb-nano repl --memory --execute "SOURCE mydb.sql"

Option 3: Periodic Snapshots

# Combined approach: both periodic dumps and shutdown dump
heliosdb-nano repl --memory --dump-on-shutdown --dump-file session_backup.sql

# In REPL, periodically dump critical data:
heliosdb> \dump /backups/hourly_$(date +\%s).sql

Comparison of Persistence Methods:

Method	Usage	When Data Saved	Restore Method
Manual \dump	Explicit dump commands	When user runs command	Manual restore
--dump-on-shutdown	Flag on startup	On process exit	Auto-source or manual
Server mode	Persistent flag	Continuous to disk	Automatic on startup
Periodic backups	Scripted dumps	On schedule	Manual restore from backup

Embedded Mode (In-Process)

use heliosdb_nano::EmbeddedDatabase;

// Create in-memory database instance
let db = EmbeddedDatabase::new_in_memory()?;

// Execute SQL
db.execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT)")?;
db.execute("INSERT INTO users VALUES (1, 'Alice')")?;

let results = db.query("SELECT * FROM users", &[])?;

Multi-User Setup

Creating User Sessions

In-memory mode supports concurrent user sessions with independent transaction contexts:

-- Sessions are created automatically on connection
-- Each connection gets a unique session ID

-- View active sessions
SELECT * FROM pg_stat_activity;

-- Example output:
-- pid | usename | application_name | state  | query_start
-- ----|---------|------------------|--------|-------------
-- 101 | alice   | psql             | active | 2025-12-08 10:00:00
-- 102 | bob     | python           | idle   | 2025-12-08 10:01:30

User Authentication

Configure authentication in config.toml:

[session]
# Session timeout in seconds
timeout_secs = 3600

# Maximum sessions per user
max_sessions_per_user = 10

# Default isolation level
default_isolation_level = "READ_COMMITTED"

[auth]
# Authentication method: trust, password, or scram-sha-256
method = "password"

# User credentials (for development - use external auth in production)
[[auth.users]]
username = "alice"
password_hash = "$argon2id$v=19$m=4096,t=3,p=1$..."
roles = ["read", "write"]

[[auth.users]]
username = "bob"
password_hash = "$argon2id$v=19$m=4096,t=3,p=1$..."
roles = ["read"]

Connecting as Different Users

# Connect via psql
psql "postgresql://alice:password@localhost:5432/heliosdb"

# Connect via Python
import psycopg2
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="alice",
    password="password",
    database="heliosdb"
)

Session Management

Session Lifecycle

1. Connection: Client connects and authenticates
2. Session Creation: System assigns unique session ID
3. Transaction Execution: User executes queries within session context
4. Session Timeout: Idle sessions terminated after timeout_secs
5. Explicit Termination: Client disconnects or calls \q

Session Isolation

Each session maintains:

• Independent transaction state: BEGIN/COMMIT/ROLLBACK per session
• Separate snapshot: MVCC snapshot based on isolation level
• Resource quotas: Per-user memory and connection limits
• Audit trail: All session actions logged

Session Monitoring

-- View all active sessions
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Count sessions per user
SELECT usename, COUNT(*) as session_count
FROM pg_stat_activity
GROUP BY usename;

-- Check session resource usage
SELECT pid, usename,
       pg_session_memory_usage(pid) as memory_bytes,
       xact_start as transaction_start
FROM pg_stat_activity;

Isolation Levels

HeliosDB Nano supports three PostgreSQL-compatible isolation levels:

1. READ COMMITTED (Default)

Guarantees:

• No dirty reads (uncommitted data)
• Fresh snapshot per SQL statement
• See latest committed data

Use Cases:

• Web applications with short transactions
• Interactive queries and reports
• Low-contention workloads

Example:

-- Session 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000

-- Session 2 (concurrent)
BEGIN;
UPDATE accounts SET balance = 1200 WHERE id = 1;
COMMIT;

-- Session 1 (continued)
SELECT balance FROM accounts WHERE id = 1; -- Returns 1200 (sees update)
COMMIT;

2. REPEATABLE READ

Guarantees:

• No dirty reads
• No non-repeatable reads
• Consistent snapshot throughout transaction
• May see phantom rows from concurrent inserts

Use Cases:

• Long-running reports requiring consistency
• Multi-step transactions reading same data
• Data exports and backups

Example:

-- Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000

-- Session 2 (concurrent)
UPDATE accounts SET balance = 1200 WHERE id = 1;
COMMIT;

-- Session 1 (continued)
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000 (snapshot isolated)
COMMIT;

3. SERIALIZABLE

Guarantees:

• Full serializability
• No dirty reads, non-repeatable reads, or phantom reads
• Transactions appear to execute sequentially
• May abort with serialization errors

Use Cases:

• Financial transactions requiring strict consistency
• Concurrent inventory management
• Critical business logic with conflicts

Example:

-- Session 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Session 2 (concurrent)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
COMMIT;

-- Session 1 (continued)
COMMIT; -- ERROR: could not serialize access due to concurrent update

Setting Isolation Levels

-- Per transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- For current session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- In config.toml (default for all sessions)
[session]
default_isolation_level = "SERIALIZABLE"

Performance Characteristics

Latency Benchmarks (v3.1.0)

Operation	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Simple SELECT	0.05ms	0.08ms	0.12ms
Single INSERT	0.15ms	0.18ms	0.25ms
UPDATE (no conflicts)	0.20ms	0.22ms	0.30ms
UPDATE (with conflicts)	0.30ms	0.35ms	5-30ms (retry)
Transaction COMMIT	0.10ms	0.12ms	0.50ms

Throughput Benchmarks

Workload	Throughput	Concurrency	Notes
Read-only queries	50,000 QPS	100 sessions	No lock contention
Mixed read/write	20,000 TPS	50 sessions	80% reads, 20% writes
Write-heavy	8,000 TPS	20 sessions	High lock contention
Serializable TXNs	3,000 TPS	10 sessions	Conflict detection overhead

Memory Usage

Base overhead: ~50MB (runtime + RocksDB)
Per session: ~2MB (snapshot + buffers)
Per active transaction: ~500KB (write buffer)
Per 1M rows (100 bytes avg): ~100MB (uncompressed)

Scaling Recommendations

Sessions	RAM Required	Notes
1-10	256MB	Development/testing
10-100	1GB	Small applications
100-1000	4GB	Medium workloads
1000-10000	16GB+	High concurrency

Resource Quotas

Configuring Quotas

[resource_quotas]
# Per-user memory limit (bytes, 0 = unlimited)
memory_limit_per_user = 1073741824  # 1GB

# Per-user connection limit
connection_limit_per_user = 10

# Per-transaction timeout (seconds)
transaction_timeout_secs = 300  # 5 minutes

# Lock acquisition timeout (milliseconds)
lock_timeout_ms = 30000  # 30 seconds

Monitoring Resource Usage

-- Check current usage vs limits
SELECT usename,
       COUNT(*) as connections,
       SUM(pg_session_memory_usage(pid)) as total_memory_bytes
FROM pg_stat_activity
GROUP BY usename;

-- Find sessions exceeding limits
SELECT pid, usename, state,
       pg_session_memory_usage(pid) / 1024 / 1024 as memory_mb
FROM pg_stat_activity
WHERE pg_session_memory_usage(pid) > 1073741824;  -- 1GB

Quota Enforcement

When quotas are exceeded:

• Connection limit: New connections rejected with error
• Memory limit: Transaction aborts with OOM error
• Transaction timeout: Transaction automatically rolled back
• Lock timeout: Lock acquisition fails, transaction can retry

Best Practices

1. Data Durability

# Schedule periodic dumps (cron)
0 */6 * * * heliosdb-nano dump --output /backups/db-$(date +\%Y\%m\%d-\%H\%M).heliodump

# Check dirty state before shutdown
SELECT pg_stat_get_dirty_bytes() > 0 as has_unsaved_changes;

# Dump if dirty
heliosdb-nano dump --output /backups/shutdown-$(date +\%Y\%m\%d-\%H\%M\%S).heliodump

2. Transaction Management

-- Keep transactions short
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
COMMIT;  -- Don't leave idle transactions open

-- Use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- For most cases
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;    -- Only when necessary

3. Lock Contention

-- Avoid long-running transactions holding locks
BEGIN;
SELECT * FROM hot_table WHERE id = 1 FOR UPDATE;  -- Acquires lock
-- ... do work quickly ...
COMMIT;  -- Release lock ASAP

-- Use batch updates to reduce lock duration
UPDATE orders SET status = 'shipped'
WHERE id IN (SELECT id FROM orders WHERE status = 'pending' LIMIT 1000);

4. Session Hygiene

-- Close idle sessions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < NOW() - INTERVAL '5 minutes';

-- Monitor session count
SELECT COUNT(*) FROM pg_stat_activity;  -- Keep under max_connections

5. Memory Management

// Use connection pooling
use r2d2_postgres::{PostgresConnectionManager, Pool};

let manager = PostgresConnectionManager::new(
    "postgresql://localhost:5432/heliosdb",
    postgres::NoTls
);
let pool = Pool::builder()
    .max_size(20)  // Limit concurrent connections
    .build(manager)?;

Example Scenarios

Scenario 1: Session-Based Web Application

Use Case: Store user session data with 30-minute TTL

use heliosdb_nano::EmbeddedDatabase;
use std::time::SystemTime;

// Initialize in-memory database
let db = EmbeddedDatabase::new_in_memory()?;

db.execute("
    CREATE TABLE sessions (
        session_id UUID PRIMARY KEY,
        user_id INT NOT NULL,
        data JSONB NOT NULL,
        created_at TIMESTAMP NOT NULL,
        expires_at TIMESTAMP NOT NULL
    )
")?;

// Create index for expiration queries
db.execute("CREATE INDEX idx_sessions_expires ON sessions(expires_at)")?;

// Insert session
db.execute("
    INSERT INTO sessions VALUES (
        gen_random_uuid(),
        1234,
        '{\"cart\": [\"item1\", \"item2\"]}',
        NOW(),
        NOW() + INTERVAL '30 minutes'
    )
")?;

// Cleanup expired sessions (run every minute)
db.execute("DELETE FROM sessions WHERE expires_at < NOW()")?;

// Schedule daily dump
// cron: 0 2 * * * heliosdb-nano dump --output /backups/sessions.heliodump

Scenario 2: Real-Time Analytics Cache

Use Case: Cache aggregated metrics with periodic refresh

-- Create metrics cache table
CREATE TABLE metrics_cache (
    metric_key TEXT PRIMARY KEY,
    metric_value NUMERIC,
    dimensions JSONB,
    last_updated TIMESTAMP
);

-- Populate from source (runs every 5 minutes)
INSERT INTO metrics_cache
SELECT
    'daily_revenue' as metric_key,
    SUM(amount) as metric_value,
    jsonb_build_object('date', CURRENT_DATE) as dimensions,
    NOW() as last_updated
FROM orders
WHERE created_at >= CURRENT_DATE
ON CONFLICT (metric_key) DO UPDATE
SET metric_value = EXCLUDED.metric_value,
    last_updated = EXCLUDED.last_updated;

-- Fast reads from cache (< 1ms)
SELECT metric_value FROM metrics_cache WHERE metric_key = 'daily_revenue';

Scenario 3: Concurrent Inventory Management

Use Case: Handle concurrent product reservations with SERIALIZABLE isolation

-- Enable SERIALIZABLE for this session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

-- Check availability
SELECT quantity FROM inventory WHERE product_id = 123;

-- Reserve items (will fail if concurrent update conflicts)
UPDATE inventory
SET quantity = quantity - 1,
    reserved = reserved + 1
WHERE product_id = 123 AND quantity > 0;

-- If successful, create reservation
INSERT INTO reservations (product_id, user_id, created_at)
VALUES (123, 456, NOW());

COMMIT;  -- May fail with serialization error, client should retry

Scenario 4: Development/Testing Environment

Use Case: Fast test execution with clean state

test_runner.sh

#!/bin/bash
# Start in-memory database
heliosdb-nano start --memory --port 5555 &
DB_PID=$!

# Wait for startup
sleep 2

# Run tests
pytest tests/

# Cleanup (data automatically discarded)
kill $DB_PID

Scenario 5: Batch Data Processing Pipeline

Use Case: Load, transform, and export data

import psycopg2

# Connect to in-memory instance
conn = psycopg2.connect("postgresql://localhost:5432/heliosdb")
cur = conn.cursor()

# Create staging table
cur.execute("""
    CREATE TABLE staging_data (
        id INT,
        raw_json JSONB
    )
""")

# Bulk load data
with open('input.jsonl') as f:
    for line in f:
        cur.execute("INSERT INTO staging_data VALUES (nextval('seq'), %s)", (line,))

conn.commit()

# Transform data
cur.execute("""
    CREATE TABLE processed_data AS
    SELECT
        id,
        raw_json->>'field1' as field1,
        (raw_json->>'field2')::INT as field2
    FROM staging_data
    WHERE raw_json->>'valid' = 'true'
""")

conn.commit()

# Export results
cur.execute("SELECT * FROM processed_data")
with open('output.csv', 'w') as f:
    # ... write results ...

# Optionally dump final state
# heliosdb-nano dump --output pipeline_state.heliodump

conn.close()

Troubleshooting

Issue: Out of Memory Errors

Symptoms:

ERROR: memory limit exceeded for user 'alice'
ERROR: cannot allocate memory for transaction

Solutions:

1. Increase per-user quota:

   [resource_quotas]
   memory_limit_per_user = 2147483648  # 2GB

2. Reduce concurrent sessions:

   [session]
   max_sessions_per_user = 5

3. Batch large operations:

   -- Instead of: INSERT INTO ... SELECT * FROM huge_table;
   -- Do this:
   INSERT INTO target SELECT * FROM huge_table LIMIT 10000;
   -- Repeat in batches

Issue: Serialization Errors

Symptoms:

ERROR: could not serialize access due to concurrent update

Solutions:

1. Use lower isolation level if acceptable:

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Implement retry logic:

   max_retries = 3
   for attempt in range(max_retries):
       try:
           conn.execute("BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE")
           # ... transaction logic ...
           conn.commit()
           break
       except psycopg2.extensions.TransactionRollbackError:
           if attempt == max_retries - 1:
               raise
           time.sleep(0.1 * (2 ** attempt))  # Exponential backoff

3. Reduce transaction duration:

   BEGIN;
   -- Minimize time between BEGIN and COMMIT
   UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
   COMMIT;

Issue: Lock Timeout Errors

Symptoms:

ERROR: lock timeout exceeded
ERROR: could not obtain lock on row

Solutions:

1. Increase lock timeout:

   [locks]
   timeout_ms = 60000  # 60 seconds

2. Check for deadlocks:

   SELECT * FROM pg_locks WHERE NOT granted;

3. Identify blocking sessions:

   SELECT blocking.pid AS blocking_pid,
          blocked.pid AS blocked_pid,
          blocking.query AS blocking_query
   FROM pg_stat_activity blocking
   JOIN pg_locks blocked_locks ON blocking.pid = blocked_locks.pid
   JOIN pg_stat_activity blocked ON blocked_locks.pid = blocked.pid
   WHERE NOT blocked_locks.granted;

Issue: Connection Limit Exceeded

Symptoms:

ERROR: connection limit exceeded for user 'alice'
ERROR: too many connections

Solutions:

1. Close idle connections:

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'alice' AND state = 'idle';

2. Use connection pooling:

   from psycopg2 import pool
   connection_pool = pool.SimpleConnectionPool(1, 10, dsn="...")

3. Increase limit:

   [resource_quotas]
   connection_limit_per_user = 20

Issue: Data Loss on Shutdown

Symptoms:

• Data disappears after restart
• No warning on shutdown

Solutions:

1. Enable shutdown warnings:

   [dump]
   warn_on_dirty_shutdown = true

2. Check dirty state before shutdown:

   heliosdb-nano status
   # Dirty state: YES (1234 uncommitted changes)

3. Automated pre-shutdown dump:

   # systemd service file
   [Service]
   ExecStop=/usr/bin/heliosdb-nano dump --output /backups/shutdown.heliodump
   ExecStop=/bin/kill -SIGTERM $MAINPID

Issue: Performance Degradation

Symptoms:

• Queries slower than expected
• High lock contention

Solutions:

1. Monitor active transactions:

   SELECT COUNT(*), state FROM pg_stat_activity GROUP BY state;

2. Check for long-running transactions:

   SELECT pid, usename, state, NOW() - xact_start as duration
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL
   ORDER BY duration DESC;

3. Analyze lock contention:

   SELECT locktype, COUNT(*) FROM pg_locks GROUP BY locktype;

4. Enable query profiling:

   EXPLAIN ANALYZE SELECT ...;

See Also

• Dump and Restore Guide - Data persistence procedures
• CLI Reference - Command-line interface
• Configuration Reference - Configuration options
• Session Management API - Programmatic API
• Multi-User Transactions Architecture - System design

---

Version: 3.1.0 Last Updated: 2025-12-08 Maintained by: HeliosDB Team