postgres extension pg_buffercache - ghdrako/doc_snipets GitHub Wiki

create extension pg_buffercache ;
\d pg_buffercache

• bufferid, the block ID in the server buffer cache
• relfilenode, which is the folder name where data is located for relation
• reltablespace, Oid of the tablespace relation uses
• reldatabase, Oid of database where location is located
• relforknumber, fork number within the relation
• relblocknumber, age number within the relation
• isdirty, true if the page is dirty
• usagecount, page LRU (least-recently used) count
• pinning_backends, information about buffer pins

postgres=# SELECT CASE WHEN c.reldatabase IS NULL THEN ''
WHEN c.reldatabase = 0 THEN ''
ELSE d.datname
END AS database,
count(*) AS cached_blocks
FROM pg_buffercache AS c
LEFT JOIN pg_database AS d
ON c.reldatabase = d.oid
GROUP BY d.datname, c.reldatabase
ORDER BY d.datname, c.reldatabase;database | cached_blocks 
----------+---------------
 postgres |           275
          |            17
          |         16092   -----> 128MB of shared_buffers with 8kB of block size, there are 16,384 buffers
(3 rows)

query provides the number of buffers used by each relation of the current database.

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER 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()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;relname             | buffers 
---------------------------------+---------
 pg_attribute                    |      34
 pg_proc                         |      25
 pg_class                        |      17
 pg_operator                     |      14
 pg_depend_reference_index       |      12
 pg_depend                       |      11
 pg_proc_oid_index               |       9
 pg_proc_proname_args_nsp_index  |       9
 pg_type                         |       8
 pg_attribute_relid_attnum_index |       8