In PostgreSQL, how does the buffer manager pick a shared buffer to evict, and how does pg_buffercache show the result?
answer
- usage count, not timestamps
- capped at five
- pinned buffers are skipped
- background writer cleans ahead
- usagecount and isdirty columns
basics
~20 sPostgreSQL uses clock-sweep: each shared buffer's usage count rises on every pin, up to 5, and falls as the clock hand passes; the first unpinned buffer at zero is evicted. pg_buffercache shows each buffer's relation, usage count and dirty flag.
solid answer
~40 sEvery shared buffer header carries a pin count and a **usage count**. A newly loaded page starts at 1, and each pin adds 1 up to a cap of 5. When a backend needs a buffer, it takes one from the free list if any never-used buffer remains; otherwise it advances the shared clock hand, `nextVictimBuffer`, skipping pinned buffers, decrementing the usage count of each unpinned one it passes, and taking the first unpinned buffer it finds at zero. If that victim is dirty, the backend must write it out first, which is the work the `background writer` tries to do in advance. The `pg_buffercache` extension shows the outcome: one row per buffer with `relfilenode`, `relblocknumber`, `isdirty`, `usagecount` and `pinning_backends`, plus `pg_buffercache_summary()` and `pg_buffercache_usage_counts()` for aggregates.
code
sql · 16 linesCREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
count(*) AS buffers,
round(avg(b.usagecount), 2) AS avg_usage,
count(*) FILTER (WHERE b.isdirty) AS dirty
FROM pg_buffercache b
JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;
SELECT * FROM pg_buffercache_usage_counts();go deeper
Know that shared_buffers is PostgreSQL's page cache and that a clock-sweep with a small usage count, not a strict LRU list, picks what to drop.
Walk through the sweep: free list first, then the clock hand skipping pinned buffers, decrementing usage counts and taking the first unpinned buffer at zero.
Use pg_buffercache and its summary functions to show which relations own the pool and how hot they are, and explain the background writer's role in keeping victims clean.
Judge when evidence from pg_buffercache justifies changing memory or workload placement, rather than reacting to a single hit ratio.
## Two counters on every buffer header PostgreSQL's shared buffer pool (`shared_buffers`) is an array of 8 kB page slots, each with a **buffer header**. Two numbers in that header drive replacement: - the **pin count** (reference count): how many backends are using the page right now. A pinned buffer can never be evicted; - the **usage count**: a small popularity score. A page read into a buffer starts at 1; every later pin adds 1 until it reaches the cap `BM_MAX_USAGE_COUNT`, which is 5. There is no timestamp and no linked list ordered by recency, so recording an access costs only an update to the header a backend touches anyway to pin it. ## The sweep, step by step When a backend needs a slot for a page that is not cached: 1. If the **free list** holds a buffer that has never contained a valid page (typical just after startup), take it. 2. Otherwise read the shared **clock hand**, `nextVictimBuffer`, and advance it to the next buffer, wrapping at the end of the array. 3. If that buffer is pinned, skip it without changing anything. 4. If it is unpinned with a usage count above zero, decrement the count and move on. 5. If it is unpinned at zero, it is the **victim**: if dirty, write it out first, then load the new page into it. A page with usage count 5 survives five passes of the hand without being touched again; a page read once survives one. If the hand circles the entire pool and finds only pinned buffers, the backend fails with `no unpinned buffers available`, a sign that `shared_buffers` is tiny compared with the concurrency. ## Where the background writer fits If the victim is dirty, the backend that needs the buffer must write it itself, which adds latency to that query. The **background writer** reduces this: every `bgwriter_delay` (200 ms by default) it looks ahead of the clock hand and writes dirty buffers that are unpinned with a usage count of zero, up to `bgwriter_lru_maxpages` (100) per round. It does not perform checkpoints; that is the checkpointer's job. ## Reading pg_buffercache The contrib `pg_buffercache` extension (installed with `CREATE EXTENSION pg_buffercache`, readable by `pg_monitor` members) exposes the pool: | Object | What it returns | |---|---| | `pg_buffercache` view | One row per buffer: `bufferid`, `relfilenode`, `reltablespace`, `reldatabase`, `relforknumber`, `relblocknumber`, `isdirty`, `usagecount`, `pinning_backends` | | `pg_buffercache_summary()` | One row: `buffers_used`, `buffers_unused`, `buffers_dirty`, `buffers_pinned`, `usagecount_avg` | | `pg_buffercache_usage_counts()` | One row per usage count, with `buffers`, `dirty` and `pinned` | | `pg_buffercache_evict()` and relatives | Superuser-only eviction functions, documented as intended for developer testing | To map rows to tables, join `relfilenode` to `pg_relation_filenode(pg_class.oid)` and restrict `reldatabase` to the current database's OID or zero (shared catalogs), because the pool is shared by all databases in the cluster. ## Interpreting the numbers - Many buffers at usage count 4 or 5 mean a working set that is being reused while it stays cached. Most buffers at 0 or 1 mean churn: pages arrive, are used once and leave. - A relation such as `bookings_pkey` holding a large, hot share of the pool is expected; a reporting table holding a large share at usage count 1 suggests one-pass reads that did not go through a ring. - A high `buffers_dirty` share tells you how much write work the checkpointer and background writer have ahead. - The view takes no buffer manager locks while it copies state, so the numbers are not a consistent snapshot across buffers; fine for diagnosis, not for exact accounting. - Unused buffers appear with every column null except `bufferid`. ## Temporary tables stay out of it The clock sweep governs the **shared** pool only. Temporary tables are private to one session, so their pages live in that backend's **local buffers**, sized by `temp_buffers` (8MB by default), and never compete with `rooms` or `bookings` for shared buffers. They also never appear in `pg_buffercache`, which is worth knowing before you hunt for a reporting job's temp table in its output. Large one-pass operations on regular tables follow a separate ring-buffer path, which is its own question. ## What an interviewer is listening for That you can state the mechanism with PostgreSQL's own names (usage count capped at 5, pins, the free list, the clock hand, the background writer cleaning ahead), and that you would check a theory with `pg_buffercache` rather than with a cache hit ratio alone.
- In PostgreSQL, why does a page read by a large sequential scan not climb to usage count 5 even if the scan touches it repeatedly?A scan that runs under a buffer access strategy, the ring used for large sequential scans, VACUUM and bulk loads, only raises a usage count from 0 to 1 and never higher. That keeps one-pass reads from looking popular, so the sweep reclaims them before the hot index and table pages that ordinary queries keep pinning.
- Your pg_buffercache_usage_counts() output shows most buffers at usage count 0 or 1 during business hours. What does that suggest?The pool is churning: pages are loaded, used about once and evicted before they are reused, so the working set does not fit or one-pass access is going through the normal sweep. Check which relations hold those buffers, look for large index or bitmap scans in reports, and only then consider sizing, which belongs to memory tuning.
- PostgreSQL evicts the least recently used page using an access timestamp.
- A buffer's usage count keeps growing with every access, without a limit.
- The clock sweep can evict a pinned buffer if its usage count is zero.
- The sweep skips a dirty buffer until the background writer has cleaned it.
- pg_buffercache_evict_all() is the normal way to clear a polluted cache in production.