pg_buffercache

查看缓冲区缓存的内容：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_indexbuffers | 12-[ RECORD 2 ]------------------------------relname | pg_dependbuffers | 10-[ RECORD 3 ]------------------------------relname | pg_rewritebuffers | 6-[ RECORD 4 ]------------------------------relname | pg_extensionbuffers | 5-[ RECORD 5 ]------------------------------relname | pg_init_privsbuffers | 5-[ RECORD 6 ]------------------------------relname | pg_statisticbuffers | 5-[ RECORD 7 ]------------------------------relname | pg_amopbuffers | 5-[ RECORD 8 ]------------------------------relname | pg_operator_oprname_l_r_n_indexbuffers | 5-[ RECORD 9 ]------------------------------relname | pg_depend_depender_indexbuffers | 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)又不脏了。有兴趣可以自己做实验玩。