The pg_buffercache module provides a means for examining what's happening in the shared buffer cache in real time.

1. create extension

lhl@localhost:~$ psql mydb
psql (9.5rc1)
Type "help" for help.

mydb=# 
mydb=# 
mydb=# create database lhl;
CREATE DATABASE
mydb=# \c lhl 
You are now connected to database "lhl" as user "lhl".
lhl=# 
lhl=# 
lhl=# create extension pg_buffercache ;
CREATE EXTENSION
lhl=# \dx
                      List of installed extensions
      Name      | Version |   Schema   |           Description           
----------------+---------+------------+---------------------------------
 pg_buffercache | 1.1     | public     | examine the shared buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


lhl=# \d+ public.pg_buffercache
                  View "public.pg_buffercache"
      Column      |   Type   | Modifiers | Storage | Description 
------------------+----------+-----------+---------+-------------
 bufferid         | integer  |           | plain   | 
 relfilenode      | oid      |           | plain   | 
 reltablespace    | oid      |           | plain   | 
 reldatabase      | oid      |           | plain   | 
 relforknumber    | smallint |           | plain   | 
 relblocknumber   | bigint   |           | plain   | 
 isdirty          | boolean  |           | plain   | 
 usagecount       | smallint |           | plain   | 
 pinning_backends | integer  |           | plain   | 
View definition:
 SELECT p.bufferid,
    p.relfilenode,
    p.reltablespace,
    p.reldatabase,
    p.relforknumber,
    p.relblocknumber,
    p.isdirty,
    p.usagecount,
    p.pinning_backends
   FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);

2. parameters

lhl=# select setting from pg_settings where name='block_size';
 setting 
---------
 32768
(1 row)

lhl=# select setting from pg_settings where name='shared_buffers';
 setting 
---------
 16384
(1 row)

lhl=# show block_size;
 block_size 
------------
 32768
(1 row)

lhl=# show shared_buffers;
 shared_buffers 
----------------
 512MB
(1 row)

lhl=# select (select setting from pg_settings where name='shared_buffers')::integer * (select setting from pg_settings where name='block_size')::integer /1024/1024 ;
 ?column? 
----------
      512
(1 row)


lhl=# select count(1) from pg_buffercache;
 count 
-------
 16384
(1 row)

3. shared buffer cache in real time

注意网上有些资料将(select setting from pg_settings where name='block_size')直接写为 8192，

在block_size 是默认的8k情况下，没问题的，

但一旦编译PostgreSQL时更改了block_size就会得到不正确的结果，所以读取pg_settings的数值是最为靠谱，更通用的方式

lhl=# SELECT                                                  

c.relname,

pg_size_pretty(count(*) * (select setting from pg_settings where name='block_size')::integer ) as buffered,

round(100.0 * count(*) /

(SELECT setting FROM pg_settings

WHERE name='shared_buffers')::integer,1)

AS buffers_percent,

round(100.0 * count(*) * (select setting from pg_settings where name='block_size')::integer /

pg_relation_size(c.oid),1)

AS percent_of_relation

FROM pg_class c

INNER JOIN pg_buffercache b

ON b.relfilenode = c.relfilenode

INNER JOIN pg_database d

ON (b.reldatabase = d.oid AND d.datname = current_database())

GROUP BY c.oid,c.relname

ORDER BY 3 DESC

LIMIT 10;

             relname              | buffered | buffers_percent | percent_of_relation 
----------------------------------+----------+-----------------+---------------------
 pg_depend_reference_index        | 288 kB   |             0.1 |                64.3
 pg_opclass_oid_index             | 64 kB    |             0.0 |               100.0
 pg_index_indrelid_index          | 64 kB    |             0.0 |               100.0
 pg_transform_type_lang_index     | 32 kB    |             0.0 |               100.0
 pg_namespace_oid_index           | 64 kB    |             0.0 |               100.0
 pg_depend                        | 256 kB   |             0.0 |                57.1
 pg_statistic_relid_att_inh_index | 64 kB    |             0.0 |               100.0
 pg_cast_source_target_index      | 64 kB    |             0.0 |               100.0
 pg_depend_depender_index         | 160 kB   |             0.0 |                38.5
 pg_operator_oid_index            | 64 kB    |             0.0 |               100.0
(10 rows)

其中， buffered总和 <= shared_buffers

参考链接

1. http://www.postgresql.org/docs/9.5/static/pgbuffercache.html