Database Cache

Your Database Is Already a Cache Machine

 before reaching disk, every query passes cache layers
 the database manages FOR you:

 SELECT ──► [ query plan cache ] ──► [ buffer pool ] ──► [disk]
                 parsed+planned        pages in RAM       reality
                 reused                hot data resident

 most "slow database" incidents are really
 "buffer pool too small for working set" or "plan cache poisoned."

The Buffer Pool

 RAM holding recently used DATA PAGES (8KB-ish blocks):

 - read hit:  page in pool → served from memory (~100x faster than disk)
 - read miss: page from disk → loaded, evicting something (LRU-ish)
 - writes:    change page IN POOL, flush to disk later (WAL protects)

 sizing rule of thumb:
   buffer pool ≥ HOT working set → disk mostly idle → fast
   buffer pool < working set     → thrash → latency collapses

 "add RAM" is often the highest-leverage database fix precisely
 because it grows this cache.

Query Plan Caches

 parsing + optimizing SQL is expensive; plans are cached by
 query SHAPE:

 prepared statements / parameterized queries → one plan, many runs
 
 string-built SQL ("...WHERE id = 912") with unique literals
 → plan cache pollution → re-planning constantly → CPU burn

 also the #1 SQL-injection defense comes free here.
 parameterize. always.

Materialized Views: Caching You Declare

 precompute expensive aggregates into real tables:

 CREATE MATERIALIZED VIEW city_trips_daily AS
   SELECT city_id, date(*), count(*), avg(fare) ...
 
 reads become trivial scans; freshness = your refresh policy
 (scheduled REFRESH, or triggers/incremental where supported)

 this is application-cache thinking moved INTO the database:
 same trade — staleness for speed — different owner.

When DB-Internal Caches Aren’t Enough

 buffer pool helps SINGLE-node reads. it can't fix:

 - cross-node fan-out      → needs app/CDN layer instead
 - connection storms       → needs pooling at app tier
 - global popularity skew  → one celebrity row still = one page;
                             external cache absorbs N reads of it

 layering rule of thumb:
   DB caches absorb REPEAT ACCESS per node.
   external caches absorb LOAD VOLUME before it arrives.

Tuning Levers That Matter

Lever	Effect
Buffer pool size	The big one — match to working set
Shared buffers / OS page cache	Second-chance layer under the pool
Statement pipelining/batching	Fewer round trips, better pool reuse
Index-only scans	Read indexes, skip heap entirely
Statistics freshness	Correct plans; stale stats = bad caching

Interview Framing

Mentioning the buffer pool earns credibility: “before adding Redis, check the working set fits in buffer pool — 64GB of RAM may solve it cheaper than new architecture.” Then position layers honestly: internal caches optimize access; external tiers cut traffic volume; know which problem you’re solving.