Unlike many traditional database products, PostgreSQL does not assume or even prefer that the majority of the memory on the system be allocated for its use. Most reads and writes from the database are done using standard operating system calls that allows the operating system's cache to work in its usual fashion. Only few exception, for example WAL in some configuration.
In PostgreSQL, there are two layers, PostgreSQL shared buffers and OS Page cache. Lots of articles talked about how they work and how to tune them, here I'd rather to just share with you how to get information from both layers. Tune the sizes of them according to your system.
To get information, there are two extensions need to be installed.
Using pg_buffercache, you can look inside the current contents of the PostgreSQL shared_buffers database cache.
It's one of optional contrib modules available that ships with PostgreSQL.
A introduction documentation to pg_buffercache is at
Installation is simple:
#yum install postgresql92-contrib
then, log into postgres
postgres=# CREATE EXTENSION pg_buffercache;
A set of functions to handle low-level management of relations using mincore to explore cache memory in OS.
More info is in the project README file, with examples, very handy. Project link:
(this installation needs postgresql-devel module):
#yum install postgresql92-devel
Download the latest version: http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz
As root user:
export PATH=/usr/local/pgsql91/bin:$PATH //Set the path to point pg_config.
tar -xvf pgfincore-v1.1.1.tar.gz
Now connect to PG and run below command
postgres=# CREATE EXTENSION pgfincore;
Get buffering status by just one SQL
Other than using external languages like perl or python to put information together, I made it by myself using SQL only, it's a long SQL, but doesn't cost much time to run. It tells you how much data buffered in database share_buffer, how much data buffered in OS cache, how much data the relation has and the percentage of data buffered in shared_buffer and OS cache.
select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered,
round(100.0 * count(*) /
round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
( select round( sum(pages_mem) * 4 /1024,0 )
from pgfincore(c.relname::text) )
as os_cache_MB ,
round(100 * (
from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1)
pg_size_pretty(pg_table_size(c.oid)) as rel_size
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()
and c.relnamespace=(select oid from pg_namespace where nspname='public'))
group by c.oid,c.relname
order by 3 desc limit 30;
t_acl | 2326MB| 75.7 | 100.0 | 29 | 1.2 | 2326 MB
t_dirs | 209MB| 6.8 | 5.0 | 2159 | 52.2 | 4134 MB
t_inodes_pkey| 70MB| 2.3 | 4.5 | 1219 | 78.3 | 1557 MB
t_dirs_pkey| 58MB| 1.9 | 1.7 | 1084 | 32.3 | 3354 MB
i_t_acl_rs_id| 52MB| 1.7 | 3.8 | 1045 | 76.7 | 1363 MB
i_dirs_ipnfsid| 52MB| 1.7 | 2.4 | 1373 | 63.1 | 2176 MB
pg_buffered stands for how much data buffered in PostgreSQL buffer cache
pgbuffer_percent stands for pg_buffered/total_buffer_size *100
percent_of_relation stands for pg_buffered/total_relation_size * 100
os_cache_mb stands for how much a relation cached in OS
Note: I changed the ouput column a bit to fit the screen, for example, percent_of_relation -- > per_of_relation
I guess other two columns not need to mention.
As a DBA, once you get to know how your production data cached, you should know where to start to tune.
Here are only thumb of rules:
Don't give all the RAM to the OS, the PostgreSQL shared buffer cache can do better than the OS is the way it keeps a usage count of buffers. OS typically use some form of LRU algorithm which will give any buffer one or two chances before that data is evicted. Most important, you lose track of io statistics.
Also, someone says that "Don't make the shared buffer cache too large, OS cache is being used anyway for reads and writes, it's extremely likely that there's going to be some wasted overlap there", but I'd like to say, from my experience, not used data evicted out of memory for long run, there is not much data overlapped in both buffer area.
In any case, it's still worth to have some OS cache for postgreSQL, for better i/o purpose.
Everything mentioned above has been tested for postgresql91-9.1.9 , 9.2, and 9.3 on Redhat 6 or SL6. Other linux kinds of platform should work same way.
If you are only interested in the content buffered shared buffer, see SQLs to check contents of pg shared buffer
Drop me an e-mail if you see problem.