Query Caching Strategies

Query Caching Strategies

Overview

Implement multi-level caching strategies using Redis, Memcached, and database-level caching. Covers cache invalidation, TTL strategies, and cache warming patterns.

When to Use

• Query result caching
• High-read workload optimization
• Reducing database load
• Improving response time
• Cache layer selection
• Cache invalidation patterns
• Distributed cache setup

Application-Level Caching

Redis Caching with PostgreSQL

Setup Redis Cache Layer:

// Node.js example with Redis
const redis = require('redis');
const client = redis.createClient({
  host: 'localhost',
  port: 6379,
  db: 0
});

// Get user with caching
async function getUser(userId) {
  const cacheKey = `user:${userId}`;

  // Check cache
  const cached = await client.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Query database
  const user = await db.query(
    'SELECT * FROM users WHERE id = $1',
    [userId]
  );

  // Cache result (TTL: 1 hour)
  await client.setex(cacheKey, 3600, JSON.stringify(user));
  return user;
}

// Cache warming on startup
async function warmCache() {
  const hotUsers = await db.query(
    'SELECT * FROM users WHERE active = true ORDER BY last_login DESC LIMIT 100'
  );

  for (const user of hotUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }
}

Query Result Caching Pattern:

// Generalized cache pattern
async function queryCached(
  key,
  queryFn,
  ttl = 3600  // Default 1 hour
) {
  // Check cache
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  // Execute query
  const result = await queryFn();

  // Cache result
  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

// Usage
const posts = await queryCached(
  'user:123:posts',
  async () => db.query(
    'SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC',
    [123]
  ),
  1800  // 30 minutes TTL
);

Memcached Caching

PostgreSQL with Memcached:

// Node.js with Memcached
const Memcached = require('memcached');
const memcached = new Memcached(['localhost:11211']);

async function getProductWithCache(productId) {
  const cacheKey = `product:${productId}`;

  try {
    // Try cache first
    const cached = await memcached.get(cacheKey);
    if (cached) return cached;
  } catch (err) {
    // Memcached down, continue to database
  }

  // Query database
  const product = await db.query(
    'SELECT * FROM products WHERE id = $1',
    [productId]
  );

  // Set cache (TTL: 3600 seconds)
  try {
    await memcached.set(cacheKey, product, 3600);
  } catch (err) {
    // Fail silently, serve from database
  }

  return product;
}

Database-Level Caching

PostgreSQL Query Cache

Materialized Views for Caching:

-- Create materialized view for expensive query
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  u.id,
  u.email,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order_value,
  MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

-- Index materialized view for fast access
CREATE INDEX idx_user_stats_email ON user_statistics(email);

-- Refresh strategy (scheduled)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

-- Query view instead of base tables
SELECT * FROM user_statistics WHERE email = '[email protected]';

Partial Indexes for Query Optimization:

-- Index only active users (reduce index size)
CREATE INDEX idx_active_users ON users(created_at DESC)
WHERE active = true AND deleted_at IS NULL;

-- Index recently created records
CREATE INDEX idx_recent_orders ON orders(user_id, total DESC)
WHERE created_at > NOW() - INTERVAL '30 days';

MySQL Query Cache

MySQL Query Cache Configuration:

-- Check query cache status
SHOW VARIABLES LIKE 'query_cache%';

-- Enable query cache
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 268435456;  -- 256MB

-- Monitor query cache
SHOW STATUS LIKE 'Qcache%';

-- View cached queries
SELECT * FROM performance_schema.table_io_waits_summary_by_table_io_type;

-- Invalidate specific queries
FLUSH QUERY CACHE;
FLUSH TABLES;

Cache Invalidation Strategies

Event-Based Invalidation

PostgreSQL with Triggers:

-- Create function to invalidate cache on write
CREATE OR REPLACE FUNCTION invalidate_user_cache()
RETURNS TRIGGER AS $$
BEGIN
  -- In production, this would publish to Redis/​Memcached
  -- PERFORM redis_publish('cache_invalidation', json_build_object(
  --   'event', 'user_updated',
  --   'user_id', NEW.id
  -- ));
  RAISE LOG 'Invalidating cache for user %', NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to users table
CREATE TRIGGER invalidate_cache_on_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION invalidate_user_cache();

-- When users are updated, trigger fires and invalidates cache
UPDATE users SET email = '[email protected]' WHERE id = 123;

Application-Level Invalidation:

// Invalidate cache on data modification
async function updateUser(userId, userData) {
  // Update database
  const updatedUser = await db.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
    [userData.name, userData.email, userId]
  );

  // Invalidate related caches
  const cacheKeys = [
    `user:${userId}`,
    `user:${userId}:profile`,
    `user:${userId}:orders`,
    'active_users_list'
  ];

  for (const key of cacheKeys) {
    await client.del(key);
  }

  return updatedUser;
}

Time-Based Invalidation

TTL-Based Cache Expiration:

// Variable TTL based on data type
const CACHE_TTLS = {
  user_profile: 3600,        // 1 hour
  product_list: 1800,        // 30 minutes
  order_summary: 300,        // 5 minutes (frequently changes)
  category_list: 86400,      // 1 day (rarely changes)
  user_settings: 7200        // 2 hours
};

async function getCachedData(key, type, queryFn) {
  const cached = await client.get(key);
  if (cached) return JSON.parse(cached);

  const result = await queryFn();
  const ttl = CACHE_TTLS[type] || 3600;

  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

LRU Cache Eviction

Redis LRU Policy:

# redis.conf
maxmemory 1gb
maxmemory-policy allkeys-lru  # Evict least recently used key

# Or other policies:
# volatile-lru: evict any key with TTL (LRU)
# allkeys-lfu: evict least frequently used key
# volatile-ttl: evict key with shortest TTL

Cache Warming

Pre-load Hot Data:

// Warm cache on application startup
async function warmApplicationCache() {
  // Warm popular users
  const popularUsers = await db.query(
    'SELECT * FROM users ORDER BY last_login DESC LIMIT 50'
  );

  for (const user of popularUsers) {
    await client.setex(
      `user:${user.id}`,
      3600,
      JSON.stringify(user)
    );
  }

  // Warm top products
  const topProducts = await db.query(
    'SELECT * FROM products ORDER BY sales DESC LIMIT 100'
  );

  for (const product of topProducts) {
    await client.setex(
      `product:${product.id}`,
      1800,
      JSON.stringify(product)
    );
  }

  console.log('Cache warming complete');
}

// Run on server startup
app.listen(3000, warmApplicationCache);

Distributed Caching

Redis Cluster Setup:

# Multi-node Redis for distributed caching
redis-server --port 6379 --cluster-enabled yes
redis-server --port 6380 --cluster-enabled yes
redis-server --port 6381 --cluster-enabled yes

# Create cluster
redis-cli --cluster create localhost:6379 localhost:6380 localhost:6381

Cross-Datacenter Cache:

// Replicate cache across regions
async function setCacheMultiRegion(key, value, ttl) {
  const regions = ['us-east', 'eu-west', 'ap-south'];

  await Promise.all(
    regions.map(region =>
      redisClients[region].setex(key, ttl, JSON.stringify(value))
    )
  );
}

// Read from nearest cache
async function getCacheNearest(key, region) {
  const value = await redisClients[region].get(key);
  if (value) return JSON.parse(value);

  // Fallback to other regions
  for (const fallbackRegion of ['us-east', 'eu-west', 'ap-south']) {
    const fallbackValue = await redisClients[fallbackRegion].get(key);
    if (fallbackValue) return JSON.parse(fallbackValue);
  }

  return null;
}

Cache Monitoring

Redis Cache Statistics:

async function getCacheStats() {
  const info = await client.info('stats');
  return {
    hits: info.keyspace_hits,
    misses: info.keyspace_misses,
    hitRate: info.keyspace_hits / (info.keyspace_hits + info.keyspace_misses)
  };
}

// Monitor hit ratio
setInterval(async () => {
  const stats = await getCacheStats();
  console.log(`Cache hit rate: ${(stats.hitRate * 100).toFixed(2)}%`);
}, 60000);

Cache Strategy Selection

Best Practices

✅ DO implement cache warming ✅ DO monitor cache hit rates ✅ DO use appropriate TTLs ✅ DO implement cache invalidation ✅ DO plan for cache failures ✅ DO test cache scenarios

❌ DON'T cache sensitive data ❌ DON'T cache without invalidation strategy ❌ DON'T ignore cache inconsistency risks ❌ DON'T use same TTL for all data

Resources

• Redis Documentation
• Memcached Documentation
• PostgreSQL Materialized Views
• Redis Cache Patterns