Query Caching Strategies

Query Caching Strategies

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

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

Quick Start

Minimal working example:

// 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
// ... (see reference guides for full implementation)

Reference Guides

Detailed implementations in the references/ directory:

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values

Event-Based Invalidation

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 = 'newemail@example.com' 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;
}

Memcached Caching

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;
}

MySQL Query Cache

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;

PostgreSQL Query Cache

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 = 'john@example.com';

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';

Redis Caching with PostgreSQL

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
);

Time-Based Invalidation

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