---
title: "HeliosDB Caching Configuration Guide"
---

# HeliosDB Caching Configuration Guide

---

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

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

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

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

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

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

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

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

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

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

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

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

---

## Memory Management

### Memory Pressure Handling

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

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

### Off-Heap Memory (Advanced)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

## Related Documentation

- [Caching Overview](https://www.heliosdb.com/docs/full/features/caching/readme/) - Architecture and concepts
- [Quick Start Guide](https://www.heliosdb.com/docs/full/features/caching/quick_start/) - Get started quickly
- [Troubleshooting Guide](https://www.heliosdb.com/docs/full/features/caching/troubleshooting/) - Problem resolution