Database Query Caching

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- MySQL/MariaDB: Check buffer pool status
SHOW ENGINE INNODB STATUS;
 
-- Key metrics from status output:
-- Buffer pool hit rate: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) 
--                       / Innodb_buffer_pool_read_requests
-- Target: > 99% hit rate
 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
/*
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_read_requests      | 890234567   | -- Logical reads (from buffer)
| Innodb_buffer_pool_reads              | 1234567     | -- Physical reads (from disk)
| Innodb_buffer_pool_pages_data         | 524288      | -- Pages containing data
| Innodb_buffer_pool_pages_free         | 1024        | -- Free pages
+---------------------------------------+-------------+
Hit rate: (890234567 - 1234567) / 890234567 = 99.86%
*/
 
-- PostgreSQL: Check shared buffers effectiveness
SELECT 
  sum(heap_blks_read) as disk_reads,
  sum(heap_blks_hit) as buffer_hits,
  round(sum(heap_blks_hit)::numeric / 
        (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) as hit_ratio
FROM pg_statio_user_tables;
 
/*
 disk_reads | buffer_hits | hit_ratio 
------------+-------------+-----------
    1234567 |   987654321 |     99.88
*/
 
-- PostgreSQL: View cached queries in pg_stat_statements
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

// Pattern 1: Generic Query Cache Wrapper
import { createHash } from 'crypto';
 
interface QueryCacheOptions {
  ttlSeconds?: number;
  bypassCache?: boolean;
  tags?: string[];
}
 
class QueryCache {
  private cache: Redis;
  private defaultTtl = 300;
 
  constructor(redisClient: Redis) {
    this.cache = redisClient;
  }
 
  /**
   * Generate cache key from query signature
   */
  private generateKey(
    queryName: string,
    params: Record<string, any>
  ): string {
    // Sort params for consistent hashing
    const sortedParams = Object.keys(params)
      .sort()
      .reduce((acc, key) => {
        acc[key] = params[key];
        return acc;
      }, {} as Record<string, any>);
 
    const hash = createHash('sha256')
      .update(JSON.stringify(sortedParams))
      .digest('hex')
      .substring(0, 16);
 
    return `query:${queryName}:${hash}`;
  }
 
  /**
   * Execute query with caching
   */
  async query<T>(
    queryName: string,
    params: Record<string, any>,
    executor: () => Promise<T>,
    options: QueryCacheOptions = {}
  ): Promise<T> {
    const { ttlSeconds = this.defaultTtl, bypassCache = false, tags = [] } = options;
 
    // Skip cache if requested
    if (bypassCache) {
      return executor();
    }
 
    const cacheKey = this.generateKey(queryName, params);
 
    // Try cache first
    const cached = await this.cache.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }
 
    // Execute query
    const result = await executor();
 
    // Cache result
    await this.cache.setex(cacheKey, ttlSeconds, JSON.stringify(result));
 
    // Track tags for invalidation
    if (tags.length > 0) {
      for (const tag of tags) {
        await this.cache.sadd(`tag:${tag}`, cacheKey);
        await this.cache.expire(`tag:${tag}`, ttlSeconds * 2);
      }
    }
 
    return result;
  }
 
  /**
   * Invalidate by tags
   */
  async invalidateByTag(tag: string): Promise<number> {
    const keys = await this.cache.smembers(`tag:${tag}`);
    if (keys.length === 0) return 0;
 
    await this.cache.del(...keys);
    await this.cache.del(`tag:${tag}`);
    
    return keys.length;
  }
}
 
// Usage example
const queryCache = new QueryCache(redisClient);
 
async function getProductsByCategory(categoryId: string): Promise<Product[]> {
  return queryCache.query(
    'getProductsByCategory',
    { categoryId },
    async () => {
      return db.products.findMany({
        where: { categoryId },
        include: { images: true, reviews: { take: 5 } },
      });
    },
    {
      ttlSeconds: 300,
      tags: [`category:${categoryId}`, 'products'],
    }
  );
}
 
// When category or products change
async function updateProduct(productId: string, data: ProductUpdate) {
  const product = await db.products.update({
    where: { id: productId },
    data,
  });
  
  // Invalidate related caches
  await queryCache.invalidateByTag(`category:${product.categoryId}`);
  await queryCache.invalidateByTag(`product:${productId}`);
  
  return product;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

// Hibernate Second-Level Cache Configuration
// In persistence.xml or application.properties:
/*
hibernate.cache.use_second_level_cache=true
hibernate.cache.use_query_cache=true
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
*/
 
// Entity with caching enabled
@Entity
@Table(name = "products")
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Product {
    @Id
    private Long id;
    
    private String name;
    private BigDecimal price;
    
    // READ_ONLY: For immutable entities
    // READ_WRITE: For mutable entities, uses soft locks
    // NONSTRICT_READ_WRITE: For entities with rare updates
    // TRANSACTIONAL: Full transaction isolation (JTA required)
}
 
// Query with cache hint
List<Product> products = entityManager
    .createQuery("SELECT p FROM Product p WHERE p.category = :cat", Product.class)
    .setParameter("cat", category)
    .setHint("org.hibernate.cacheable", true)
    .setHint("org.hibernate.cacheRegion", "product-by-category")
    .getResultList();
 
// Manual cache eviction
Cache cache = entityManager.getEntityManagerFactory().getCache();
cache.evict(Product.class, productId);           // Single entity
cache.evict(Product.class);                       // All products
cache.evictAll();                                 // Everything
 
// Using Hibernate directly for query cache
sessionFactory.getCache().evictQueryRegion("product-by-category");

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

// Adding caching to Prisma with extension pattern
 
import Redis from 'ioredis';
 
const redis = new Redis();
 
// Prisma extension for transparent caching
.$extends({
  query: {
    product: {
      async findUnique({ args, query }) {
        if (!args.where.id) return query(args);
        
        const cacheKey = `product:${args.where.id}`;
        
        // Check cache
        const cached = await redis.get(cacheKey);
        if (cached) {
          return JSON.parse(cached);
        }
        
        // Execute query
        const result = await query(args);
        
        // Cache result
        if (result) {
          await redis.setex(cacheKey, 300, JSON.stringify(result));
        }
        
        return result;
      },
      
      async update({ args, query }) {
        // Execute update
        const result = await query(args);
        
        // Invalidate cache
        await redis.del(`product:${args.where.id}`);
        
        return result;
      },
    },
  },
});
 
// Usage is transparent
const product = await prisma.product.findUnique({
  where: { id: 'prod-123' },
});

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

// PostgreSQL prepared statement example
// The database caches the plan after first execution
 
// First execution: Parse → Analyze → Plan → Execute
const stmt = await client.query({
  name: 'get-user-orders',  // Cached by this name
  text: 'SELECT * FROM orders WHERE user_id = $1 AND status = $2',
  values: [userId, 'pending'],
});
 
// Subsequent executions: Execute only (plan reused)
const stmt2 = await client.query({
  name: 'get-user-orders',  // Same name = cached plan
  text: 'SELECT * FROM orders WHERE user_id = $1 AND status = $2',
  values: [differentUserId, 'pending'],
});
 
// Connection pool prepared statement caching (node-postgres)
import { Pool } from 'pg';
 
const pool = new Pool({
  max: 20,
  // Each connection maintains its own prepared statement cache
  // Be mindful of per-connection memory usage
});
 
// Prisma prepares statements automatically when using $queryRaw
const orders = await prisma.$queryRaw`
  SELECT * FROM orders 
  WHERE user_id = ${userId} 
  AND status = ${status}
`;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- PostgreSQL Materialized View example
-- Use case: Dashboard showing sales by category over time
 
-- The underlying query is expensive (millions of rows, aggregation)
CREATE MATERIALIZED VIEW sales_by_category_daily AS
SELECT 
  date_trunc('day', sold_at) as sale_date,
  category_id,
  COUNT(*) as transaction_count,
  SUM(amount) as total_amount,
  AVG(amount) as avg_amount,
  COUNT(DISTINCT customer_id) as unique_customers
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
GROUP BY date_trunc('day', sold_at), category_id
WITH DATA;  -- Populate immediately (vs WITH NO DATA to defer)
 
-- Create index for common query pattern
CREATE INDEX idx_sales_cat_date 
ON sales_by_category_daily(category_id, sale_date DESC);
 
-- Query is now fast (reads from the materialized view)
SELECT * FROM sales_by_category_daily
WHERE category_id = 5
AND sale_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY sale_date DESC;
 
-- Refresh the materialized view (updates stale data)
-- Note: This locks the view during refresh
REFRESH MATERIALIZED VIEW sales_by_category_daily;
 
-- Concurrent refresh (no lock, requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_category_daily;
 
-- Check when view was last refreshed
SELECT schemaname, matviewname, 
       pg_stat_get_last_autoanalyze_time(
         (schemaname || '.' || matviewname)::regclass
       ) as last_refresh
FROM pg_matviews
WHERE matviewname = 'sales_by_category_daily';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

// Routing reads to replicas, writes to primary
interface DatabaseConfig {
  primary: string;
  replicas: string[];
}
 
class ReplicaAwareConnection {
  private primary: PrismaClient;
  private replicas: PrismaClient[];
  private replicaIndex = 0;
 
  constructor(config: DatabaseConfig) {
    this.primary = new PrismaClient({
      datasources: { db: { url: config.primary } }
    });
    
    this.replicas = config.replicas.map(url =>
      new PrismaClient({
        datasources: { db: { url } }
      })
    );
  }
 
  /**
   * Get connection for read operations
   * Uses round-robin across replicas
   */
  getReadClient(): PrismaClient {
    if (this.replicas.length === 0) {
      return this.primary;
    }
    
    const client = this.replicas[this.replicaIndex];
    this.replicaIndex = (this.replicaIndex + 1) % this.replicas.length;
    return client;
  }
 
  /**
   * Get connection for write operations
   */
  getWriteClient(): PrismaClient {
    return this.primary;
  }
}
 
// Usage
const db = new ReplicaAwareConnection({
  primary: process.env.DATABASE_PRIMARY_URL,
  replicas: [
    process.env.DATABASE_REPLICA_1_URL,
    process.env.DATABASE_REPLICA_2_URL,
  ],
});
 
// Read operations go to replicas
async function getUser(id: string) {
  return db.getReadClient().user.findUnique({ where: { id } });
}
 
// Write operations go to primary
async function updateUser(id: string, data: Partial<User>) {
  return db.getWriteClient().user.update({
    where: { id },
    data,
  });
}
 
// Handle replication lag for read-after-write consistency
async function updateAndReload(id: string, data: Partial<User>) {
  // Write to primary
  await db.getWriteClient().user.update({
    where: { id },
    data,
  });
  
  // Read from PRIMARY to avoid replication lag
  return db.getWriteClient().user.findUnique({ where: { id } });
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

// Pattern 1: Entity-based invalidation with dependency tracking
class CacheInvalidator {
  private dependencies: Map<string, Set<string>> = new Map();
 
  /**
   * Register that a cache key depends on an entity
   */
  registerDependency(entity: string, entityId: string, cacheKey: string): void {
    const dependencyKey = `${entity}:${entityId}`;
    
    if (!this.dependencies.has(dependencyKey)) {
      this.dependencies.set(dependencyKey, new Set());
    }
    this.dependencies.get(dependencyKey)!.add(cacheKey);
  }
 
  /**
   * Get all cache keys that depend on an entity
   */
  getDependentKeys(entity: string, entityId: string): string[] {
    const dependencyKey = `${entity}:${entityId}`;
    return Array.from(this.dependencies.get(dependencyKey) || []);
  }
 
  /**
   * Invalidate all caches depending on an entity
   */
  async invalidateEntity(
    entity: string, 
    entityId: string,
    cache: Redis
  ): Promise<number> {
    const keys = this.getDependentKeys(entity, entityId);
    
    if (keys.length === 0) return 0;
    
    await cache.del(...keys);
    
    // Clean up dependency tracking
    this.dependencies.delete(`${entity}:${entityId}`);
    
    return keys.length;
  }
}
 
// Usage in repository layer
class ProductRepository {
  private cache: Redis;
  private invalidator: CacheInvalidator;
 
  async getProductsByCategory(categoryId: string): Promise<Product[]> {
    const cacheKey = `products:category:${categoryId}`;
    
    const cached = await this.cache.get(cacheKey);
    if (cached) return JSON.parse(cached);
    
    const products = await db.products.findMany({
      where: { categoryId },
    });
    
    // Cache and register dependencies
    await this.cache.setex(cacheKey, 300, JSON.stringify(products));
    
    // This cache key depends on each product
    for (const product of products) {
      this.invalidator.registerDependency('product', product.id, cacheKey);
    }
    // And on the category itself
    this.invalidator.registerDependency('category', categoryId, cacheKey);
    
    return products;
  }
 
  async updateProduct(id: string, data: ProductUpdate): Promise<Product> {
    const product = await db.products.update({
      where: { id },
      data,
    });
    
    // Invalidate all caches depending on this product
    await this.invalidator.invalidateEntity('product', id, this.cache);
    
    return product;
  }
}
 
// Pattern 2: Database trigger-based invalidation
// Using PostgreSQL LISTEN/NOTIFY
/*
CREATE OR REPLACE FUNCTION notify_cache_invalidation()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'cache_invalidation',
    json_build_object(
      'table', TG_TABLE_NAME,
      'operation', TG_OP,
      'id', COALESCE(NEW.id, OLD.id)
    )::text
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER products_cache_invalidation
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION notify_cache_invalidation();
*/
 
// Listen for invalidation events in Node.js
import { Client } from 'pg';
 
async function setupInvalidationListener(cache: Redis): Promise<void> {
  const client = new Client();
  await client.connect();
  
  await client.query('LISTEN cache_invalidation');
  
  client.on('notification', async (msg) => {
    const payload = JSON.parse(msg.payload!);
    console.log(`Cache invalidation: ${payload.table} ${payload.operation} ${payload.id}`);
    
    // Invalidate based on table
    switch (payload.table) {
      case 'products':
        await cache.del(`product:${payload.id}`);
        // Could also use tag-based invalidation for related caches
        break;
      case 'categories':
        await cache.del(`category:${payload.id}`);
        break;
    }
  });
}