---
title: "In-Memory Mode User Guide"
---

# In-Memory Mode User Guide


## Table of Contents

1. [Overview](#overview)
2. [When to Use In-Memory Mode](#when-to-use-in-memory-mode)
3. [Getting Started](#getting-started)
4. [Multi-User Setup](#multi-user-setup)
5. [Session Management](#session-management)
6. [Isolation Levels](#isolation-levels)
7. [Performance Characteristics](#performance-characteristics)
8. [Resource Quotas](#resource-quotas)
9. [Best Practices](#best-practices)
10. [Example Scenarios](#example-scenarios)
11. [Troubleshooting](#troubleshooting)

## Overview

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

- **No disk I/O**: reads and writes stay in memory
- **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

```bash
# 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

```bash
# 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**
```bash
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**
```bash
# Start REPL with automatic shutdown dump
heliosdb-nano repl --memory --dump-on-shutdown --dump-file mydb.heliodump

# 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.heliodump

# Restore into a data directory and continue from there
heliosdb-nano restore --input mydb.heliodump --target ./mydb
heliosdb-nano repl --data-dir ./mydb
```

**Option 3: Periodic Snapshots**
```bash
# 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)

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

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

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

```bash
# 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

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

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

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

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

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

| 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

```toml
[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

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

```bash
# 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

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

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

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

```rust
// 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

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

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

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

```bash
#!/bin/bash
# test_runner.sh

# 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

```python
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:**
   ```toml
   [resource_quotas]
   memory_limit_per_user = 2147483648 # 2GB
   ```

2. **Reduce concurrent sessions:**
   ```toml
   [session]
   max_sessions_per_user = 5
   ```

3. **Batch large operations:**
   ```sql
   -- 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:**
   ```sql
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   ```

2. **Implement retry logic:**
   ```python
   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:**
   ```sql
   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:**
   ```toml
   [locks]
   timeout_ms = 60000 # 60 seconds
   ```

2. **Check for deadlocks:**
   ```sql
   SELECT * FROM pg_locks WHERE NOT granted;
   ```

3. **Identify blocking sessions:**
   ```sql
   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:**
   ```sql
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'alice' AND state = 'idle';
   ```

2. **Use connection pooling:**
   ```python
   from psycopg2 import pool
   connection_pool = pool.SimpleConnectionPool(1, 10, dsn="...")
   ```

3. **Increase limit:**
   ```toml
   [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:**
   ```toml
   [dump]
   warn_on_dirty_shutdown = true
   ```

2. **Check dirty state before shutdown:**
   ```bash
   heliosdb-nano status
   # Dirty state: YES (1234 uncommitted changes)
   ```

3. **Automated pre-shutdown dump:**
   ```bash
   # 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:**
   ```sql
   SELECT COUNT(*), state FROM pg_stat_activity GROUP BY state;
   ```

2. **Check for long-running transactions:**
   ```sql
   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:**
   ```sql
   SELECT locktype, COUNT(*) FROM pg_locks GROUP BY locktype;
   ```

4. **Enable query profiling:**
   ```sql
   EXPLAIN ANALYZE SELECT...;
   ```

## See Also

- [Dump and Restore Guide](https://www.heliosdb.com/docs/nano/guides/user/dump_restore/) - Data persistence procedures
- [CLI Reference](https://www.heliosdb.com/docs/nano/reference/cli_reference/) - Command-line interface
- [Configuration Reference](https://www.heliosdb.com/docs/nano/reference/configuration_reference/) - Configuration options
- [Session Management API](https://www.heliosdb.com/docs/nano/api/session_management/) - Programmatic API
- [Multi-User Transactions Architecture](https://www.heliosdb.com/docs/nano/architecture/multi_user_transactions/) - System design