postgres://internals

The Buffer Pool

// the problem

Postgres never reads or writes your table's file directly. Every 8 KB page it touches goes through a fixed-size slab of shared memory called shared_buffers — the buffer pool. If the page you need is already there, it's a hit (fast, no I/O). If not, it's a miss: read it from disk, and if the pool is full, kick something else out. Which page gets evicted, and how the hot ones survive, is one of the quietly clever parts of Postgres.

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.

fig.01 · shared buffer poolrequest a page
request page
hits
0
misses
0
evictions
0
writebacks
0
hit ratio
0%
buffer slots
▼ hand
empty
▼ hand
empty
▼ hand
empty
▼ hand
empty

hit = page already cached · miss = read from disk (evict via clock-sweep if full) · dots = usage count, protects hot pages from eviction

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 (from a ctid, an index pointer, a scan position — everything from the earlier lessons).
  2. Look it up in the buffer pool. Present → pin it and use it (a hit).
  3. Absent → a miss: find a free buffer, or evict one to make room, then read the page from disk into that buffer.

A hit costs nothing but 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. A healthy OLTP database runs at 99%+.

(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.

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; its contents must be written back first. (And thanks to the WAL lesson, the WAL record for that change is already durable, so the data-page write can happen lazily.) The background writer and checkpointer drip dirty pages out to disk over time, so a query that triggers an eviction rarely has to wait for a write itself.

// why it matters · sizing shared_buffers

A common starting point is ~25% of RAM for shared_buffers. Not more, because Postgres relies on the operating system's page cache too — data is effectively double-buffered, and an oversized shared_buffers steals RAM the OS cache could use (and makes checkpoints heavier). The OS cache turns many “disk reads” into fast memory reads even when they miss Postgres's own pool.

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.

sql · live postgresrun me first
⌘/ctrl + enter

// note · inspect the pool directly

The pg_buffercache extension exposes exactly which pages are cached right now (SELECT * FROM pg_buffercache) — invaluable for understanding a real workload's working set. (It's a contrib module, not bundled into this in-browser Postgres.)

Your turn

// challengewrite it yourself

Show the size of the shared buffer pool.

⌘/ctrl + enter
// challengeuses table t from the setup cell above

Run a scan and show its buffer usage — prove the pages came from the shared buffer pool (a cache hit).

⌘/ctrl + enter

// what you now understand

  • 01Postgres accesses the heap only through the buffer pool (shared_buffers); a page that's already cached is a hit, otherwise a miss that reads from disk.
  • 02The cache hit ratio is the key read-performance metric — healthy OLTP is 99%+.
  • 03When the pool is full, clock-sweep picks a victim: a hand sweeps buffers, decrementing usage counts and evicting the first that reaches 0, so hot pages survive.
  • 04Modified pages are dirty and must be written back before eviction; the WAL made that write lazy, and the bgwriter/checkpointer flush them over time.
  • 05shared_buffers is usually ~25% of RAM because Postgres also leans on the OS page cache (double buffering); oversizing it hurts.
  • 06EXPLAIN (ANALYZE, BUFFERS) reports 'shared hit' (from the pool) vs 'read' (from disk/OS) — fewer reads is the goal.

// self-test

A 4-buffer pool holds pages A (used often), B, C, D. A miss needs to load page E. Which is most likely evicted?

// self-test

Why is setting shared_buffers to 90% of RAM usually a bad idea?

// go deeper

nextLocks & Deadlocks