Caching strategies - database caching, application-level caching, and cache invalidation

Database Caching

Caching reduces database load by storing frequently accessed data in faster storage layers. Effective caching can dramatically improve application performance.

Caching Architecture

┌─────────────────────────────────────────────────────────┐
│                    Application                          │
│                         │                               │
│                         ▼                               │
│              ┌──────────────────┐                       │
│              │   Application    │  L1: In-process       │
│              │      Cache       │  (fastest, limited)   │
│              └────────┬─────────┘                       │
│                       │ miss                            │
│                       ▼                                 │
│              ┌──────────────────┐                       │
│              │  Distributed     │  L2: Redis/Memcached  │
│              │     Cache        │  (fast, scalable)     │
│              └────────┬─────────┘                       │
│                       │ miss                            │
│                       ▼                                 │
│              ┌──────────────────┐                       │
│              │    Database      │  L3: Buffer pool      │
│              │  (Buffer Pool)   │  (memory + disk)      │
│              └────────┬─────────┘                       │
│                       │                                 │
│                       ▼                                 │
│              ┌──────────────────┐                       │
│              │      Disk        │  Persistent storage   │
│              └──────────────────┘                       │
└─────────────────────────────────────────────────────────┘

Database Buffer Pool

PostgreSQL Shared Buffers

-- Configure shared buffers (typically 25% of RAM)
-- postgresql.conf
shared_buffers = '4GB'

-- Check buffer cache hit ratio
SELECT
    sum(heap_blks_hit) as hits,
    sum(heap_blks_read) as reads,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::float as ratio
FROM pg_statio_user_tables;

-- Check specific table cache status
SELECT relname, heap_blks_hit, heap_blks_read,
       heap_blks_hit::float / (heap_blks_hit + heap_blks_read) as hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY heap_blks_read DESC;

-- Target: > 99% cache hit ratio

MySQL InnoDB Buffer Pool

-- Configure buffer pool (70-80% of RAM for dedicated servers)
-- my.cnf
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8  -- For large pools

-- Check buffer pool status
SHOW ENGINE INNODB STATUS;

-- Monitor buffer pool hit ratio
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS hit_ratio
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

Query Result Caching

Application-Level Caching with Redis

import redis
import json

redis_client = redis.Redis(host='localhost', port=6379)

def get_user(user_id):
    # Check cache first
    cache_key = f"user:{user_id}"
    cached = redis_client.get(cache_key)

    if cached:
        return json.loads(cached)

    # Cache miss - query database
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)

    # Store in cache with TTL
    redis_client.setex(
        cache_key,
        3600,  # 1 hour TTL
        json.dumps(user)
    )

    return user

def update_user(user_id, data):
    # Update database
    db.execute("UPDATE users SET ... WHERE id = %s", user_id)

    # Invalidate cache
    redis_client.delete(f"user:{user_id}")

Caching Patterns

Cache-Aside (Lazy Loading):
┌─────────────────────────────────────────────────────────┐
│ Read:                                                   │
│   1. Check cache                                        │
│   2. If miss, read from DB                              │
│   3. Store in cache                                     │
│   4. Return data                                        │
│                                                         │
│ Write:                                                  │
│   1. Write to DB                                        │
│   2. Invalidate/update cache                            │
└─────────────────────────────────────────────────────────┘

Write-Through:
┌─────────────────────────────────────────────────────────┐
│ Write:                                                  │
│   1. Write to cache                                     │
│   2. Cache writes to DB (synchronously)                 │
│   3. Return success                                     │
│                                                         │
│ Pros: Data consistency                                  │
│ Cons: Higher write latency                              │
└─────────────────────────────────────────────────────────┘

Write-Behind (Write-Back):
┌─────────────────────────────────────────────────────────┐
│ Write:                                                  │
│   1. Write to cache                                     │
│   2. Return success immediately                         │
│   3. Async write to DB later                            │
│                                                         │
│ Pros: Low write latency                                 │
│ Cons: Risk of data loss                                 │
└─────────────────────────────────────────────────────────┘

Cache Invalidation

Time-Based (TTL)

# Simple TTL-based expiration
redis_client.setex("key", 3600, "value")  # Expires in 1 hour

# Considerations:
# - Short TTL: More DB hits, fresher data
# - Long TTL: Fewer DB hits, staler data
# - Balance based on data update frequency

Event-Based Invalidation

# Delete on update
def update_product(product_id, data):
    db.execute("UPDATE products SET ... WHERE id = %s", product_id)
    redis_client.delete(f"product:{product_id}")

# Using pub/sub for distributed invalidation
def invalidate_cache(key):
    redis_client.publish("cache-invalidation", key)

# Subscriber
pubsub = redis_client.pubsub()
pubsub.subscribe("cache-invalidation")
for message in pubsub.listen():
    if message["type"] == "message":
        redis_client.delete(message["data"])

Cache Stampede Prevention

import time
import random

def get_with_lock(key, fetch_func, ttl=3600):
    cached = redis_client.get(key)
    if cached:
        return json.loads(cached)

    # Try to acquire lock
    lock_key = f"lock:{key}"
    if redis_client.setnx(lock_key, "1"):
        redis_client.expire(lock_key, 10)  # Lock expires in 10s

        try:
            # Fetch data
            data = fetch_func()
            redis_client.setex(key, ttl, json.dumps(data))
            return data
        finally:
            redis_client.delete(lock_key)
    else:
        # Wait and retry
        time.sleep(0.1 + random.uniform(0, 0.1))
        return get_with_lock(key, fetch_func, ttl)

# Or use probabilistic early expiration
def get_with_early_expiry(key, fetch_func, ttl=3600, beta=1):
    cached = redis_client.get(key)
    if cached:
        data, stored_at = json.loads(cached)
        age = time.time() - stored_at
        # Probabilistic early refresh
        if age < ttl and random.random() > beta * (age / ttl):
            return data

    # Refresh cache
    data = fetch_func()
    redis_client.setex(key, ttl, json.dumps([data, time.time()]))
    return data

Caching Strategies by Use Case

Session Caching

# Store session in Redis
def create_session(user_id):
    session_id = generate_session_id()
    session_data = {"user_id": user_id, "created_at": time.time()}
    redis_client.setex(
        f"session:{session_id}",
        86400,  # 24 hours
        json.dumps(session_data)
    )
    return session_id

def get_session(session_id):
    data = redis_client.get(f"session:{session_id}")
    if data:
        # Extend TTL on access (sliding expiration)
        redis_client.expire(f"session:{session_id}", 86400)
        return json.loads(data)
    return None

Aggregation Caching

# Cache expensive aggregations
def get_dashboard_stats():
    cache_key = "dashboard:stats"
    cached = redis_client.get(cache_key)

    if cached:
        return json.loads(cached)

    # Expensive query
    stats = db.query("""
        SELECT
            COUNT(*) as total_orders,
            SUM(total) as revenue,
            AVG(total) as avg_order_value
        FROM orders
        WHERE created_at > NOW() - INTERVAL '30 days'
    """)

    # Cache for 5 minutes
    redis_client.setex(cache_key, 300, json.dumps(stats))
    return stats

Full-Page Caching

# Cache entire rendered pages
def get_product_page(product_id):
    cache_key = f"page:product:{product_id}"
    cached = redis_client.get(cache_key)

    if cached:
        return cached  # Return HTML directly

    # Render page
    product = get_product(product_id)
    html = render_template("product.html", product=product)

    # Cache for 1 hour
    redis_client.setex(cache_key, 3600, html)
    return html

Materialized Views

PostgreSQL Materialized Views

-- Create materialized view
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(created_at) as date,
    COUNT(*) as order_count,
    SUM(total) as revenue,
    AVG(total) as avg_order_value
FROM orders
GROUP BY DATE(created_at);

-- Create index on materialized view
CREATE INDEX idx_sales_summary_date ON daily_sales_summary(date);

-- Refresh (full refresh)
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- Refresh concurrently (requires unique index)
CREATE UNIQUE INDEX idx_sales_summary_date_unique ON daily_sales_summary(date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- Schedule automatic refresh (using pg_cron)
SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary');

MySQL Summary Tables

-- Create summary table
CREATE TABLE hourly_stats (
    hour DATETIME PRIMARY KEY,
    page_views INT,
    unique_visitors INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Populate with scheduled event
CREATE EVENT update_hourly_stats
ON SCHEDULE EVERY 1 HOUR
DO
INSERT INTO hourly_stats (hour, page_views, unique_visitors)
SELECT
    DATE_FORMAT(NOW() - INTERVAL 1 HOUR, '%Y-%m-%d %H:00:00'),
    COUNT(*),
    COUNT(DISTINCT visitor_id)
FROM page_views
WHERE viewed_at >= NOW() - INTERVAL 1 HOUR
ON DUPLICATE KEY UPDATE
    page_views = VALUES(page_views),
    unique_visitors = VALUES(unique_visitors);

Cache Monitoring

Redis Monitoring

# Redis CLI stats
redis-cli INFO stats
redis-cli INFO memory

# Key metrics:
# - keyspace_hits / keyspace_misses (hit ratio)
# - used_memory
# - evicted_keys
# - connected_clients

# Monitor cache effectiveness
def get_cache_stats():
    info = redis_client.info()
    hits = info.get('keyspace_hits', 0)
    misses = info.get('keyspace_misses', 0)
    total = hits + misses

    return {
        'hit_ratio': hits / total if total > 0 else 0,
        'memory_used': info.get('used_memory_human'),
        'evicted_keys': info.get('evicted_keys'),
        'connected_clients': info.get('connected_clients')
    }

Best Practices

Cache close to the consumer: Reduce network hops
Use appropriate TTL: Balance freshness vs hit ratio
Monitor cache hit ratio: Target > 90% for hot data
Handle cache failures gracefully: Fall back to database
Warm cache on deploy: Pre-populate critical data
Use cache tags for invalidation: Group related cache entries
Set memory limits: Prevent OOM with eviction policies
Cache serialization: Use efficient formats (msgpack, protobuf)

# Example: Graceful cache fallback
def get_user_safe(user_id):
    try:
        cache_key = f"user:{user_id}"
        cached = redis_client.get(cache_key)
        if cached:
            return json.loads(cached)
    except redis.RedisError:
        # Log error, continue to database
        pass

    # Fallback to database
    return db.query("SELECT * FROM users WHERE id = %s", user_id)

Database Caching

On this page