pg_buffercache & Cache Introspection

Look directly inside shared_buffers, catch PostgreSQL's ring-buffer protection for large scans in the act, and see why a bigger cache does not always mean more caching

Every other lab in this block has measured PostgreSQL from the outside — counters, timestamps, file sizes. pg_buffercache looks directly inside shared_buffers itself: which relation owns each of its 8KB buffers, right now, at the moment you query it.\n\nThis lab creates two tables — one comfortably smaller than shared_buffers, one considerably larger — scans both, and checks what pg_buffercache actually shows. The result is more interesting than a clean "small fits, large does not": PostgreSQL uses a bulk-read ring buffer strategy specifically to stop one big sequential scan from evicting everything else useful out of a shared cache, which means neither table ends up cached quite the way a first guess would predict — and increasing shared_buffers afterward does not fix it the way you might expect either.

What's In the Cache, Right Now

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname, count(*) AS buffers_cached, pg_size_pretty(count(*) * 8192) AS cached_size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relname IN ('small_tbl', 'large_tbl')
GROUP BY c.relname;

Each row in pg_buffercache is one 8KB buffer slot in shared_buffers. Joining on pg_relation_filenode() attributes each occupied slot back to the table it belongs to — a live snapshot of exactly what is resident at this instant.

Snapshot vs Cumulative Counter

pg_buffercache answers "what is cached right now" — pg_statio_user_tables.heap_blks_hit / heap_blks_read answers "across all activity since the last reset, what fraction was served from cache". A table can show a near-100% cumulative hit ratio from earlier activity while a buffercache snapshot taken later shows only part of it still resident — the two views are not measuring the same thing, and neither one is wrong.

The Ring Buffer That Protects Everything Else

A sequential scan of a table bigger than a small fraction of shared_buffers uses a bounded ring of buffers rather than the entire cache — otherwise, one enormous SELECT * could evict every other table's pages from memory in a single pass. This is a deliberate protection built into PostgreSQL's buffer manager, not a bug or a misconfiguration.

pg_buffercache

A contrib extension exposing one row per 8KB buffer slot in shared_buffers, including which relation (via relfilenode) currently occupies it, whether it is dirty, and its usage count. It requires no restart to install and reads live shared memory state — a direct look inside the cache rather than an inference from counters.

Bulk-Read Ring Buffer

PostgreSQL's buffer access strategy for large sequential scans, which limits such a scan to cycling through a bounded ring of buffers instead of claiming the entire shared_buffers pool. This protects every other table's cached pages from being evicted by one big scan, at the cost of that scan itself never getting to fully cache the table it is reading.

📐 Check the Cache Size and Table Sizes

Confirm shared_buffers, and how each table's size compares against it.

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.small_tbl')) AS small_size, pg_size_pretty(pg_total_relation_size('monitoring.large_tbl')) AS large_size, current_setting('shared_buffers') AS shared_buffers;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.small_tbl')) AS small_size, pg_size_pretty(pg_total_relation_size('monitoring.large_tbl')) AS large_size, current_setting('shared_buffers') AS shared_buffers;" SET small_size | large_size | shared_buffers ------------+------------+---------------- 320 kB | 49 MB | 32MB (1 row)

📖 Scan Both Tables

Read every row of both tables so their pages actually pass through shared_buffers.

psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.small_tbl; SELECT count(*) FROM monitoring.large_tbl;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.small_tbl; SELECT count(*) FROM monitoring.large_tbl;" SET count ------- 5000 (1 row) count -------- 200000 (1 row)

🔬 Check What Is Actually Cached

Query pg_buffercache to see exactly how many buffers each table occupies right now.

psql -U postgres -d beer_db -c "SELECT v.relname, coalesce(bc.buffers,0) AS buffers_cached, pg_size_pretty(coalesce(bc.buffers,0) * 8192) AS cached_size FROM (VALUES ('small_tbl'),('large_tbl')) AS v(relname) LEFT JOIN (SELECT c.relname, count(*) AS buffers FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) GROUP BY c.relname) bc ON bc.relname = v.relname ORDER BY v.relname;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT v.relname, coalesce(bc.buffers,0) AS buffers_cached, pg_size_pretty(coalesce(bc.buffers,0) * 8192) AS cached_size FROM (VALUES ('small_tbl'),('large_tbl')) AS v(relname) LEFT JOIN (SELECT c.relname, count(*) AS buffers FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) GROUP BY c.relname) bc ON bc.relname = v.relname ORDER BY v.relname;" SET relname | buffers_cached | cached_size -----------+----------------+------------- large_tbl | 2920 | 23 MB small_tbl | 23 | 184 kB (2 rows)

📈 Compare Against the Cumulative Hit Ratio

Check pg_statio_user_tables for both tables and see a very different-looking number from the buffercache snapshot.

psql -U postgres -d beer_db -c "SELECT relname, heap_blks_read, heap_blks_hit, round(100.0*heap_blks_hit/nullif(heap_blks_hit+heap_blks_read,0),1) AS hit_pct FROM pg_statio_user_tables WHERE relname IN ('small_tbl','large_tbl') ORDER BY relname;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, heap_blks_read, heap_blks_hit, round(100.0*heap_blks_hit/nullif(heap_blks_hit+heap_blks_read,0),1) AS hit_pct FROM pg_statio_user_tables WHERE relname IN ('small_tbl','large_tbl') ORDER BY relname;" SET relname | heap_blks_read | heap_blks_hit | hit_pct -----------+----------------+---------------+--------- large_tbl | 3131 | 214513 | 98.6 small_tbl | 23 | 5117 | 99.6 (2 rows)

⬆️ Increase shared_buffers

Bump shared_buffers to 128MB and restart to apply it — shared_buffers is postmaster-context, like logging_collector.

as-postgres psql -U postgres -c "ALTER SYSTEM SET shared_buffers = '128MB';"

as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w

student@lab:~$ as-postgres psql -U postgres -c "ALTER SYSTEM SET shared_buffers = '128MB';" ALTER SYSTEM student@lab:~$ as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w waiting for server to shut down.... done server stopped waiting for server to start....2026-06-26 16:03:17.961 UTC [169] LOG: starting PostgreSQL 18.4 on i686-buildroot-linux-gnu, compiled by i686-buildroot-linux-gnu-gcc.br_real (Buildroot 2024.02.3) 12.3.0, 32-bit 2026-06-26 16:03:17.961 UTC [169] LOG: listening on IPv4 address "0.0.0.0", port 5432 2026-06-26 16:03:17.962 UTC [169] LOG: listening on IPv6 address "::", port 5432 2026-06-26 16:03:17.968 UTC [169] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2026-06-26 16:03:18.027 UTC [172] LOG: database system was shut down at 2026-06-26 16:03:17 UTC 2026-06-26 16:03:18.060 UTC [169] LOG: database system is ready to accept connections done server started

🔁 Re-Scan on the Bigger Cache

Confirm the new shared_buffers value, then scan both tables again from the now-empty cache.

psql -U postgres -d beer_db -c "SHOW shared_buffers;"

psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.small_tbl; SELECT count(*) FROM monitoring.large_tbl;"

student@lab:~$ psql -U postgres -d beer_db -c "SHOW shared_buffers;" SET shared_buffers ---------------- 128MB (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.small_tbl; SELECT count(*) FROM monitoring.large_tbl;" SET count ------- 5000 (1 row) count -------- 200000 (1 row)

🤔 Check the Cache Again — and Get Surprised

Query pg_buffercache one more time. The bigger cache does not make the one-off scan cache more of itself.

psql -U postgres -d beer_db -c "SELECT v.relname, coalesce(bc.buffers,0) AS buffers_cached, pg_size_pretty(coalesce(bc.buffers,0) * 8192) AS cached_size FROM (VALUES ('small_tbl'),('large_tbl')) AS v(relname) LEFT JOIN (SELECT c.relname, count(*) AS buffers FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) GROUP BY c.relname) bc ON bc.relname = v.relname ORDER BY v.relname;"

Lab 2.4.6 complete. You looked directly inside shared_buffers and found a real mechanism, not a guess:\n\n\n pg_buffercache installed : ✅ live snapshot of cache occupancy\n Snapshot vs cumulative ratio : ✅ told apart correctly\n Ring-buffer strategy : ✅ observed capping a large scan\n shared_buffers increased : ✅ confirmed it did not change the cap\n

Enable JavaScript to run the live terminal and track your progress.