Chapter 23: Buffer Pool and Memory Management
The buffer pool is the database's RAM cache. It's the single biggest factor in query performance — if your working set fits in the buffer pool, everything is fast.
How It Works
Query: SELECT * FROM users WHERE id = 42
1. Determine which page contains row id=42 (via index)
→ Page 157
2. Is page 157 in the buffer pool?
YES → return data from RAM (buffer hit, ~100ns)
NO → read page 157 from disk into buffer pool (buffer miss, ~100μs)
→ evict least-recently-used page if pool is full
→ return data
Buffer hit ratio = hits / (hits + misses)
Target: > 99%
Eviction Policies
- LRU (Least Recently Used): evict the page accessed longest ago
- Clock sweep (PostgreSQL): approximation of LRU, more efficient
- ARC (Adaptive Replacement Cache): balances recency and frequency
Configuration
# PostgreSQL
shared_buffers = 16GB # typically 25% of system RAM
effective_cache_size = 48GB # hint to planner: total available cache (OS + DB)
work_mem = 256MB # per-operation memory (sorts, hash joins)
maintenance_work_mem = 2GB # for VACUUM, CREATE INDEX
# MySQL/InnoDB
innodb_buffer_pool_size = 48G # typically 70-80% of system RAMMonitoring
-- PostgreSQL: check hit ratio
SELECT
sum(blks_hit) AS hits,
sum(blks_read) AS misses,
round(sum(blks_hit)::numeric / (sum(blks_hit) + sum(blks_read)) * 100, 2) AS hit_ratio
FROM pg_stat_database;
-- Should be > 99%. If lower, increase shared_buffers or add RAM.Key Takeaways
- Buffer pool = RAM cache for disk pages. Biggest performance factor.
- Hit ratio > 99% = good. Below that = add RAM or reduce working set.
- PostgreSQL: shared_buffers = 25% RAM. InnoDB: buffer_pool = 70-80% RAM.
- work_mem controls per-operation memory (sorts, hashes) — too low = disk spills