How Database Caching Works

What is Caching?

Caching stores frequently accessed data in a faster, closer layer so future requests skip the slower source. For databases, this means avoiding redundant disk I/O and re-execution of expensive queries.

Why do we need it?

Problem	How caching helps
Slow disk reads	Data in RAM is orders of magnitude faster than SSD/HDD
Repeated queries	Identical queries return pre-computed results without re-execution
High DB load	Fewer queries reach the database → more headroom for complex operations
Latency-sensitive reads	Response times drop from milliseconds to microseconds

Key terminology

• Cache hit — data found in cache (fast path)
• Cache miss — data not in cache; must be fetched from the source (slow path)
• Hit ratio — hits / (hits + misses) — the primary cache health metric
• TTL (Time-to-Live) — how long an entry is considered valid before refresh
• Eviction policy — rule for which data to remove when the cache is full (LRU, LFU, TTL)
• Cache invalidation — removing or updating stale data in the cache

Hit/Miss Flow

Watch how requests flow through the cache layer. A hit returns data instantly; a miss queries the database and stores the result for next time:

0 / 18

Client

→

Cache

→

Database

←Response

Levels of Database Caching

Caching happens at multiple layers between the client and disk:

Client Request
     ↓
[1] Application cache   (Redis, Memcached, in-process)
     ↓ miss
[2] Query cache         (built into some RDBMS — rare in modern systems)
     ↓ miss
[3] Buffer pool         (DB engine memory — always present)
     ↓ miss
[4] Disk / SSD          (actual data files)

1. Buffer Pool / Page Cache

The buffer pool is the database engine's internal memory cache. It stores data pages (fixed-size blocks, typically 8–16 KB) recently read from or written to disk.

• Every SELECT, INSERT, UPDATE, DELETE operates on pages in the buffer pool
• Dirty pages (modified but not yet flushed to disk) are written back by the checkpoint process
• This is the most fundamental caching layer — present in every major RDBMS

Buffer pool hit ratio — production target: > 95%

-- PostgreSQL
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

-- MySQL
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

2. Query Cache

Some databases map a literal SQL string to its result set. If the same query re-executes and no underlying data changed, the cached result is returned directly.

Database	Query Cache Support
MySQL	Deprecated in 5.7, removed in 8.0 — global mutex caused contention under writes
PostgreSQL	No built-in query cache; relies on application-level caching
SQL Server	Plan cache (caches execution plans, not results)

Why MySQL removed it

Every write to any table invalidated all cached queries for that table under a global mutex. The overhead exceeded the benefit in most real-world workloads.

3. Application-Level Cache

An external cache sits between the application and database. The app checks cache first; only on a miss does it query the DB and populate cache.

Cache	Best for
Redis	Rich data structures, persistence, pub/sub, TTL support
Memcached	Simple key-value, multithreaded, very fast for basic lookups
In-process (IMemoryCache)	No network hop, fastest option, per-instance only

// Cache-aside read pattern
async Task<User> GetUser(int id)
{
    var key = $"user:{id}";

    var cached = await cache.GetStringAsync(key);
    if (cached is not null)
        return Deserialize<User>(cached);   // Hit

    var user = await db.Users.FindAsync(id); // Miss → query DB

    await cache.SetStringAsync(key, Serialize(user), new() // Populate cache
    {
        AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(30)
    });

    return user;
}

Cache Strategies Compared

Toggle between the five strategies — watch when the client gets the ACK and when the database is actually touched:

The most common pattern. Application checks the cache first; on a miss, it queries the database and stores the result. Next read is a cache hit.

0 / 0

Client

→

Cache

→

Database

←

Read Strategies

Cache-Aside (Lazy Loading)

The application manages the cache explicitly. On a read, check cache first; on a miss, query the source and populate cache yourself.

• Pros: Simple, full control, works with any cache store
• Cons: Cache logic leaks into application code; cold starts are slow
• Best for: General-purpose, mixed read/write workloads

Read-Through

The cache provider sits between the application and the database. On a miss, the cache provider automatically loads data from the database — the application only ever talks to the cache layer.

• Pros: Application code stays clean (no cache logic), consistent cache behavior across services
• Cons: Tightly coupled to cache provider capabilities, less control over load logic
• Best for: Read-heavy workloads where you want clean separation of concerns
• Examples: Spring Cache (@Cacheable), Hibernate second-level cache, Redis with Lua scripts

Write Strategies

Write-Through

Every write goes to both the cache and the database synchronously before acknowledging the client.

• Pros: Strong consistency — cache and DB always in sync; reads always fast
• Cons: Write latency is high (two synchronous writes)
• Best for: Read-heavy workloads where consistency matters more than write speed

Write-Around

Writes go directly to the database, bypassing the cache entirely. The cache is only populated when data is read (miss → query DB → store in cache).

• Pros: Cache doesn't get polluted with write-only data; no write amplification
• Cons: Recently written data isn't in cache until first read; burst of misses after writes
• Best for: Write-heavy workloads where recently written data isn't immediately read (logs, analytics, bulk imports)

Write-Behind (Write-Back)

Writes go to the cache only; the client is acknowledged immediately. The database is updated asynchronously in batches.

• Pros: Very fast writes; batch writes reduce DB load
• Cons: Data loss risk if cache crashes before flush; eventual consistency
• Best for: Write-heavy workloads that can tolerate some data loss (session state, telemetry counters)

Strategy comparison

Aspect	Cache-Aside	Read-Through	Write-Through	Write-Around	Write-Behind
Write path	App updates DB, then invalidates cache	Same as Cache-Aside	Cache + DB synchronously	Direct to DB only, bypass cache	Cache only; DB async
Read path	App checks cache → miss → queries DB → stores	Cache provider auto-loads from DB on miss	Cache always populated after writes	Miss → DB → store (same as Cache-Aside)	Cache always populated after writes
Consistency	Eventual — stale until invalidation	Eventual — same as Cache-Aside	Strong — always in sync	Eventual — cache populated lazily	Eventual — DB lags behind cache
Write latency	Normal	N/A (read-only strategy)	High (two synchronous writes)	Normal (one DB write)	Very fast (cache write only)
Data loss risk	None	None	None	None	Yes — cache crash loses unflushed writes
Best for	General-purpose, mixed workloads	Read-heavy, clean app code	Read-heavy, strong consistency	Write-heavy, data not re-read immediately	Write-heavy, eventual consistency OK

Combining strategies

Read and write strategies can be paired:

Read + Write	Effect
Cache-Aside + Write-Around	Simple, cache only populated on reads
Cache-Aside + Write-Through	Consistent, but app manages cache
Read-Through + Write-Through	Clean app code + strong consistency
Read-Through + Write-Behind	Clean app code + fast writes (eventual consistency)

Cache Invalidation Strategies

Knowing when cached data is stale is the hardest problem in caching.

TTL-Based Expiration

Every entry has a time-to-live. When TTL expires, the entry is automatically evicted.

• Pros: Simple, no coordination needed
• Cons: Stale during TTL window; short TTLs reduce staleness but increase misses
• Best for: Infrequently changing data where slight staleness is acceptable

Explicit Invalidation

The app or a database trigger removes/updates cache entries when data changes.

• Pros: Precise control
• Cons: Requires awareness of when data changes
• Common technique: PostgreSQL LISTEN/NOTIFY, MongoDB Change Streams, SQL Server Change Tracking

Eviction Policies

When the cache is full, something must be removed:

Policy	Evicts	Best for
LRU (Least Recently Used)	Longest-unused entry	General-purpose — assumes recent access predicts future access
LFU (Least Frequently Used)	Least-accessed entry	Stable patterns where some data is genuinely more popular
FIFO (First In, First Out)	Oldest entry	Simple implementations; rarely optimal
TTL-based	Expired entries regardless of space	Time-sensitive data (sessions, API responses)

Common Pitfalls

Cache Stampede (Thundering Herd)

A popular entry expires and many concurrent requests all miss the cache simultaneously, hammering the database.

Fix: Distributed lock/mutex so only one request rebuilds; or refresh before expiry (e.g., at 80% of TTL with jitter).

Cache Penetration

Queries for non-existent data never cache (no entry to store), so every request hits the DB.

Fix: Cache null results with a short TTL; or use a Bloom filter to check existence first.

Cache Breakdown

A single hot key expires, causing a sudden spike.

Fix: Never expire hot keys — refresh them asynchronously in the background.

Over-caching

Don't cache: frequently updated data, real-time data (account balances), or disproportionately large blobs.

Rule of thumb: Cache data that is read frequently and changes rarely.

Caching in Practice

Database	Buffer Pool Config	Query Cache	Application Cache
PostgreSQL	shared_buffers (~25% RAM)	None	Redis / app-level
MySQL	innodb_buffer_pool_size	Removed in 8.0	Redis / app-level
SQL Server	Buffer Pool (auto-managed)	Plan cache (execution plans)	Redis / app-level

-- PostgreSQL: preload table into buffer pool
SELECT pg_prewarm('users');

-- SQL Server: check buffer pool usage
SELECT COUNT(*) * 8 / 1024 AS total_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID();

# Redis: basic cache operations
SET user:42 '{"name":"Alice"}' EX 1800   # Set with 30min TTL
GET user:42                               # Get
DEL user:42                               # Invalidate

When to Use Which Strategy

Scenario	Recommended Approach
Read-heavy, data changes rarely	Cache-aside or read-through with long TTL
Read-heavy, data changes occasionally	Cache-aside + explicit invalidation on write
Read-heavy, want clean app code	Read-through + write-through
Write-heavy, data not re-read immediately	Write-around (cache populated only on reads)
Write-heavy, eventual consistency OK	Write-behind cache
Strong consistency required	Write-through cache or no cache
Real-time data (balances, inventory)	No cache — always query the database
Session data, rate limiting	Redis with TTL

Related Topics

• Indexing — reduce query cost before caching
• Redis — the most popular cache engine
• System Design: Caching — caching in distributed systems