查看缓冲区缓存的内容：
create extension pg_buffercache;
select c.relname, count(1) as buffers from pg_class c 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.relname order by 2 desc;
-[ RECORD 1 ]------------------------------
relname | pg_depend_reference_index
buffers | 12
-[ RECORD 2 ]------------------------------
relname | pg_depend
buffers | 10
-[ RECORD 3 ]------------------------------
relname | pg_rewrite
buffers | 6
-[ RECORD 4 ]------------------------------
relname | pg_extension
buffers | 5
-[ RECORD 5 ]------------------------------
relname | pg_init_privs
buffers | 5
-[ RECORD 6 ]------------------------------
relname | pg_statistic
buffers | 5
-[ RECORD 7 ]------------------------------
relname | pg_amop
buffers | 5
-[ RECORD 8 ]------------------------------
relname | pg_operator_oprname_l_r_n_index
buffers | 5
-[ RECORD 9 ]------------------------------
relname | pg_depend_depender_index
buffers | 5
缓存的都是数据字典视图。
排除掉此类视图 ：
select c.relname, count(1) as buffers from pg_class c join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where c.relname not like 'pg%' group by c.relname order by 2 desc;
relname | buffers
---------+---------
(0 rows)
创建自己的表，然后插入记录：
create table test(id numeric,name text);
insert into test values(1,'dxmy');
test=# select * from test;
id | name
----+------
1 | dxmy
(1 row)
然后再查询：
test=# select c.relname,b.isdirty, count(1) as buffers from pg_class c join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where c.relname not like 'pg%' group by c.relname,b.isdirty order by 2 desc;
relname | isdirty | buffers
---------+---------+---------
test | t | 1
(1 row)
发现我们新建的表及插入的数据缓存了：
其中，isdirty是f，意思就是不脏，来修改一下：
update test set id=2;
test=# update test set id=2;
UPDATE 1
再查一次：
test=# select c.relname,b.isdirty, count(1) as buffers from pg_class c join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where c.relname not like 'pg%' group by c.relname,b.isdirty order by 2 desc;
relname | isdirty | buffers
---------+---------+---------
test | t | 1
(1 row)
isdirty变为了t，说明是脏数据了。
来个检查点：
test=# checkpoint;
CHECKPOINT
再查一次：
test=# select c.relname,b.isdirty, count(1) as buffers from pg_class c join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where c.relname not like 'pg%' group by c.relname,b.isdirty order by 2 desc;
relname | isdirty | buffers
---------+---------+---------
test | f | 1
(1 row)
又不脏了。
有兴趣可以自己做实验玩。
