Caching Layers - Learning Module

Loading content...

0/273

Database Query Caching

Caching at the Data Layer

Database queries are often the slowest component of application request flows. A single complex query can take hundreds of milliseconds—parsing SQL, planning execution, reading from disk, joining tables, aggregating results. When the same query runs repeatedly with the same results, this repeated work is pure waste.

Database query caching addresses this by storing query results so that identical subsequent queries return instantly from cache rather than re-executing. This happens at multiple levels: the database's internal buffer cache and query cache, result caching at the ORM layer, and explicit application-managed query result caching.

Understanding these layers—their capabilities, limitations, and interactions—enables you to design systems that handle read-heavy workloads efficiently while maintaining data consistency.

What You Will Learn

This page covers database internal caching mechanisms (buffer pools, query caches), query result caching patterns, ORM-level caching strategies, prepared statement caching, materialized views as cached queries, and the consistency challenges unique to database caching. By the end, you'll be able to optimize database performance through strategic query caching at every layer.

Database Internal Caching

Modern databases include sophisticated internal caching mechanisms that operate transparently to applications. Understanding these helps you configure databases optimally and recognize when additional caching layers are needed.

Buffer Pool (Page Cache):

The buffer pool is the most important database cache. It stores recently accessed data pages in memory, avoiding disk reads for hot data.

┌─────────────────────────────────────────────────────────────┐
│                      Buffer Pool (RAM)                       │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌────────┐ │
│  │ Table A │ │ Table A │ │ Index B │ │ Table C │ │Index A │ │
│  │ Page 1  │ │ Page 42 │ │ Page 7  │ │ Page 3  │ │Root Pg │ │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘ └────────┘ │
└─────────────────────────────────────────────────────────────┘
                              ↑
                    Hot data stays in memory
                              ↓
┌─────────────────────────────────────────────────────────────┐
│                      Disk Storage                            │
│                 (Cold data, full dataset)                    │
└─────────────────────────────────────────────────────────────┘

Database Internal Cache Types
Cache Type	What It Caches	Management	Key Configuration
Buffer Pool (InnoDB)	Data pages, index pages	LRU with access aging	innodb_buffer_pool_size (50-80% of RAM)
Query Cache (deprecated)	Query text → result set	Exact text matching, any write invalidates	Removed in MySQL 8.0
Plan Cache	Parsed/optimized query plans	LRU, per-query limits	plan_cache_mode (PostgreSQL)
Result Cache (Oracle)	Query results	Per-query opt-in, dependency tracking	RESULT_CACHE hint
Shared Buffers (PostgreSQL)	Table/index pages	Clock sweep algorithm	shared_buffers (25% of RAM minimum)

database-cache-configuration.sql
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;

MySQL Query Cache is Deprecated

MySQL's query cache was removed in version 8.0 because it became a contention bottleneck at scale. Any table write invalidated all cached queries for that table, and cache synchronization serialized concurrent access. For query result caching, use application-level or external caches (Redis/Memcached) instead.

Query Result Caching Patterns

Since built-in query caches are limited or deprecated, application-managed query result caching is the primary approach. This involves caching query results keyed by the query signature (parameters included).

Query Result Caching Architecture:

Application
    │
    ├─→ Generate cache key from query + parameters
    │
    ├─→ Check cache (Redis/Memcached/In-memory)
    │       │
    │       ├─ HIT: Return cached result
    │       │
    │       └─ MISS: Execute query → Cache result → Return
    │
    └─→ Database

query-caching-patterns.ts
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;
}

Caching Complex Queries:

Complex queries with joins, aggregations, or pagination require careful cache key design:

Query Characteristic	Cache Key Considerations
Pagination	Include page number, page size: `query:listUsers:page=2:size=20`
Sorting	Include sort field and direction: `query:products:sort=price_desc`
Filters	Hash all filter values: `query:search:filters=abc123`
User context	Include user ID for personalized queries
Date ranges	Be careful—today's range is different tomorrow

Paginated Query Caching Strategy

For paginated lists, consider caching the full ID list separately from individual items. Cache the query 'products in category X sorted by price' → [id1, id2, id3, ...], then fetch hydrated items individually (which are cached per-item). This allows item updates without invalidating all page caches.

ORM-Level Caching

Object-Relational Mappers (ORMs) can provide caching at multiple levels: first-level cache (session/unit-of-work scope), second-level cache (application-wide), and query cache (result set caching).

First-Level Cache (Identity Map):

Most ORMs cache loaded entities within a session/transaction. Loading the same entity twice returns the same object—no second query.

// Prisma example - within same transaction
const tx = await prisma.$transaction(async (tx) => {
  const user1 = await tx.user.findUnique({ where: { id: '123' } }); // DB query
  
  // Later in same transaction
  const user2 = await tx.user.findUnique({ where: { id: '123' } }); // Might be cached*
  
  // *Note: Prisma doesn't have traditional identity map, but other ORMs do
});

Second-Level Cache:

Persists across sessions, shared by all application threads. Hibernate, Entity Framework, and Django ORM support this.

hibernate-caching-config.java
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");

ORM Caching Support by Framework
ORM/Framework	First-Level	Second-Level	Query Cache	Notes
Hibernate (Java)	Identity Map per Session	Ehcache, Infinispan, Hazelcast	Built-in query cache	Most mature caching support
Entity Framework (.NET)	Change tracker per DbContext	Requires extension (EFSecondLevelCache)	extension needed	Weaker native support
Django ORM (Python)	None built-in	django-cachalot, django-cacheops	Per-queryset caching	Relies on external packages
SQLAlchemy (Python)	Identity Map per Session	dogpile.cache integration	Manual implementation	Flexible but manual
Prisma (TypeScript)	None built-in	Community extensions	Manual caching layer	Add Redis/memcached manually

prisma-caching-layer.ts
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' },
});

ORM Cache Consistency

ORM second-level caches can become stale if data is modified outside the ORM (raw SQL, other applications, database triggers). Either ensure all modifications go through the ORM, implement external cache invalidation, or use short TTLs and accept eventual consistency.

Prepared Statement Caching

While not caching result data, prepared statement caching significantly improves query performance by reusing query plans. The database parses and optimizes the query once, then reuses the plan for subsequent executions with different parameters.

Why This Matters:

Query execution has multiple phases:

Parse — Tokenize SQL text
Analyze — Validate tables, columns, permissions
Plan — Determine optimal execution strategy (indexes, join order)
Execute — Run the plan and return results

Steps 1-3 can take significant time for complex queries. Prepared statements cache the result of these steps.

prepared-statement-caching.ts
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}
`;

Database Prepared Statement Behavior
Database	Plan Cache Scope	Cache Configuration	Notes
PostgreSQL	Per-connection	plan_cache_mode (auto, force_generic, force_custom)	Generic plans after 5 executions by default
MySQL	Per-connection	max_prepared_stmt_count (global limit)	Server-side prepared statements
SQL Server	Instance-wide	Automatic plan cache	Parameter sniffing can cause issues
Oracle	Shared pool	CURSOR_SHARING parameter	Extensive cursor caching options

Generic vs. Custom Plans:

PostgreSQL distinguishes between:

Custom plans — Optimized for specific parameter values. Better for skewed data.
Generic plans — One plan for all parameter values. Less planning overhead.

After 5 executions, PostgreSQL switches to generic plans if they perform comparably. For highly selective queries with parameter-dependent performance, you may need to force custom plans:

-- Force custom plan for this session
SET plan_cache_mode = force_custom_plan;

-- Or use dynamic SQL in application to prevent caching
-- (trade-off: parse overhead every time)

Parameter Sniffing Issues

SQL Server's 'parameter sniffing' caches plans based on first execution's parameters. If those parameters are atypical (very selective or very broad), the cached plan is suboptimal for typical usage. Solutions: OPTION (RECOMPILE), OPTIMIZE FOR hints, or plan guides.

Materialized Views: Persistent Query Caching

Materialized views are database objects that store the results of a query physically on disk. Unlike regular views (which are just stored queries), materialized views persist their results and can be indexed, making complex aggregations and joins accessible with simple table scans.

Regular View vs. Materialized View:

Regular View:
  SELECT * FROM sales_summary  →  Executes underlying query every time
                                   (may be expensive)

Materialized View:
  SELECT * FROM sales_summary_mv  →  Reads pre-computed result
                                      (fast, but may be stale)

materialized-views.sql
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';

Materialized View Refresh Strategies
Strategy	How It Works	Trade-offs
Manual REFRESH	Explicitly triggered (cron, app event)	Full control, but may forget or be delayed
Incremental refresh	Only processes changes (Oracle FAST REFRESH)	Efficient but requires materialized view logs
Concurrent refresh (PostgreSQL)	REFRESH CONCURRENTLY allows reads during refresh	Requires unique index, more resources
Scheduled (pg_cron)	Cron job runs REFRESH periodically	Predictable but fixed staleness window
Trigger-based	Triggers update view on base table changes	Near-real-time but adds write overhead
Streaming (Materialize)	Real-time incremental view maintenance	Always fresh but specialized database

When to Use Materialized Views:

Good Use Cases	Poor Use Cases
Complex aggregations for dashboards	Rapidly changing data needing real-time accuracy
Reports run repeatedly	Queries with highly variable parameters
Denormalized views for read optimization	Small result sets (regular query is fine)
Pre-joined tables for common access patterns	Write-heavy tables (refresh overhead)

Materialized Views with dbt

Tools like dbt (data build tool) manage materialized views as code, handling dependencies, refreshes, and testing. For analytics workloads, dbt materializations (table, view, incremental) provide a mature workflow for managing pre-computed query results.

Read Replicas as Query Cache

Database read replicas can function as a form of distributed cache. While not caching query results directly, they cache the entire data set and serve read queries, offloading the primary database and reducing query latency through geographic distribution.

Architecture:

                    ┌──────────────────┐
                    │    Application   │
                    └────────┬─────────┘
                             │
            ┌────────────────┼────────────────┐
            │                │                │
            ▼                ▼                ▼
     ┌──────────┐     ┌──────────┐     ┌──────────┐
     │  Primary │     │ Replica  │     │ Replica  │
     │  (write) │────▶│   (US)   │     │   (EU)   │
     └──────────┘     └──────────┘     └──────────┘
                         ▲                  ▲
                         │                  │
                    Reads from          Reads from
                    US users            EU users

read-replica-routing.ts
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 } });
}

Read Replica Caching Trade-offs
Aspect	Benefit	Challenge
Scalability	Add replicas to handle more read traffic	Replication overhead increases with replica count
Latency	Deploy replicas near users geographically	Replication lag varies by distance
Consistency	Eventually consistent reads	Read-after-write may see stale data
Availability	Replicas provide redundancy	Promoting replica to primary is complex
Cost	Scale reads without scaling write capacity	Each replica costs storage and compute

Replication Lag and Consistency

Read replicas are eventually consistent—writes to primary take time to replicate. A user who updates their profile may briefly see old data if the read hits a replica. Solutions: read-from-primary for critical reads, check LSN/GTID for sync status, or add artificial delay before read-after-write.

Cache Invalidation for Database Queries

Database query cache invalidation is particularly challenging because any row change can affect multiple cached queries. A single UPDATE to a product may invalidate:

The product detail cache
Category listing caches (multiple categories if it moved)
Search result caches
Recommendation caches
Aggregate statistics caches

Invalidation Strategies:

database-invalidation-patterns.ts
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;
    }
  });
}

Change Data Capture (CDC)

For sophisticated cache invalidation, consider Change Data Capture tools like Debezium. They stream database changes to Kafka, where consumers can process invalidations reliably. This decouples invalidation logic from write paths and handles failures gracefully.

Caching Strategy Selection

Choosing where to cache database query results depends on your consistency requirements, access patterns, and infrastructure. Here's a decision framework:

Database Caching Layer Selection
Layer	Speed	Consistency	Scale	Best For
Database buffer pool	Fast (memory)	Perfect (same source)	Single DB	Default—tune size appropriately
Prepared statements	Plan reuse	Perfect	Per-connection	Repeated queries with parameters
Materialized views	Fast reads	Refresh-based staleness	Query-specific	Complex aggregations, denormalized reads
Read replicas	Eventually consistent	Lag-dependent	Horizontal	Read-heavy geographic distribution
ORM second-level cache	In-process/distributed	Write-through or manual	Application	Entity-focused caching with ORM
Application cache (Redis)	Sub-millisecond	TTL + invalidation	Horizontal	Custom query results, flexibility

Decision Tree:

1. Is the query inherently slow (complex joins, aggregations)?
   ├─ YES → Consider materialized views
   └─ NO → Continue to 2

2. Is the data read-heavy with infrequent writes?
   ├─ YES → Query result caching (Redis) with TTL
   └─ NO → Continue to 3

3. Is eventual consistency acceptable?
   ├─ YES → Read replicas + cache with short TTL
   └─ NO → Continue to 4

4. Is strong consistency required?
   ├─ YES → Cache with write-through invalidation or skip caching
   └─ Consider trade-offs: latency vs consistency

5. Is the working set < available memory?
   ├─ YES → Tune buffer pool; may not need application cache
   └─ NO → External cache (Redis) handles eviction

Start Simple, Add Complexity as Needed

Before adding caching layers, ensure your queries are optimized (proper indexes, efficient SQL). A well-indexed query that returns in 5ms doesn't need caching. Cache for queries that are expensive despite optimization, or when you need to reduce database load.

Summary and Best Practices

Database query caching operates at multiple levels—from internal buffer pools to external result caches. Understanding these layers enables you to optimize database performance strategically, choosing the right caching approach for each query pattern.

Database Query Caching Best Practices

•Tune the buffer pool first — Ensure your database's memory cache is sized appropriately (50-80% of RAM for dedicated DB servers). This is free caching.
•Use prepared statements — Let the database cache query plans. All modern ORMs do this automatically.
•Consider materialized views for analytics — Pre-compute expensive aggregations; refresh on schedule or after writes.
•Cache query results at the application level — Use Redis/Memcached for explicit control over TTL and invalidation.
•Design cache keys carefully — Include all query parameters; normalize for consistent cache hits.
•Plan invalidation with the cache — Determine how updates will invalidate cached queries before implementing caching.
•Use read replicas for scale — Offload read traffic, but account for replication lag in your consistency model.
•Monitor cache effectiveness — Track hit ratios at every layer; a cache that never hits is wasted memory.
•Handle thundering herd — Use request coalescing for cache population on popular queries.
•Consider CDC for sophisticated invalidation — Change Data Capture enables reliable, decoupled cache invalidation.

What's Next:

We've covered caching at the browser, CDN, application, and database query levels. The final page in this module examines Distributed Caching—where we explore Redis, Memcached, and other distributed cache systems that provide shared, scalable caching across application instances.

Page Complete

You now understand database query caching—from internal buffer pools and prepared statements to materialized views, ORM caching, and application-managed query result caches. You can select the appropriate caching layer for different query patterns and implement effective invalidation strategies.