Extensions
pg_buffercache

pg_buffercache

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
See Also
pg_repack
pgfincore
pgcozy
pg_prewarm
pgmeminfo
pg_squeeze
old_snapshot
system_stats

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.

Last updated on
sslinfopg_walinspect