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 16
- function
pg_buffercache_summary()
added (commit 2589434a)
- function
- PostgreSQL 10
- modified to run with fewer locks (commit 6e654546)
- PostgreSQL 9.5
pinning_backends
column added to show information about buffer pins (commit f5779198)
- PostgreSQL 8.3
usagecount
column 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