Re: [GENERAL] pg_buffercache view

In response to "Sorin N. Ciolofan" <ciolofan(at)ics(dot)forth(dot)gr>:
>
> Dear all,
>
> About the pg_buffercache view:
> I couldn't find the description for this view in the manual at
> http://www.postgresql.org/docs/8.2/interactive/catalogs.html
> However I found the readme file provided in the /contrib./pg_buffercache of
> the source code for version 8.2.3

Since pg_buffercache is contributed software, it's not documented in the
official PostgreSQL docs.

> Here it's written the following description:
>
> Column | references | Description
>
> ----------------+----------------------+------------------------------------
> bufferid | | Id, 1..shared_buffers.
> relfilenode | pg_class.relfilenode | Refilenode of the relation.
> reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
> reldatabase | pg_database.oid | Database for the relation.
> relblocknumber | | Offset of the page in the
> relation.
> isdirty | | Is the page dirty?
>
> I've 2 questions:
> 1)
> I was not able to find the field "oid" from pg_database view. Could you
> please tell me what is the actual name of the column for which reldatabase
> is reffering to?

At the end of the README is an example query that I think answers your
question:
SELECT c.relname, count(*) AS buffers
FROM pg_class c, pg_buffercache b
WHERE b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

> 2)
> In readme file is also written:
> "Unused buffers are shown with all fields null except buffered".
> A "used" buffer means that is used 100% or could it be filled only
> partially?

Yes. The buffer is either "used" or "not used", but pg_buffercache doesn't
know what percentage of it is used. >0% is used. 0% is not used.

> Is there any way to know at a certain moment with precision how much shared
> memory expressed in Mb is used?

The precision is +/- 1 buffer. I expect that trying to get more precision out
of the system will result in considerable performance degradation as the
data is collected and/or tracked.

--
Bill Moran
http://www.potentialtech.com