HeliosDB Caching Configuration Guide

Version: 1.0 Last Updated: 2025-11-30

Quick Start

-- Enable query caching
ALTER DATABASE ENABLE QUERY_CACHE;

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

-- Enable for table
ALTER TABLE frequently_queried ENABLE INTELLIGENT_CACHING;

Cache Types

Query Result Cache

-- Cache full query results
SELECT /*+ CACHE */ customer_id, COUNT(*) as orders
FROM orders
GROUP BY customer_id;

-- Manual cache management
CACHE SELECT * FROM products WHERE active = true;
UNCACHE SELECT * FROM products;

Column-Level Cache

-- Cache frequently accessed column
ALTER TABLE orders
ADD COLUMN cached_amount DECIMAL(10,2) AS (quantity * unit_price)
STORED WITH CACHE;

-- Refresh cache
REFRESH CACHE ON TABLE orders COLUMN amount;

Distributed Cache

-- Global cache across cluster
ALTER TABLE customers ENABLE GLOBAL_CACHE;

-- Configure replication
ALTER TABLE customers SET CACHE_PARAMS (
  replication_factor = 3,
  consistency_level = 'EVENTUAL'
);

Configuration Parameters

Cache Size & Eviction

ALTER DATABASE SET CACHE_PARAMS (
  -- Memory limits
  max_size_mb = 2048,           -- Total cache size
  per_query_max_mb = 512,       -- Per-query limit

  -- Eviction strategy
  eviction_policy = 'W_TINYLFU', -- Window-TinyLFU (default)
  -- OR 'LRU' (Least Recently Used)
  -- OR 'LFU' (Least Frequently Used)
  -- OR 'RANDOM'

  -- TTL (Time To Live)
  ttl_seconds = 3600            -- Cache for 1 hour
);

Adaptive Caching

-- ML-based intelligent caching
ALTER DATABASE ENABLE ADAPTIVE_CACHING;

ALTER DATABASE SET CACHE_PARAMS (
  learning_mode = 'RL',         -- Reinforcement Learning
  adaptation_interval = 300,    -- Check every 5 minutes
  confidence_threshold = 0.85   -- High confidence before adapting
);

Monitoring Cache

-- Cache statistics
SELECT
  cache_hits,
  cache_misses,
  hit_rate,
  eviction_count,
  memory_used_mb
FROM cache_statistics;

-- Per-table cache performance
SELECT
  table_name,
  cache_entries,
  avg_entry_size_kb,
  hit_rate,
  last_updated
FROM table_cache_stats;

Best Practices

Cache hot data - Cache frequently accessed tables
Set appropriate TTL - Balance freshness and performance
Monitor hit rates - Aim for 80%+ cache hit rate
Tune eviction policy - Use W_TINYLFU for most workloads
Disable for write-heavy tables - Cache slows writes

Troubleshooting

-- Check cache health
SELECT * FROM cache_diagnostic_report;

-- Increase hit rate
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 4096,  -- Double cache size
  eviction_policy = 'W_TINYLFU'
);

-- Reduce memory pressure
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 512,   -- Reduce cache
  per_query_max_mb = 128
);

Related Documentation: