pg_buffercache
A contrib module providing 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 added in PostgreSQL 8.1.
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
- PostgreSQL 10
- modified to run with fewer locks (commit 6e654546)
- PostgreSQL 9.5
pinning_backendscolumn added to show information about buffer pins (commit f5779198)
- PostgreSQL 8.3
usagecountcolumn added to show usage count statistics (commit b0194ab1)
- PostgreSQL 8.1
- added (commit de004e44)
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;
References
- PostgreSQL documentation: pg_buffercache