HeliosDB Caching Configuration Guide¶

Version: 7.0 Last Updated: 2025-01-04

Configuration Overview¶

This guide provides comprehensive coverage of all caching configuration options in HeliosDB. Configuration can be applied at multiple levels: database, table, and query.

Cache Sizing¶

Database-Level Memory Allocation¶

-- Total cache memory budget
ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 4096              -- 4 GB total cache memory
);

-- Maximum memory per cached query result
ALTER DATABASE SET CACHE_PARAMS (
  per_query_max_mb = 512          -- Prevent single query from consuming too much
);

-- Maximum number of cached entries
ALTER DATABASE SET CACHE_PARAMS (
  max_entries = 100000            -- Limit entry count regardless of size
);

Sizing Guidelines¶

Workload Type	Recommended Cache Size	Per-Query Max
OLTP	10-20% of available RAM	64-256 MB
Analytics	25-40% of available RAM	512 MB - 2 GB
Mixed	15-25% of available RAM	128-512 MB
Edge/Embedded	50-100 MB	10-50 MB

Calculating Optimal Cache Size¶

-- Analyze working set size
SELECT
  COUNT(*) as total_queries,
  SUM(result_size_bytes) / 1024 / 1024 as total_result_mb,
  AVG(result_size_bytes) / 1024 as avg_result_kb,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY result_size_bytes) / 1024 as p95_result_kb
FROM query_execution_log
WHERE execution_time > NOW() - INTERVAL '24 hours';

-- View current memory usage
SELECT
  cache_type,
  entries,
  size_bytes / 1024 / 1024 as size_mb,
  max_size_bytes / 1024 / 1024 as max_size_mb,
  ROUND(size_bytes::numeric / max_size_bytes * 100, 2) as utilization_pct
FROM cache_memory_stats;

Per-Tier Sizing (Multi-Tier Cache)¶

-- Configure L1-L4 tier sizes
ALTER DATABASE SET CACHE_PARAMS (
  l1_size_mb = 50,                -- Client cache (per client)
  l2_size_mb = 10240,             -- Edge cache (per location)
  l3_size_mb = 512000,            -- Regional cache
  l4_size_mb = 2097152            -- Cloud cache (2 TB)
);

TTL (Time To Live) Settings¶

Global TTL Configuration¶

-- Set default TTL for all cache entries
ALTER DATABASE SET CACHE_PARAMS (
  ttl_seconds = 3600              -- 1 hour default
);

-- Set minimum and maximum TTL bounds
ALTER DATABASE SET CACHE_PARAMS (
  min_ttl_seconds = 10,           -- Minimum 10 seconds
  max_ttl_seconds = 86400         -- Maximum 24 hours
);

Table-Specific TTL¶

-- Static reference data - long TTL
ALTER TABLE country_codes SET CACHE_PARAMS (
  ttl_seconds = 86400             -- 24 hours
);

-- Frequently changing data - short TTL
ALTER TABLE stock_prices SET CACHE_PARAMS (
  ttl_seconds = 5                 -- 5 seconds
);

-- Disable TTL (rely on invalidation only)
ALTER TABLE session_data SET CACHE_PARAMS (
  ttl_seconds = NULL              -- No expiration
);

Adaptive TTL¶

Enable ML-based TTL adjustment based on access patterns:

ALTER DATABASE SET CACHE_PARAMS (
  adaptive_ttl_enabled = true,
  adaptive_ttl_min = 60,          -- Minimum 1 minute
  adaptive_ttl_max = 7200,        -- Maximum 2 hours
  adaptive_ttl_learning_rate = 0.1
);

How Adaptive TTL Works: 1. System tracks access patterns for each query 2. Frequently accessed queries get longer TTL 3. Rarely accessed queries get shorter TTL 4. Rapidly changing data gets shorter TTL

Eviction Policies¶

W-TinyLFU (Default, Recommended)¶

Window-TinyLFU provides excellent hit rates for most workloads by combining recency and frequency.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'W_TINYLFU',
  tinylfu_window_percent = 1,     -- 1% window cache
  tinylfu_protected_percent = 80, -- 80% protected segment
  tinylfu_probation_percent = 19  -- 19% probation segment
);

Internal Structure:

+----------------+     +-------------------+     +------------------+
| Window Cache   | --> | Probation Segment | --> | Protected Segment|
| (1% - LRU)     |     | (19% - frequency) |     | (80% - frequency)|
+----------------+     +-------------------+     +------------------+
     ^                         |                         |
     |                         v                         v
     +<--- New entries    Low frequency            High frequency
                          evicted first            evicted last

LRU (Least Recently Used)¶

Simple recency-based eviction, predictable behavior.

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

Use When: - Access patterns show strong temporal locality - Debugging (predictable behavior) - Memory constrained environments

LFU (Least Frequently Used)¶

Frequency-based eviction, good for skewed workloads.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'LFU',
  lfu_aging_period_seconds = 3600  -- Reset counts every hour
);

Use When: - Clear hot/cold data separation - Workload has stable access patterns - Long-running analytics queries

ML-Based Eviction¶

Machine learning model predicts future access likelihood.

ALTER DATABASE SET CACHE_PARAMS (
  eviction_policy = 'ML',
  ml_model_type = 'GRADIENT_BOOSTING',  -- or 'RANDOM_FOREST', 'NEURAL_NET'
  ml_update_interval_seconds = 300,      -- Retrain every 5 minutes
  ml_training_sample_size = 100000       -- Use last 100K accesses
);

Feature Inputs for ML Model: - Hour of day (0-23) - Day of week (0-6) - Time since last access - Historical access count - Query execution cost - Result size - Table count in query

Random (Testing Only)¶

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

Memory Management¶

Memory Pressure Handling¶

-- Configure memory pressure thresholds
ALTER DATABASE SET CACHE_PARAMS (
  memory_pressure_low_threshold = 0.7,   -- Start evicting at 70%
  memory_pressure_high_threshold = 0.9,  -- Aggressive eviction at 90%
  memory_pressure_critical = 0.95        -- Emergency eviction at 95%
);

-- Configure eviction batch size
ALTER DATABASE SET CACHE_PARAMS (
  eviction_batch_size = 100,             -- Evict 100 entries at a time
  eviction_batch_delay_ms = 10           -- 10ms between batches
);

Memory Allocation Strategy¶

-- Pre-allocate cache memory
ALTER DATABASE SET CACHE_PARAMS (
  memory_allocation = 'PREALLOCATED',    -- or 'DYNAMIC'
  preallocate_percentage = 80            -- Allocate 80% upfront
);

Off-Heap Memory (Advanced)¶

-- Use off-heap memory for large caches
ALTER DATABASE SET CACHE_PARAMS (
  use_off_heap = true,
  off_heap_max_mb = 32768,               -- 32 GB off-heap
  off_heap_allocation_size_mb = 64       -- Allocate in 64 MB chunks
);

Distributed Cache Setup¶

Cluster Configuration¶

-- Enable distributed caching
ALTER DATABASE ENABLE DISTRIBUTED_CACHE;

-- Configure cluster nodes
ALTER DATABASE SET CACHE_PARAMS (
  cluster_nodes = 'node1:5432,node2:5432,node3:5432',
  node_id = 'node1'
);

Consistency Levels¶

-- Eventual Consistency (default, fastest)
ALTER TABLE logs SET CACHE_PARAMS (
  consistency_level = 'EVENTUAL'
);

-- Strong Consistency (slowest, always fresh)
ALTER TABLE financial_transactions SET CACHE_PARAMS (
  consistency_level = 'STRONG',
  sync_timeout_ms = 5000                 -- 5 second timeout
);

-- Read Your Writes (session consistency)
ALTER TABLE user_profiles SET CACHE_PARAMS (
  consistency_level = 'READ_YOUR_WRITES'
);

Replication Settings¶

-- Configure replication factor
ALTER TABLE products SET CACHE_PARAMS (
  replication_factor = 3,                -- 3 copies across nodes
  read_quorum = 2,                       -- Read from 2 nodes
  write_quorum = 2                       -- Write to 2 nodes
);

Failure Detection¶

ALTER DATABASE SET CACHE_PARAMS (
  heartbeat_interval_ms = 1000,          -- Heartbeat every 1 second
  failure_detection_timeout_ms = 10000,  -- Mark dead after 10 seconds
  enable_partition_detection = true
);

Partition Handling¶

ALTER DATABASE SET CACHE_PARAMS (
  partition_strategy = 'MAJORITY_WINS',  -- or 'PAUSED', 'CONTINUE_DEGRADED'
  rejoin_reconciliation = 'LAST_WRITE_WINS'  -- or 'VERSION_VECTOR'
);

Cache Warming¶

Predictive Warming¶

-- Enable ML-based predictive warming
ALTER DATABASE ENABLE PREDICTIVE_CACHE_WARMING;

ALTER DATABASE SET CACHE_PARAMS (
  warming_strategy = 'ML_PREDICTION',
  warming_interval_seconds = 60,         -- Check every minute
  warming_lookahead_minutes = 15,        -- Predict 15 minutes ahead
  warming_confidence_threshold = 0.85,   -- Only warm with 85%+ confidence
  warming_max_concurrent = 10            -- Max 10 concurrent warming queries
);

Temporal Pattern Warming¶

-- Warm cache based on time-of-day patterns
ALTER DATABASE SET CACHE_PARAMS (
  warming_strategy = 'TEMPORAL',
  warming_history_days = 7,              -- Learn from last 7 days
  warming_time_window_minutes = 30       -- Warm for 30-minute windows
);

Access Log Based Warming¶

-- Warm cache based on recent access log
ALTER DATABASE SET CACHE_PARAMS (
  warming_strategy = 'ACCESS_LOG',
  warming_access_log_hours = 24,         -- Use last 24 hours
  warming_top_n_queries = 1000           -- Warm top 1000 queries
);

Manual Cache Warming¶

-- Warm specific query
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;

-- Warm on startup
ALTER DATABASE SET CACHE_PARAMS (
  warm_on_startup = true,
  startup_warm_queries = 500             -- Warm top 500 queries on startup
);

Invalidation Configuration¶

Push-Based Invalidation¶

-- Configure invalidation propagation
ALTER DATABASE SET CACHE_PARAMS (
  invalidation_mode = 'PUSH',            -- or 'PULL', 'HYBRID'
  invalidation_batch_size = 100,
  invalidation_batch_delay_ms = 5
);

Query-Semantic Invalidation¶

-- Enable intelligent invalidation
ALTER DATABASE SET CACHE_PARAMS (
  semantic_invalidation = true,
  column_level_tracking = true,          -- Track which columns each query reads
  row_level_invalidation = true          -- Invalidate at row level when possible
);

Invalidation Logging¶

-- Enable invalidation logging for debugging
ALTER DATABASE SET CACHE_PARAMS (
  invalidation_logging = true,
  invalidation_log_retention_hours = 24
);

-- View invalidation log
SELECT
  invalidation_time,
  table_name,
  affected_queries,
  invalidation_type,
  source_operation
FROM cache_invalidation_log
ORDER BY invalidation_time DESC
LIMIT 100;

Performance Tuning¶

Concurrency Settings¶

-- Configure cache access concurrency
ALTER DATABASE SET CACHE_PARAMS (
  read_concurrency = 64,                 -- Concurrent reads
  write_concurrency = 8,                 -- Concurrent writes/insertions
  lock_stripe_count = 256                -- Lock striping for reduced contention
);

Caching Thresholds¶

-- Only cache queries meeting these criteria
ALTER DATABASE SET CACHE_PARAMS (
  min_execution_time_ms = 10,            -- Only cache queries > 10ms
  min_result_size_bytes = 1024,          -- Only cache results > 1 KB
  max_result_size_bytes = 104857600,     -- Don't cache results > 100 MB
  min_access_count_before_cache = 2      -- Only cache after 2 accesses
);

Compression¶

-- Enable cache compression
ALTER DATABASE SET CACHE_PARAMS (
  compression_enabled = true,
  compression_algorithm = 'LZ4',         -- or 'ZSTD', 'SNAPPY', 'NONE'
  compression_level = 3,                 -- 1-9 (higher = more compression)
  compression_min_size_bytes = 4096      -- Only compress entries > 4 KB
);

Statement Cache Tuning¶

-- Configure prepared statement cache
ALTER DATABASE SET CACHE_PARAMS (
  statement_cache_size = 10000,          -- Max cached statements
  statement_cache_memory_mb = 100,       -- Max memory for statements
  statement_normalization = true         -- Normalize SQL for better hits
);

Monitoring Configuration¶

Metrics Collection¶

-- Enable detailed metrics
ALTER DATABASE SET CACHE_PARAMS (
  metrics_enabled = true,
  metrics_resolution_seconds = 10,       -- Collect every 10 seconds
  metrics_retention_hours = 168          -- Keep for 1 week
);

Alerting Thresholds¶

-- Configure cache health alerts
ALTER DATABASE SET CACHE_PARAMS (
  alert_hit_rate_threshold = 0.7,        -- Alert if hit rate < 70%
  alert_memory_threshold = 0.95,         -- Alert if memory > 95%
  alert_eviction_rate_threshold = 1000   -- Alert if evictions > 1000/min
);

Debug Logging¶

-- Enable verbose cache logging (use sparingly)
ALTER DATABASE SET CACHE_PARAMS (
  debug_logging = true,
  log_cache_hits = false,                -- Don't log every hit
  log_cache_misses = true,               -- Log misses
  log_evictions = true,                  -- Log evictions
  log_invalidations = true               -- Log invalidations
);

Configuration Examples¶

High-Performance OLTP¶

ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 8192,
  per_query_max_mb = 128,
  max_entries = 500000,
  eviction_policy = 'W_TINYLFU',
  ttl_seconds = 300,
  compression_enabled = false,
  read_concurrency = 128,
  semantic_invalidation = true
);

ALTER TABLE orders ENABLE INTELLIGENT_CACHING;
ALTER TABLE customers ENABLE INTELLIGENT_CACHING;
ALTER TABLE products ENABLE INTELLIGENT_CACHING;

Analytics/Data Warehouse¶

ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 65536,
  per_query_max_mb = 4096,
  max_entries = 50000,
  eviction_policy = 'LFU',
  ttl_seconds = 7200,
  compression_enabled = true,
  compression_algorithm = 'ZSTD',
  min_execution_time_ms = 100
);

ALTER TABLE fact_sales ENABLE INTELLIGENT_CACHING;
ALTER TABLE dim_products SET CACHE_PARAMS (ttl_seconds = 86400);
ALTER TABLE dim_customers SET CACHE_PARAMS (ttl_seconds = 86400);

Multi-Region Global Application¶

ALTER DATABASE ENABLE DISTRIBUTED_CACHE;

ALTER DATABASE SET CACHE_PARAMS (
  cluster_nodes = 'us-east:5432,eu-west:5432,ap-south:5432',
  consistency_level = 'EVENTUAL',
  replication_factor = 2,
  l2_size_mb = 10240,
  l3_size_mb = 102400,
  invalidation_mode = 'PUSH',
  heartbeat_interval_ms = 500,
  failure_detection_timeout_ms = 5000
);

ALTER TABLE global_config SET CACHE_PARAMS (
  consistency_level = 'STRONG',
  replication_factor = 3
);

Edge/IoT Deployment¶

ALTER DATABASE SET CACHE_PARAMS (
  max_size_mb = 128,
  per_query_max_mb = 16,
  eviction_policy = 'LRU',
  ttl_seconds = 60,
  compression_enabled = true,
  compression_algorithm = 'LZ4',
  use_off_heap = false
);

Configuration Reference¶

Complete Parameter List¶

Parameter	Type	Default	Description
`max_size_mb`	Integer	1024	Maximum cache size in MB
`per_query_max_mb`	Integer	256	Max size per cached query
`max_entries`	Integer	10000	Maximum cache entries
`eviction_policy`	String	'W_TINYLFU'	Eviction algorithm
`ttl_seconds`	Integer	3600	Default TTL in seconds
`consistency_level`	String	'EVENTUAL'	Distributed consistency
`compression_enabled`	Boolean	false	Enable compression
`compression_algorithm`	String	'LZ4'	Compression algorithm
`semantic_invalidation`	Boolean	true	Query-aware invalidation
`warming_strategy`	String	'ML_PREDICTION'	Cache warming method
`metrics_enabled`	Boolean	true	Collect metrics
`debug_logging`	Boolean	false	Verbose logging

Viewing Current Configuration¶

-- View all cache settings
SELECT setting_name, setting_value, setting_type, description
FROM database_settings
WHERE setting_name LIKE 'cache%'
ORDER BY setting_name;

-- View table-specific settings
SELECT table_name, setting_name, setting_value
FROM table_cache_settings
ORDER BY table_name, setting_name;

Caching Overview - Architecture and concepts
Quick Start Guide - Get started quickly
Troubleshooting Guide - Problem resolution