pg_buffercache

examine the shared buffer cache

pg_buffercache : examine the shared buffer cache

Overview

ID	Extension	Package	Version	Category	License	Language
6930	pg_buffercache	pg_buffercache	`1.5`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
Need By	pgcozy
See Also	pgfincore pgcozy pg_prewarm pg_cooldown toastinfo pagevis pageinspect pg_freespacemap pg_visibility pgstattuple

Packages

PG18	PG17	PG16	PG15	PG14
1.5	1.5	1.5	1.5	1.5

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

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.