The Buffer Pool

Request pages below. Watch hits vs misses, the usage count each page builds up, and the clock hand that chooses victims when the pool fills.

Try this: request A, B, C, D to fill a 4-slot pool (all misses), then request A a few times (hits — its usage count climbs). Now request E, F, G: misses force evictions, but A — with its high usage count — keeps surviving while the cold pages get thrown out.

The read path

Every page access follows the same steps:

1. Compute the page's address — a (relation, block number) pair — from a ctid, an index pointer, or a scan position (everything from the earlier lessons).
2. Look it up. Postgres keeps a hash table mapping (relation, block) → buffer slot, so this is O(1). Found → pin the buffer (bump its pin count so nobody can evict it while you're reading) and use it. That's a hit.
3. Not found → a miss: grab a free buffer, or evict one to make room, then read the 8 KB page from disk into it — and unpin when done.

A hit costs nothing but a memory access; a miss costs a real disk read. The ratio of hits to total accesses — the cache hit ratio — is the single most important number for read performance, and a healthy OLTP database runs at 99%+. You can read it straight off the server's cumulative counters:

SELECT blks_hit, blks_read,
     round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

Every page access is tallied as a hit (blks_hit) or a read (blks_read); the percentage is how well your working set fits in memory. Sliding below ~99% on an OLTP workload usually means shared_buffers is too small, or the working set has simply outgrown RAM.

(A couple of exceptions to “everything goes through the shared pool”: temporary tables use per-session local buffers, and large sequential scans, VACUUM, and COPY deliberately use a small ring buffer so a one-off bulk operation can't evict your whole working set.)

Clock-sweep: choosing a victim

When the pool is full, Postgres needs to evict a page — ideally the least useful one. Tracking true LRU for hundreds of thousands of buffers would be too expensive, so it uses clock-sweep, an LRU approximation:

• Each buffer has a small usage count (0–5). A hit bumps it up (capped).
• A hand sweeps the buffers in a circle. At each buffer it either evicts it (if usage is 0) or decrements the count and moves on.

So a page must go several full sweeps without being touched before it's evicted — which means frequently-used (hot) pages naturally stay cached while one-off pages age out quickly. That's exactly the survival you watched above.

Why approximate LRU instead of doing it exactly? True LRU has to move the just-touched buffer to the front of a list on every single access — a global lock that hundreds of CPU cores would fight over constantly, throttling the whole server. Clock-sweep only bumps a per-buffer counter on a hit (no global lock) and lets the hand do the sorting lazily. (Genuinely free buffers — never used, or just evicted — sit on a small free list that's checked before the hand sweeps at all.)

Dirty pages and write-back

When a query modifies a page, its buffer is marked dirty — changed in memory but not yet on disk. A dirty page can't just be discarded on eviction; its contents must be written back first. (Thanks to the WAL lesson, the WAL record for that change is already durable, so the data-page write can happen lazily.) Two background processes handle those writes so query backends usually don't have to:

• The checkpointer flushes all dirty pages at each checkpoint — that's what bounds crash recovery (WAL lesson).
• The background writer continuously trickles out dirty buffers that are near the clock hand — i.e. about to be evicted — so that a backend needing a free buffer usually finds an already-clean one.

When both fall behind, the cost lands on your query: a backend that needs a free buffer must evict a dirty one and write it out synchronously itself (a “backend write”) before it can even start its read — a latency spike that shows up as slow queries under write-heavy load.

Seeing it in a real plan

That's what Buffers: in EXPLAIN (ANALYZE, BUFFERS) reports — shared hit = pages served from the buffer pool, read = pages that had to come from disk (or the OS cache). Fewer reads is the goal.

DROP TABLE IF EXISTS t;
CREATE TABLE t (id int primary key, v int);
INSERT INTO t SELECT g, g % 100 FROM generate_series(1, 20000) g;
ANALYZE t;
SHOW shared_buffers;                       -- the size of the pool
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM t;  -- 'shared hit' = from the pool

Your turn

SHOW shared_buffers;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM t;