pg_buffercache

A contrib module providing real-time statistical information about the shared buffer cache

pg_buffercache is a contrib module which provides real-time statistical information about the shared buffer cache.

pg_buffercache was first introduced in PostgreSQL 8.1 (release notes; commit de004e44).

Extension objects

pg_buffercache provides a function pg_buffercache_pages(), and a convenience view pg_buffercache returning information about the state of the server's shared buffer cache.

Both view and extension will return a record for each shared buffer. Note that records may be returned for buffers in use by another database.

Change history

Examples

Obtain sample output for a particular table:

postgres=# SELECT * FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('foo') LIMIT 1\x\g\x

-[ RECORD 1 ]----+------
bufferid         | 1962
relfilenode      | 19246
reltablespace    | 1663
reldatabase      | 11134
relforknumber    | 0
relblocknumber   | 0
isdirty          | f
usagecount       | 5
pinning_backends | 0

Query to extract records for tables in the current database only:

    SELECT n.nspname, c.relname, bc.*
      FROM pg_buffercache bc
INNER JOIN pg_class c ON c.oid=pg_filenode_relation(bc.reltablespace, bc.relfilenode)
INNER JOIN pg_namespace n ON c.relnamespace = n.oid
     WHERE bc.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
  ORDER BY 1,2;

Categories

Contrib module, Data consistency and forensics, Extension, PostgreSQL internals