HeliosDB Caching System

Version: 7.0 Last Updated: 2025-01-04

Overview

HeliosDB implements a sophisticated multi-tier caching architecture designed to dramatically reduce query latency while maintaining strong consistency guarantees. The caching system intelligently caches query results, prepared statements, and frequently accessed data across multiple tiers, from client-side caches to distributed global caches.

Architecture

4-Tier Caching Hierarchy

HeliosDB’s caching architecture consists of four tiers, each optimized for different latency and capacity requirements:

+---------------------------+
|   L1: Client Cache        |  <1ms latency
|   (Browser/Mobile App)    |  10-50 MB capacity
+---------------------------+
            |
            v
+---------------------------+
|   L2: Edge Cache          |  <10ms latency
|   (CDN Edge Locations)    |  1-10 GB per location
+---------------------------+
            |
            v
+---------------------------+
|   L3: Regional Cache      |  20-50ms latency
|   (Regional Data Centers) |  50-500 GB per region
+---------------------------+
            |
            v
+---------------------------+
|   L4: Cloud Cache         |  50-150ms latency
|   (Origin Data Center)    |  1-10 TB capacity
+---------------------------+
            |
            v
+---------------------------+
|   Database Storage        |
|   (Source of Truth)       |
+---------------------------+

Tier Details

L1: Client Cache

The client cache runs directly in the user’s browser (IndexedDB) or mobile application (SQLite). It provides sub-millisecond access to recently queried data.

Latency: <1ms
Capacity: 10-50 MB
TTL: 5-60 seconds (push-invalidated)
Hit Rate: ~95% for repeat queries

// JavaScript SDK example
const result = await heliosdb.query({
  sql: "SELECT * FROM products WHERE id = $1",
  params: [productId],
  cacheOptions: {
    tier: 'L1',
    ttl: 30 // seconds
  }
});

L2: Edge Cache

Edge caches are deployed at 200+ CDN edge locations worldwide, providing global low-latency access to cached query results.

Latency: <10ms
Capacity: 1-10 GB per location
TTL: 1-10 minutes (push-invalidated)
Hit Rate: 85-90%

L3: Regional Cache

Regional caches serve as aggregation points for edge nodes and handle cache misses from L2.

Latency: 20-50ms
Capacity: 50-500 GB per region
TTL: 10-60 minutes
Hit Rate: 70-80%

L4: Cloud Cache

The cloud cache is co-located with the origin database and provides the largest cache capacity.

Latency: 50-150ms
Capacity: 1-10 TB
TTL: Hours to days
Hit Rate: 60-70%

Cache Types

1. Query Result Cache

Caches complete query results for rapid retrieval on subsequent executions.

-- Enable query result caching
ALTER DATABASE ENABLE QUERY_CACHE;

-- Configure cache parameters
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 2048,
  eviction_policy = 'W_TINYLFU',
  ttl_seconds = 3600
);

-- Cache a specific query result
SELECT /*+ CACHE */ customer_id, SUM(order_total)
FROM orders
GROUP BY customer_id;

-- Manual cache control
CACHE SELECT * FROM products WHERE active = true;
UNCACHE SELECT * FROM products WHERE category = 'electronics';

2. Statement Cache (Prepared Statements)

Caches parsed and optimized query plans for prepared statements.

use heliosdb_pooling::{IntelligentConnectionPool, Parameters, ParameterValue};

// Create pool with statement caching
let pool = IntelligentConnectionPool::new(config, PoolMode::Transaction)?;
pool.initialize().await?;

// Prepare statement (cached automatically)
let stmt = pool.prepare("SELECT * FROM users WHERE id = $1")?;

// Execute with parameters - reuses cached plan
let params = Parameters::Positional(vec![ParameterValue::Int64(123)]);
pool.execute(&stmt, params).await?;

// Check cache performance
let stats = pool.statement_cache_stats();
println!("Hit rate: {:.2}%", stats.hit_rate * 100.0);

3. Buffer Pool

In-memory cache for frequently accessed data pages and indexes.

-- Configure buffer pool size
ALTER SYSTEM SET buffer_pool_size_gb = 16;

-- View buffer pool statistics
SELECT
  pool_name,
  pages_total,
  pages_used,
  hit_ratio,
  evictions
FROM buffer_pool_stats;

4. Distributed/Global Cache

Provides cache coherence across multiple database nodes with configurable consistency levels.

-- Enable distributed caching
ALTER TABLE products ENABLE GLOBAL_CACHE;

-- Configure consistency level
ALTER TABLE products SET CACHE_PARAMS (
  replication_factor = 3,
  consistency_level = 'EVENTUAL'  -- or 'STRONG', 'READ_YOUR_WRITES'
);

Eviction Policies

HeliosDB supports multiple eviction strategies:

W-TinyLFU (Default)

Window-TinyLFU combines frequency and recency, providing excellent hit rates for diverse workloads.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'W_TINYLFU'
);

How it works:

New entries go into a small “window” cache (LRU)
On eviction from window, frequency is estimated using Count-Min Sketch
Victims are selected by comparing frequency scores
Protects against scan pollution

LRU (Least Recently Used)

Simple recency-based eviction, good for temporal locality.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'LRU'
);

LFU (Least Frequently Used)

Frequency-based eviction, optimal for skewed access patterns.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'LFU'
);

ML-Based Eviction

Machine learning model predicts which entries are least likely to be accessed.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'ML',
  ml_model_update_interval = 300  -- Update model every 5 minutes
);

Cache Invalidation

Push-Based Invalidation

HeliosDB uses push-based invalidation to maintain consistency across all cache tiers:

Database Write (UPDATE products SET price=99.99 WHERE id=123)
                    |
                    v
        +-------------------+
        | Invalidation      |
        | Engine            |
        +-------------------+
                    |
    +---------------+---------------+
    |               |               |
    v               v               v
+--------+    +---------+    +---------+
|L3 Hubs |    |L2 Edges |    |L1 Client|
|(5ms)   |    |(15ms)   |    |(25ms)   |
+--------+    +---------+    +---------+

Total invalidation latency: ~45ms (all tiers invalidated)

Query-Semantic Invalidation

The invalidation engine analyzes SQL statements to invalidate only affected queries:

-- When this UPDATE runs:
UPDATE products SET price = 99.99 WHERE id = 123;

-- These cached queries are invalidated:
-- Query 1: SELECT * FROM products WHERE id = 123
-- Query 2: SELECT price FROM products WHERE id = 123

-- But NOT these (unaffected):
-- Query 3: SELECT * FROM products WHERE id = 456
-- Query 4: SELECT name FROM products WHERE id = 123

Benefits:

85-95% reduction in invalidation overhead
97%+ cache hit rate (vs. 60% with time-based expiration)
Zero staleness violations

Manual Invalidation

-- Invalidate specific queries
INVALIDATE CACHE WHERE table_name = 'products';

-- Invalidate by key
INVALIDATE CACHE KEY 'query_hash_abc123';

-- Clear all caches
FLUSH ALL CACHES;

Consistency Levels

Eventual Consistency

Cache may be temporarily stale, but will eventually converge.

ALTER TABLE logs SET CACHE_PARAMS (
  consistency_level = 'EVENTUAL'
);

Use when:

Data freshness is not critical
Maximum read performance needed
Analytics/reporting workloads

Strong Consistency

All reads see the most recent write.

ALTER TABLE accounts SET CACHE_PARAMS (
  consistency_level = 'STRONG'
);

Use when:

Financial data
Inventory management
Correctness is critical

Read-Your-Writes

Clients always see their own writes.

ALTER TABLE user_preferences SET CACHE_PARAMS (
  consistency_level = 'READ_YOUR_WRITES'
);

Use when:

User-facing applications
Session state
Balance between performance and consistency

Cache Warming

HeliosDB supports predictive cache warming based on historical access patterns:

Automatic Warming

ALTER DATABASE ENABLE PREDICTIVE_CACHE_WARMING;

ALTER DATABASE SET CACHE_PARAMS (
  warming_strategy = 'ML_PREDICTION',  -- or 'TEMPORAL', 'ACCESS_LOG'
  warming_interval_seconds = 60,
  warming_confidence_threshold = 0.85
);

Manual Warming

-- Warm cache with specific queries
WARM CACHE WITH QUERY 'SELECT * FROM products WHERE featured = true';

-- Warm from access log
WARM CACHE FROM ACCESS_LOG SINCE '24 hours ago' TOP 100;

Monitoring

Cache Statistics

-- Overall cache statistics
SELECT
  cache_hits,
  cache_misses,
  hit_rate,
  eviction_count,
  memory_used_mb,
  avg_access_time_us
FROM cache_statistics;

-- Per-table cache performance
SELECT
  table_name,
  cache_entries,
  avg_entry_size_kb,
  hit_rate,
  invalidation_count
FROM table_cache_stats
ORDER BY hit_rate DESC;

-- Tier-specific statistics
SELECT
  tier,
  total_hits,
  total_misses,
  hit_rate,
  avg_latency_ms,
  current_size_mb
FROM cache_tier_stats;

Cache Diagnostic Report

SELECT * FROM cache_diagnostic_report;

Returns detailed analysis including:

Hit rate trends
Memory pressure indicators
Eviction patterns
Invalidation frequency
Recommended optimizations

Performance Expectations

Typical Hit Rates

Workload Type	Expected Hit Rate
OLTP	85-95%
Batch Processing	90-99%
Analytics	60-80%
Mixed	75-90%

Latency Comparison

Scenario	Latency
L1 Cache Hit	<1ms
L2 Cache Hit	~10ms
L3 Cache Hit	~40ms
L4 Cache Hit	~100ms
Database Query	150-500ms

Statement Cache Performance

Operation	Time
Cache Hit	5-10 microseconds
Cache Miss (with parsing)	50-100 microseconds
Parameter Binding	1-5 microseconds

Best Practices

Cache hot data - Focus caching on frequently accessed tables and queries
Set appropriate TTLs - Balance freshness requirements with performance gains
Monitor hit rates - Aim for 80%+ overall cache hit rate
Use W-TinyLFU - Default eviction policy works well for most workloads
Avoid caching write-heavy tables - Cache overhead can slow writes
Normalize SQL - Consistent query formatting improves cache key matching
Use prepared statements - Statement cache dramatically improves repeated query performance
Enable invalidation logging - Debug cache consistency issues
Size caches appropriately - Larger is not always better; consider working set size
Test with production workloads - Cache behavior varies significantly by access pattern

Quick Start Guide - Get started with caching
Configuration Guide - Detailed configuration options
Troubleshooting Guide - Common issues and solutions
Statement Cache Reference
Connection Pooling Guide

Glossary

Cache Hit: Query result found in cache, returned without database access
Cache Miss: Query result not in cache, requires database execution
Cache Eviction: Removing entries from cache to make room for new data
TTL (Time To Live): Maximum time a cache entry remains valid
Cache Coherence: Ensuring all cache copies have consistent data
Push Invalidation: Server actively notifies caches when data changes
Query-Semantic Invalidation: Analyzing SQL to invalidate only affected queries
W-TinyLFU: Window-TinyLFU, a frequency-aware eviction algorithm

HeliosDB Caching System

HeliosDB Caching System

Overview

Architecture

4-Tier Caching Hierarchy

Tier Details

L1: Client Cache

L2: Edge Cache

L3: Regional Cache

L4: Cloud Cache

Cache Types

1. Query Result Cache

2. Statement Cache (Prepared Statements)

3. Buffer Pool

4. Distributed/Global Cache

Eviction Policies

W-TinyLFU (Default)

LRU (Least Recently Used)

LFU (Least Frequently Used)

ML-Based Eviction

Cache Invalidation

Push-Based Invalidation

Query-Semantic Invalidation

Manual Invalidation

Consistency Levels

Eventual Consistency

Strong Consistency

Read-Your-Writes

Cache Warming

Automatic Warming

Manual Warming

Monitoring

Cache Statistics

Cache Diagnostic Report

Performance Expectations

Typical Hit Rates

Latency Comparison

Statement Cache Performance

Best Practices

Related Documentation

Glossary