PG提供了一个扩展pg_buffercache来查看缓存区的内容。
create database test;
CREATE DATABASE create extension pg_buffercache ;
CREATE EXTENSION
创建个测试数据库test,并且添加扩展。
psql -d test test=# select distinct reldatabase from pg_buffercache ;
reldatabase
------------- 16394
13322
0
(4 rows)
在缓存区中找到两个数据库的内容，带0的记录表示缓存区未使用。
test=# \! oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
13322 postgres pg_default
13321 template0 pg_default
1 template1 pg_default
16394 test pg_default
通过SQL更直观的来看一下：
select
c.relname,
count(*) 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; relname | buffers
-----------------------------------+---------
pg_operator | 14
pg_depend_reference_index | 12
pg_depend | 10
pg_rewrite | 6
pg_description | 6
pg_amop | 5
这些内容，都是数据字典视图。
test=# create table lsang(id serial,name varchar(20));
CREATE TABLE test=# insert into lsang(name) values('Michael');
INSERT 0 1 test=# select * from lsang;
id | name
----+---------
1 | Michael
(1 row) select
c.relname,
count(*) 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
--------------+---------
lsang | 1
lsang_id_seq | 1
(2 rows)
创建表并插入数据，我们通过pg_buffercache能够查询到buffers。
我们来看看数据缓存区是否为脏的。
select
c.relname,
b.isdirty
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%' ;
relname | isdirty
--------------+---------
lsang_id_seq | f
lsang | f
注意，isdirty标记的为f:
test=# update lsang set name = 'Michael.Sang';
UPDATE 1
再次查询结果:
relname | isdirty
--------------+---------
lsang_id_seq | f
lsang | t
(2 rows)
结果告诉我们，缓存区是脏的，我们可以强制设置个检查点:
test=# checkpoint ;
CHECKPOINT
重复上面查询:
relname | isdirty
--------------+---------
lsang_id_seq | f
lsang | f
(2 rows)
这样缓存区就不再是脏的了。