Redis Caching with PostgreSQL: Complete Implementation Guide

[Application]
     ↓
[Redis Cache] ← Cache Miss → [PostgreSQL]
     ↓
[Cache Hit: Return Data]

# redis.conf
maxmemory 2gb
maxmemory-policy allkeys-lru
appendonly yes
appendfsync everysec

# cache_manager.py
import redis
import psycopg2
from functools import wraps
import json

class CacheManager:
    def __init__(self):
        self.redis_client = redis.Redis(
            host='localhost',
            port=6379,
            db=0,
            decode_responses=True
        )
        
        self.pg_conn = psycopg2.connect(
            dbname="your_db",
            user="your_user",
            password="your_password",
            host="localhost"
        )
    
    def cache_query(self, ttl_seconds=300):
        def decorator(func):
            @wraps(func)
            def wrapper(*args, **kwargs):
                # Generate cache key
                cache_key = f"{func.__name__}:{str(args)}:{str(kwargs)}"
                
                # Try to get from cache
                cached_result = self.redis_client.get(cache_key)
                if cached_result:
                    return json.loads(cached_result)
                
                # Execute query if cache miss
                result = func(*args, **kwargs)
                
                # Cache the result
                self.redis_client.setex(
                    cache_key,
                    ttl_seconds,
                    json.dumps(result)
                )
                
                return result
            return wrapper
        return decorator
    
    def invalidate_cache(self, pattern):
        """Invalidate cache entries matching pattern"""
        for key in self.redis_client.scan_iter(pattern):
            self.redis_client.delete(key)

# Usage Example
cache_mgr = CacheManager()

@cache_mgr.cache_query(ttl_seconds=300)
def get_user_profile(user_id):
    with cache_mgr.pg_conn.cursor() as cur:
        cur.execute("""
            SELECT id, username, email, created_at
            FROM users
            WHERE id = %s
        """, (user_id,))
        return cur.fetchone()

// cacheManager.js
const Redis = require('ioredis');
const { Pool } = require('pg');

class CacheManager {
    constructor() {
        this.redis = new Redis({
            host: 'localhost',
            port: 6379,
            db: 0
        });
        
        this.pool = new Pool({
            user: 'your_user',
            host: 'localhost',
            database: 'your_db',
            password: 'your_password',
            port: 5432,
        });
    }
    
    async cacheQuery(key, query, params = [], ttl = 300) {
        try {
            // Try cache first
            const cached = await this.redis.get(key);
            if (cached) {
                return JSON.parse(cached);
            }
            
            // Cache miss, query database
            const client = await this.pool.connect();
            const result = await client.query(query, params);
            client.release();
            
            // Cache the result
            await this.redis.setex(
                key,
                ttl,
                JSON.stringify(result.rows)
            );
            
            return result.rows;
        } catch (error) {
            console.error('Cache query error:', error);
            throw error;
        }
    }
    
    async invalidateCache(pattern) {
        const stream = this.redis.scanStream({
            match: pattern,
            count: 100
        });
        
        return new Promise((resolve, reject) => {
            stream.on('data', async (keys) => {
                if (keys.length) {
                    await this.redis.del(...keys);
                }
            });
            
            stream.on('end', resolve);
            stream.on('error', reject);
        });
    }
}

// Usage Example
const cacheManager = new CacheManager();

async function getUserProfile(userId) {
    const cacheKey = `user:profile:${userId}`;
    const query = `
        SELECT id, username, email, created_at
        FROM users
        WHERE id = $1
    `;
    
    return await cacheManager.cacheQuery(
        cacheKey,
        query,
        [userId],
        300
    );
}

class CacheAside:
    def get_or_set(self, key, ttl, fetch_func):
        # Try to get from cache
        value = self.redis_client.get(key)
        if value:
            return json.loads(value)
        
        # Cache miss: fetch and store
        value = fetch_func()
        self.redis_client.setex(
            key,
            ttl,
            json.dumps(value)
        )
        return value

# Usage
def fetch_data():
    return db.query("SELECT * FROM large_table")

result = cache_aside.get_or_set(
    "large_table_data",
    300,
    fetch_data
)

class WriteThroughCache:
    def write(self, key, value):
        # Write to database
        self.write_to_db(value)
        
        # Update cache
        self.redis_client.set(
            key,
            json.dumps(value)
        )
    
    def write_to_db(self, value):
        with self.pg_conn.cursor() as cur:
            cur.execute("""
                INSERT INTO items (data)
                VALUES (%s)
            """, (json.dumps(value),))
            self.pg_conn.commit()

class CacheWarmer:
    def warm_cache(self):
        """Pre-populate cache with frequently accessed data"""
        queries = [
            ("popular_products", "SELECT * FROM products WHERE popular = true"),
            ("active_users", "SELECT * FROM users WHERE last_login > now() - interval '24 hours'"),
            ("site_settings", "SELECT * FROM settings")
        ]
        
        for cache_key, query in queries:
            with self.pg_conn.cursor() as cur:
                cur.execute(query)
                result = cur.fetchall()
                self.redis_client.setex(
                    cache_key,
                    3600,  # 1 hour TTL
                    json.dumps(result)
                )

class CacheInvalidator:
    def __init__(self):
        self.patterns = {
            'user': [
                'user:profile:*',
                'user:preferences:*'
            ],
            'product': [
                'product:details:*',
                'product:inventory:*'
            ]
        }
    
    def invalidate_entity(self, entity_type, entity_id):
        """Invalidate all caches related to an entity"""
        if entity_type in self.patterns:
            for pattern in self.patterns[entity_type]:
                specific_pattern = pattern.replace('*', str(entity_id))
                self.redis_client.delete(specific_pattern)

# Usage
invalidator = CacheInvalidator()
invalidator.invalidate_entity('user', user_id)

def setup_ttl_invalidation():
    """Configure different TTLs for different types of data"""
    ttl_config = {
        'user:profile': 3600,        # 1 hour
        'product:details': 86400,    # 1 day
        'site:stats': 300,           # 5 minutes
        'search:results': 600        # 10 minutes
    }
    
    return ttl_config

class RedisMonitor:
    def get_stats(self):
        """Get Redis statistics"""
        info = self.redis_client.info()
        return {
            'used_memory': info['used_memory_human'],
            'hit_rate': info['keyspace_hits'] / (info['keyspace_hits'] + info['keyspace_misses']),
            'connected_clients': info['connected_clients'],
            'evicted_keys': info['evicted_keys']
        }
    
    def monitor_hit_rate(self):
        """Monitor cache hit rate"""
        stats = self.get_stats()
        if stats['hit_rate'] < 0.8:  # Alert if hit rate below 80%
            self.alert('Low cache hit rate: {:.2f}%'.format(stats['hit_rate'] * 100))

-- Monitor cache effectiveness
WITH cache_stats AS (
    SELECT
        date_trunc('hour', timestamp) as hour,
        count(*) as total_requests,
        sum(case when cache_hit then 1 else 0 end) as cache_hits
    FROM request_log
    GROUP BY 1
)
SELECT
    hour,
    total_requests,
    cache_hits,
    round((cache_hits::float / total_requests) * 100, 2) as hit_rate
FROM cache_stats
ORDER BY hour DESC;