pg_buffercache

examine the shared buffer cache

https://www.postgresql.org/docs/current/pgbuffercache.html

Overview

Package	Version	Category	License	Language
`pg_buffercache`	`1.5`	STAT	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
6930	`pg_buffercache`	No	Yes	No	Yes	No	No	-

Related	`pgfincore` `pgcozy` `pg_prewarm` `pg_cooldown` `toastinfo` `pagevis` `pageinspect` `pg_freespacemap` `pg_visibility` `pgstattuple`
Depended By	`pgcozy`

Version

PG18	PG17	PG16	PG15	PG14
1.5	1.5	1.5	1.5	1.5

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION pg_buffercache;

Usage

pg_buffercache: inspect the shared buffer cache

pg_buffercache provides views and functions to examine what is stored in the PostgreSQL shared buffer cache in real time.

Views

pg_buffercache – detailed per-buffer information:

-- Top 10 relations by buffer usage
SELECT n.nspname, c.relname, count(*) AS buffers
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()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname
ORDER BY 3 DESC
LIMIT 10;

Columns: bufferid, relfilenode, reltablespace, reldatabase, relforknumber, relblocknumber, isdirty, usagecount, pinning_backends.

Summary Functions

-- Quick buffer cache summary (cheaper than the view)
SELECT * FROM pg_buffercache_summary();
--  buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg

-- Buffer distribution by usage count
SELECT * FROM pg_buffercache_usage_counts();
--  usage_count | buffers | dirty | pinned

Eviction Functions (Superuser, Developer Testing Only)

-- Evict a single buffer by ID
SELECT * FROM pg_buffercache_evict(42);

-- Evict all buffers for a relation
SELECT * FROM pg_buffercache_evict_relation('my_table'::regclass);

-- Evict all unpinned buffers
SELECT * FROM pg_buffercache_evict_all();

NUMA View

-- NUMA node mapping for shared buffers
SELECT * FROM pg_buffercache_numa;
-- Returns: bufferid, os_page_num, numa_node

Access

Restricted to superusers and roles with pg_monitor privileges.