pg_buffercache

The pg_buffercache module provides a way to examine the shared buffer cache in real time.

The module provides a C function pg_buffercache_pages that returns a set of records, plus a view pg_buffercache that wraps the function for convenient use.

By default, usage is restricted to superusers and members of the pg_monitor role. Access can be granted to others using GRANT.

1. pg_buffercache View

The columns displayed by the view are defined as shown in Table C.15.

Table C.15. pg_buffercache Columns

Column Type/Description
bufferid integer — ID, in the range 1..shared_buffers
relfilenode oid (references pg_class.relfilenode) — relation file node number
reltablespace oid (references pg_tablespace.oid) — tablespace OID of the relation
reldatabase oid (references pg_database.oid) — database OID of the relation
relforknumber smallint — fork number within the relation
relblocknumber bigint — page number within the relation
isdirty boolean — whether the page is dirty
usagecount smallint — Clock-sweep access count
pinning_backends integer — number of backends pinning this buffer

There is one row for each buffer in the shared cache. Unused buffers have all columns NULL except for bufferid. Shared system catalogs are shown with a database OID of zero.

Because the buffer cache is shared by all databases, there will typically be pages from relations that do not belong to the current database. This means that for some rows there may not be matching join rows in pg_class, or there may even be incorrect joins. If you attempt to join with pg_class, it is a good idea to restrict the join to rows where reldatabase equals the current database OID or zero.

Since the buffer manager lock is not used to copy the buffer state data that the view will display, accessing the pg_buffercache view has less impact on normal buffer activity, but it does not provide a consistent result set across all buffers. However, we ensure that the information for each individual buffer is accurate.

2. Sample Output

test=## SELECT n.nspname, c.relname, count(*) AS buffers

test-## FROM pg_buffercache b JOIN pg_class c

test-## ON b.relfilenode = pg_relation_filenode(c.oid) AND

test-## b.reldatabase IN (0, (SELECT oid FROM pg_database

test(## WHERE datname = current_database()))

test-## JOIN pg_namespace n ON n.oid = c.relnamespace

test-## GROUP BY n.nspname, c.relname

test-## ORDER BY 3 DESC

test-## LIMIT 10;

nspname | relname | buffers

------------+--------------------------------+---------

test | bloomidx | 11778

test | tbloom | 530

pg_catalog | pg_proc | 198

pg_catalog | pg_depend | 136

pg_catalog | pg_depend_reference_index | 88

pg_catalog | pg_attribute | 75

pg_toast | pg_toast_2618 | 73

pg_catalog | pg_depend_depender_index | 71

pg_catalog | pg_proc_proname_args_nsp_index | 51

pg_catalog | pg_collation | 49

(10 rows)