---
title: "HeliosDB Caching Quick Start Guide"
---

# HeliosDB Caching Quick Start Guide

---

## Prerequisites

- HeliosDB Full installed
- Database created and accessible
- Appropriate permissions (`ALTER DATABASE`, `ALTER TABLE`)

---

## 5-Minute Setup

### Step 1: Enable Query Caching

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

```sql
-- 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](https://www.heliosdb.com/docs/full/features/caching/configuration_guide/) - Advanced configuration options
- [Troubleshooting Guide](https://www.heliosdb.com/docs/full/features/caching/troubleshooting/) - Detailed problem resolution
- [Caching Overview](https://www.heliosdb.com/docs/full/features/caching/readme/) - Architecture and concepts

---

## Quick Reference

### Enable Caching

```sql
ALTER DATABASE ENABLE QUERY_CACHE;
ALTER TABLE mytable ENABLE INTELLIGENT_CACHING;
```

### Configure Cache

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

### Monitor Cache

```sql
SELECT * FROM cache_statistics;
SELECT * FROM table_cache_stats;
```

### Invalidate Cache

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