Storage, Execution & Recovery Internals
Buffer Pools and Caching
A buffer pool maps persistent page identifiers to memory frames, coordinates concurrent users, and schedules dirty-page writes. Pinning protects active frames from eviction; latches protect in-memory structures; WAL ordering makes eventual dirty-page flush safe.
- A page table separates identity from frame address.
- Pins and latches solve different hazards.
- Dirty does not mean immediately durable.
- Replacement approximates future reuse.
- Foreground misses can inherit eviction work.
- The OS cache may duplicate database pages.
| State | Eligible as victim? | Required transition before reuse |
|---|---|---|
| Pinned clean/dirty | No | All users unpin |
| Unpinned clean | Yes | Remove mapping before frame reuse |
| Unpinned dirty | Yes | Select victim; flush WAL through pageLSN as needed; write page; then reuse |
| I/O in progress | No | Waiters observe completion/error consistently |