Looking inside: buffer cache, visibility map, free space and WAL

conceptThe concept

The visibility map matters twice: index-only scans skip the heap for all-visible pages, and VACUUM skips all-visible pages (and aggressive VACUUM skips all-frozen ones). The free space map is how INSERT and non-HOT UPDATE find a page with room. The buffer cache usage count (0 to 5) is the clock-sweep score: buffers with high counts survive eviction.

whyWhy it matters

"Is the hot table in memory?", "Why are index-only scans still fetching heap pages?", "Where did 40 GB of WAL per hour come from?" Each of these is a guess until you can query the structure directly. These extensions answer them in seconds and are safe to use on production for targeted relations.

Use caseReal-world scenario

An index-only scan reports Heap Fetches: 180000. pg_visibility_map_summary shows only 20% of pages all-visible because autovacuum rarely runs on that insert-heavy table; lowering autovacuum_vacuum_insert_scale_factor fixes it. Later, WAL volume doubles after a deploy; pg_get_wal_stats shows full-page images from a new index on a random UUID column.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION pg_buffercache;
SELECT c.relname, count(*) AS buffers,
       pg_size_pretty(count(*) * 8192) AS cached,
       round(avg(b.usagecount), 2) AS avg_usage
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND c.relnamespace = 'public'::regnamespace
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 5;
SELECT buffers_used, buffers_unused, buffers_dirty, round(usagecount_avg::numeric, 2) AS usagecount_avg
FROM pg_buffercache_summary();
ROLLBACK;

BEGIN;
CREATE EXTENSION pg_buffercache;
SELECT usage_count, buffers, dirty FROM pg_buffercache_usage_counts() ORDER BY usage_count;
ROLLBACK;

BEGIN;
CREATE EXTENSION pg_visibility;
SELECT 'before' AS at, * FROM pg_visibility_map_summary('orders');
UPDATE orders SET total = total WHERE id <= 300;
SELECT 'after' AS at, * FROM pg_visibility_map_summary('orders');
SELECT count(*) AS corrupt_tids FROM pg_check_visible('orders');
ROLLBACK;

BEGIN;
CREATE EXTENSION pg_freespacemap;
SELECT count(*) AS pages, sum(avail) AS free_bytes, max(avail) AS max_on_one_page
FROM pg_freespace('orders');
ROLLBACK;

BEGIN;
CREATE EXTENSION pg_walinspect;
CREATE TEMP TABLE wal_window AS
  SELECT pg_logical_emit_message(false, 'course', 'start', true) AS from_lsn, NULL::pg_lsn AS to_lsn;
UPDATE orders SET status = status WHERE id <= 3;
UPDATE wal_window SET to_lsn = pg_logical_emit_message(false, 'course', 'end', true);
SELECT resource_manager, record_type, record_length, fpi_length
FROM wal_window, pg_get_wal_records_info(from_lsn, to_lsn)
WHERE resource_manager <> 'Heap2'
ORDER BY start_lsn;
SELECT "resource_manager/record_type" AS rmgr, count, round(record_size_percentage::numeric, 1) AS size_pct
FROM wal_window, pg_get_wal_stats(from_lsn, to_lsn)
WHERE count > 0
ORDER BY count DESC, rmgr;
ROLLBACK;

-- needs pg_monitor (buffercache, visibility) or pg_read_server_files (walinspect)
GRANT pg_monitor TO ops_readonly;

-- cache share per table, biggest first
SELECT c.relname, pg_size_pretty(count(*) * 8192) AS cached,
       round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers'), 1) AS pct_of_cache
FROM pg_buffercache b JOIN pg_class c ON c.relfilenode = b.relfilenode
GROUP BY 1 ORDER BY count(*) DESC LIMIT 20;

-- WAL between two points in time: note the LSNs from pg_current_wal_lsn() before and after a job
SELECT * FROM pg_get_wal_stats('3F/A0000000', '3F/C0000000', per_record => true)
ORDER BY combined_size DESC LIMIT 10;

internalsUnder the hood

The visibility map and free space map are separate forks of each relation (_vm and _fsm files). The VM uses 2 bits per heap page; the FSM stores free space in 1/256 of a page (32-byte steps) in a small tree so a backend can find a page with enough room without scanning. pg_walinspect reads only WAL that is already flushed, which is why a range ending in an uncommitted transaction's records is rejected. Reading the pg_buffercache view takes each buffer header lock briefly; on servers with hundreds of GB of shared_buffers, prefer the summary functions.

Avoid / preferDo this, not that

-- every minute, from monitoring
SELECT * FROM pg_buffercache;

SELECT * FROM pg_buffercache_summary();
-- full per-relation breakdown only when investigating

SELECT pg_buffercache_evict_relation('orders');  -- "to test cold cache" on production

-- test cold-cache behaviour on a staging copy

PitfallsCommon mistakes

• Joining pg_buffercache to pg_class on oid instead of relfilenode.
• Expecting pg_walinspect to show records from a transaction that has not committed or flushed yet.
• Reading low all-visible counts as a problem on a table that is updated constantly; it matters most for tables you expect index-only scans on.
• Forgetting that these functions are per database: buffers of other databases have a different reldatabase.

LimitsWhen not to use it

Do not use these as routine monitoring for every table; cumulative statistics views (pg_statio_*, pg_stat_io, pg_stat_wal) give cache hit ratios and WAL volume far more cheaply. Reach for the extensions when a specific question needs page-level detail.

SecuritySecurity notes

pg_buffercache and pg_visibility reveal which tables are in use and how; pg_walinspect exposes decoded changes, including data in some record descriptions. Grant pg_monitor or pg_read_server_files only to operations roles, never to application users.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION pg_buffercache;
SELECT count(*) AS buffers,
       round(100.0 * count(*) / (SELECT count(*) FROM pg_buffercache), 2) AS pct_of_cache
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
WHERE c.oid = 'orders'::regclass
   OR c.oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 'orders'::regclass);
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Database physical storage
• TOAST