HeliosDB Caching Quick Start Guide

Version: 7.0

Prerequisites

HeliosDB 5.0 or later installed
Database created and accessible
Appropriate permissions (ALTER DATABASE, ALTER TABLE)

5-Minute Setup

Step 1: Enable Query Caching

-- Enable query result caching for the database
ALTER DATABASE ENABLE QUERY_CACHE;

-- Verify caching is enabled
SELECT setting_value FROM database_settings WHERE setting_name = 'query_cache_enabled';

Step 2: Configure Basic Parameters

-- Set cache size and TTL
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 1024,           -- 1 GB cache
  eviction_policy = 'W_TINYLFU', -- Best general-purpose policy
  ttl_seconds = 3600            -- 1 hour default TTL
);

Step 3: Enable Table-Level Caching

-- Enable intelligent caching for specific tables
ALTER TABLE products ENABLE INTELLIGENT_CACHING;
ALTER TABLE customers ENABLE INTELLIGENT_CACHING;
ALTER TABLE orders ENABLE INTELLIGENT_CACHING;

Step 4: Verify Cache is Working

-- Run a query
SELECT * FROM products WHERE category = 'electronics';

-- Run it again and check stats
SELECT * FROM products WHERE category = 'electronics';

-- View cache statistics
SELECT cache_hits, cache_misses, hit_rate
FROM cache_statistics;

Expected output:

cache_hits | cache_misses | hit_rate
-----------|--------------|----------
         1 |            1 |     0.50

Enabling Caching

Database-Level Caching

-- Enable all caching features
ALTER DATABASE ENABLE QUERY_CACHE;
ALTER DATABASE ENABLE STATEMENT_CACHE;
ALTER DATABASE ENABLE BUFFER_POOL_CACHE;

-- Or enable everything at once
ALTER DATABASE ENABLE ALL_CACHES;

Table-Level Caching

-- Standard caching
ALTER TABLE table_name ENABLE CACHING;

-- Intelligent ML-based caching
ALTER TABLE table_name ENABLE INTELLIGENT_CACHING;

-- Distributed/global caching (for multi-node deployments)
ALTER TABLE table_name ENABLE GLOBAL_CACHE;

Query-Level Caching

-- Cache hint
SELECT /*+ CACHE */ * FROM products WHERE id = $1;

-- Cache with custom TTL
SELECT /*+ CACHE TTL=300 */ * FROM products WHERE category = $1;

-- Explicit cache command
CACHE SELECT * FROM frequently_accessed_data;

Basic Configuration

Memory Allocation

-- Set total cache memory
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 2048  -- 2 GB total cache
);

-- Set per-query limit (prevents single query consuming all cache)
ALTER DATABASE SET CACHE_PARAMS (
  per_query_max_mb = 256  -- Max 256 MB per cached query
);

TTL Settings

-- Global default TTL
ALTER DATABASE SET CACHE_PARAMS (
  ttl_seconds = 3600  -- 1 hour
);

-- Table-specific TTL
ALTER TABLE products SET CACHE_PARAMS (
  ttl_seconds = 300  -- 5 minutes (for frequently changing data)
);

ALTER TABLE static_config SET CACHE_PARAMS (
  ttl_seconds = 86400  -- 24 hours (for rarely changing data)
);

Eviction Policy

-- W-TinyLFU (recommended for most workloads)
ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'W_TINYLFU'
);

-- Other options:
-- 'LRU' - Least Recently Used (simple, predictable)
-- 'LFU' - Least Frequently Used (good for hot/cold data)
-- 'ML'  - Machine learning based (adaptive)

Monitoring Cache Performance

Quick Health Check

-- Overall cache health
SELECT
  cache_hits,
  cache_misses,
  ROUND(hit_rate * 100, 2) as hit_rate_pct,
  eviction_count,
  ROUND(memory_used_mb, 2) as memory_mb
FROM cache_statistics;

Expected healthy output:

cache_hits | cache_misses | hit_rate_pct | eviction_count | memory_mb
-----------|--------------|--------------|----------------|----------
    50000  |        2500  |        95.24 |           1500 |    512.75

Per-Table Statistics

SELECT
  table_name,
  cache_entries,
  ROUND(avg_entry_size_kb, 2) as avg_kb,
  ROUND(hit_rate * 100, 2) as hit_rate_pct,
  invalidation_count
FROM table_cache_stats
ORDER BY hit_rate DESC
LIMIT 10;

Real-Time Monitoring

-- Watch cache hit rate over time
SELECT
  time_bucket('1 minute', recorded_at) as minute,
  SUM(hits) as total_hits,
  SUM(misses) as total_misses,
  ROUND(SUM(hits)::numeric / NULLIF(SUM(hits) + SUM(misses), 0) * 100, 2) as hit_rate_pct
FROM cache_metrics
WHERE recorded_at > NOW() - INTERVAL '1 hour'
GROUP BY minute
ORDER BY minute DESC;

Cache Invalidation

Automatic Invalidation

HeliosDB automatically invalidates cache entries when data changes:

-- This UPDATE automatically invalidates related cache entries
UPDATE products SET price = 99.99 WHERE id = 123;

-- Affected cached queries are immediately invalidated:
-- - SELECT * FROM products WHERE id = 123
-- - SELECT price FROM products WHERE id = 123
-- - Any query that reads the modified row

Manual Invalidation

-- Invalidate by table
INVALIDATE CACHE WHERE table_name = 'products';

-- Invalidate all product-related caches
INVALIDATE CACHE WHERE table_name LIKE 'product%';

-- Clear entire cache
FLUSH ALL CACHES;

-- Uncache specific query
UNCACHE SELECT * FROM products WHERE featured = true;

Common Configurations

High-Traffic Web Application

-- Large cache, aggressive caching
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 8192,             -- 8 GB
  eviction_policy = 'W_TINYLFU',
  ttl_seconds = 300               -- 5 minutes
);

-- Cache frequently accessed pages
ALTER TABLE products ENABLE INTELLIGENT_CACHING;
ALTER TABLE categories ENABLE INTELLIGENT_CACHING;
ALTER TABLE users ENABLE INTELLIGENT_CACHING;

-- Enable predictive warming
ALTER DATABASE ENABLE PREDICTIVE_CACHE_WARMING;

Analytics/Reporting

-- Larger per-query limit for big result sets
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 16384,            -- 16 GB
  per_query_max_mb = 1024,        -- 1 GB per query
  eviction_policy = 'LFU',        -- Frequency-based for repeated reports
  ttl_seconds = 3600              -- 1 hour
);

-- Cache expensive analytics queries
ALTER TABLE sales_facts ENABLE INTELLIGENT_CACHING;
ALTER TABLE dim_products ENABLE INTELLIGENT_CACHING;

Real-Time Application (Low Latency)

-- Smaller cache, lower TTL for freshness
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 2048,
  eviction_policy = 'W_TINYLFU',
  ttl_seconds = 30                -- 30 seconds max staleness
);

-- Enable strong consistency for critical tables
ALTER TABLE account_balances SET CACHE_PARAMS (
  consistency_level = 'STRONG'
);

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

Development/Testing

-- Small cache, easy debugging
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 256,
  eviction_policy = 'LRU',
  ttl_seconds = 60
);

-- Enable verbose logging
ALTER DATABASE SET CACHE_PARAMS (
  enable_debug_logging = true
);

Distributed Cache Setup

For multi-node deployments, enable distributed caching:

Basic Setup

-- Enable global cache for shared tables
ALTER TABLE products ENABLE GLOBAL_CACHE;

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

Troubleshooting Quick Fixes

Low Hit Rate (<70%)

-- Increase cache size
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 4096  -- Double current size
);

-- Check if working set exceeds cache
SELECT
  SUM(entry_size_bytes) / 1024 / 1024 as working_set_mb,
  (SELECT setting_value FROM database_settings WHERE setting_name = 'cache_max_size_mb') as cache_size_mb
FROM cache_entries;

High Memory Usage

-- Reduce cache size
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 1024,
  per_query_max_mb = 128
);

-- Identify large cached entries
SELECT query_hash, size_bytes / 1024 / 1024 as size_mb
FROM cache_entries
ORDER BY size_bytes DESC
LIMIT 10;

Stale Data Issues

-- Reduce TTL
ALTER TABLE products SET CACHE_PARAMS (
  ttl_seconds = 60
);

-- Enable stronger consistency
ALTER TABLE products SET CACHE_PARAMS (
  consistency_level = 'STRONG'
);

-- Force immediate invalidation
INVALIDATE CACHE WHERE table_name = 'products';

Cache Not Being Used

-- Verify caching is enabled
SELECT setting_name, setting_value
FROM database_settings
WHERE setting_name LIKE '%cache%';

-- Check table caching status
SELECT table_name, caching_enabled
FROM table_caching_status;

-- Ensure queries are cacheable
SELECT * FROM cache_diagnostic_report;

Next Steps

Configuration Guide - Advanced configuration options
Troubleshooting Guide - Detailed problem resolution
Caching Overview - Architecture and concepts

Quick Reference

Enable Caching

ALTER DATABASE ENABLE QUERY_CACHE;
ALTER TABLE mytable ENABLE INTELLIGENT_CACHING;

Configure Cache

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

Monitor Cache

SELECT * FROM cache_statistics;
SELECT * FROM table_cache_stats;

Invalidate Cache

INVALIDATE CACHE WHERE table_name = 'mytable';
FLUSH ALL CACHES;