pg_buffercache

What pg_buffercache does

pg_buffercache exposes a view — also called pg_buffercache — with one row per buffer slot in shared_buffers. Each row tells you the relation filenode, database OID, block number, dirty flag, usage count, and pinning count for that buffer. Unused slots appear as rows with null values.

This gives you a complete snapshot of what PostgreSQL is keeping in memory at any given moment. You can join the view to pg_class to translate filenode numbers into table and index names, then aggregate to answer practical questions: which tables dominate the cache, what percentage of a table's pages are cached, how many dirty buffers are waiting to be written, and whether your shared_buffers setting is well-sized for your workload.

Starting in PostgreSQL 16, the extension also provides two functions — pg_buffercache_summary() and pg_buffercache_usage_counts() — that return aggregate statistics without scanning every buffer slot. These are significantly cheaper than querying the full view and are suitable for continuous monitoring.

When to use pg_buffercache

pg_buffercache is a diagnostic instrument, not something you query in production dashboards on every request. You reach for it when you have a specific question about how memory is being spent — and you would like a specific answer, not a guess.

• Sizing shared_buffers — check whether your buffer cache is fully utilized or has significant unused space
• Identifying cache hogs — find which tables or indexes consume the most buffer cache, especially after bulk loads or schema changes
• Diagnosing eviction pressure — examine usage count distribution to determine whether hot pages are being evicted too quickly
• Tracking dirty buffers — see how many modified pages are waiting for the background writer or checkpointer to flush
• Planning prewarm strategies — inspect what is cached before a restart so you know what to load back with pg_prewarm
• Validating cache behavior after tuning — confirm that changes to shared_buffers, effective_cache_size, or checkpoint settings have the expected effect

Installation and setup

pg_buffercache is a contrib module that ships with PostgreSQL — no external packages to install. It does not require shared_preload_libraries, so no server restart is needed. A single statement and the pantry is open.

-- pg_buffercache is a contrib module — no shared_preload_libraries needed
CREATE EXTENSION pg_buffercache;

-- Verify it's working
SELECT count(*) FROM pg_buffercache;

The extension requires the pg_monitor role (or superuser) to read the view. On managed platforms, this is typically granted by default to the admin user.

View columns

The pg_buffercache view exposes one row per buffer slot. These are the columns you will work with most.

-- Key columns in the pg_buffercache view
bufferid          -- buffer slot number (1..shared_buffers)
relfilenode       -- filenode of the cached relation
reldatabase       -- OID of the database (0 for shared catalogs)
relblocknumber    -- page number within the relation
isdirty           -- true if the page has been modified but not yet written
usagecount        -- clock-sweep access count (0-5, higher = more recently used)
pinning_backends  -- number of backends currently pinning this buffer

The usagecount column reflects PostgreSQL's clock-sweep eviction algorithm. Each access increments the count (capped at 5). The clock sweep decrements counts as it scans for pages to evict, removing pages that reach 0. Higher counts mean the page has been accessed recently and frequently. A page at 5 is well-established. A page at 0 is living on borrowed time.

Practical queries

Buffer cache usage per table

Shows how much of the buffer cache each table occupies and what percentage of those pages are dirty. There is almost always one table consuming more than its fair share. This query identifies the culprit.

-- Buffer cache hit ratio per table
SELECT
  c.relname,
  count(*) AS buffers,
  round(
    100.0 * count(*) /
    (SELECT setting::bigint FROM pg_settings WHERE name = 'shared_buffers'),
    2
  ) AS pct_of_cache,
  round(
    100.0 * sum(CASE WHEN b.isdirty THEN 1 ELSE 0 END) / count(*),
    2
  ) AS pct_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 20;

Most cached relations

Ranks tables and indexes by how many pages they hold in the buffer cache, with human-readable sizes for context.

-- Top 20 most cached relations (tables and indexes)
SELECT
  c.relname,
  c.relkind,
  pg_size_pretty(pg_relation_size(c.oid)) AS relation_size,
  count(*) AS cached_pages,
  pg_size_pretty(count(*) * 8192::bigint) AS cached_size
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.oid, c.relname, c.relkind
ORDER BY cached_pages DESC
LIMIT 20;

Dirty buffer distribution

Identifies which relations have the most modified-but-not-yet-written pages. A high concentration of dirty buffers in one relation typically means heavy write activity — or that the checkpointer is not keeping pace. Either way, worth knowing before it becomes a problem rather than after.

-- Dirty buffer distribution by relation
SELECT
  c.relname,
  count(*) FILTER (WHERE b.isdirty) AS dirty_buffers,
  count(*) AS total_buffers,
  round(
    100.0 * count(*) FILTER (WHERE b.isdirty) / count(*),
    2
  ) AS dirty_pct
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
HAVING count(*) FILTER (WHERE b.isdirty) > 0
ORDER BY dirty_buffers DESC
LIMIT 20;

Summary functions (PostgreSQL 16+)

These functions return aggregate statistics without scanning every buffer slot — a welcome addition for anyone who needs to check in on the cache regularly without holding a lock on every buffer header while doing so.

-- PostgreSQL 16+: fast aggregate stats without scanning every buffer
SELECT * FROM pg_buffercache_summary();

-- Returns: buffers_used, buffers_unused, buffers_dirty,
--          buffers_pinned, usagecount_avg

-- Usage count distribution (PostgreSQL 16+)
SELECT * FROM pg_buffercache_usage_counts();

-- Returns one row per usage count value (0-5):
--   usage_count, buffers, dirty, pinned

Cloud availability

How Gold Lapel relates

There is a distinction here that I think matters. pg_buffercache shows you the physical layer — what PostgreSQL is holding in shared memory right now, regardless of who put it there. Autovacuum, background jobs, ad-hoc queries, your application — they all share the same buffer pool, and pg_buffercache makes no distinction between them.

Gold Lapel operates at the logical layer. As a proxy between your application and PostgreSQL, it tracks what your application is actually requesting — the query patterns and access frequencies that directly affect your users. The buffer cache sees everything. Gold Lapel sees only what matters to your workload.

These perspectives are genuinely complementary. Use pg_buffercache to verify that your critical tables are staying resident and that shared_buffers is appropriately sized for the workload. Use Gold Lapel to ensure the queries reaching those tables are well-optimized in the first place. When you find cache misses correlating with slow queries at the proxy, the diagnosis is usually clear: either the working set has outgrown available memory, or an index is missing. Both are eminently fixable — once you know to look.