Database Caching
Database caching is the cache layer built into the database engine itself — PostgreSQL's `shared_buffers` and OS page cache, MySQL's InnoDB buffer pool, the (deprecated) MySQL query cache, SQL Server's plan cache. It is the deepest cache layer, transparent to the application, and the foundation that makes modern relational databases usable. The trade-off is that you have very little control: the engine chooses what to cache based on access patterns, and tuning is limited to sizing the buffer and choosing storage layouts.
Foundational.
How it works
Database caching is the cache layer built into the database engine. It is not a separate process or a separate store — it is part of the database's own memory, managed by the engine's own logic. The application has no direct control over it; the engine decides what to cache based on its own access statistics.
There are two distinct caches inside most databases:
-
Buffer pool / shared buffers. This caches 8 KB (PostgreSQL) or 16 KB (InnoDB) disk pages in RAM. When the engine needs a page, it checks the buffer pool first; on a hit, no disk I/O is needed. On a miss, the engine reads from disk and stores the page in the pool, evicting the least-recently-used page if the pool is full. This is the foundation — every relational database does this.
-
Query cache (deprecated in MySQL 8.0). This caches the result of a query, keyed by the literal query string. If the same query arrives again and no underlying table has changed, the engine returns the cached result without executing. Sounds great — but in practice the invalidation cost (any write to any table in the query invalidates all queries referencing that table) caused more contention than the cache saved. MySQL removed it in 8.0.
-
Plan cache. This caches the parsed-and-optimized query plan, so the engine doesn't have to re-parse and re-optimize the same SQL repeatedly. Does not cache results — only the plan. Useful for parameterized queries that are executed many times.
The crucial insight: the buffer pool is the cache that matters. It is automatic, transparent, and huge — but its size and the size of your working set determine whether your database feels fast (everything in RAM) or slow (disk I/O on every query).
Buffer pool sizing — the most important database tuning knob.
The single most important tuning decision for a database is the size of its buffer pool. The rule of thumb:
- PostgreSQL: set
shared_buffersto 25% of system RAM. The OS page cache uses the rest, cooperating with the engine. - MySQL/InnoDB: set
innodb_buffer_pool_sizeto 50-75% of system RAM. InnoDB does its own caching and does not rely on the OS page cache as heavily. - MS SQL Server: set
max server memoryto ~75% of system RAM (the engine manages its own buffer pool). - Oracle: SGA target similar, ~60-70% of RAM.
The deeper question is whether your working set fits in the buffer pool. The working set is the data that is actively being read — typically the hot indexes, the recently-written rows, the hot lookup tables. If the working set fits, hit rate is 99%+ and latency is uniform and low. If the working set exceeds the buffer pool, the engine evicts and reads back constantly (cache thrashing), and latency becomes dominated by disk I/O.
This is the cliff that every database hits: the moment the working set exceeds RAM, performance degrades 10-100x. The fix is either more RAM (vertical scaling), sharding (horizontal scaling), or a tiered storage layer (NVMe for cold, RAM for hot — what databases like Aerospike or ScyllaDB do natively).
MySQL's query cache (cached result sets keyed by query string) was a tempting feature: identical queries return instantly if the underlying data hasn't changed. But in practice it was a net loss for most workloads. Every write to a table invalidated every cached query referencing that table — a single UPDATE could invalidate thousands of cached queries. The invalidation lock contended with reads, slowing down the very queries the cache was meant to speed up. On write-heavy workloads, the query cache made the system slower, not faster. MySQL removed it in 8.0. The lesson: caches whose invalidation cost scales with write rate are dangerous. The buffer pool, which invalidates only the modified page, scales gracefully.
Database cache vs application cache — when each matters.
The database cache (buffer pool) and the application cache (Redis/Memcached) are complementary, not competing. They serve different needs:
-
Database cache caches pages, not results. A query that hits the buffer pool still executes — parses, plans, walks the index, joins, filters. The cache only saves the disk I/O. This is great for query throughput (no disk waits) but does not eliminate query execution cost. A complex analytical query on hot data still takes 50 ms even with a perfect buffer pool hit rate.
-
Application cache caches results. A query that hits Redis returns in 1 ms with zero database work. The execution cost (parsing, planning, joining) is eliminated, not just the I/O.
The decision rule: if the same query is executed thousands of times per second with the same result, an application cache (Redis) eliminates the repeated execution cost. If the queries are diverse (different filters, different joins) but operate on the same underlying pages, the database cache handles it for free.
A typical system uses both: Redis for the top-N hot queries (the ones being executed repeatedly), and the database buffer pool for everything else (one-off queries, joins, scans). Adding Redis does not reduce the importance of the buffer pool — it just absorbs the highest-frequency queries, freeing the buffer pool to do its job on the long tail.
When database caching is the right layer to tune:
- Always — it is the foundation. Every database deployment should size its buffer pool correctly.
- When the working set fits in RAM — the system feels instant. Sizing the buffer pool to fit the working set is the single highest-leverage tuning.
- When queries are diverse — different filters, different joins, but on the same hot data. The buffer pool handles this for free; an application cache cannot.
- When query execution is cheap but disk I/O is the bottleneck. Index lookups, primary-key fetches.
When to add an application cache instead (or in addition):
- When the same query is executed thousands of times per second — Redis eliminates the execution cost, not just the I/O.
- When the working set does not fit in RAM — an application cache can hold just the hot keys, fitting in much less RAM than the full dataset.
- When query execution itself is expensive (analytical queries, multi-table joins) and you want to skip it entirely.
- When you need cross-DB caching (multiple DBs sharing a hot result) — the buffer pool is per-DB.
When NOT to rely on the buffer pool alone:
- Working set exceeds RAM — the cache thrashes, latency degrades 10-100x.
- You need cross-shard caching — the buffer pool is per-instance.
- You need cache invalidation semantics the engine does not provide (per-key, per-session).
- You need predictable latency — buffer pool hit rate fluctuates with the working set; an explicit cache gives you control.
Your PostgreSQL database was fast at 50 GB of data. You added more data; at 200 GB it became 50x slower, even though queries are the same. What happened, and what is the fix?
Pick one answer.
Why did MySQL remove the query cache in version 8.0, and what is the lesson for cache design?
Pick one answer.
A query takes 30 ms on your PostgreSQL database. The execution plan is optimal, the indexes are perfect, and `EXPLAIN ANALYZE` shows 99% of the time is in "I/O Read." What does this tell you, and what should you do?
Pick one answer.
Engineering mental model
Mental model. Think of Database Caching as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Database Caching mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”
Before choosing Database Caching, name the workload, the critical user path, the dominant bottleneck, the failure you are trying to absorb, and the trade-off you are willing to accept. If you cannot name those five things, the design is probably premature.
SELECT id, created_at
FROM records
WHERE tenant_id = ?
ORDER BY created_at DESC
LIMIT 50;
-- Ask: which index makes this query predictable at scale?Back-of-the-envelope reasoning
Example: 5M writes/day × 1 KB/row ≈ 5 GB/day of logical data. Add indexes, replication, backups and growth headroom before sizing a real store.
Interactive thought experiment: Database Caching
Change the variables below and predict what breaks first in Database Caching. The production lab can later reuse these same inputs.
Change one variable at a time. Predict the failure mode first, then move the slider and see whether your mental model matches the simplified system response.
If you are stuck on Database Caching, start by drawing the request path and marking every network hop, stateful component, queue, cache and failure boundary. Then estimate where the system will saturate.
You increase traffic by 10× in a system using Database Caching. What should you inspect first?
Pick one answer.
Which statement is the safest engineering habit when using Database Caching?
Pick one answer.
You have dashboards for traffic, latency, errors and saturation. You can change the architecture, but every change has operational cost.
Production scenario: your system uses Database Caching, traffic suddenly spikes, and p99 latency doubles. What is your first move?
Interview drill
Answer this without notes: When would you choose Database Caching, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.
For Database Caching, reason in this order: what data is hottest, where can it live closer to the caller, what makes it stale, and what happens on a miss or cache failure. A cache is an optimization boundary, not the source of truth.
Numerical sanity check
A useful first-order model is cache_load = request_rate × (1 - hit_rate). If traffic is 20,000 req/s and the hit rate is 90%, roughly 2,000 req/s still reaches the origin before considering misses caused by expiration or eviction.
Do not optimize for a memorized definition. Reason from the workload and failure mode.
You have a read-heavy Database Caching path. Traffic doubles overnight. What metric would you inspect first, and what would convince you to add another cache layer?
Pick one answer.
What you gain, what you pay
- +Transparent — application code is unchanged; the engine handles everything.
- +Foundation — without it, relational databases would be unusably slow on any non-trivial dataset.
- +Cooperative with OS page cache — Postgres + Linux uses both layers automatically.
- +Engine-aware — caches pages, plans, and (sometimes) results with appropriate invalidation semantics.
- +Free — included in the database; no extra infrastructure.
- −Limited operator control — sizing is the main knob; the engine chooses what to cache.
- −Per-instance — does not help across shards or replicas.
- −Working set cliff — if working set exceeds RAM, performance degrades 10-100x.
- −Double caching — the same page may be in shared_buffers AND the OS page cache (wastes RAM).
- −Query result caching (where it exists) is dangerous — invalidation cost scales with write rate.
How this breaks in production
- Working set exceeds RAM → buffer pool thrashes, latency degrades 10-100x (the cliff).
- Long-running transactions pin old versions of pages, preventing vacuum/cleanup → buffer bloat.
- Query cache contention (MySQL pre-8.0) — global lock slows reads on write-heavy workloads.
- Cold start after restart — buffer pool is empty; first queries are slow until warm.
- Cache pollution — a single full-table scan evicts the hot working set (PostgreSQL has no scan-resistant eviction by default).
- OOM if buffer pool sized too aggressively — leaves no RAM for OS / connections / queries.
Don't fall into these traps
- •Setting `shared_buffers` too small — most cloud default DBs ship with a few GB; production needs 25% of RAM.
- •Setting `shared_buffers` too large (>40% of RAM) — leaves no room for the OS page cache, which Postgres relies on.
- •Adding indexes when the problem is the buffer pool — index optimization does not fix cache thrashing.
- •Treating the buffer pool as a substitute for an application cache — page caching does not eliminate query execution cost.
- •Not warming the cache after a restart — first production traffic takes the hit of cold-cache misses.
- •Running a single full-table scan query that evicts the hot working set from the buffer pool.
Real systems using this
How real systems implement this
- PostgreSQL shared_buffers + OS page cache — PostgreSQL uses a 25%-of-RAM shared_buffers pool plus the OS page cache for the rest. The two layers cooperate — the engine manages its pool, the kernel manages the rest, and a page may be in both. The recommendation is explicit: shared_buffers = 25% of RAM because the OS page cache handles the rest.
- MySQL/InnoDB buffer pool — InnoDB manages its own buffer pool (50-75% of RAM recommended) and does its own LRU eviction, with the middle-of-list insertion trick to prevent full-table scans from evicting the hot working set. Online buffer pool resizing (since 5.7) lets you resize without restart.
- Elasticsearch filesystem cache — Elasticsearch relies almost entirely on the OS page cache (via Lucene's MMapDirectory). The recommendation is to give the JVM only 50% of RAM and leave the rest for the OS page cache — the inverse of the database pattern.
- Cassandra row cache + key cache — Cassandra offers both a key cache (always on) and a row cache (off by default, opt-in for very hot tables). The row cache is risky because it can hurt write performance; most production deployments disable it and rely on the OS page cache and SSTable bloom filters.
Practice saying it out loud
- Q1Your database was fast at 50 GB and is 50x slower at 200 GB. Diagnose the issue and propose fixes.
- Q2When does adding Redis in front of a database help, and when is it unnecessary because the buffer pool is enough?
- Q3Why did MySQL remove the query cache? What is the general lesson for cache design?
- Q4How would you size `shared_buffers` for a PostgreSQL instance with 64 GB of RAM? Why 25%?
- Q5A query takes 30 ms with a perfect plan and 99% I/O Read time. What is the problem and what do you do?
Further reading & references
Core explanations are original NO CAP material. External references are provided for deeper study and standards.
What next?
Mark as understood once the mental model clicks.
Next recommended
Application Caching